-- 042_wayleave_coverage_tables.sql
-- Wayleave coverage approvals, migration 1 of 3
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 1)
--
-- The two tables the approval model needs. Nothing is backfilled and nothing is
-- dropped here — 043 does that, so this migration is inert until it runs and can be
-- applied without taking the module down.
--
-- THE MODEL, in one paragraph. Coverage is the union of approved claims: one row per
-- premise per source that has ever claimed it, and a premise is covered if ANY of its
-- rows is approved. Pending is NOT stored — it is the live resolution of the four
-- sources diffed against these rows. Only decisions are recorded, and rejection is
-- recorded solely so a rejected change stops reappearing in the queue on every load.
--
-- That derivation is why this needs no refresh triggers. A stocklist that gains
-- premises, or a basedata.abp refresh bringing new-builds inside an existing polygon,
-- changes what the sources resolve to and therefore shows up as a pending change on
-- its own. The alternative — materialising every resolution — is what would have
-- needed five triggers to keep honest.
--
-- ONE state COLUMN, NOT TWO BOOLEANS. wayleave_polygon_uprns encodes four states
-- across is_assigned x is_approved, with no name on any of them, and that is a large
-- part of why the current approval flow is hard to reason about. A named state is
-- worth the varchar.

-- ── 0. Guards ───────────────────────────────────────────────────────────────

DO $$
BEGIN
    IF to_regclass('wayleave.wayleaves') IS NULL THEN
        RAISE EXCEPTION 'wayleave.wayleaves is missing — the realignment (031-041) must be applied first';
    END IF;
    IF to_regclass('wayleave.wayleave_coverage') IS NOT NULL THEN
        RAISE EXCEPTION '042 has already run — wayleave.wayleave_coverage exists';
    END IF;
END $$;

-- ── 1. wayleave_approvals ───────────────────────────────────────────────────
-- Created first: coverage rows point at the batch that last decided them.
--
-- One row per DECISION, not per premise. Approving a 1,000-premise stocklist is one
-- approval, and that is what makes the reference useful and the journal line readable.
-- It is also why the coverage table gets no history trigger: per-row history would
-- write a thousand rows for one click, at a grain nobody reads.

CREATE TABLE wayleave.wayleave_approvals (
    id               bigint      GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id      integer     NOT NULL,

    -- Per-wayleave counter. seq is what is allocated and constrained; reference is
    -- derived from it so the two can never disagree. WL42-7 reads as "the seventh
    -- decision on wayleave 42" — sortable, collision-free, and meaningful in a journal
    -- line in a way a random hash would not be.
    seq              integer     NOT NULL,
    reference        varchar(30) GENERATED ALWAYS AS ('WL' || wayleave_id || '-' || seq) STORED,

    action           varchar(10) NOT NULL,
    source           varchar(20) NOT NULL,
    -- '' rather than NULL for direct adds, so the natural keys below need no COALESCE
    -- and ON CONFLICT can target a plain constraint.
    source_ref       varchar(50) NOT NULL DEFAULT '',

    -- Counts AT THE TIME of the decision, so the journal line stays true even after
    -- the underlying source changes. These are NET figures — what coverage actually
    -- moved by — because overlap means a stocklist bringing 1,000 premises where 300
    -- are already covered changes coverage by 700.
    uprn_added       integer     NOT NULL DEFAULT 0,
    uprn_removed     integer     NOT NULL DEFAULT 0,

    decided_user     integer     NOT NULL,
    decided_datetime timestamp without time zone NOT NULL DEFAULT now(),

    -- Set when a later approval reverses this one. Reversal is batch-level: reversing
    -- WL42-7 undoes all of it, which is the unit people decided at.
    reversed_by      bigint,

    CONSTRAINT wayleave_approvals_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_approvals_seq_uq UNIQUE (wayleave_id, seq),
    CONSTRAINT wayleave_approvals_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_approvals_reversed_by_fk FOREIGN KEY (reversed_by)
        REFERENCES wayleave.wayleave_approvals(id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_approvals_decided_user_fk FOREIGN KEY (decided_user)
        REFERENCES users.users(id),
    CONSTRAINT wayleave_approvals_action_ck CHECK (action IN ('approve', 'reject', 'reverse')),
    CONSTRAINT wayleave_approvals_source_ck  CHECK (source IN ('polygon', 'direct', 'stocklist', 'title', 'retained'))
);

CREATE INDEX wayleave_approvals_wayleave_idx ON wayleave.wayleave_approvals (wayleave_id, seq DESC);

COMMENT ON COLUMN wayleave.wayleave_approvals.seq IS
    'Per-wayleave counter, allocated as MAX(seq)+1 inside the deciding transaction. The UNIQUE on (wayleave_id, seq) is the guard against two concurrent decisions taking the same number.';
COMMENT ON COLUMN wayleave.wayleave_approvals.uprn_added IS
    'NET premises covered by this decision, not the gross count the source produced. A count that says gross where it means net makes the premise total look broken.';

-- ── 2. wayleave_coverage ────────────────────────────────────────────────────

CREATE TABLE wayleave.wayleave_coverage (
    id                bigint      GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id       integer     NOT NULL,
    uprn              bigint      NOT NULL,

    -- Which source claims this premise. 'retained' is not a source anyone attaches:
    -- a row becomes retained when a REMOVAL is rejected, meaning the premise stays
    -- covered while nothing produces it any more. Left attributed to the polygon that
    -- used to contain it, it would show as a pending removal forever; as 'retained' it
    -- stops diffing against that polygon and reads honestly on the map.
    source            varchar(20) NOT NULL,
    -- polygon id, stocklist id or title number. '' for direct and for retained rows
    -- whose origin is kept in retained_from_ref below.
    --
    -- varchar because it holds integers for two sources and a title number for a
    -- third. Typed columns per source would mean four mostly-null columns plus a
    -- check constraint keeping them mutually exclusive.
    source_ref        varchar(50) NOT NULL DEFAULT '',

    state             varchar(10) NOT NULL DEFAULT 'pending',
    approval_id       bigint,

    proposed_user     integer,
    proposed_datetime timestamp without time zone NOT NULL DEFAULT now(),
    decided_user      integer,
    decided_datetime  timestamp without time zone,

    -- What the row was before its removal was rejected, so provenance survives the
    -- re-sourcing and the premise popup can say where it came from originally.
    retained_from_source varchar(20),
    retained_from_ref    varchar(50),

    CONSTRAINT wayleave_coverage_pk PRIMARY KEY (id),
    -- source_ref is NOT NULL precisely so this works without COALESCE: a nullable
    -- column here would let two identical direct claims coexist, because NULLs are
    -- distinct in a unique constraint.
    CONSTRAINT wayleave_coverage_uq UNIQUE (wayleave_id, uprn, source, source_ref),
    CONSTRAINT wayleave_coverage_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_coverage_approval_fk FOREIGN KEY (approval_id)
        REFERENCES wayleave.wayleave_approvals(id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_coverage_source_ck CHECK (source IN ('polygon', 'direct', 'stocklist', 'title', 'retained')),
    CONSTRAINT wayleave_coverage_state_ck  CHECK (state  IN ('pending', 'approved', 'rejected'))
);

-- (wayleave_id, state) carries the coverage read, which is the hot path: the premise
-- count, the list view and the map all ask for approved rows on one wayleave.
CREATE INDEX wayleave_coverage_wayleave_state_idx ON wayleave.wayleave_coverage (wayleave_id, state);
CREATE INDEX wayleave_coverage_uprn_idx           ON wayleave.wayleave_coverage (uprn);
CREATE INDEX wayleave_coverage_approval_idx       ON wayleave.wayleave_coverage (approval_id);

COMMENT ON TABLE wayleave.wayleave_coverage IS
    'Canonical coverage: one row per premise per claiming source. A premise is covered if ANY of its rows is approved — no priority order, because with one row plus priority every detach means recomputing which source wins. Pending is derived by diffing the live source resolution against these rows, not stored.';
COMMENT ON COLUMN wayleave.wayleave_coverage.state IS
    'pending | approved | rejected. Rejection is stored only so a rejected change stops reappearing in the pending queue on every load.';

-- ── 3. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_ref text;
BEGIN
    IF to_regclass('wayleave.wayleave_coverage')  IS NULL
       OR to_regclass('wayleave.wayleave_approvals') IS NULL THEN
        RAISE EXCEPTION '042: one or both tables were not created';
    END IF;

    -- The generated reference must actually produce WL<id>-<n>. Probed with a real
    -- insert and deleted again, because a GENERATED expression that is wrong is
    -- otherwise invisible until the first decision is made.
    --
    -- No exception handler around this. An earlier draft wrapped it in WHEN others so
    -- an empty wayleaves table would not fail the migration — which also swallowed the
    -- format failure it exists to catch. The emptiness case is handled by asking
    -- first, so a genuine mismatch still aborts.
    IF EXISTS (SELECT 1 FROM wayleave.wayleaves) THEN
        INSERT INTO wayleave.wayleave_approvals
            (wayleave_id, seq, action, source, decided_user)
        SELECT w.wayleave_id, 999999, 'approve', 'direct', w.created_user
          FROM wayleave.wayleaves w
         WHERE w.created_user IS NOT NULL
         LIMIT 1;

        SELECT reference INTO v_ref
          FROM wayleave.wayleave_approvals WHERE seq = 999999;

        IF v_ref IS NULL OR v_ref !~ '^WL\d+-999999$' THEN
            RAISE EXCEPTION '042: reference generated as %, expected WL<id>-999999',
                COALESCE(v_ref, '<null>');
        END IF;

        DELETE FROM wayleave.wayleave_approvals WHERE seq = 999999;
        RAISE NOTICE '042: reference format verified as %', v_ref;
    ELSE
        RAISE NOTICE '042: no wayleave rows, reference format not probed';
    END IF;

    RAISE NOTICE '042: coverage and approvals tables created; 043 backfills and drops the old pair';
END $$;
