CREATE TABLE parking_tariffs_relation (
    parking_id VARCHAR(50) NOT NULL,
    tariff_id VARCHAR(50) NOT NULL,
    source VARCHAR(50) NOT NULL,
    updated_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL,
    CONSTRAINT parking_parkings_tariffs_pk PRIMARY KEY (parking_id, tariff_id)
);

CREATE INDEX parking_tariffs_relation_source_idx ON parking_tariffs_relation (source);

INSERT INTO parking_tariffs_relation (
    SELECT
        p.id as parking_id,
        ptd.tariff_id,
        ptd.source,
        ptd.updated_at,
        ptd.created_at
    FROM parkings p
    INNER JOIN (
        SELECT
            DISTINCT (tariff_id) tariff_id,
            source,
            updated_at,
            created_at
        FROM parkings_tariffs pt
    ) ptd
    ON p.tariff_id = ptd.tariff_id
) ON CONFLICT DO NOTHING;

INSERT INTO parking_tariffs_relation (
    SELECT
        pm.id as parking_id,
        ptd.tariff_id,
        ptd.source,
        ptd.updated_at,
        ptd.created_at
    FROM parking_machines pm
    INNER JOIN (
        SELECT
            DISTINCT (tariff_id) tariff_id,
                     source,
                     updated_at,
                     created_at
        FROM parkings_tariffs pt
    ) ptd
    ON pm.tariff_id = ptd.tariff_id
) ON CONFLICT DO NOTHING;

DROP VIEW v_parkings_filtered;
DROP VIEW v_parking_machines_filtered;

ALTER TABLE parkings_secondary NO INHERIT parkings;
ALTER TABLE parkings DROP COLUMN tariff_id;
ALTER TABLE parkings_secondary DROP COLUMN tariff_id;
ALTER TABLE parkings_secondary INHERIT parkings;
ALTER TABLE parking_machines DROP COLUMN tariff_id;

CREATE VIEW v_parkings_filtered AS
WITH parkings_loc AS (
    SELECT
        p.id AS id,
        pl.sanitized_locations AS location
    FROM
        ONLY parkings p
    INNER JOIN parkings_location pl
        ON p.source = pl.source AND p.source_id = pl.source_id
    WHERE
        p.source IN (
            SELECT
                source
            FROM
                parking_sources ps
            WHERE
                ps.api_v3_allowed
        )
)
SELECT
    *
FROM
    ONLY parkings p
UNION ALL
SELECT
    *
FROM
    ONLY parkings_secondary ps
WHERE
    ps.id NOT IN (
        SELECT
            DISTINCT ps.id
        FROM
            parkings_loc plo
        INNER JOIN parkings_secondary ps
            ON ST_Intersects(plo.location, ps.sanitized_location)
    );

create view v_parking_machines_filtered as
with parking_machines_primary as (
    select * from parking_machines where source != 'osm'
), parking_machines_secondary as (
    select * from parking_machines where source = 'osm'
)
select * from parking_machines_primary p1
union all
select * from parking_machines_secondary p2
where p2.id not in (
    select distinct p2.id
    from parking_machines_primary p1
    inner join parking_machines_secondary p2 ON ST_Distance(p1.location::geography, p2.location::geography) < 15
);
