-- 048_wayleave_source_views_filterable.sql
-- Let WHERE wayleave_id = ? actually reach the source views.
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 5)
--
-- 043 built each source view as `live FULL JOIN cov`, exposing
-- COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id, and 045's comment claimed the
-- predicate would reach the spatial join because the CTEs are inlined. Inlining is real
-- and irrelevant: A QUAL ON A COALESCE OVER A FULL JOIN CANNOT BE PUSHED INTO EITHER
-- SIDE. Both halves are computed for every wayleave in the database and the filter is
-- applied to the result. The plan for one wayleave showed it plainly:
--
--   Seq Scan on wayleave_polygons p        (no wayleave_id condition, all rows)
--   Seq Scan on stocklist_premises sp      (rows=563525)
--   Filter: (COALESCE(p.wayleave_id, wayleave_coverage.wayleave_id) = 1)
--
-- THE BIGGER COST WAS INDIRECT. Estimating 2,277,468 rows for a nested loop that returned
-- 9 put the query cost at 3.2 million, far above jit_above_cost and both JIT optimisation
-- thresholds, so PostgreSQL spent 598ms of a 675ms query JIT-compiling 116 functions to
-- run 77ms of work. Fixing the estimate is what stops that, and the estimate is only
-- absurd because the predicate never lands.
--
-- THE FIX: UNION ALL, NOT FULL JOIN. Quals DO push into set-operation branches, so each
-- branch takes wayleave_id from a real table column rather than a COALESCE:
--
--   branch 1  what the source produces now, LEFT JOINed to any decision on it.
--             wayleave_id comes from the source table. in_source is true by construction,
--             so map_state here can never be 'removing'.
--   branch 2  decided rows the source no longer produces, anti-joined against it.
--             wayleave_id comes from wayleave_coverage. in_source is false by
--             construction, so 'approved' here means exactly 'removing'.
--
-- The two branches are disjoint by that anti-join, so no row appears twice.
--
-- vw_uprns_from_direct is NOT rebuilt: it reads wayleave_coverage directly, has no join
-- to push through, and already filters.
--
-- DROP AND CREATE, not CREATE OR REPLACE, because replace cannot change a column's type
-- and the branch expressions resolve types differently from the COALESCEs they replace.
-- map_layers rows reference these by name, not by OID, so re-creating them is safe -
-- vw_wayleave_pending is the only database-level dependency and is rebuilt below.

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

DO $$
BEGIN
    IF to_regclass('wayleave.vw_wayleave_pending') IS NULL THEN
        RAISE EXCEPTION '048: vw_wayleave_pending missing - apply 045 first';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_indexes
                    WHERE schemaname = 'stocklists' AND tablename = 'stocklist_premises'
                      AND indexname = 'stocklist_premises_stocklist_idx') THEN
        RAISE EXCEPTION '048: apply 047 first - without it the stocklist branch still scans the whole table';
    END IF;
END $$;

DROP VIEW wayleave.vw_wayleave_pending;
DROP VIEW wayleave.vw_uprns_from_boundary;
DROP VIEW wayleave.vw_uprns_from_stocklists;
DROP VIEW wayleave.vw_uprns_from_titles;

-- ── 2. Boundary ─────────────────────────────────────────────────────────────

CREATE VIEW wayleave.vw_uprns_from_boundary AS
 SELECT p.wayleave_id,
    abp.uprn,
    p.id::text AS source_ref,
    'polygon'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    true AS in_source,
    COALESCE(c.state, 'pending'::character varying)::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_polygons p
     JOIN basedata.abp abp ON public.st_within(abp.geom, p.geom)
     LEFT JOIN wayleave.wayleave_coverage c
            ON c.wayleave_id      = p.wayleave_id
           AND c.uprn             = abp.uprn
           AND c.source           = 'polygon'
           AND c.source_ref::text = p.id::text
           AND c.state           <> 'removed'
     LEFT JOIN users.users u ON u.id = c.decided_user
  WHERE p.is_deleted = false
UNION ALL
 SELECT c.wayleave_id,
    c.uprn,
    c.source_ref::text,
    'polygon'::text,
    c.state,
    false,
    CASE WHEN c.state = 'approved' THEN 'removing' ELSE c.state::text END,
    c.decided_user,
    c.decided_datetime,
    u.username,
    abp.address,
    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 = 'polygon' AND c.state <> 'removed'
    -- Scoped by wayleave_id first so the polygon index does the work; the ::text compare
    -- then picks the one polygon. Deliberately not c.source_ref::integer, which would
    -- use the primary key but throw on a single non-numeric ref and take the whole view
    -- down with it.
    AND NOT EXISTS (
        SELECT 1
          FROM wayleave.wayleave_polygons p2
          JOIN basedata.abp a2 ON public.st_within(a2.geom, p2.geom)
         WHERE p2.is_deleted   = false
           AND p2.wayleave_id  = c.wayleave_id
           AND p2.id::text     = c.source_ref::text
           AND a2.uprn         = c.uprn);

-- ── 3. Stocklists ───────────────────────────────────────────────────────────
-- stocklists.stocklist_premises has NO is_deleted column (older legacy schema) - do
-- not add a filter on it here.

CREATE VIEW wayleave.vw_uprns_from_stocklists AS
 SELECT ws.wayleave_id,
    sp.uprn,
    ws.stocklist_id::text AS source_ref,
    'stocklist'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    true AS in_source,
    COALESCE(c.state, 'pending'::character varying)::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_stocklists ws
     JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ws.stocklist_id
     LEFT JOIN wayleave.wayleave_coverage c
            ON c.wayleave_id      = ws.wayleave_id
           AND c.uprn             = sp.uprn
           AND c.source           = 'stocklist'
           AND c.source_ref::text = ws.stocklist_id::text
           AND c.state           <> 'removed'
     LEFT JOIN users.users u ON u.id = c.decided_user
     LEFT JOIN basedata.abp abp ON abp.uprn = sp.uprn
  WHERE ws.is_deleted = false
UNION ALL
 SELECT c.wayleave_id,
    c.uprn,
    c.source_ref::text,
    'stocklist'::text,
    c.state,
    false,
    CASE WHEN c.state = 'approved' THEN 'removing' ELSE c.state::text END,
    c.decided_user,
    c.decided_datetime,
    u.username,
    abp.address,
    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 = 'stocklist' AND c.state <> 'removed'
    -- (uprn, stocklist_id) serves this one, which is why it leads on uprn: here the uprn
    -- is known and the stocklist is being tested. 047's index is for the other direction.
    AND NOT EXISTS (
        SELECT 1
          FROM wayleave.wayleave_stocklists ws2
          JOIN stocklists.stocklist_premises sp2 ON sp2.stocklist_id = ws2.stocklist_id
         WHERE ws2.is_deleted        = false
           AND ws2.wayleave_id       = c.wayleave_id
           AND ws2.stocklist_id::text = c.source_ref::text
           AND sp2.uprn              = c.uprn);

-- ── 4. Titles ───────────────────────────────────────────────────────────────

CREATE VIEW wayleave.vw_uprns_from_titles AS
 SELECT wl.wayleave_id,
    abp.uprn,
    wl.title_number::text AS source_ref,
    'title'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    true AS in_source,
    COALESCE(c.state, 'pending'::character varying)::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_landregistry wl
     JOIN basedata.abp abp ON abp.title_no = wl.title_number::text
     LEFT JOIN wayleave.wayleave_coverage c
            ON c.wayleave_id      = wl.wayleave_id
           AND c.uprn             = abp.uprn
           AND c.source           = 'title'
           AND c.source_ref::text = wl.title_number::text
           AND c.state           <> 'removed'
     LEFT JOIN users.users u ON u.id = c.decided_user
  WHERE wl.is_deleted = false AND abp.uprn IS NOT NULL
UNION ALL
 SELECT c.wayleave_id,
    c.uprn,
    c.source_ref::text,
    'title'::text,
    c.state,
    false,
    CASE WHEN c.state = 'approved' THEN 'removing' ELSE c.state::text END,
    c.decided_user,
    c.decided_datetime,
    u.username,
    abp.address,
    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 = 'title' AND c.state <> 'removed'
    AND NOT EXISTS (
        SELECT 1
          FROM wayleave.wayleave_landregistry wl2
          JOIN basedata.abp a2 ON a2.title_no = wl2.title_number::text
         WHERE wl2.is_deleted         = false
           AND wl2.wayleave_id        = c.wayleave_id
           AND wl2.title_number::text = c.source_ref::text
           AND a2.uprn                = c.uprn);

-- ── 5. Pending diff, unchanged ──────────────────────────────────────────────
-- Recreated verbatim from 045; it is only dropped above because it depends on the three
-- views. Its own shape was never the problem.

CREATE 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,
                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. Filter by wayleave_id - since 048 that predicate reaches the base tables.';

-- ── 6. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_bad  text;
    v_plan text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_bad
      FROM unnest(ARRAY['vw_uprns_from_boundary', 'vw_uprns_from_stocklists',
                        'vw_uprns_from_titles', 'vw_wayleave_pending']) AS v
     WHERE to_regclass('wayleave.' || v) IS NULL;
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '048: view(s) not created: %', v_bad;
    END IF;

    -- Every layer view must still expose map_state, or its style key resolves to nothing
    -- and the points render INVISIBLY rather than wrongly.
    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',
                        'vw_uprns_retained']) AS v
     WHERE NOT EXISTS (SELECT 1 FROM information_schema.columns
                        WHERE table_schema = 'wayleave' AND table_name = v
                          AND column_name = 'map_state');
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '048: map_state missing from: %', v_bad;
    END IF;

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

-- The whole point of the migration, asserted rather than assumed. Checked against the
-- view DEFINITION, not against an EXPLAIN: whether the planner picks an index or a
-- sequential scan depends on table size and stats, so a plan assertion would fail on a
-- client database with a small stocklist_premises for a reason that is not a fault. What
-- must be true everywhere is that no FULL JOIN remains, because that is the construct the
-- predicate could not cross.
DO $$
DECLARE
    v_bad text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_bad
      FROM unnest(ARRAY['vw_uprns_from_boundary', 'vw_uprns_from_stocklists',
                        'vw_uprns_from_titles']) AS v
     WHERE pg_get_viewdef(('wayleave.' || v)::regclass) ILIKE '%FULL JOIN%';
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '048: FULL JOIN still present in: % - the wayleave_id predicate cannot cross it', v_bad;
    END IF;
END $$;
