-- 044_wayleave_coverage_reversible.sql
-- Make an approval genuinely reversible.
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 5)
--
-- 042 gave coverage three states and 043 built the read path over them. Writing the
-- decision endpoint against that shape exposed two holes, both only visible on reverse,
-- which is exactly the operation the feature exists for.
--
-- HOLE 1 — APPROVING A REMOVAL HAD NOWHERE TO PUT THE ROW. A source that stops
-- producing a premise shows as a pending removal; approving it has to uncover the
-- premise. With only pending/approved/rejected the only way to uncover is DELETE, and a
-- deleted row cannot be restored, so reversing that batch brought back its additions and
-- silently dropped its removals. The reversal reported success and left coverage wrong.
--
-- 'removed' is the missing state: not covered (vw_wayleave_coverage reads 'approved'
-- only), still present, still reversible. It doubles as the tombstone that stops the
-- source re-proposing the premise on every load — but only while the source is still
-- not producing it. The cov CTEs below drop 'removed' rows, so if the polygon is redrawn
-- to include the premise again it reappears as a fresh pending addition, which is what
-- it is. The ON CONFLICT in the approve path then updates that same row back to
-- approved rather than failing on the unique constraint.
--
-- HOLE 2 — REVERSE COULD NOT KNOW WHAT TO RESTORE. It set every row in the batch to
-- 'pending', which is right for an approved addition and wrong for an approved removal:
-- that row was 'approved' before the decision, not 'pending'. Anything derived from the
-- action would need a per-row rule per action, so the row records what it was instead.
-- prior_state makes reverse one assignment and correct by construction for decisions
-- this migration has not been written to anticipate.
--
-- No data conversion: nothing has been approved on this schema yet, so prior_state is
-- NULL on the rows that exist and COALESCE handles them.

-- ── 1. Guard ────────────────────────────────────────────────────────────────

DO $$
BEGIN
    IF to_regclass('wayleave.wayleave_coverage') IS NULL THEN
        RAISE EXCEPTION '044: wayleave.wayleave_coverage missing - apply 042 first';
    END IF;
    IF to_regclass('wayleave.vw_uprns_from_boundary') IS NULL THEN
        RAISE EXCEPTION '044: wayleave.vw_uprns_from_boundary missing - apply 043 first';
    END IF;
END $$;

-- ── 2. The removed state ────────────────────────────────────────────────────

ALTER TABLE wayleave.wayleave_coverage
    DROP CONSTRAINT IF EXISTS wayleave_coverage_state_ck;

ALTER TABLE wayleave.wayleave_coverage
    ADD CONSTRAINT wayleave_coverage_state_ck
    CHECK (state IN ('pending', 'approved', 'rejected', 'removed'));

COMMENT ON COLUMN wayleave.wayleave_coverage.state IS
    'pending | approved | rejected | removed. Only approved counts as coverage. rejected and removed are both tombstones, kept so a decided change stops reappearing in the pending queue on every load - rejected for an addition that was refused, removed for a removal that was accepted. Neither is deleted, because a deleted row cannot be reversed.';

-- ── 3. What to restore on reverse ───────────────────────────────────────────

ALTER TABLE wayleave.wayleave_coverage
    ADD COLUMN IF NOT EXISTS prior_state varchar(10);

COMMENT ON COLUMN wayleave.wayleave_coverage.prior_state IS
    'The state this row held before the decision in approval_id changed it. Reverse restores it verbatim rather than deriving a state from the action, because the correct target differs per row within one batch: an approved addition goes back to pending, an approved removal back to approved.';

-- ── 4. Rebuild the source views ─────────────────────────────────────────────
-- Only the cov CTE changes: 'removed' rows drop out, so they neither render on the map
-- (no style token, so they would have been silently invisible) nor suppress a premise
-- the source has genuinely started producing again.
--
-- CREATE OR REPLACE, not DROP: the column list and types are unchanged, and dropping
-- would take the map_layers rows pointing at them with it.

CREATE OR REPLACE VIEW wayleave.vw_uprns_from_boundary AS
 WITH live AS (
         SELECT p.wayleave_id, abp.uprn, p.id::text AS source_ref
           FROM wayleave.wayleave_polygons p
             JOIN basedata.abp abp ON public.st_within(abp.geom, p.geom)
          WHERE p.is_deleted = false
        ), cov AS (
         SELECT wayleave_coverage.wayleave_id, wayleave_coverage.uprn,
                wayleave_coverage.source_ref, wayleave_coverage.state,
                wayleave_coverage.decided_user, wayleave_coverage.decided_datetime,
                wayleave_coverage.proposed_datetime
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.source = 'polygon'
            AND wayleave_coverage.state <> 'removed'
        )
 SELECT COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id,
    COALESCE(l.uprn, c.uprn) AS uprn,
    COALESCE(l.source_ref, c.source_ref) AS source_ref,
    'polygon'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    (l.uprn IS NOT NULL) AS in_source,
        CASE
            WHEN c.state = 'approved' AND l.uprn IS NULL THEN 'removing'
            ELSE COALESCE(c.state, 'pending'::character varying)::text
        END AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM live l
     FULL JOIN cov c ON c.wayleave_id = l.wayleave_id AND c.uprn = l.uprn AND c.source_ref = l.source_ref
     LEFT JOIN basedata.abp abp ON abp.uprn = COALESCE(l.uprn, c.uprn)
     LEFT JOIN users.users u ON u.id = c.decided_user;

-- A direct claim cannot reach 'removed' - the approve path only removes what its source
-- has stopped producing, and a direct claim IS its own source. Filtered anyway so all
-- five views answer the same question.
CREATE OR REPLACE VIEW wayleave.vw_uprns_from_direct AS
 SELECT c.wayleave_id,
    c.uprn,
    c.source_ref,
    'direct'::text AS src,
    c.state,
    true AS in_source,
    c.state::text AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM wayleave.wayleave_coverage c
     LEFT JOIN users.users u ON u.id = c.decided_user
     LEFT JOIN basedata.abp abp ON abp.uprn = c.uprn
  WHERE c.source = 'direct' AND c.state <> 'removed';

-- stocklists.stocklist_premises has NO is_deleted column (older legacy schema) - do
-- not add a filter on it here.
CREATE OR REPLACE VIEW wayleave.vw_uprns_from_stocklists AS
 WITH live AS (
         SELECT ws.wayleave_id, sp.uprn, ws.stocklist_id::text AS source_ref
           FROM wayleave.wayleave_stocklists ws
             JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ws.stocklist_id
          WHERE ws.is_deleted = false
        ), cov AS (
         SELECT wayleave_coverage.wayleave_id, wayleave_coverage.uprn,
                wayleave_coverage.source_ref, wayleave_coverage.state,
                wayleave_coverage.decided_user, wayleave_coverage.decided_datetime
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.source = 'stocklist'
            AND wayleave_coverage.state <> 'removed'
        )
 SELECT COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id,
    COALESCE(l.uprn, c.uprn) AS uprn,
    COALESCE(l.source_ref, c.source_ref) AS source_ref,
    'stocklist'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    (l.uprn IS NOT NULL) AS in_source,
        CASE
            WHEN c.state = 'approved' AND l.uprn IS NULL THEN 'removing'
            ELSE COALESCE(c.state, 'pending'::character varying)::text
        END AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM live l
     FULL JOIN cov c ON c.wayleave_id = l.wayleave_id AND c.uprn = l.uprn AND c.source_ref = l.source_ref
     LEFT JOIN basedata.abp abp ON abp.uprn = COALESCE(l.uprn, c.uprn)
     LEFT JOIN users.users u ON u.id = c.decided_user;

CREATE OR REPLACE VIEW wayleave.vw_uprns_from_titles AS
 WITH live AS (
         SELECT wl.wayleave_id, abp.uprn, wl.title_number::text AS source_ref
           FROM wayleave.wayleave_landregistry wl
             JOIN basedata.abp abp ON abp.title_no = wl.title_number::text
          WHERE wl.is_deleted = false AND abp.uprn IS NOT NULL
        ), cov AS (
         SELECT wayleave_coverage.wayleave_id, wayleave_coverage.uprn,
                wayleave_coverage.source_ref, wayleave_coverage.state,
                wayleave_coverage.decided_user, wayleave_coverage.decided_datetime
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.source = 'title'
            AND wayleave_coverage.state <> 'removed'
        )
 SELECT COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id,
    COALESCE(l.uprn, c.uprn) AS uprn,
    COALESCE(l.source_ref, c.source_ref) AS source_ref,
    'title'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    (l.uprn IS NOT NULL) AS in_source,
        CASE
            WHEN c.state = 'approved' AND l.uprn IS NULL THEN 'removing'
            ELSE COALESCE(c.state, 'pending'::character varying)::text
        END AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM live l
     FULL JOIN cov c ON c.wayleave_id = l.wayleave_id AND c.uprn = l.uprn AND c.source_ref = l.source_ref
     LEFT JOIN basedata.abp abp ON abp.uprn = COALESCE(l.uprn, c.uprn)
     LEFT JOIN users.users u ON u.id = c.decided_user;

-- ── 5. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_bad text;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'wayleave' AND table_name = 'wayleave_coverage'
                      AND column_name = 'prior_state') THEN
        RAISE EXCEPTION '044: prior_state not added';
    END IF;

    -- Read the constraint rather than probing it with an insert: on an empty wayleaves
    -- table an INSERT ... SELECT probe writes nothing and passes vacuously, which is the
    -- state this database is in.
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
         WHERE conrelid = 'wayleave.wayleave_coverage'::regclass
           AND conname  = 'wayleave_coverage_state_ck'
           AND pg_get_constraintdef(oid) LIKE '%removed%'
    ) THEN
        RAISE EXCEPTION '044: state check does not accept removed';
    END IF;

    -- Every source view must now hide removed rows, or the tombstone would render as an
    -- unstyled (therefore invisible) point and would suppress a premise the source has
    -- started producing again.
    SELECT string_agg(v, ', ') INTO v_bad
      FROM unnest(ARRAY['vw_uprns_from_boundary', 'vw_uprns_from_direct',
                        'vw_uprns_from_stocklists', 'vw_uprns_from_titles']) AS v
     WHERE pg_get_viewdef(('wayleave.' || v)::regclass) NOT LIKE '%removed%';
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '044: removed rows not filtered out of: %', v_bad;
    END IF;

    PERFORM 1 FROM wayleave.vw_uprns_from_boundary   LIMIT 1;
    PERFORM 1 FROM wayleave.vw_uprns_from_direct     LIMIT 1;
    PERFORM 1 FROM wayleave.vw_uprns_from_stocklists LIMIT 1;
    PERFORM 1 FROM wayleave.vw_uprns_from_titles     LIMIT 1;
END $$;
