-- 055_wayleave_excluded_layer.sql
-- A refused removal leaves the premise IN. The separate layer is for what is OUT.
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phase 7)
--
-- I built `retained` as a third thing: covered, but shown on its own layer instead of the
-- source's, on the reasoning that no source produces it so no source layer fits. Dave's
-- reading is the right one (2026-08-15): "It was approved and in, but we rejected the
-- removal, therefore it is still in." A premise whose removal was refused is not a special
-- category. It is approved. It shows with the approved premises, on the layer of the source
-- that brought it in, and the flag's only job is to stop the diff asking about it again.
--
-- AND THE LAYER WAS SHOWING THE WRONG SET. What is worth a layer of its own is what is no
-- longer of interest - premises that were proposed and refused, or were covered and taken
-- off - so someone can see what this wayleave used to concern itself with. Those are the
-- 'rejected' and 'removed' rows, which until now existed only as invisible tombstones
-- serving the diff and the reversal.
--
-- So the two halves swap: retained rows go back to rendering as approved on their source
-- layer, and vw_uprns_retained is replaced by vw_uprns_excluded.
--
-- The flag keeps its name. `retained` reads correctly for what it now means - retained IN
-- the wayleave - and the column is internal. It was the layer that misused the word.
--
-- WHY THE FLAG IS STILL NEEDED. Without it a refused removal is re-raised forever: the
-- polygon no longer contains the premise, so the source view computes 'removing' on every
-- single load and the change reappears in the queue after being decided. The flag says the
-- question has been answered. What changes here is only what the row then looks like -
-- 'approved' rather than a state of its own.
--
-- A rejected ADDITION still appears on its source layer too, styled 'rejected'. It is
-- inside the polygon, and "why is this in my boundary and not covered" deserves an answer
-- where the user is looking. It appears on the excluded layer as well; the two answer
-- different questions and either can be switched off.

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

DO $$
BEGIN
    IF to_regclass('wayleave.vw_uprns_retained') IS NULL THEN
        RAISE EXCEPTION '055: apply 053 first';
    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. Source views: a retained premise reads as approved ───────────────────
-- Branch 2 is "decided rows the source no longer produces". A retained row is exactly
-- that, and it is covered - so it reports 'approved', which puts it on the source layer
-- with the rest and keeps it out of the pending diff, since the diff only looks at
-- 'pending' and 'removing'. No filter is needed to exclude it any more; saying what it is
-- does the job that excluding it used to.

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.retained            THEN 'approved'
         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'
    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);

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.retained            THEN 'approved'
         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'
    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);

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.retained            THEN 'approved'
         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);

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.retained                          THEN 'approved'
            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'
    AND NOT (c.withdrawn AND c.state <> 'approved');

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.';

-- ── 3. The excluded layer ───────────────────────────────────────────────────
-- Premises this wayleave was once concerned with and no longer is: an addition that was
-- refused, or coverage that was taken off. Both were previously invisible - they existed
-- only to stop the diff re-raising a decided change and to give a reversal something to
-- restore - which meant the one question a legal document invites, "what did this used to
-- cover", had no answer on screen.
--
-- map_state carries 'rejected' or 'removed' so the two are styled apart: never in, versus
-- was in and taken off.

DROP VIEW wayleave.vw_uprns_retained;

CREATE VIEW wayleave.vw_uprns_excluded AS
 SELECT c.wayleave_id,
    c.uprn,
    c.source,
    c.source_ref,
    'excluded'::text AS src,
    c.state,
    false AS in_source,
    c.state::text AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    a.reference AS approval_reference,
    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 wayleave.wayleave_approvals a ON a.id = c.approval_id
     LEFT JOIN basedata.abp abp ON abp.uprn = c.uprn
  WHERE c.state IN ('rejected', 'removed');

COMMENT ON VIEW wayleave.vw_uprns_excluded IS
    'Premises no longer of interest to this wayleave: rejected (proposed and refused, never covered) and removed (was covered, taken off). Replaces vw_uprns_retained, which showed the opposite set - a premise whose removal was refused is still IN and belongs with the approved premises on its source layer.';

-- ── 4. Repoint the map layer ────────────────────────────────────────────────
-- The relation is stored THREE times on a map_layers row - database_schema,
-- database_table, and again inside the url string. All three, or the layer registers,
-- enables and stays permanently empty.

DO $$
DECLARE
    v_n integer;
BEGIN
    UPDATE public.map_layers
       SET layer_title     = 'UPRNs - removed or rejected',
           layer_name      = 'wayleaveUprnsExcluded',
           tooltip         = 'Premises this wayleave no longer covers: refused additions, and coverage taken off',
           database_table  = 'vw_uprns_excluded',
           url             = 'fn/map_get_v2.php?geotable=wayleave.vw_uprns_excluded&geomfield=geom'
     WHERE database_schema = 'wayleave'
       AND database_table  = 'vw_uprns_retained';
    GET DIAGNOSTICS v_n = ROW_COUNT;

    -- 025's map-layer UPDATE matched nothing and reported success, which is how two
    -- opportunity layers ended up permanently empty. Every statement asserts its count.
    IF v_n <> 1 THEN
        RAISE EXCEPTION '055: layer repoint matched % row(s), expected 1', v_n;
    END IF;
END $$;

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

DO $$
DECLARE
    v_bad text;
    v_url text;
BEGIN
    IF to_regclass('wayleave.vw_uprns_excluded') IS NULL THEN
        RAISE EXCEPTION '055: vw_uprns_excluded not created';
    END IF;
    IF to_regclass('wayleave.vw_uprns_retained') IS NOT NULL THEN
        RAISE EXCEPTION '055: vw_uprns_retained still present';
    END IF;

    SELECT url INTO v_url FROM public.map_layers
     WHERE database_schema = 'wayleave' AND database_table = 'vw_uprns_excluded';
    IF v_url IS NULL OR v_url NOT LIKE '%vw_uprns_excluded%' THEN
        RAISE EXCEPTION '055: layer url does not name the new view: %', COALESCE(v_url, '<no row>');
    END IF;

    -- Every layer view must expose map_state, or its style key resolves to nothing and
    -- getPointStyle falls through to pointStyleDefault, which has no image - so 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_excluded']) 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 '055: map_state missing from: %', v_bad;
    END IF;

    -- A retained premise must now read as approved from its source view. This is the whole
    -- behavioural change, so it is asserted rather than assumed.
    IF EXISTS (
        SELECT 1
          FROM wayleave.wayleave_coverage c
          JOIN wayleave.vw_uprns_from_boundary v
            ON v.wayleave_id = c.wayleave_id AND v.uprn = c.uprn
           AND v.source_ref = c.source_ref::text
         WHERE c.retained AND c.source = 'polygon' AND v.map_state <> 'approved'
    ) THEN
        RAISE EXCEPTION '055: a retained polygon premise does not report as approved';
    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;
    PERFORM 1 FROM wayleave.vw_uprns_excluded        LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_pending      LIMIT 1;
END $$;
