CREATE TABLE IF NOT EXISTS parkings_average_occupancy (
    parking_id varchar(50) NOT NULL,
	source varchar(255) NOT NULL,
    source_id varchar(255) NOT NULL,
	day_of_week int4 NOT NULL,
	"hour" int4 NOT NULL,
	average_occupancy double precision NOT NULL,
	record_count integer NOT NULL,
	last_updated timestamptz NOT 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_average_occupancy_pkey PRIMARY KEY (source, source_id, day_of_week, "hour")
);

CREATE INDEX IF NOT EXISTS parkings_average_occupancy_last_updated ON parkings_average_occupancy USING btree (last_updated desc);
CREATE INDEX IF NOT EXISTS parkings_average_occupancy_parking_id ON parkings_average_occupancy (parking_id);

CREATE OR REPLACE PROCEDURE calculate_average_occupancy()
LANGUAGE plpgsql
AS $$
	BEGIN
		WITH average_occupancy_agg AS (
        	SELECT
                parking_id,
                "source",
                "source_id",
                extract(dow FROM "date_modified" AT TIME ZONE 'Europe/Prague') AS "day_of_week",
                extract(hour FROM "date_modified" AT TIME ZONE 'Europe/Prague') AS "hour",
                avg("occupied_spot_number") AS "average_occupancy",
                count(source_id) as record_count,
                MAX(date_modified) as last_updated,
                NULL::bigint as create_batch_id,
                CURRENT_TIMESTAMP as created_at,
                NULL::bigint as created_by,
                NULL::bigint as update_batch_id,
                CURRENT_TIMESTAMP as updated_at,
                NULL::bigint as updated_by
            FROM parkings_measurements_part
            WHERE total_spot_number IS NOT null AND date_modified > coalesce((SELECT MAX(last_updated) FROM parkings_average_occupancy), to_timestamp(0))
            GROUP BY 1, 2, 3, 4, 5
            ORDER BY 1, 2, 3, 4
        )
        INSERT INTO parkings_average_occupancy (parking_id,"source",source_id,day_of_week,"hour",average_occupancy,record_count,last_updated,create_batch_id,created_at,created_by,update_batch_id,updated_at,updated_by)
        SELECT parking_id,"source",source_id,day_of_week,"hour",average_occupancy,record_count,last_updated,create_batch_id,created_at,created_by,update_batch_id,updated_at,updated_by
        FROM average_occupancy_agg
        ON CONFLICT (source, source_id, day_of_week, "hour") DO UPDATE
        SET
            average_occupancy = ((EXCLUDED.average_occupancy * EXCLUDED.record_count) + (parkings_average_occupancy.average_occupancy * parkings_average_occupancy.record_count)) / (EXCLUDED.record_count + parkings_average_occupancy.record_count),
            record_count = EXCLUDED.record_count + parkings_average_occupancy.record_count,
            last_updated = EXCLUDED.last_updated,
            updated_at = NOW();
	END
$$
;

CREATE OR REPLACE VIEW v_parkings_latest_measurements AS
    SELECT DISTINCT ON (source, source_id) *
    FROM parkings_measurements_part m
    WHERE date_modified >= NOW() - INTERVAL '1h'
    ORDER BY m.source ASC, m.source_id ASC, m.date_modified DESC;
