-- 041_wayleave_drop_releases.sql
-- Removes the releases concept, and reshapes the four UPRN views around what the
-- premises list actually needs.
--
-- RELEASES WERE NEVER WANTED (Dave, 2026-08-12). "Releasing" a UPRN recorded against
-- the wayleave tables means nothing on its own — a wayleave STATUS of Complete, tied
-- to a project, is the thing that carries meaning. The concept was already half gone:
-- docs/unusedfiles.md records wayleave_releases_load.php and _save.php as removed with
-- the releases tab in the v2 refactor, so nothing has been able to set the flag for
-- some time. The table, the `released` expression in all four views and the
-- released/not-released map styling all survived that removal and are going now.
--
-- WHAT THE VIEWS GAIN INSTEAD. The premises list needs to answer "how did this premise
-- get here, who added it and when", which none of the four views carried:
--   src             — 'polygon' | 'direct' | 'stocklist' | 'title', constant per view
--   added_user      — the actor, from each source table's own column
--   added_datetime  — when
--   added_username  — resolved, so the list does not have to
--
-- src also gives the map something real to style on. The four point layers were
-- registered against vector_style_field = 'released' (migration 037), which is the
-- column being dropped — and with no style field the lookup returns undefined and
-- falls through to pointStyleDefault, which has no `image` and so renders points
-- invisibly. Keying on src restores the per-source colouring the v2 editor had, via
-- the standard mechanism rather than a bespoke makePointStyle().
--
-- POLYGON STYLE. The boundary layer was registered with vector_style = 'boundary_styles',
-- which is keyed by a polygon_style attribute the view does not expose — so every
-- lookup missed and fell through to defaultStyles.grey, a grey dashed line. 'blue' is
-- an existing entry in polyStyleManager.defaultStyles and is exactly the 2px blue
-- stroke wanted, so this is a value change, not new style code.
--
-- Views must be DROPped rather than replaced: CREATE OR REPLACE VIEW cannot remove a
-- column, and `released` is going from all four.

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

DO $$
BEGIN
    IF to_regclass('wayleave.wayleave_releases') IS NULL THEN
        RAISE EXCEPTION '041 has already run — wayleave.wayleave_releases is gone';
    END IF;
    IF to_regclass('wayleave.vw_uprns_from_boundary') IS NULL THEN
        RAISE EXCEPTION 'the UPRN views are missing — apply 036 first';
    END IF;
END $$;

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

DO $$
DECLARE
    v_n bigint;
BEGIN
    SELECT count(*) INTO v_n FROM wayleave.wayleave_releases;
    RAISE NOTICE '041: dropping wayleave.wayleave_releases — % row(s) discarded', v_n;
END $$;

-- ── 2. Drop the four views, then the table they reference ───────────────────

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 TABLE wayleave.wayleave_releases;

-- ── 3. Recreate them ────────────────────────────────────────────────────────

CREATE VIEW wayleave.vw_uprns_from_boundary AS
 SELECT apu.wayleave_id,
    apu.uprn,
    apu.is_approved,
    abp.address AS address_full,
    abp.geom,
    'polygon'::text AS src,
    apu.actioned_user_id AS added_user,
    apu.action_datetime  AS added_datetime,
    u.username           AS added_username
   FROM wayleave.wayleave_polygon_uprns apu
     JOIN basedata.abp abp ON abp.uprn = apu.uprn
     LEFT JOIN users.users u ON u.id = apu.actioned_user_id
  WHERE apu.is_assigned = true;

CREATE VIEW wayleave.vw_uprns_from_direct AS
 SELECT au.wayleave_id,
    au.uprn,
    abp.address AS address_full,
    abp.geom,
    'direct'::text AS src,
    au.added_user,
    au.added_datetime,
    u.username AS added_username
   FROM wayleave.wayleave_uprns au
     JOIN basedata.abp abp ON abp.uprn = au.uprn
     LEFT JOIN users.users u ON u.id = au.added_user
  WHERE au.is_deleted = false;

-- 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 ast.wayleave_id,
    sp.uprn,
    abp.address AS address_full,
    abp.geom,
    'stocklist'::text AS src,
    ast.added_user,
    ast.added_datetime,
    u.username AS added_username
   FROM wayleave.wayleave_stocklists ast
     JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ast.stocklist_id
     JOIN basedata.abp abp ON abp.uprn = sp.uprn
     LEFT JOIN users.users u ON u.id = ast.added_user
  WHERE ast.is_deleted = false;

CREATE VIEW wayleave.vw_uprns_from_titles AS
 SELECT alr.wayleave_id,
    abp.uprn,
    abp.address AS address_full,
    abp.geom,
    'title'::text AS src,
    alr.added_user,
    alr.added_datetime,
    u.username AS added_username
   FROM wayleave.wayleave_landregistry alr
     JOIN basedata.abp abp ON abp.title_no = alr.title_number::text
     LEFT JOIN users.users u ON u.id = alr.added_user
  WHERE alr.is_deleted = false
    AND abp.uprn IS NOT NULL;

-- ── 4. Re-point the map layer styling ───────────────────────────────────────

DO $$
DECLARE
    v_poly   integer;
    v_points integer;
BEGIN
    UPDATE public.map_layers
       SET vector_style = 'blue', vector_style_field = ''
     WHERE database_schema = 'wayleave' AND database_table = 'vw_wayleave_polygons';
    GET DIAGNOSTICS v_poly = ROW_COUNT;

    UPDATE public.map_layers
       SET vector_style_field = 'src'
     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_points = ROW_COUNT;

    -- 025's map-layer UPDATE matched nothing and reported success; every repair
    -- statement here asserts its own row count.
    IF v_poly <> 1 THEN
        RAISE EXCEPTION '041: polygon layer style update matched % row(s), expected 1', v_poly;
    END IF;
    IF v_points <> 4 THEN
        RAISE EXCEPTION '041: point layer style update matched % row(s), expected 4', v_points;
    END IF;

    RAISE NOTICE '041: polygon layer styled blue; four point layers keyed on src';
END $$;

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

DO $$
DECLARE
    v_bad text;
BEGIN
    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']) AS v
     WHERE EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'wayleave' AND table_name = v
                      AND column_name = 'released');
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '041: released still present on: %', v_bad;
    END IF;

    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']) AS v
     WHERE NOT EXISTS (SELECT 1 FROM information_schema.columns
                        WHERE table_schema = 'wayleave' AND table_name = v
                          AND column_name = 'src');
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '041: src missing from: %', v_bad;
    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;

    RAISE NOTICE '041: releases gone; the four UPRN views now carry src, added_user, added_datetime and added_username';
END $$;
