CREATE TABLE IF NOT EXISTS parkings_measurements_part (
--	id bigserial NOT NULL,
	"source" varchar(255) NOT NULL,
	source_id varchar(255) NOT NULL,
	available_spot_number int4 NULL,
	closed_spot_number int4 NULL,
	occupied_spot_number int4 NULL,
	total_spot_number int4 NULL,
	date_modified int8 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_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 (1609455600000); -- '2021-01-01'

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2021 PARTITION OF parkings_measurements_part
    FOR VALUES FROM (1609455600000) TO (1640991600000); -- '2021-01-01'

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2022 PARTITION OF parkings_measurements_part
    FOR VALUES FROM (1640991600000) TO (1672527600000); -- '2021-01-01'

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2023 PARTITION OF parkings_measurements_part
    FOR VALUES FROM (1672527600000) TO (1704063600000); -- '2021-01-01'

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2024 PARTITION OF parkings_measurements_part
    FOR VALUES FROM (1704063600000) TO (1735686000000); -- '2021-01-01'

CREATE TABLE IF NOT EXISTS parkings_measurements_part_2025 PARTITION OF parkings_measurements_part
    FOR VALUES FROM (1735686000000) TO (1767222000000); -- '2021-01-01'

CREATE TABLE IF NOT EXISTS parkings_measurements_part_max PARTITION OF parkings_measurements_part
    FOR VALUES FROM (1767222000000) TO (maxvalue); -- '2021-01-01'

ALTER TABLE parkings_measurements_part DROP CONSTRAINT IF EXISTS parkings_measurements_part_pk;
ALTER TABLE parkings_measurements_part ADD CONSTRAINT parkings_measurements_part_pk PRIMARY KEY (source, source_id, date_modified);

CREATE TABLE IF NOT EXISTS parkings_measurements_actual (
	"source" varchar(255) NOT NULL,
	source_id varchar(255) NOT NULL,
	available_spot_number int4 NULL,
	closed_spot_number int4 NULL,
	occupied_spot_number int4 NULL,
	total_spot_number int4 NULL,
	date_modified int8 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_measurements_actual_pk PRIMARY KEY (source, source_id)
);

CREATE TABLE IF NOT EXISTS parkings_tariffs (
	tariff_id varchar(50) NOT NULL,
	"source" varchar(255) NOT NULL,
	last_updated timestamptz NOT NULL,
	payment_mode varchar(255) NOT NULL,
	payment_additional_description text NULL,
	free_of_charge bool NOT NULL,
	url_link_address text NULL,
	charge_band_name varchar(50) NOT NULL,
	accepts_payment_card bool NULL,
	accepts_cash bool NULL,
	accepts_mobile_payment bool NULL,
	charge_currency varchar(50) NOT NULL,
	charge numeric NOT NULL,
	charge_type varchar(50) NULL,
	charge_order_index int2 NOT NULL,
	charge_interval int4 NULL,
	max_iterations_of_charge int4 NULL,
	min_iterations_of_charge int4 NULL,
	start_time_of_period varchar(50) NULL,
	end_time_of_period varchar(50) NULL,
	allowed_vehicle_type varchar(255) NULL,
	allowed_fuel_type varchar(255) 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,
	accepts_litacka bool NULL,

	CONSTRAINT parkings_tariffs_pk PRIMARY KEY (tariff_id, charge_order_index)
);

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;

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;
