-- Drop trigger first to avoid execution during following operations
DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions;

ALTER TABLE waterway.fairway_dimensions
    ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
        CHECK (NOT isempty(validity));

-- Assume existing entries have been valid since last accepted import
WITH imps AS (
    SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area
        FROM import.imports
        WHERE kind = 'fd' AND state = 'accepted'
)
UPDATE waterway.fairway_dimensions fd SET validity = tstzrange(
    COALESCE(
        (SELECT max(changed)
            FROM imps
            WHERE fd.id IN(
                SELECT id
                FROM jsonb_to_recordset(fd_area)
                    AS fd_area (id bigint, lat numeric, lon numeric))),
        current_timestamp),
    NULL);

CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique
    AFTER INSERT OR UPDATE OF area, validity, staging_done
    ON waterway.fairway_dimensions
    FOR EACH ROW EXECUTE FUNCTION prevent_st_equals(
        'area', 'validity WITH &&', 'staging_done');

CREATE INDEX fairway_dimensions_validity
    ON waterway.fairway_dimensions USING GiST (validity);