partitioner.jl

### A Pluto.jl notebook ###
# v0.19.43

using Markdown
using InteractiveUtils

# ╔═╡ 906b1266-7986-4429-b90a-5ed8ef8de9e5
using DuckDB, DataFrames

# ╔═╡ fa77ad68-4820-11ef-19f5-33ac7cf17545
readdir("Norse-from-TEM")

# ╔═╡ 29936d83-61d8-4935-a9ba-d22b531585b9
begin
	con = DBInterface.connect(DuckDB.DB)
	DBInterface.execute(con, "CREATE TABLE a AS SELECT * FROM 'Norse-from-TEM/assets-rep-periods-partitions.csv'")
	DBInterface.execute(con, "SELECT * FROM a") |> DataFrame
end

# ╔═╡ ce420413-2166-46ed-8661-5fb5a9d32ab7
begin
	con2 = DBInterface.connect(DuckDB.DB)
	
	assets = ["alice", "bob", "carlos"]
	rps = 1:3

	time_horizon_per_rp = [12; 12; fill(24, length(rps) - 2)]

	manual_override = Dict{Tuple{String,Int}, Tuple{String, String}}(
		("alice", 1) => ("uniform", "3"),
		("bob", 1) => ("uniform", "4"),
		("alice", 2) => ("uniform", "3"),
		("bob", 2) => ("uniform", "4"),
		("carlos", 2) => ("uniform", "12"),
		("alice", 3) => ("explicit", "2;1;3;6;6;6"),
		("bob", 3) => ("math", "2x1+3x4+1x4+1x6"),
	)
	
	DBInterface.execute(con2,
    	"CREATE OR REPLACE TABLE
			asset_partition(
				asset STRING,
				rep_period INT,
				specification STRING,
				partition STRING
			)",
	)

	appender = DuckDB.Appender(con2, "asset_partition")
	for a in assets, rp in rps
		DuckDB.append(appender, a)
		DuckDB.append(appender, rp)
		(spec, part) = get(manual_override, (a, rp), ("uniform", 1))
			
		DuckDB.append(appender, spec)
		DuckDB.append(appender, part)
		DuckDB.end_row(appender)
	end
	# close the appender after all rows 
	DuckDB.close(appender)

	DBInterface.execute(con2, "SELECT * FROM asset_partition") |> DataFrame
end

# ╔═╡ 6d02b60d-5f5c-4932-bd9d-f86bf612fc95
begin
	# Unroller
	DBInterface.execute(con2,
		"CREATE OR REPLACE TABLE
			unr_asset_partition(
				asset STRING,
				rep_period INT,
				range_start INT,
				range_end INT
			)",
	)

	function _append_given_durations(appender, row, durations)
		s = 1
		for Δ in durations
			e = s + Δ - 1
			DuckDB.append(appender, row.asset)
			DuckDB.append(appender, row.rep_period)
			DuckDB.append(appender, row.specification)
			DuckDB.append(appender, s)
			DuckDB.append(appender, e)
			DuckDB.end_row(appender)
			s = e + 1
		end
	end

	unr_appender = DuckDB.Appender(con2, "unr_asset_partition")
	for row in DBInterface.execute(con2, "SELECT * FROM asset_partition")
		time_horizon = time_horizon_per_rp[row.rep_period]

		durations = if row.specification == "uniform"
			step = Meta.parse(row.partition)
			durations = fill(step, div(time_horizon, step))
		elseif row.specification == "explicit"
			durations = Meta.parse.(split(row.partition, ";"))
		elseif row.specification == "math"
			atoms = split(row.partition, "+")
			durations = vcat([
				begin
					r, d = Meta.parse.(split(atom, "x"))
					fill(d, r)
				end for atom in atoms
			]...)
		else
			error("Row specification '$(row.specification)' is not valid")
		end

		_append_given_durations(unr_appender, row, durations)
	end
	DuckDB.close(unr_appender)

	DBInterface.execute(con2, "SELECT * FROM unr_asset_partition WHERE rep_period = '2'") |> DataFrame
end

# ╔═╡ 9c479aac-5603-4231-aa71-ed325b4bffc4
begin
	DBInterface.execute(con2,
		"CREATE OR REPLACE TABLE
			cons_partition_highest(
				name STRING,
				rep_period INT,
				range_start INT,
				range_end INT
			)",
	)

	constraints_highest = Dict(
		"balance" => ["alice", "bob"],
	)

	cons_highest_appender = DuckDB.Appender(con2, "cons_partition_highest")
	for (cons_name, merging_assets) in constraints_highest, rp in rps
		sql_asset_set = "(" * join(("'$x'" for x in merging_assets), ", ") * ")"
		range_starts = DBInterface.execute(con2,
			"SELECT DISTINCT ON(range_start) range_start
				FROM unr_asset_partition
				WHERE
					rep_period = '$rp' AND
					asset IN $sql_asset_set
				ORDER BY range_start ASC"
		) |> DataFrame |> df -> df.range_start
		for (i, s) in enumerate(range_starts)
			e = if i == length(range_starts)
				time_horizon_per_rp[rp]
			else
				range_starts[i + 1] - 1
			end
			DuckDB.append(cons_highest_appender, cons_name)
			DuckDB.append(cons_highest_appender, rp)
			DuckDB.append(cons_highest_appender, s)
			DuckDB.append(cons_highest_appender, e)
			DuckDB.end_row(cons_highest_appender)
		end
	end
	DuckDB.close(cons_highest_appender)

	DBInterface.execute(con2, "SELECT * FROM cons_partition_highest WHERE rep_period = '1'") |> DataFrame
end

# ╔═╡ 68fb5799-1e74-46f0-b8db-2beebaef6e28
begin
	DBInterface.execute(con2,
		"CREATE OR REPLACE TABLE
			cons_partition_lowest(
				name STRING,
				rep_period INT,
				range_start INT,
				range_end INT
			)",
	)

	constraints_lowest = Dict(
		"balance" => ["alice", "bob"],
	)

	cons_lowest_appender = DuckDB.Appender(con2, "cons_partition_lowest")
	for (cons_name, merging_assets) in constraints_lowest, rp in rps
		sql_asset_set = "(" * join(("'$x'" for x in merging_assets), ", ") * ")"

		s = 1
		e = 1
		for row in DBInterface.execute(con2,
			"SELECT *
				FROM unr_asset_partition
				WHERE
					rep_period = '$rp' AND
					asset IN $sql_asset_set
				ORDER BY range_start ASC"
		)
			if row.range_start > s
				# Time to create a new row
				DuckDB.append(cons_lowest_appender, cons_name)
				DuckDB.append(cons_lowest_appender, rp)
				DuckDB.append(cons_lowest_appender, s)
				DuckDB.append(cons_lowest_appender, e)
				DuckDB.end_row(cons_lowest_appender)
				# Update s and e
				s = e + 1
			end
			e = max(row.range_end, e)
		end
		if e ≥ s
			DuckDB.append(cons_lowest_appender, cons_name)
			DuckDB.append(cons_lowest_appender, rp)
			DuckDB.append(cons_lowest_appender, s)
			DuckDB.append(cons_lowest_appender, e)
			DuckDB.end_row(cons_lowest_appender)
		end
	end
	DuckDB.close(cons_lowest_appender)

	DBInterface.execute(con2, "SELECT * FROM cons_partition_lowest WHERE rep_period = '1'") |> DataFrame
end

# ╔═╡ 63094abe-9e70-4d80-9e34-223aa140b856
DBInterface.execute(con2,
			"SELECT *
				FROM unr_asset_partition
				WHERE
					rep_period = '1' AND
					asset IN ('alice', 'bob')
				ORDER BY range_start ASC, range_end DESC"
		) |> DataFrame

# ╔═╡ 9b071ca5-db6e-49e1-bc8d-670dfc26e96a
DBInterface.execute(con2, "SELECT DISTINCT ON(range_start) asset, range_start FROM unr_asset_partition WHERE rep_period = '1' AND asset IN ('alice', 'bob') ORDER BY range_start ASC") |> DataFrame

# ╔═╡ 00000000-0000-0000-0000-000000000001
PLUTO_PROJECT_TOML_CONTENTS = """
[deps]
DataFrames = "a93c6f00-e57d-5684-b7b6-d8193f3e46c0"
DuckDB = "d2f5444f-75bc-4fdf-ac35-56f514c445e1"

[compat]
DataFrames = "~1.6.1"
DuckDB = "~1.0.0"
"""

# ╔═╡ 00000000-0000-0000-0000-000000000002
PLUTO_MANIFEST_TOML_CONTENTS = """
# This file is machine-generated - editing it directly is not advised

julia_version = "1.10.4"
manifest_format = "2.0"
project_hash = "652ca65d6cb1372c987eb21cebf7356889daad13"

[[deps.Artifacts]]
uuid = "56f22d72-fd6d-98f1-02f0-08ddc0907c33"

[[deps.Base64]]
uuid = "2a0f44e3-6c83-55bd-87e4-b1978d98bd5f"

[[deps.Compat]]
deps = ["TOML", "UUIDs"]
git-tree-sha1 = "b1c55339b7c6c350ee89f2c1604299660525b248"
uuid = "34da2185-b29b-5c13-b0c7-acf172513d20"
version = "4.15.0"
weakdeps = ["Dates", "LinearAlgebra"]

    [deps.Compat.extensions]
    CompatLinearAlgebraExt = "LinearAlgebra"

[[deps.CompilerSupportLibraries_jll]]
deps = ["Artifacts", "Libdl"]
uuid = "e66e0078-7015-5450-92f7-15fbd957f2ae"
version = "1.1.1+0"

[[deps.Crayons]]
git-tree-sha1 = "249fe38abf76d48563e2f4556bebd215aa317e15"
uuid = "a8cc5b0e-0ffa-5ad4-8c14-923d3ee1735f"
version = "4.1.1"

[[deps.DBInterface]]
git-tree-sha1 = "a444404b3f94deaa43ca2a58e18153a82695282b"
uuid = "a10d1c49-ce27-4219-8d33-6db1a4562965"
version = "2.6.1"

[[deps.DataAPI]]
git-tree-sha1 = "abe83f3a2f1b857aac70ef8b269080af17764bbe"
uuid = "9a962f9c-6df0-11e9-0e5d-c546b8b5ee8a"
version = "1.16.0"

[[deps.DataFrames]]
deps = ["Compat", "DataAPI", "DataStructures", "Future", "InlineStrings", "InvertedIndices", "IteratorInterfaceExtensions", "LinearAlgebra", "Markdown", "Missings", "PooledArrays", "PrecompileTools", "PrettyTables", "Printf", "REPL", "Random", "Reexport", "SentinelArrays", "SortingAlgorithms", "Statistics", "TableTraits", "Tables", "Unicode"]
git-tree-sha1 = "04c738083f29f86e62c8afc341f0967d8717bdb8"
uuid = "a93c6f00-e57d-5684-b7b6-d8193f3e46c0"
version = "1.6.1"

[[deps.DataStructures]]
deps = ["Compat", "InteractiveUtils", "OrderedCollections"]
git-tree-sha1 = "1d0a14036acb104d9e89698bd408f63ab58cdc82"
uuid = "864edb3b-99cc-5e75-8d2d-829cb0a9cfe8"
version = "0.18.20"

[[deps.DataValueInterfaces]]
git-tree-sha1 = "bfc1187b79289637fa0ef6d4436ebdfe6905cbd6"
uuid = "e2d170a0-9d28-54be-80f0-106bbe20a464"
version = "1.0.0"

[[deps.Dates]]
deps = ["Printf"]
uuid = "ade2ca70-3891-5945-98fb-dc099432e06a"

[[deps.DuckDB]]
deps = ["DBInterface", "Dates", "DuckDB_jll", "FixedPointDecimals", "Tables", "UUIDs", "WeakRefStrings"]
git-tree-sha1 = "dc1fd531149c0aacefe280d1b9756d3e6c6ac3cf"
uuid = "d2f5444f-75bc-4fdf-ac35-56f514c445e1"
version = "1.0.0"

[[deps.DuckDB_jll]]
deps = ["Artifacts", "JLLWrappers", "Libdl"]
git-tree-sha1 = "9858c927af2f906ac3740ab366b999c7894283f9"
uuid = "2cbbab25-fc8b-58cf-88d4-687a02676033"
version = "1.0.0+0"

[[deps.FixedPointDecimals]]
deps = ["Parsers"]
git-tree-sha1 = "cd96db4ebe96ee1063ee1deddef318c6d5844cff"
uuid = "fb4d412d-6eee-574d-9565-ede6634db7b0"
version = "0.5.2"

[[deps.Future]]
deps = ["Random"]
uuid = "9fa8497b-333b-5362-9e8d-4d0656e87820"

[[deps.InlineStrings]]
deps = ["Parsers"]
git-tree-sha1 = "86356004f30f8e737eff143d57d41bd580e437aa"
uuid = "842dd82b-1e85-43dc-bf29-5d0ee9dffc48"
version = "1.4.1"

    [deps.InlineStrings.extensions]
    ArrowTypesExt = "ArrowTypes"

    [deps.InlineStrings.weakdeps]
    ArrowTypes = "31f734f8-188a-4ce0-8406-c8a06bd891cd"

[[deps.InteractiveUtils]]
deps = ["Markdown"]
uuid = "b77e0a4c-d291-57a0-90e8-8db25a27a240"

[[deps.InvertedIndices]]
git-tree-sha1 = "0dc7b50b8d436461be01300fd8cd45aa0274b038"
uuid = "41ab1584-1d38-5bbf-9106-f11c6c58b48f"
version = "1.3.0"

[[deps.IteratorInterfaceExtensions]]
git-tree-sha1 = "a3f24677c21f5bbe9d2a714f95dcd58337fb2856"
uuid = "82899510-4779-5014-852e-03e436cf321d"
version = "1.0.0"

[[deps.JLLWrappers]]
deps = ["Artifacts", "Preferences"]
git-tree-sha1 = "7e5d6779a1e09a36db2a7b6cff50942a0a7d0fca"
uuid = "692b3bcd-3c85-4b1f-b108-f13ce0eb3210"
version = "1.5.0"

[[deps.LaTeXStrings]]
git-tree-sha1 = "50901ebc375ed41dbf8058da26f9de442febbbec"
uuid = "b964fa9f-0449-5b57-a5c2-d3ea65f4040f"
version = "1.3.1"

[[deps.Libdl]]
uuid = "8f399da3-3557-5675-b5ff-fb832c97cbdb"

[[deps.LinearAlgebra]]
deps = ["Libdl", "OpenBLAS_jll", "libblastrampoline_jll"]
uuid = "37e2e46d-f89d-539d-b4ee-838fcccc9c8e"

[[deps.Markdown]]
deps = ["Base64"]
uuid = "d6f4376e-aef5-505a-96c1-9c027394607a"

[[deps.Missings]]
deps = ["DataAPI"]
git-tree-sha1 = "ec4f7fbeab05d7747bdf98eb74d130a2a2ed298d"
uuid = "e1d29d7a-bbdc-5cf2-9ac0-f12de2c33e28"
version = "1.2.0"

[[deps.OpenBLAS_jll]]
deps = ["Artifacts", "CompilerSupportLibraries_jll", "Libdl"]
uuid = "4536629a-c528-5b80-bd46-f80d51c5b363"
version = "0.3.23+4"

[[deps.OrderedCollections]]
git-tree-sha1 = "dfdf5519f235516220579f949664f1bf44e741c5"
uuid = "bac558e1-5e72-5ebc-8fee-abe8a469f55d"
version = "1.6.3"

[[deps.Parsers]]
deps = ["Dates", "PrecompileTools", "UUIDs"]
git-tree-sha1 = "8489905bcdbcfac64d1daa51ca07c0d8f0283821"
uuid = "69de0a69-1ddd-5017-9359-2bf0b02dc9f0"
version = "2.8.1"

[[deps.PooledArrays]]
deps = ["DataAPI", "Future"]
git-tree-sha1 = "36d8b4b899628fb92c2749eb488d884a926614d3"
uuid = "2dfb63ee-cc39-5dd5-95bd-886bf059d720"
version = "1.4.3"

[[deps.PrecompileTools]]
deps = ["Preferences"]
git-tree-sha1 = "5aa36f7049a63a1528fe8f7c3f2113413ffd4e1f"
uuid = "aea7be01-6a6a-4083-8856-8a6e6704d82a"
version = "1.2.1"

[[deps.Preferences]]
deps = ["TOML"]
git-tree-sha1 = "9306f6085165d270f7e3db02af26a400d580f5c6"
uuid = "21216c6a-2e73-6563-6e65-726566657250"
version = "1.4.3"

[[deps.PrettyTables]]
deps = ["Crayons", "LaTeXStrings", "Markdown", "PrecompileTools", "Printf", "Reexport", "StringManipulation", "Tables"]
git-tree-sha1 = "66b20dd35966a748321d3b2537c4584cf40387c7"
uuid = "08abe8d2-0d0c-5749-adfa-8a2ac140af0d"
version = "2.3.2"

[[deps.Printf]]
deps = ["Unicode"]
uuid = "de0858da-6303-5e67-8744-51eddeeeb8d7"

[[deps.REPL]]
deps = ["InteractiveUtils", "Markdown", "Sockets", "Unicode"]
uuid = "3fa0cd96-eef1-5676-8a61-b3b8758bbffb"

[[deps.Random]]
deps = ["SHA"]
uuid = "9a3f8284-a2c9-5f02-9a11-845980a1fd5c"

[[deps.Reexport]]
git-tree-sha1 = "45e428421666073eab6f2da5c9d310d99bb12f9b"
uuid = "189a3867-3050-52da-a836-e630ba90ab69"
version = "1.2.2"

[[deps.SHA]]
uuid = "ea8e919c-243c-51af-8825-aaa63cd721ce"
version = "0.7.0"

[[deps.SentinelArrays]]
deps = ["Dates", "Random"]
git-tree-sha1 = "ff11acffdb082493657550959d4feb4b6149e73a"
uuid = "91c51154-3ec4-41a3-a24f-3f23e20d615c"
version = "1.4.5"

[[deps.Serialization]]
uuid = "9e88b42a-f829-5b0c-bbe9-9e923198166b"

[[deps.Sockets]]
uuid = "6462fe0b-24de-5631-8697-dd941f90decc"

[[deps.SortingAlgorithms]]
deps = ["DataStructures"]
git-tree-sha1 = "66e0a8e672a0bdfca2c3f5937efb8538b9ddc085"
uuid = "a2af1166-a08f-5f64-846c-94a0d3cef48c"
version = "1.2.1"

[[deps.SparseArrays]]
deps = ["Libdl", "LinearAlgebra", "Random", "Serialization", "SuiteSparse_jll"]
uuid = "2f01184e-e22b-5df5-ae63-d93ebab69eaf"
version = "1.10.0"

[[deps.Statistics]]
deps = ["LinearAlgebra", "SparseArrays"]
uuid = "10745b16-79ce-11e8-11f9-7d13ad32a3b2"
version = "1.10.0"

[[deps.StringManipulation]]
deps = ["PrecompileTools"]
git-tree-sha1 = "a04cabe79c5f01f4d723cc6704070ada0b9d46d5"
uuid = "892a3eda-7b42-436c-8928-eab12a02cf0e"
version = "0.3.4"

[[deps.SuiteSparse_jll]]
deps = ["Artifacts", "Libdl", "libblastrampoline_jll"]
uuid = "bea87d4a-7f5b-5778-9afe-8cc45184846c"
version = "7.2.1+1"

[[deps.TOML]]
deps = ["Dates"]
uuid = "fa267f1f-6049-4f14-aa54-33bafae1ed76"
version = "1.0.3"

[[deps.TableTraits]]
deps = ["IteratorInterfaceExtensions"]
git-tree-sha1 = "c06b2f539df1c6efa794486abfb6ed2022561a39"
uuid = "3783bdb8-4a98-5b6b-af9a-565f29a5fe9c"
version = "1.0.1"

[[deps.Tables]]
deps = ["DataAPI", "DataValueInterfaces", "IteratorInterfaceExtensions", "LinearAlgebra", "OrderedCollections", "TableTraits"]
git-tree-sha1 = "cb76cf677714c095e535e3501ac7954732aeea2d"
uuid = "bd369af6-aec1-5ad0-b16a-f7cc5008161c"
version = "1.11.1"

[[deps.UUIDs]]
deps = ["Random", "SHA"]
uuid = "cf7118a7-6976-5b1a-9a39-7adc72f591a4"

[[deps.Unicode]]
uuid = "4ec0a83e-493e-50e2-b9ac-8f72acf5a8f5"

[[deps.WeakRefStrings]]
deps = ["DataAPI", "InlineStrings", "Parsers"]
git-tree-sha1 = "b1be2855ed9ed8eac54e5caff2afcdb442d52c23"
uuid = "ea10d353-3f73-51f8-a26c-33c1cb351aa5"
version = "1.4.2"

[[deps.libblastrampoline_jll]]
deps = ["Artifacts", "Libdl"]
uuid = "8e850b90-86db-534c-a0d3-1478176c7d93"
version = "5.8.0+1"
"""

# ╔═╡ Cell order:
# ╠═906b1266-7986-4429-b90a-5ed8ef8de9e5
# ╠═fa77ad68-4820-11ef-19f5-33ac7cf17545
# ╠═29936d83-61d8-4935-a9ba-d22b531585b9
# ╠═ce420413-2166-46ed-8661-5fb5a9d32ab7
# ╠═6d02b60d-5f5c-4932-bd9d-f86bf612fc95
# ╠═9c479aac-5603-4231-aa71-ed325b4bffc4
# ╠═68fb5799-1e74-46f0-b8db-2beebaef6e28
# ╠═63094abe-9e70-4d80-9e34-223aa140b856
# ╠═9b071ca5-db6e-49e1-bc8d-670dfc26e96a
# ╟─00000000-0000-0000-0000-000000000001
# ╟─00000000-0000-0000-0000-000000000002