-- 063_project_premises_map_layer.sql
-- The Project Premises map layer for the project editor.
-- (docs/2026-08-31-project-premises-map-layer.md)
--
-- One read view plus its public.map_layers registration. Nothing else changes: the
-- premises themselves are already materialised by 056-058, so this is a new way of
-- looking at rows that exist, not a new source of truth.
--
-- WHAT THE LAYER SHOWS. Every premise in the viewing project's LINEAGE - the project
-- itself, every ancestor above it and every descendant below it - so opening any project
-- in a 1-2-3 chain shows all three projects' premises at once. Siblings and cousins are
-- deliberately NOT included: a premise held by a sibling appears only where a shared
-- ancestor holds it too. That is the same lineage the is_valid rule uses, and it is what
-- makes three styles sufficient - here, above, below - with nothing left over.
--
-- Seeing an ADJACENT project's premises is a real want and is not what this answers
-- (Dave, 2026-08-31). It is logged in docs/improvement-opportunities.md, because the
-- honest version of it is about adjacency rather than hierarchy: an unrelated project
-- over the fence is exactly as invisible here as a sibling, and widening the lineage to
-- the whole tree would fix the sibling case while leaving the general one untouched.
--
-- THE STYLE KEY MUST EXIST IN JS, and this migration cannot check it. vector_style names
-- projectPremiseStyles in pointStyleManager (www/js/map_layer_utils.js) and
-- vector_style_field names premise_level. 037's warning applies unchanged and is worth
-- restating: project_edit_v2.js's private getPointStyle does
-- pointStyleManager[key][type] with no guard, so a style key that does not exist throws
-- and takes the whole map down - and a key that exists while a VALUE does not resolves to
-- pointStyleDefault, which has no image, so the points render invisibly rather than
-- wrongly. The three values premise_level can take are asserted at the foot of this file;
-- the JS half is asserted by looking at it.
--
-- THE LAYER IS OFF BY DEFAULT (visible = false on the page config). OpenLayers only
-- fetches a vector source once the layer is first rendered, so an unticked layer costs
-- the editor nothing on load. It is a per-page override, so it can be turned on in
-- Admin -> Map Layers without a migration.
--
-- NOT EDITABLE, anywhere: layer_editable, layer_allow_create and layer_allow_delete are
-- all false. Premises are resolved from the boundary by
-- projects.refresh_project_premises() and there is nothing on this layer a user could
-- correctly move or delete.

-- == 0. Guards ==============================================================

DO $$
BEGIN
    IF to_regclass('projects.project_premises') IS NULL
    OR to_regclass('projects.vw_project_ancestors') IS NULL THEN
        RAISE EXCEPTION '063: projects.project_premises / vw_project_ancestors missing - apply 056-058 first';
    END IF;

    IF to_regclass('basedata.vw_abp_min') IS NULL THEN
        RAISE EXCEPTION '063: basedata.vw_abp_min is missing';
    END IF;

    -- 061 added projects.projects.is_deleted, which the view filters on.
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'projects' AND table_name = 'projects'
                      AND column_name = 'is_deleted') THEN
        RAISE EXCEPTION '063: projects.projects has no is_deleted column - apply 061 first';
    END IF;

    IF to_regclass('projects.vw_project_premises_map') IS NOT NULL THEN
        RAISE EXCEPTION '063 has already run - projects.vw_project_premises_map exists';
    END IF;

    IF EXISTS (SELECT 1 FROM public.map_layers WHERE layer_name = 'projectPremises') THEN
        RAISE EXCEPTION '063 has already run - a map_layers row named projectPremises exists';
    END IF;
END $$;

-- == 1. projects.vw_project_premises_map ====================================
--
-- CORRELATED, NOT SET-BASED, and that is the whole shape of it. This is 048's lesson and
-- 058's header rule: a view that resolves every project's lineage and lets the caller
-- filter afterwards cannot have WHERE project_id = ? pushed into it, so every layer fetch
-- would walk the recursive ancestor view once per project in the database. Driving off
-- projects.projects with a LATERAL means the filter lands on the primary key, one row,
-- and the lineage is resolved for that project alone.
--
-- The consequence, stated so it is not discovered later: this view is RIGHT behind a
-- project filter and WRONG for an estate-wide question. Nothing should select from it
-- unfiltered - projects.vw_project_premises and projects.vw_premise_claimants are the
-- views for that.
--
-- levels_below is signed and is the only thing the classification reads:
--     0   the viewing project itself
--   > 0   a descendant, that many levels down
--   < 0   an ancestor, that many levels up
-- Both halves come out of projects.vw_project_ancestors, read in its two directions -
-- ancestor_id = me gives me and everything below, project_id = me gives me and
-- everything above.
--
-- THE THREE LEVELS, and why they are keyed the way they are:
--   this_project    the viewing project holds the premise AND counts it (is_valid)
--   sub_project     something below the viewing project counts it. This deliberately
--                   also catches the case where the viewing project holds the premise
--                   with is_valid false, because the only way to be invalid is for a
--                   descendant to hold it - so the two are the same fact.
--   parent_project  only a project above holds it at all
-- That is the same test the Premises tab chips use: is_valid reads as This Project and
-- its inverse as Sub-projects. Keying on hierarchy_role instead would repeat the fault
-- found during the premises build - hierarchy_role only carries a value where an ANCESTOR
-- also holds the premise, so the top project of a chain could never be chipped for
-- premises it counts while the one below it was.
--
-- counted_project_id is NULL when the lineage contains more than one valid claimant,
-- which happens when two branches under the viewing project both hold the premise. Same
-- no-tie-break rule as vw_premise_claimants: there is no single owner to name, and an
-- arbitrary first() would read as an answer. count(DISTINCT ...) rather than count(*)
-- because a self-parent or an A -> B -> A cycle can put one project in both halves of
-- the lineage union; the cycle guard in vw_project_ancestors stops that recursing but
-- does not stop it double-counting here.
--
-- is_deleted is filtered on the VIEWING project only, following 062: a map view filters
-- itself so the rule is true for GeoServer and every other reader, not only for requests
-- arriving through map_get_v2.php. The lineage needs no such filter - a project must be
-- hierarchy-free to be deleted, so a deleted project can never be somebody's ancestor or
-- descendant, and its own premises are tombstoned by the refresh either way.
--
-- company_id is the viewing project's, and exists so this view behaves under
-- map_get_v2.php's no-project_id branch, which appends a company_id predicate and would
-- otherwise fail on a missing column. Record-level scoping for the normal path is
-- userCheckCompanyProjectAssigned() in that endpoint, keyed on the same project_id.

CREATE VIEW projects.vw_project_premises_map AS
SELECT v.project_id,
       v.company_id,
       c.uprn,
       -- The style key. Machine token, never display text: a style key that stops
       -- resolving renders the points invisibly rather than wrongly, so it must not
       -- move when the wording does.
       c.premise_level,
       -- The same fact in the words the Premises tab uses. This is what the editor
       -- popup shows; premise_level is suppressed there.
       CASE c.premise_level
            WHEN 'this_project' THEN 'This Project'
            WHEN 'sub_project'  THEN 'Sub-project'
            ELSE                     'Parent Project'
       END::text AS hierarchy_level,
       c.counted_project_id,
       cp.project_name AS counted_project,
       a.address,
       a.postcode,
       a.geom
  FROM projects.projects v
  CROSS JOIN LATERAL (
        SELECT pp.uprn,
               CASE
                   WHEN bool_or(l.levels_below = 0 AND pp.is_valid) THEN 'this_project'
                   WHEN bool_or(l.levels_below >= 0)                THEN 'sub_project'
                   ELSE                                                  'parent_project'
               END::text AS premise_level,
               CASE WHEN count(DISTINCT l.holder_id) FILTER (WHERE pp.is_valid) = 1
                    THEN min(l.holder_id) FILTER (WHERE pp.is_valid)
               END AS counted_project_id
          FROM (
                -- The viewing project and everything below it.
                SELECT anc.project_id  AS holder_id,  anc.depth AS levels_below
                  FROM projects.vw_project_ancestors anc
                 WHERE anc.ancestor_id = v.project_id
                UNION ALL
                -- Everything strictly above it.
                SELECT anc.ancestor_id, -anc.depth
                  FROM projects.vw_project_ancestors anc
                 WHERE anc.project_id = v.project_id
                   AND anc.depth > 0
               ) l
          JOIN projects.project_premises pp
            ON pp.project_id = l.holder_id
           AND pp.state = 'approved'
         GROUP BY pp.uprn
       ) c
  JOIN basedata.vw_abp_min a ON a.uprn = c.uprn
  LEFT JOIN projects.projects cp ON cp.project_id = c.counted_project_id
 WHERE v.is_deleted = false;

COMMENT ON VIEW projects.vw_project_premises_map IS
    'Map layer source: every premise in a project lineage - the project, its ancestors and its descendants - one row per premise per viewing project. premise_level styles it (this_project / sub_project / parent_project) and says where the premise is counted, not what the viewing project is. Correlated on purpose: filter it by project_id, and never select from it unfiltered - use vw_project_premises or vw_premise_claimants for estate-wide questions.';

-- == 2. Register the layer ==================================================
--
-- layer_id is allocated from max()+1 rather than hardcoded: the values differ between
-- environments, which is why 030 had to key its repair on table name rather than on ids.
--
-- The url carries NO where clause. project_edit_v2.js appends the project filter at fetch
-- time, exactly as every other project vector layer does; a stored where clause would pin
-- every user to one project.
--
-- z_index 12 puts premises above the boundary and the neighbour outlines and below the
-- plan network (duct 14, cables 20, structures 22, equipment 23) - premises are the
-- context you plan against, so nothing being planned should end up behind them.
--
-- label_field is empty deliberately. A project's premises are addresses at building
-- scale; labelling them is unreadable at any zoom where more than a handful are on
-- screen, and the styles carry no ol.style.Text, so nothing would render anyway.

DO $$
DECLARE
    v_id integer;
BEGIN
    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,
        'Project Premises',
        'projectPremises',
        '',
        'Premises across this project and its hierarchy. Filled purple is counted here, teal is counted by a sub-project, grey outline is counted above.',
        'vectorPoint',
        'project',
        'checkbox',
        'project',
        0, 0, 30,
        'fn/map_get_v2.php?geotable=projects.vw_project_premises_map&geomfield=geom',
        12,
        false,
        'projectPremiseStyles',
        'premise_level',
        '',
        '',
        false,
        'uprn',
        NULL,
        '',
        '[]'::jsonb,
        now(),
        true,
        'projects',
        'vw_project_premises_map'
    );

    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, 'projectedit', false, false,
        false, false, false, false
    );

    RAISE NOTICE '063: registered layer % as projectPremises (projects.vw_project_premises_map)', v_id;
END $$;

-- == 3. Verify ==============================================================

DO $$
DECLARE
    v_url     text;
    v_missing text;
    v_bad     text;
    v_pages   integer;
BEGIN
    -- The relation is stored THREE times on a map_layers row - database_schema,
    -- database_table, and again inside the url string. 025 updated the columns and never
    -- looked at the url, which left two opportunity layers registered, enabled and
    -- permanently empty with nothing on screen to say so.
    SELECT url INTO v_url FROM public.map_layers WHERE layer_name = 'projectPremises';

    IF v_url IS DISTINCT FROM 'fn/map_get_v2.php?geotable=projects.vw_project_premises_map&geomfield=geom' THEN
        RAISE EXCEPTION '063: layer url does not match database_schema/database_table: %', COALESCE(v_url, '<no row>');
    END IF;

    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.layer_name = 'projectPremises' AND pc.page_context = 'projectedit';

    IF v_pages <> 1 THEN
        RAISE EXCEPTION '063: % projectedit page-config row(s), expected 1', v_pages;
    END IF;

    -- map_get_v2.php refuses a where key that is not a real column, and the editor always
    -- sends project_id. geom is what geomfield names. Both are checked against
    -- information_schema by the endpoint, so check them the same way here.
    SELECT string_agg(cn, ', ') INTO v_missing
      FROM unnest(ARRAY['project_id', 'company_id', 'uprn', 'premise_level',
                        'hierarchy_level', 'counted_project_id', 'counted_project',
                        'address', 'postcode', 'geom']) AS cn
     WHERE NOT EXISTS (SELECT 1 FROM information_schema.columns
                        WHERE table_schema = 'projects'
                          AND table_name  = 'vw_project_premises_map'
                          AND column_name = cn);

    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION '063: column(s) missing from the view: %', v_missing;
    END IF;

    -- Every value premise_level takes must have a style entry in pointStyleManager, and
    -- an unknown value renders INVISIBLY rather than wrongly. This asserts the SQL half:
    -- that the column produces only the three keys the JS defines.
    --
    -- This one read is deliberately unfiltered, which is the thing the view's comment
    -- says not to do - it resolves the lineage for every project at once. That is the
    -- point: it is the only way to assert the column over the whole estate, it also
    -- proves the view executes, and it is paid once here rather than on every layer
    -- fetch. Same shape as 057's seed, and comparable work.
    SELECT string_agg(DISTINCT premise_level, ', ') INTO v_bad
      FROM projects.vw_project_premises_map
     WHERE premise_level NOT IN ('this_project', 'sub_project', 'parent_project');

    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '063: premise_level produced unexpected value(s): %', v_bad;
    END IF;

    RAISE NOTICE '063: done. www/js/map_layer_utils.js MUST define pointStyleManager.projectPremiseStyles with this_project / sub_project / parent_project, or the project editor map throws on render.';
END $$;
