-- 045_wayleave_pending_view.sql
-- One definition of "what is awaiting approval".
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 5)
--
-- 043 gave vw_wayleave_list a pending_count reading rows WHERE state = 'pending'. That
-- is wrong, and wrong in the direction that hides work: pending is DERIVED for three of
-- the four sources. Only a direct UPRN stores a pending row — a polygon that grew, a
-- stocklist that gained premises and a title that now resolves to more UPRNs are all
-- pending by virtue of the live resolution differing from the approved set, with no row
-- written anywhere. The badge would have read 0 while a thousand premises waited.
--
-- The fix is a view that does the diff, so the editor header, the Approvals tab and
-- anything added later share one definition instead of each carrying a copy.
--
-- pending_count IS DROPPED FROM vw_wayleave_list RATHER THAN FIXED. Computing it there
-- would mean running ST_Within over basedata.abp for every polygon of every wayleave on
-- each load of the list page. Scoped to one wayleave the predicate pushes down into the
-- source views and the spatial index does the work; unscoped it is a full cross join.
-- A pending badge on the list was my addition in 043, not a requirement — the editor
-- header is where it belongs and where it is affordable.

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

DO $$
BEGIN
    IF to_regclass('wayleave.vw_uprns_from_boundary') IS NULL THEN
        RAISE EXCEPTION '045: source views missing - apply 043 and 044 first';
    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 '045: prior_state missing - apply 044 first';
    END IF;
END $$;

-- ── 2. The pending diff ─────────────────────────────────────────────────────
-- One row per source change: the polygon, stocklist, title or direct UPRN that has
-- something outstanding. Filter it by wayleave_id — every CTE below is referenced once
-- so PostgreSQL inlines them, and the predicate reaches the spatial join.
--
-- GROSS AND NET ARE BOTH REPORTED, and this is the part most likely to be got wrong.
-- Coverage is the union of approved claims, so a stocklist bringing 1,000 premises where
-- 300 are already covered by an approved polygon changes coverage by 700. A panel
-- showing 1,000 would not match the premise count after approving, and the module would
-- look broken. adding_gross is what the source produces; adding_net is what would
-- actually become covered.
--
-- Removals have the same asymmetry in reverse: a premise leaving one source stays
-- covered if another approved source still claims it, so removing_net excludes those.
--
-- The two EXISTS tests hit wayleave_coverage directly rather than through a CTE. A CTE
-- referenced twice is materialised by default, which would scan the whole table on every
-- call; as written each test uses the (wayleave_id, state) index.

CREATE OR REPLACE VIEW wayleave.vw_wayleave_pending AS
 WITH all_src AS (
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_boundary
         UNION ALL
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_direct
         UNION ALL
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_stocklists
         UNION ALL
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_titles
        ), marked AS (
         SELECT a.wayleave_id, a.uprn, a.src, a.source_ref, a.map_state,
                EXISTS (SELECT 1 FROM wayleave.wayleave_coverage c
                         WHERE c.wayleave_id = a.wayleave_id
                           AND c.uprn        = a.uprn
                           AND c.state       = 'approved') AS covered_any,
                -- Any OTHER approved claim. The row being removed is itself approved, so
                -- it has to be excluded or nothing would ever count as a net removal.
                EXISTS (SELECT 1 FROM wayleave.wayleave_coverage c
                         WHERE c.wayleave_id = a.wayleave_id
                           AND c.uprn        = a.uprn
                           AND c.state       = 'approved'
                           AND NOT (c.source = a.src AND c.source_ref = a.source_ref)) AS covered_other
           FROM all_src a
          WHERE a.map_state IN ('pending', 'removing')
        )
 SELECT m.wayleave_id,
    m.src,
    m.source_ref,
    count(*) FILTER (WHERE m.map_state = 'pending')                          AS adding_gross,
    count(*) FILTER (WHERE m.map_state = 'pending'  AND NOT m.covered_any)   AS adding_net,
    count(*) FILTER (WHERE m.map_state = 'removing')                         AS removing_gross,
    count(*) FILTER (WHERE m.map_state = 'removing' AND NOT m.covered_other) AS removing_net
   FROM marked m
  GROUP BY m.wayleave_id, m.src, m.source_ref;

COMMENT ON VIEW wayleave.vw_wayleave_pending IS
    'One row per outstanding source change. Pending is derived, not stored: only a direct UPRN writes a pending row, so counting wayleave_coverage would miss every polygon, stocklist and title change. Always filter by wayleave_id - unfiltered this is a spatial join across every polygon in the database.';

-- ── 3. List view without the misleading count ───────────────────────────────
-- DROP and CREATE, not CREATE OR REPLACE: replace cannot remove a column.

DROP VIEW wayleave.vw_wayleave_list;

CREATE VIEW wayleave.vw_wayleave_list AS
 WITH prem AS (
         SELECT wayleave_coverage.wayleave_id,
                count(DISTINCT wayleave_coverage.uprn) AS premise_count
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.state = 'approved'
          GROUP BY wayleave_coverage.wayleave_id
        ), poly AS (
         SELECT wayleave_polygons.wayleave_id, count(*) AS polygon_count
           FROM wayleave.wayleave_polygons
          WHERE wayleave_polygons.is_deleted = false
          GROUP BY wayleave_polygons.wayleave_id
        )
 SELECT w.wayleave_id,
    w.parent_wayleave_id,
    pw.wayleave_name AS parent_wayleave_name,
    w.wayleave_name,
    w.wayleave_status_id,
    st.wayleave_status_desc,
    st.wayleave_status_style,
    w.wayleave_manager,
    pm.username AS wayleave_manager_name,
    w.created_datetime,
    to_char(w.created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS created_date,
    w.created_user,
    uc.username AS created_username,
    to_char(w.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    w.modified_user,
    um.username AS modified_username,
    w.company_id,
    c.company_name,
    w.account_id,
    ac.account_name,
    w.stocklist_id,
    w.cover_image_url,
    COALESCE(prem.premise_count, 0::bigint) AS premise_count,
    COALESCE(poly.polygon_count, 0::bigint) AS polygon_count
   FROM wayleave.wayleaves w
     LEFT JOIN wayleave.wayleaves pw ON w.parent_wayleave_id = pw.wayleave_id
     LEFT JOIN wayleave.wayleave_status st ON st.wayleave_status_id = w.wayleave_status_id
     LEFT JOIN users.users pm ON pm.id = w.wayleave_manager
     LEFT JOIN users.users uc ON uc.id = w.created_user
     LEFT JOIN users.users um ON um.id = w.modified_user
     LEFT JOIN users.companies c ON c.company_id = w.company_id
     LEFT JOIN accounts.accounts ac ON ac.account_id = w.account_id
     LEFT JOIN prem ON prem.wayleave_id = w.wayleave_id
     LEFT JOIN poly ON poly.wayleave_id = w.wayleave_id
  WHERE w.is_deleted = false;

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

DO $$
DECLARE
    v_cols text;
BEGIN
    IF to_regclass('wayleave.vw_wayleave_pending') IS NULL THEN
        RAISE EXCEPTION '045: vw_wayleave_pending not created';
    END IF;

    SELECT string_agg(v, ', ') INTO v_cols
      FROM unnest(ARRAY['wayleave_id', 'src', 'source_ref', 'adding_gross', 'adding_net',
                        'removing_gross', 'removing_net']) AS v
     WHERE NOT EXISTS (SELECT 1 FROM information_schema.columns
                        WHERE table_schema = 'wayleave' AND table_name = 'vw_wayleave_pending'
                          AND column_name = v);
    IF v_cols IS NOT NULL THEN
        RAISE EXCEPTION '045: vw_wayleave_pending missing column(s): %', v_cols;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'wayleave' AND table_name = 'vw_wayleave_list'
                  AND column_name = 'pending_count') THEN
        RAISE EXCEPTION '045: pending_count still on vw_wayleave_list';
    END IF;

    -- Selectable, not merely creatable: a view can be created against columns that
    -- resolve at parse time and still fail on first read.
    PERFORM 1 FROM wayleave.vw_wayleave_pending LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_list    LIMIT 1;
END $$;
