-- date_modified timestamptz -> bigint
ALTER TABLE parkings ALTER COLUMN date_modified TYPE bigint USING extract(epoch FROM date_modified) * 1000;
ALTER TABLE parkings_measurements_actual ALTER COLUMN date_modified TYPE bigint USING extract(epoch FROM date_modified) * 1000;

-- remove new table and dependant views
DROP VIEW IF EXISTS v_park_and_ride_capacities;
DROP VIEW IF EXISTS v_park_and_ride;
DROP MATERIALIZED VIEW IF EXISTS v_tsk_average_occupancy;
DROP INDEX IF EXISTS v_tsk_average_occupancy_idx;

DROP INDEX IF EXISTS parkings_measurements_part_pk;
DROP INDEX IF EXISTS parkings_measurements_part_idx;

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

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


-- 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,
    to_timestamp((m.date_modified / 1000)::double precision) 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,
        to_timestamp((m.date_modified / 1000)::double precision) AS updated_at_measurements,
        date_trunc('hour'::text, to_timestamp((m.date_modified / 1000)::double precision)) + ((date_part('minute'::text, to_timestamp((m.date_modified / 1000)::double precision)) + 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
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);
