-- date_modified bigint -> timestamptz
ALTER TABLE parkings ALTER COLUMN date_modified TYPE timestamptz USING to_timestamp(date_modified / 1000);
ALTER TABLE parkings_measurements_actual ALTER COLUMN date_modified TYPE timestamptz USING to_timestamp(date_modified / 1000);

-- rename old table
ALTER TABLE parkings_measurements_part RENAME TO parkings_measurements_part_old;
ALTER TABLE parkings_measurements_part_min RENAME TO parkings_measurements_part_min_old;
ALTER TABLE parkings_measurements_part_2021 RENAME TO parkings_measurements_part_2021_old;
ALTER TABLE parkings_measurements_part_2022 RENAME TO parkings_measurements_part_2022_old;
ALTER TABLE parkings_measurements_part_2023 RENAME TO parkings_measurements_part_2023_old;
ALTER TABLE parkings_measurements_part_2024 RENAME TO parkings_measurements_part_2024_old;
ALTER TABLE parkings_measurements_part_2025 RENAME TO parkings_measurements_part_2025_old;
ALTER TABLE parkings_measurements_part_max RENAME TO parkings_measurements_part_max_old;

ALTER INDEX parkings_measurements_part_pk RENAME TO parkings_measurements_part_pk_old;
ALTER INDEX parkings_measurements_part_idx RENAME TO parkings_measurements_part_idx_old;

-- create new table with date_modified timestamptz
CREATE TABLE IF NOT EXISTS parkings_measurements_part (
	"source" varchar(255) NOT NULL,
	source_id varchar(255) NOT NULL,
	parking_id character varying(50),
	available_spot_number int4 NULL,
	closed_spot_number int4 NULL,
	occupied_spot_number int4 NULL,
	total_spot_number int4 NULL,
	date_modified timestamptz NULL,
	create_batch_id int8 NULL,
	created_at timestamptz NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamptz NULL,
	updated_by varchar(150) NULL,

	CONSTRAINT parkings_measurements_part_pk PRIMARY KEY (source, source_id, date_modified)
) PARTITION BY RANGE(date_modified);

CREATE TABLE IF NOT EXISTS parkings_measurements_part_min PARTITION OF parkings_measurements_part
    FOR VALUES FROM (minvalue) TO ('2020-12-31T23:00:00.000Z');

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2021 PARTITION OF parkings_measurements_part
    FOR VALUES FROM ('2020-12-31T23:00:00.000Z') TO ('2021-12-31T23:00:00.000Z');

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2022 PARTITION OF parkings_measurements_part
    FOR VALUES FROM ('2021-12-31T23:00:00.000Z') TO ('2022-12-31T23:00:00.000Z');

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2023 PARTITION OF parkings_measurements_part
    FOR VALUES FROM ('2022-12-31T23:00:00.000Z') TO ('2023-12-31T23:00:00.000Z');

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2024 PARTITION OF parkings_measurements_part
    FOR VALUES FROM ('2023-12-31T23:00:00.000Z') TO ('2024-12-31T23:00:00.000Z');

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2025 PARTITION OF parkings_measurements_part
    FOR VALUES FROM ('2024-12-31T23:00:00.000Z') TO ('2025-12-31T23:00:00.000Z');

CREATE TABLE IF NOT EXISTS parkings_measurements_part_max PARTITION OF parkings_measurements_part
    FOR VALUES FROM ('2025-12-31T23:00:00.000Z') TO (maxvalue);

CREATE INDEX IF NOT EXISTS parkings_measurements_part_idx ON parkings_measurements_part USING btree (date_modified desc);


-- LINK VIEWS

-- parkings.v_park_and_ride_capacities source
CREATE OR REPLACE VIEW v_park_and_ride_capacities AS
SELECT
    DISTINCT ON (p.source_id) p.source_id,
    p.name,
    m.total_spot_number AS total_spot_number_measurements,
    m.date_modified AS last_date
FROM parkings p
LEFT JOIN parkings_measurements_part m ON p.source_id::text = m.source_id::text
WHERE p.category::text = 'park_and_ride'::text
ORDER BY p.source_id, p.name, m.date_modified DESC;


-- parkings.v_park_and_ride source
CREATE OR REPLACE VIEW v_park_and_ride AS
SELECT
    DISTINCT ON (pr.name, pr.updated_at_15_min) pr.name,
    pr.x,
    pr.y,
    pr.updated_at_15_min,
    pr.total_spot_number_measurements,
    pr.occupied_spot_number,
    pr.occupied_spot_number::double precision / NULLIF(pr.total_spot_number_measurements, 0)::double precision AS ocuupied_proportion,
    date_part('year'::text, pr.updated_at_15_min) AS year,
    date_part('month'::text, pr.updated_at_15_min) AS month,
    date_part('day'::text, pr.updated_at_15_min) AS day,
    date_part('dow'::text, pr.updated_at_15_min) AS dow,
    date_part('hour'::text, pr.updated_at_15_min) AS hour,
    date_part('minute'::text, pr.updated_at_15_min) AS minute,
    date_part('hour'::text, pr.updated_at_15_min) + date_part('minute'::text, pr.updated_at_15_min) / 60::double precision AS hour_quarter
FROM (
    SELECT
        p.source_id,
        p.name,
        st_x(p.location) AS x,
        st_y(p.location) AS y,
        p.total_spot_number AS total_spot_number_parking,
        m.available_spot_number,
        m.occupied_spot_number,
        m.total_spot_number AS total_spot_number_measurements,
        m.date_modified AS updated_at_measurements,
        date_trunc('hour'::text, m.date_modified) + ((date_part('minute'::text, m.date_modified) + 7.5::double precision)::integer / 15)::double precision * '00:15:00'::interval AS updated_at_15_min
    FROM parkings p
    LEFT JOIN parkings_measurements_part m ON p.source_id::text = m.source_id::text
    WHERE p.category::text = 'park_and_ride'::text
    ) pr
WHERE pr.updated_at_15_min < date_trunc('month'::text, CURRENT_DATE::TIMESTAMP WITH TIME ZONE)
ORDER BY pr.name, pr.updated_at_15_min;


-- parkings.v_tsk_average_occupancy source
DROP MATERIALIZED VIEW IF EXISTS v_tsk_average_occupancy;
DROP INDEX IF EXISTS v_tsk_average_occupancy_idx;

CREATE MATERIALIZED VIEW IF NOT EXISTS v_tsk_average_occupancy AS
WITH average_occupancy_agg AS (
	SELECT
		"source_id",
		extract(dow FROM "date_modified" AT TIME ZONE 'Europe/Prague')::text AS "day_of_week",
		extract(hour FROM "date_modified" 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 "date_modified" > 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);
