-- 052_wayleave_direct_withdrawal.sql
-- An approved direct UPRN can be withdrawn, like every other source can be detached.
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 5)
--
-- Removing a direct UPRN only worked before it was approved; a stocklist or a title could
-- be detached at any point (Dave, 2026-08-14). That is an inconsistency I introduced, and
-- the reasoning behind it was wrong.
--
-- The reasoning was: withdrawing approved coverage is a decision, so it belongs on the
-- Approvals tab rather than in a side panel. True - but that is exactly what detaching a
-- stocklist does. It does not uncover anything. It makes the coverage show as a PENDING
-- REMOVAL, which then goes through approval like any other change. The side panel proposes;
-- the tab decides. Direct claims were the odd one out because they have no link table whose
-- is_deleted flag could carry the proposal.
--
-- withdrawn IS THAT FLAG. It is the direct source's is_deleted: the claim is still there,
-- the user has said they no longer want it, and the removal is outstanding until someone
-- decides. Every mechanism downstream then works unchanged, because they all key on
-- in_source:
--
--   approve  - the row is not in_source, so it becomes 'removed'. Uncovered.
--   reject   - the row is not in_source, so it becomes 'retained'. Stays covered.
--   reverse  - restores prior_state and leaves withdrawn set, so the proposed removal
--              reappears as outstanding. Which is what reversing a decision means.
--
-- A PENDING claim is still deleted outright rather than withdrawn. Nothing has been decided
-- about it, so there is no decision to reverse and nothing for an approver to look at - a
-- tombstone there would be a queue entry for a change nobody ever made.

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

DO $$
BEGIN
    IF to_regclass('wayleave.wayleave_coverage') IS NULL THEN
        RAISE EXCEPTION '052: wayleave.wayleave_coverage 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 = 'prior_state') THEN
        RAISE EXCEPTION '052: apply 044 first';
    END IF;
END $$;

-- ── 2. The flag ─────────────────────────────────────────────────────────────

ALTER TABLE wayleave.wayleave_coverage
    ADD COLUMN IF NOT EXISTS withdrawn boolean NOT NULL DEFAULT false;

COMMENT ON COLUMN wayleave.wayleave_coverage.withdrawn IS
    'Direct claims only: the user has asked for this premise to come off, and the removal is outstanding until approved. The direct source has no link table, so this is its is_deleted - detaching a stocklist sets is_deleted on wayleave_stocklists and produces exactly the same pending removal. Never set on a pending claim: those are deleted outright, because there is no decision to reverse.';

-- ── 3. The direct source view honours it ────────────────────────────────────
-- CREATE OR REPLACE, not DROP: the column list and types are unchanged, so
-- vw_wayleave_pending keeps working without being rebuilt.
--
-- in_source stops being a constant. It was `true` because a direct claim IS its own
-- source and so can never stop being produced - which is right, until the person who
-- added it says otherwise. That is the whole change; map_state follows from it, and every
-- consumer downstream already reads both.

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,
    (NOT c.withdrawn) AS in_source,
        CASE
            WHEN c.withdrawn AND c.state = 'approved' THEN 'removing'
            ELSE c.state::text
        END 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'
    -- withdrawn is only meaningful on an approved claim. Anything else is a row that
    -- should not exist, and hiding it beats showing a state with no defined meaning.
    AND NOT (c.withdrawn AND c.state <> 'approved');

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

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'wayleave' AND table_name = 'wayleave_coverage'
                      AND column_name = 'withdrawn') THEN
        RAISE EXCEPTION '052: withdrawn column not added';
    END IF;

    -- The view must actually read it, or a withdrawal would be stored and never surface
    -- as a pending removal - silent, and only visible as a premise that will not come off.
    IF pg_get_viewdef('wayleave.vw_uprns_from_direct'::regclass) NOT LIKE '%withdrawn%' THEN
        RAISE EXCEPTION '052: vw_uprns_from_direct does not reference withdrawn';
    END IF;

    -- map_state is what the map layers style on and what the pending diff filters on.
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'wayleave' AND table_name = 'vw_uprns_from_direct'
                      AND column_name = 'map_state') THEN
        RAISE EXCEPTION '052: map_state missing from vw_uprns_from_direct';
    END IF;

    PERFORM 1 FROM wayleave.vw_uprns_from_direct LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_pending  LIMIT 1;
END $$;
