-- 043_wayleave_coverage_views.sql
-- Wayleave coverage approvals, migration 2 of 2
-- (docs/2026-08-13-wayleave-coverage-approvals.md, Phases 2 and 3)
--
-- Switches the read path onto wayleave_coverage and drops the two tables it replaces.
-- Requires 042.
--
-- NO BACKFILL. Dave is clearing the polygons on dev and starting fresh (2026-08-13),
-- so wayleave_polygon_uprns and wayleave_uprns are dropped rather than converted. The
-- plan doc's Phase 2 was written assuming a backfill; without one it collapses into
-- this migration, because a view cannot outlive the table it reads: dropping those two
-- tables while the views still referenced them would need CASCADE, which would take
-- the views silently.
--
-- PENDING IS DERIVED, NOT STORED. wayleave_coverage holds decisions — approved and
-- rejected rows — and nothing else. What a source produces right now is resolved live
-- and joined to those decisions here. A premise a source has started producing, with
-- no decision against it, appears as state 'pending' without anything having written a
-- row for it.
--
-- That is what makes drift free. A stocklist that gains premises, or a basedata.abp
-- refresh bringing new-builds inside an existing polygon, changes the live half of
-- these joins and shows up as pending on the next read. The alternative — writing
-- pending rows on resolve — would turn opening the editor into a write, and would need
-- something to delete stale pending rows when a source stops producing them.
--
-- map_state IS THE STYLING KEY. Decision state alone is not enough: an approved
-- premise whose source no longer produces it is a pending REMOVAL, and looks nothing
-- like a covered one on a map. One column, five values, so a layer has a single thing
-- to style on:
--
--   approved   decided, and the source still produces it        (covered)
--   pending    the source produces it, no decision yet
--   rejected   decided against, source still produces it
--   removing   approved, but the source no longer produces it   (pending removal)
--   retained   a removal was rejected; no source produces it    (its own layer)
--
-- PERFORMANCE NOTE, worth reading before this goes near a large client. The polygon
-- view now runs ST_Within against basedata.abp on every read, where the old one read a
-- materialised table. Consumers filter by wayleave_id afterwards, and a FULL OUTER JOIN
-- can stop Postgres pushing that predicate down into the spatial half. If polygon
-- layers become slow, the fix is to materialise the polygon resolution specifically —
-- it is the only expensive one; stocklist and title resolution are ordinary joins —
-- rather than to abandon the derived model. Measure before changing anything.

-- ── 0. Guards ───────────────────────────────────────────────────────────────

DO $$
BEGIN
    IF to_regclass('wayleave.wayleave_coverage') IS NULL THEN
        RAISE EXCEPTION 'wayleave.wayleave_coverage is missing — apply 042 first';
    END IF;
    IF to_regclass('wayleave.wayleave_polygon_uprns') IS NULL THEN
        RAISE EXCEPTION '043 has already run — the old UPRN tables are gone';
    END IF;
END $$;

-- ── 1. Report what is discarded ─────────────────────────────────────────────

DO $$
DECLARE
    v_poly bigint;
    v_dir  bigint;
BEGIN
    SELECT count(*) INTO v_poly FROM wayleave.wayleave_polygon_uprns;
    SELECT count(*) INTO v_dir  FROM wayleave.wayleave_uprns;
    RAISE NOTICE '043: discarding % polygon UPRN row(s) and % direct UPRN row(s) — not converted, by decision', v_poly, v_dir;
END $$;

-- ── 2. Drop the read path, then the tables ──────────────────────────────────

DROP VIEW wayleave.vw_uprns_from_boundary;
DROP VIEW wayleave.vw_uprns_from_direct;
DROP VIEW wayleave.vw_uprns_from_stocklists;
DROP VIEW wayleave.vw_uprns_from_titles;
DROP VIEW wayleave.vw_wayleave_list;

DROP TABLE wayleave.wayleave_polygon_uprns;
DROP TABLE wayleave.wayleave_uprns;

-- ── 3. The canonical coverage set ───────────────────────────────────────────
-- Approved rows, one per premise per claiming source. A premise covered by two sources
-- appears twice — that is deliberate, and callers wanting the premise COUNT must use
-- count(DISTINCT uprn), which vw_wayleave_list below does.
--
-- This is the relation the project-matching backlog item should read rather than
-- specifying its own set.

CREATE VIEW wayleave.vw_wayleave_coverage AS
 SELECT c.wayleave_id,
    c.uprn,
    c.source,
    c.source_ref,
    c.retained_from_source,
    c.retained_from_ref,
    c.decided_user,
    c.decided_datetime,
    a.reference AS approval_reference,
    abp.address AS address_full,
    abp.geom
   FROM wayleave.wayleave_coverage c
     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 = 'approved';

-- ── 4. The four source views ────────────────────────────────────────────────
-- Each is the same shape: what the source produces now, FULL OUTER JOINed to the
-- decisions recorded for it, so a premise appears whether it is only proposed, only
-- decided, or both.

CREATE VIEW wayleave.vw_uprns_from_boundary AS
 WITH live AS (
         SELECT p.wayleave_id, abp.uprn, p.id::text AS source_ref
           FROM wayleave.wayleave_polygons p
             JOIN basedata.abp abp ON public.st_within(abp.geom, p.geom)
          WHERE p.is_deleted = false
        ), cov AS (
         SELECT wayleave_coverage.wayleave_id, wayleave_coverage.uprn,
                wayleave_coverage.source_ref, wayleave_coverage.state,
                wayleave_coverage.decided_user, wayleave_coverage.decided_datetime,
                wayleave_coverage.proposed_datetime
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.source = 'polygon'
        )
 SELECT COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id,
    COALESCE(l.uprn, c.uprn) AS uprn,
    COALESCE(l.source_ref, c.source_ref) AS source_ref,
    'polygon'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    (l.uprn IS NOT NULL) AS in_source,
        CASE
            WHEN c.state = 'approved' AND l.uprn IS NULL THEN 'removing'
            ELSE COALESCE(c.state, 'pending'::character varying)::text
        END AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM live l
     FULL JOIN cov c ON c.wayleave_id = l.wayleave_id AND c.uprn = l.uprn AND c.source_ref = l.source_ref
     LEFT JOIN basedata.abp abp ON abp.uprn = COALESCE(l.uprn, c.uprn)
     LEFT JOIN users.users u ON u.id = c.decided_user;

-- Direct adds have no source outside coverage: the claim IS the row, written as
-- 'pending' when someone adds the UPRN. So there is no live half to join to, and
-- in_source is always true — a direct claim cannot stop being produced, it can only be
-- rejected or reversed.
CREATE VIEW wayleave.vw_uprns_from_direct AS
 SELECT c.wayleave_id,
    c.uprn,
    c.source_ref,
    'direct'::text AS src,
    c.state,
    true AS in_source,
    c.state::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_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';

-- 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
 WITH live AS (
         SELECT ws.wayleave_id, sp.uprn, ws.stocklist_id::text AS source_ref
           FROM wayleave.wayleave_stocklists ws
             JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ws.stocklist_id
          WHERE ws.is_deleted = false
        ), cov AS (
         SELECT wayleave_coverage.wayleave_id, wayleave_coverage.uprn,
                wayleave_coverage.source_ref, wayleave_coverage.state,
                wayleave_coverage.decided_user, wayleave_coverage.decided_datetime
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.source = 'stocklist'
        )
 SELECT COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id,
    COALESCE(l.uprn, c.uprn) AS uprn,
    COALESCE(l.source_ref, c.source_ref) AS source_ref,
    'stocklist'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    (l.uprn IS NOT NULL) AS in_source,
        CASE
            WHEN c.state = 'approved' AND l.uprn IS NULL THEN 'removing'
            ELSE COALESCE(c.state, 'pending'::character varying)::text
        END AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM live l
     FULL JOIN cov c ON c.wayleave_id = l.wayleave_id AND c.uprn = l.uprn AND c.source_ref = l.source_ref
     LEFT JOIN basedata.abp abp ON abp.uprn = COALESCE(l.uprn, c.uprn)
     LEFT JOIN users.users u ON u.id = c.decided_user;

CREATE VIEW wayleave.vw_uprns_from_titles AS
 WITH live AS (
         SELECT wl.wayleave_id, abp.uprn, wl.title_number::text AS source_ref
           FROM wayleave.wayleave_landregistry wl
             JOIN basedata.abp abp ON abp.title_no = wl.title_number::text
          WHERE wl.is_deleted = false AND abp.uprn IS NOT NULL
        ), cov AS (
         SELECT wayleave_coverage.wayleave_id, wayleave_coverage.uprn,
                wayleave_coverage.source_ref, wayleave_coverage.state,
                wayleave_coverage.decided_user, wayleave_coverage.decided_datetime
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.source = 'title'
        )
 SELECT COALESCE(l.wayleave_id, c.wayleave_id) AS wayleave_id,
    COALESCE(l.uprn, c.uprn) AS uprn,
    COALESCE(l.source_ref, c.source_ref) AS source_ref,
    'title'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    (l.uprn IS NOT NULL) AS in_source,
        CASE
            WHEN c.state = 'approved' AND l.uprn IS NULL THEN 'removing'
            ELSE COALESCE(c.state, 'pending'::character varying)::text
        END AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM live l
     FULL JOIN cov c ON c.wayleave_id = l.wayleave_id AND c.uprn = l.uprn AND c.source_ref = l.source_ref
     LEFT JOIN basedata.abp abp ON abp.uprn = COALESCE(l.uprn, c.uprn)
     LEFT JOIN users.users u ON u.id = c.decided_user;

-- ── 5. Retained ─────────────────────────────────────────────────────────────
-- Premises kept after a removal was rejected. Their own layer because nothing produces
-- them any more, so there is no source layer they could sit on — which is the whole
-- reason 'retained' exists as a source value rather than as a flag on the original row.

CREATE VIEW wayleave.vw_uprns_retained AS
 SELECT c.wayleave_id,
    c.uprn,
    c.retained_from_source,
    c.retained_from_ref,
    'retained'::text AS src,
    c.state,
    false AS in_source,
    'retained'::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_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 = 'retained' AND c.state = 'approved';

-- ── 6. List view ────────────────────────────────────────────────────────────
-- Rebuilt because its premise count read the two dropped tables. It now reads
-- coverage, which is both simpler and the point: one definition of what a wayleave
-- covers, used by the list, the editor header and anything downstream.

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
        ), pend AS (
         SELECT wayleave_coverage.wayleave_id,
                count(*) AS pending_count
           FROM wayleave.wayleave_coverage
          WHERE wayleave_coverage.state = 'pending'
          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(pend.pending_count, 0::bigint) AS pending_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 pend ON pend.wayleave_id = w.wayleave_id
     LEFT JOIN poly ON poly.wayleave_id = w.wayleave_id
  WHERE w.is_deleted = false;

-- ── 7. Map layers ───────────────────────────────────────────────────────────
-- The four existing layers restyle on map_state; the retained layer is new. Every
-- statement asserts its row count: 025's map-layer UPDATE matched nothing and reported
-- success, which is how two opportunity layers ended up permanently empty.

DO $$
DECLARE
    v_n   integer;
    v_id  integer;
BEGIN
    UPDATE public.map_layers
       SET vector_style_field = 'map_state'
     WHERE database_schema = 'wayleave'
       AND database_table IN ('vw_uprns_from_boundary', 'vw_uprns_from_direct',
                              'vw_uprns_from_stocklists', 'vw_uprns_from_titles');
    GET DIAGNOSTICS v_n = ROW_COUNT;
    IF v_n <> 4 THEN
        RAISE EXCEPTION '043: restyle matched % layer(s), expected 4', v_n;
    END IF;

    SELECT COALESCE(max(layer_id), 0) + 1 INTO v_id FROM public.map_layers;

    -- The relation is stored THREE times here — database_schema, database_table, and
    -- again inside the url string. All three, or the layer registers, enables and
    -- stays permanently empty.
    INSERT INTO public.map_layers (
        layer_id, layer_title, layer_name, wms_layer_name, tooltip,
        layer_type, layer_category, interact_type, interact_group,
        toggle_min_zoom, source_min_zoom, source_max_zoom,
        url, z_index, layer_interactive,
        vector_style, vector_style_field, label_field, render_mode,
        layer_is_edited, uid, api_key, attribute_form, editable_fields,
        created_at, layer_active, database_schema, database_table
    ) VALUES (
        v_id, 'UPRNs - retained', 'wayleaveUprnsRetained', '',
        'Premises kept after a removal was rejected — no source produces them now',
        'vectorPoint', 'wayleave', 'checkbox', 'wayleave',
        0, 0, 30,
        'fn/map_get_v2.php?geotable=wayleave.vw_uprns_retained&geomfield=geom',
        214, true,
        'wayleavePremiseStyles', 'map_state', 'address_full', '',
        false, 'uprn', NULL, '', '[]'::jsonb,
        now(), true, 'wayleave', 'vw_uprns_retained'
    );

    INSERT INTO public.map_layer_page_config (
        layer_id, page_context, layer_editable, layer_allow_create,
        visible, snap_to_this, focus_on_load, layer_allow_delete
    ) VALUES (v_id, 'wayleaveedit', false, false, true, false, false, false);

    RAISE NOTICE '043: four layers restyled on map_state; retained registered as layer %', v_id;
END $$;

-- ── 8. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_missing text;
    v_bad     text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_missing
      FROM unnest(ARRAY['vw_wayleave_coverage', 'vw_uprns_from_boundary',
                        'vw_uprns_from_direct', 'vw_uprns_from_stocklists',
                        'vw_uprns_from_titles', 'vw_uprns_retained',
                        'vw_wayleave_list']) AS v
     WHERE to_regclass('wayleave.' || v) IS NULL;
    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION '043: view(s) not created: %', v_missing;
    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_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 '043: map_state missing from: %', v_bad;
    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_coverage    LIMIT 1;
    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_retained       LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_list        LIMIT 1;

    RAISE NOTICE '043: read path now reads wayleave_coverage; the old UPRN tables are gone';
END $$;
