create materialized view if not exists v_tsk_average_occupancy as
with average_occupancy_agg as (
	select
		"source_id",
		extract(dow from to_timestamp("date_modified" / 1000) at time zone 'Europe/Prague')::text as "day_of_week",
		extract(hour from to_timestamp("date_modified" / 1000) at time zone 'Europe/Prague') as "hour",
		round(avg("occupied_spot_number"))::int as "average_occupancy"
	from
		parkings_measurements_part
	where "source" in ('tsk', 'TSK') and to_timestamp("date_modified" / 1000) > now() - interval '1 month'
	group by 1, 2, 3
	order by 1, 2, 3
),
average_occupancy_hour_json_agg as (
	select
		"source_id",
		"day_of_week",
		json_object_agg(lpad("hour"::text, 2, '0'), "average_occupancy" order by 1) as "hour_agg"
	from average_occupancy_agg
	group by 1, 2
)
select
	"source_id",
	json_object_agg(lpad("day_of_week", 2, '0'), "hour_agg") as "average_occupancy"
from average_occupancy_hour_json_agg
group by 1;

create unique index v_tsk_average_occupancy_idx on v_tsk_average_occupancy using btree (source_id);

create or replace view v_tsk_districts as
select
	distinct "source_id",
	coalesce(districts."district_name_slug", districts."district_name") as "district"
from
	parkings
left join
    common.citydistricts as districts on ST_Intersects(districts."geom", "location")
where
    "source" in ('tsk', 'TSK')
    and ST_GeometryType("location") = 'ST_Point';
