-- 036_wayleave_views.sql
-- Wayleave module realignment, migration 6 of 7
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 6)
--
-- The six views 032 dropped, recreated against the rebuilt tables, plus the two the
-- module never had: vw_wayleave_list and vw_parent_wayleave_links. Requires 035.
--
-- Also renames public.file_uploads.agreement_id to wayleave_id. That column sits
-- outside the wayleave schema, which is why 034's sweep did not flag it, but it is a
-- foreign key onto the wayleave entity and every sibling column is named for its
-- module (project_id, stocklist_id, account_id, opportunity_id). D7 covers it.
-- file_upload.php and serve_file.php follow in Phase 7.
--
-- THE entity STRING STAYS 'wayleave', SINGULAR. public.file_uploads.entity holds an
-- entity type, not a module key — the same namespace as users.user_item_permissions
-- .item_type, where project / account / stocklist / opportunity are all singular.
-- Only the module key went plural (031). Changing this one would break
-- file_upload.php's $allowedEntities lookup for no gain.
--
-- ORIGINAL DEFINITIONS came from sql/netplanner_schema_20260706.sql, which is
-- authoritative for these six: db/archive/005_wayleave_map_layer_views.sql created
-- them, it is folded into that baseline, and no migration since has altered them.
-- Logic is carried across unchanged apart from the table and column renames.
--
-- MAP LAYER REGISTRATION IS NOT HERE. It needs the shape of the existing
-- public.map_layers rows to copy, and lands in 037.

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

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
                    WHERE n.nspname = 'public' AND p.proname = 'fn_wayleaves_history') THEN
        RAISE EXCEPTION 'history triggers are missing — apply 035 first';
    END IF;

    IF EXISTS (SELECT 1 FROM pg_views WHERE schemaname = 'wayleave') THEN
        RAISE EXCEPTION '036 has already run — the wayleave schema already has views';
    END IF;
END $$;

-- ── 1. public.file_uploads.agreement_id → wayleave_id ───────────────────────

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'public' AND table_name = 'file_uploads'
                  AND column_name = 'agreement_id') THEN
        ALTER TABLE public.file_uploads RENAME COLUMN agreement_id TO wayleave_id;
        RAISE NOTICE '036: public.file_uploads.agreement_id renamed to wayleave_id';
    ELSIF EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_schema = 'public' AND table_name = 'file_uploads'
                     AND column_name = 'wayleave_id') THEN
        RAISE NOTICE '036: public.file_uploads.wayleave_id already renamed, skipping';
    ELSE
        RAISE EXCEPTION 'public.file_uploads has neither agreement_id nor wayleave_id';
    END IF;
END $$;

-- ── 2. Coverage and attachment views ────────────────────────────────────────

CREATE VIEW wayleave.vw_wayleave_polygons AS
 SELECT wayleave_polygons.id,
    wayleave_polygons.wayleave_id,
    wayleave_polygons.label,
    wayleave_polygons.geom
   FROM wayleave.wayleave_polygons
  WHERE wayleave_polygons.is_deleted = false;

-- Keeps the file_is_deleted filter, which the projects / accounts / stocklists
-- equivalents still lack. wayleave_file_delete.php is the only path in the app that
-- sets that flag, so this view is the only one where it is currently load-bearing.
CREATE VIEW wayleave.vw_file_uploads AS
 SELECT fu.id,
    fu.file_id,
    to_char(fu.file_upload_datetime, 'DD mon YYYY HH24:MI'::text) AS file_upload_datetime,
    fu.file_upload_datetime AS file_upload_datetime_raw,
    u.username,
    fu.file_upload_user,
    fu.file_name,
    fu.file_type,
    fu.wayleave_id,
    fu.file_category,
    fu.file_description,
    fu.file_size,
    fu.file_size_raw,
    fu.file_location
   FROM public.file_uploads fu
     LEFT JOIN users.users u ON fu.file_upload_user = u.id
  WHERE fu.entity::text = 'wayleave'::text
    AND fu.file_is_deleted = false
  ORDER BY fu.file_upload_datetime DESC;

CREATE VIEW wayleave.vw_uprns_from_boundary AS
 SELECT apu.wayleave_id,
    apu.uprn,
    apu.is_approved,
    abp.address AS address_full,
    abp.geom,
        CASE
            WHEN r.uprn IS NOT NULL THEN true
            ELSE false
        END AS released
   FROM wayleave.wayleave_polygon_uprns apu
     JOIN basedata.abp abp ON abp.uprn = apu.uprn
     LEFT JOIN wayleave.wayleave_releases r
            ON r.wayleave_id = apu.wayleave_id AND r.uprn = apu.uprn AND r.is_deleted = false
  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,
        CASE
            WHEN r.uprn IS NOT NULL THEN true
            ELSE false
        END AS released
   FROM wayleave.wayleave_uprns au
     JOIN basedata.abp abp ON abp.uprn = au.uprn
     LEFT JOIN wayleave.wayleave_releases r
            ON r.wayleave_id = au.wayleave_id AND r.uprn = au.uprn AND r.is_deleted = false
  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,
        CASE
            WHEN r.uprn IS NOT NULL THEN true
            ELSE false
        END AS released
   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 wayleave.wayleave_releases r
            ON r.wayleave_id = ast.wayleave_id AND r.uprn = sp.uprn AND r.is_deleted = false
  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,
        CASE
            WHEN r.uprn IS NOT NULL THEN true
            ELSE false
        END AS released
   FROM wayleave.wayleave_landregistry alr
     JOIN basedata.abp abp ON abp.title_no = alr.title_number::text
     LEFT JOIN wayleave.wayleave_releases r
            ON r.wayleave_id = alr.wayleave_id AND r.uprn = abp.uprn AND r.is_deleted = false
  WHERE alr.is_deleted = false
    AND abp.uprn IS NOT NULL;

-- ── 3. Hierarchy view ───────────────────────────────────────────────────────
-- Mirrors projects.vw_parent_project_links and opportunity's equivalent. Both
-- centroids must exist for a line, so a wayleave with no geom produces no row —
-- same as the other modules, and renderHierarchyAlerts() only draws distance for
-- projects anyway.

CREATE VIEW wayleave.vw_parent_wayleave_links AS
 SELECT w.wayleave_id,
    w.wayleave_name,
    w.parent_wayleave_id,
    pw.wayleave_name AS parent_wayleave_name,
    public.st_makeline(public.st_centroid(w.geom), public.st_centroid(pw.geom)) AS geom,
    round((public.st_distance(public.st_centroid(w.geom), public.st_centroid(pw.geom)))::numeric, 0) AS distance
   FROM wayleave.wayleaves w
     JOIN wayleave.wayleaves pw ON w.parent_wayleave_id = pw.wayleave_id;

-- ── 4. List view ────────────────────────────────────────────────────────────
-- New. Absorbs the premise-count CTEs that wayleave_list_load.php built by hand, and
-- takes the projects/opportunity list shape so data_get.php can serve it like every
-- other module's list.
--
-- modified_username joins on modified_user, NOT created_user. vw_opportunity_list
-- shipped with that copy-paste bug and showed the creator in the modified-by column
-- for months; it is not being reproduced here.
--
-- is_deleted is filtered AT THE VIEW, so no consumer has to remember to. Wayleave is
-- the only entity table with a soft-delete flag.

CREATE VIEW wayleave.vw_wayleave_list AS
 WITH all_uprns AS (
         SELECT wayleave_polygon_uprns.wayleave_id, wayleave_polygon_uprns.uprn
           FROM wayleave.wayleave_polygon_uprns
          WHERE wayleave_polygon_uprns.is_assigned = true
            AND wayleave_polygon_uprns.is_approved = true
        UNION
         SELECT wayleave_uprns.wayleave_id, wayleave_uprns.uprn
           FROM wayleave.wayleave_uprns
          WHERE wayleave_uprns.is_deleted = false
        UNION
         SELECT ws.wayleave_id, sp.uprn
           FROM wayleave.wayleave_stocklists ws
             JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ws.stocklist_id
          WHERE ws.is_deleted = false
        ), prem AS (
         SELECT all_uprns.wayleave_id, count(DISTINCT all_uprns.uprn) AS premise_count
           FROM all_uprns
          GROUP BY all_uprns.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_reference,
    w.wayleave_type,
    w.wayleave_team,
    w.wayleave_status_id,
    st.wayleave_status_desc,
    st.wayleave_status_style,
    w.wayleave_manager,
    pm.username AS wayleave_manager_name,
    w.ecd_date,
    w.signed_date,
    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;

-- ── 5. Verify ───────────────────────────────────────────────────────────────
-- Invariants asserted directly against the catalog, not derived from counts. Two
-- earlier migrations in this set failed on assertions that were themselves wrong
-- (033 checked a scope it did not own, 035 miscounted a column delta) — both cost a
-- run and reported nothing real.

DO $$
DECLARE
    v_missing text;
    v_bad     text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_missing
      FROM unnest(ARRAY['vw_wayleave_polygons', 'vw_file_uploads', 'vw_uprns_from_boundary',
                        'vw_uprns_from_direct', 'vw_uprns_from_stocklists',
                        'vw_uprns_from_titles', 'vw_parent_wayleave_links',
                        'vw_wayleave_list']) AS v
     WHERE to_regclass('wayleave.' || v) IS NULL;

    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION '036: view(s) not created: %', v_missing;
    END IF;

    -- Every view must be selectable. A view can be created against columns that
    -- resolve at parse time and still fail on first read, and the editor is where
    -- that would otherwise surface.
    PERFORM 1 FROM wayleave.vw_wayleave_polygons     LIMIT 1;
    PERFORM 1 FROM wayleave.vw_file_uploads          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_parent_wayleave_links LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_list         LIMIT 1;

    -- The columns the list page and dashboard depend on. Aliased as c and compared
    -- explicitly rather than leaning on an implicit unnest.* reference.
    SELECT string_agg(c, ', ') INTO v_bad
      FROM unnest(ARRAY['modified_username', 'wayleave_status_style', 'premise_count',
                        'parent_wayleave_name', 'wayleave_manager_name']) AS c
     WHERE NOT EXISTS (
        SELECT 1 FROM information_schema.columns ic
         WHERE ic.table_schema = 'wayleave' AND ic.table_name = 'vw_wayleave_list'
           AND ic.column_name = c);

    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '036: vw_wayleave_list is missing column(s): %', v_bad;
    END IF;

    RAISE NOTICE '036: eight views created and all selectable; map layers follow in 037';
END $$;
