-- 037_wayleave_map_layers.sql
-- Wayleave module realignment, migration 7 of 7
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 6)
--
-- Registers the five wayleave entity views in public.map_layers so the standard layer
-- loader serves them, instead of the five hardcoded fn/map_get_v2.php calls in
-- wayleave_edit_v2.js. Requires 036.
--
-- WHY IT MATTERS BEYOND TIDINESS: map_get_v2.php gates on the SCHEMA of the requested
-- relation, so today a user with wayleave read can request ANY relation in the wayleave
-- schema, not only these five. Registration is what lets that gate tighten to the named
-- relation via map_layer_page_config.page_context -> routes.php. That tightening is
-- Phase 7 code; this migration is its prerequisite.
--
-- URLS CARRY NO WHERE CLAUSE, deliberately. The editor appends the record filter at
-- fetch time — project_edit_v2.js does layer.url + '&where={"project_id":"…"}' — so the
-- stored url is the unscoped relation, exactly as layers 18/35/36 have it. The wayleave
-- v3 editor appends '&where={"wayleave_id":"…"}'. A stored where clause would pin every
-- user to one record.
--
-- ── THE ONE THING THAT WILL BREAK IF PHASE 8 LAGS ──────────────────────────────
-- vector_style is a KEY into pointStyleManager / polyStyleManager in
-- js/map_layer_utils.js. The four UPRN layers below use 'wayleavePremiseStyles',
-- WHICH DOES NOT EXIST YET. Today's valid point keys are only PlanEquipmentstyles,
-- PlanStructurestyles and Accountstyles — there has never been a premise/UPRN point
-- style, because wayleave_edit_v2.js built its own makePointStyle() inline. Phase 8
-- must add wayleavePremiseStyles to pointStyleManager.
--
-- This is not a cosmetic risk. map_layer_utils.js's getPointStyle() guards the lookup
-- (pointStyleManager[cat] && pointStyleManager[cat][type]), but the private copy in
-- project_edit_v2.js:559 does NOT — it does pointStyleManager[layerCategory][type]
-- directly, so an unknown key throws "Cannot read properties of undefined" and takes
-- the whole map down. The v3 editor must use the MapLayerUtils version, not a copy.
-- Polygons are safe either way: getPolyStyle falls back through defaultStyles to grey.
--
-- There is no window where a user hits this: the module is down from 031 until Phase 7
-- deploys, and Phase 8 follows immediately. Stated so it is not discovered as a mystery.
--
-- STYLE FIELD: the four UPRN views all expose `released` (boolean), so styles can
-- distinguish released premises from covered ones. JS coerces obj[true] to obj['true'],
-- so the style entries key on the strings 'true' / 'false'.
--
-- LAYERS ARE READ-ONLY HERE. layer_editable / allow_create / allow_delete are all false.
-- Polygon drawing stays with the module's own tooling (wayleave_coverage_polygon_save.php
-- and the editor's draw controls), which is module-specific functionality this work does
-- not redesign. Moving it onto the standard editing machinery is a separate decision.

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

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

    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION 'view(s) missing, apply 036 first: %', v_missing;
    END IF;

    IF EXISTS (SELECT 1 FROM public.map_layers WHERE database_schema = 'wayleave') THEN
        RAISE EXCEPTION '037 has already run — wayleave layers are registered';
    END IF;
END $$;

-- ── 1. Register the five layers ─────────────────────────────────────────────
-- layer_id is allocated from max()+1 rather than hardcoded: the values differ between
-- environments, which is why migration 030 had to key its repair on table name rather
-- than on the ids Phase 0 had recorded.

DO $$
DECLARE
    spec text[][] := ARRAY[
        --  view                      title                     name                  type          uid     label_field   style                   style_field  z    focus
        ARRAY['vw_wayleave_polygons',  'Wayleave Polygons',      'wayleavePolygons',   'vectorPoly', 'id',   'label',      'boundary_styles',      '',          '200', 'true'],
        ARRAY['vw_uprns_from_boundary','UPRNs - from Polygons',  'wayleaveUprnsPoly',  'vectorPoint','uprn', 'address_full','wayleavePremiseStyles','released',  '210', 'false'],
        ARRAY['vw_uprns_from_direct',  'UPRNs - added directly', 'wayleaveUprnsDirect','vectorPoint','uprn', 'address_full','wayleavePremiseStyles','released',  '211', 'false'],
        ARRAY['vw_uprns_from_stocklists','UPRNs - from Stocklists','wayleaveUprnsStock','vectorPoint','uprn','address_full','wayleavePremiseStyles','released',  '212', 'false'],
        ARRAY['vw_uprns_from_titles',  'UPRNs - from Titles',    'wayleaveUprnsTitle', 'vectorPoint','uprn', 'address_full','wayleavePremiseStyles','released',  '213', 'false']
    ];
    i        integer;
    v_id     integer;
    v_count  integer := 0;
BEGIN
    FOR i IN 1 .. array_length(spec, 1) LOOP
        SELECT COALESCE(max(layer_id), 0) + 1 INTO v_id FROM public.map_layers;

        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,
            spec[i][2],
            spec[i][3],
            '',
            'Wayleave coverage layer',
            spec[i][4],
            'wayleave',
            'checkbox',
            'wayleave',
            0, 0, 30,
            'fn/map_get_v2.php?geotable=wayleave.' || spec[i][1] || '&geomfield=geom',
            spec[i][9]::integer,
            true,
            spec[i][7],
            spec[i][8],
            spec[i][6],
            '',
            false,
            spec[i][5],
            NULL,
            '',
            '[]'::jsonb,
            now(),
            true,
            'wayleave',
            spec[i][1]
        );

        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, spec[i][10]::boolean, false
        );

        RAISE NOTICE '037: registered layer % as % (wayleave.%)', v_id, spec[i][3], spec[i][1];
        v_count := v_count + 1;
    END LOOP;

    IF v_count <> 5 THEN
        RAISE EXCEPTION '037: registered % layer(s), expected 5', v_count;
    END IF;
END $$;

-- ── 2. Verify ───────────────────────────────────────────────────────────────
-- The relation is stored TWICE here — database_schema/database_table, read by
-- map_layer_manager.php on the save path, and again inside the url string, which is
-- what the editor actually fetches. Migration 025 updated the columns and never looked
-- at the url, leaving two opportunity layers registered, enabled and permanently empty
-- with map_get_v2.php answering "Invalid table name." and nothing on screen to say so.
-- This asserts the two agree, per row.

DO $$
DECLARE
    v_bad   text;
    v_layers integer;
    v_pages  integer;
BEGIN
    SELECT string_agg(layer_name, ', ') INTO v_bad
      FROM public.map_layers
     WHERE database_schema = 'wayleave'
       AND url <> 'fn/map_get_v2.php?geotable=' || database_schema || '.' || database_table || '&geomfield=geom';

    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '037: url does not match database_schema/database_table on: %', v_bad;
    END IF;

    SELECT count(*) INTO v_layers FROM public.map_layers WHERE database_schema = 'wayleave';
    SELECT count(*) INTO v_pages
      FROM public.map_layer_page_config pc
      JOIN public.map_layers l ON l.layer_id = pc.layer_id
     WHERE l.database_schema = 'wayleave' AND pc.page_context = 'wayleaveedit';

    IF v_layers <> 5 OR v_pages <> 5 THEN
        RAISE EXCEPTION '037: % layer row(s) and % page-config row(s), expected 5 and 5', v_layers, v_pages;
    END IF;

    -- Every registered relation must actually exist and be readable.
    IF EXISTS (
        SELECT 1 FROM public.map_layers
         WHERE database_schema = 'wayleave'
           AND to_regclass(database_schema || '.' || database_table) IS NULL
    ) THEN
        RAISE EXCEPTION '037: a registered layer points at a relation that does not exist';
    END IF;

    RAISE NOTICE '037: five wayleave layers registered. PHASE 8 MUST ADD wayleavePremiseStyles TO pointStyleManager, or the four UPRN layers throw on render.';
END $$;
