-- 054_wayleave_approval_membership.sql
-- A decision records which premises it touched.
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 6)
--
-- The Approvals tab could only show the premise list for the LATEST decision in a chain
-- (Dave, 2026-08-15). Approve, reverse, reject the same polygon and WL1-1 and WL1-2 open
-- empty; only WL1-3 has anything.
--
-- The cause is that the detail read wayleave_coverage.approval_id, which is a SINGLE
-- COLUMN ON THE ROW. It answers "which decision currently owns this premise", not "which
-- decisions have touched it", and every new decision overwrites it. Reverse is worse: it
-- sets approval_id back to NULL on the rows it releases, so a reverse decision's detail was
-- always empty and always would be.
--
-- A decision is immutable once made, so its membership belongs ON THE DECISION rather than
-- being inferred from rows that keep moving.
--
-- ARRAYS, NOT A JOIN TABLE. The obvious shape is one row per premise per decision, and it
-- is the wrong one here: approving a stocklist of 1,000 premises would write 1,000 rows for
-- one click, and this data is never queried relationally - it is read back whole, for one
-- decision, to fill a modal. Two arrays are one row, roughly 8KB at that size, and unnest
-- gives the modal exactly the list it wants. If "which decisions touched UPRN X" is ever
-- needed, a GIN index on these columns answers it without changing the shape.
--
-- ADDED AND REMOVED mean what they mean everywhere else on this table: premises this
-- decision left COVERED, and premises it left NOT covered. That reads consistently across
-- all three actions - approve covers and uncovers, reject refuses additions and retains
-- removals, and reverse puts rows back to approved or back to pending.

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

DO $$
BEGIN
    IF to_regclass('wayleave.wayleave_approvals') IS NULL THEN
        RAISE EXCEPTION '054: wayleave.wayleave_approvals not found';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'wayleave' AND table_name = 'wayleave_coverage'
                      AND column_name = 'retained') THEN
        RAISE EXCEPTION '054: apply 053 first';
    END IF;
END $$;

-- ── 2. Membership ───────────────────────────────────────────────────────────

ALTER TABLE wayleave.wayleave_approvals
    ADD COLUMN IF NOT EXISTS uprn_added_list   bigint[] NOT NULL DEFAULT '{}';

ALTER TABLE wayleave.wayleave_approvals
    ADD COLUMN IF NOT EXISTS uprn_removed_list bigint[] NOT NULL DEFAULT '{}';

COMMENT ON COLUMN wayleave.wayleave_approvals.uprn_added_list IS
    'The premises this decision left COVERED. Recorded on the decision because it is immutable, unlike wayleave_coverage.approval_id, which is overwritten by the next decision to touch the same rows and cleared entirely by a reversal.';

COMMENT ON COLUMN wayleave.wayleave_approvals.uprn_removed_list IS
    'The premises this decision left NOT covered. See uprn_added_list.';

-- ── 3. Backfill what is still recoverable ───────────────────────────────────
-- Only the decision that currently OWNS a coverage row can have its membership
-- reconstructed - which is the whole limitation this migration removes, so anything
-- superseded stays empty. That is honest: it was never recorded. Decisions made from here
-- on carry their own list.

DO $$
DECLARE
    v_n integer;
BEGIN
    UPDATE wayleave.wayleave_approvals a
       SET uprn_added_list = COALESCE((
               SELECT array_agg(c.uprn ORDER BY c.uprn)
                 FROM wayleave.wayleave_coverage c
                WHERE c.approval_id = a.id
                  AND c.state = 'approved' AND NOT c.retained
           ), '{}'),
           uprn_removed_list = COALESCE((
               SELECT array_agg(c.uprn ORDER BY c.uprn)
                 FROM wayleave.wayleave_coverage c
                WHERE c.approval_id = a.id
                  AND (c.state <> 'approved' OR c.retained)
           ), '{}')
     WHERE EXISTS (SELECT 1 FROM wayleave.wayleave_coverage c WHERE c.approval_id = a.id);
    GET DIAGNOSTICS v_n = ROW_COUNT;
    RAISE NOTICE '054: membership reconstructed for % decision(s)', v_n;
END $$;

-- ── 4. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_bad text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_bad
      FROM unnest(ARRAY['uprn_added_list', 'uprn_removed_list']) AS v
     WHERE NOT EXISTS (SELECT 1 FROM information_schema.columns
                        WHERE table_schema = 'wayleave' AND table_name = 'wayleave_approvals'
                          AND column_name = v);
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '054: column(s) not added: %', v_bad;
    END IF;

    -- NOT NULL with a default, so no decision ever has a null list to guard against when
    -- reading it back. unnest(NULL) yields nothing, which would look identical to a
    -- decision that genuinely touched no premises.
    IF EXISTS (SELECT 1 FROM wayleave.wayleave_approvals
                WHERE uprn_added_list IS NULL OR uprn_removed_list IS NULL) THEN
        RAISE EXCEPTION '054: null membership list present';
    END IF;
END $$;
