-- sql/wayleave_02_coverage.sql
-- Wayleave module — Phase 0 step 2 of 4.
-- Coverage tables: polygons, direct UPRNs, stocklists, polygon->UPRN tracking with approval workflow.
-- Prereq: sql/wayleave_01_core.sql has been run.
-- Run: psql -U postgres -d netplanner -f sql/wayleave_02_coverage.sql

BEGIN;

-- Direct-added UPRNs
CREATE TABLE IF NOT EXISTS wayleave.agreement_uprns (
    id             bigserial PRIMARY KEY,
    agreement_id   integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    uprn           bigint  NOT NULL,
    added_user     integer NOT NULL,
    added_datetime timestamp NOT NULL DEFAULT now(),
    is_deleted     boolean NOT NULL DEFAULT false,
    UNIQUE (agreement_id, uprn)
);
CREATE INDEX IF NOT EXISTS idx_agr_uprns_agreement ON wayleave.agreement_uprns(agreement_id) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_agr_uprns_uprn      ON wayleave.agreement_uprns(uprn)         WHERE is_deleted = false;

-- Attached stocklists
CREATE TABLE IF NOT EXISTS wayleave.agreement_stocklists (
    id             bigserial PRIMARY KEY,
    agreement_id   integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    stocklist_id   integer NOT NULL,
    added_user     integer NOT NULL,
    added_datetime timestamp NOT NULL DEFAULT now(),
    is_deleted     boolean NOT NULL DEFAULT false,
    UNIQUE (agreement_id, stocklist_id)
);
CREATE INDEX IF NOT EXISTS idx_agr_stocklists_agreement ON wayleave.agreement_stocklists(agreement_id) WHERE is_deleted = false;

-- Drawn polygons (may be many per agreement)
CREATE TABLE IF NOT EXISTS wayleave.agreement_polygons (
    id                bigserial PRIMARY KEY,
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    geom              public.geometry(Polygon, 27700) NOT NULL,
    label             varchar(250),
    added_user        integer NOT NULL,
    added_datetime    timestamp NOT NULL DEFAULT now(),
    modified_user     integer,
    modified_datetime timestamp,
    is_deleted        boolean NOT NULL DEFAULT false
);
CREATE INDEX IF NOT EXISTS idx_agr_poly_agreement ON wayleave.agreement_polygons(agreement_id) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_agr_poly_geom      ON wayleave.agreement_polygons USING GIST (geom);

-- Polygon-derived UPRN tracking with approval workflow.
-- Four states via (is_assigned, is_approved):
--   (true,  false) pending add      — newly entered polygon, awaiting approval
--   (true,  true)  active           — in polygon, confirmed
--   (false, true)  pending removal  — left polygon but was previously approved
--   (false, false) inactive         — not in polygon, never approved / rejected
CREATE TABLE IF NOT EXISTS wayleave.agreement_polygon_uprns (
    id                bigserial PRIMARY KEY,
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    uprn              bigint  NOT NULL,
    is_assigned       boolean NOT NULL DEFAULT true,
    is_approved       boolean NOT NULL DEFAULT false,
    actioned_user_id  integer NOT NULL,
    action_datetime   timestamp NOT NULL DEFAULT now(),
    approved_user_id  integer,
    approved_datetime timestamp,
    UNIQUE (agreement_id, uprn)
);
CREATE INDEX IF NOT EXISTS idx_agr_poly_uprns_agreement ON wayleave.agreement_polygon_uprns(agreement_id);
CREATE INDEX IF NOT EXISTS idx_agr_poly_uprns_active
    ON wayleave.agreement_polygon_uprns(agreement_id, uprn)
    WHERE is_assigned = true AND is_approved = true;

COMMIT;

-- Verify:
--   \d wayleave.agreement_polygon_uprns
