-- 057_project_premises_refresh.sql
-- Project premises, migration 2 of 3
-- (docs/2026-08-17-project-premises-uprn-table.md, Phase 2)
--
-- The lineage view, the shared resolution function, the two triggers that keep it
-- honest, and the seed. After this migration projects.project_premises is populated
-- and self-maintaining, but still nothing reads it — 058 is what points the views and
-- the endpoints at it.
--
-- ONE SHARED FUNCTION. projects.refresh_project_premises() is called by the triggers,
-- by the seed at the foot of this file and by sql/project_premises_resync.sql, so
-- "what is inside a boundary" and "what is valid" have one definition that cannot
-- drift between three copies.
--
-- ST_Intersects, NOT ST_Within. Wayleave coverage uses ST_Within(abp.geom, polygon).
-- This uses ST_Intersects because that is what all nine existing consumers use, and
-- for a POINT the two differ only when the point lies exactly on the boundary ring —
-- ST_Intersects counts it, ST_Within does not. Matching today's numbers exactly is
-- worth more than cross-module symmetry, because the whole value of the change depends
-- on nobody having to ask why a count moved. The asymmetry is deliberate, on the record.
--
-- basedata.abp gets no address-quality filter (no valid_address_flag, no classification
-- test) for the same reason: the current queries have none. Rows with geom IS NULL —
-- which sql/abp_sync.sql can produce when coordinates are missing — can never be inside
-- a boundary and need no special handling. Rows with a NULL uprn are excluded, because
-- project_premises.uprn is NOT NULL and such a row cannot be represented at all; abp
-- has no unique index on uprn either, so the resolve takes DISTINCT uprn.
--
-- TWO PROPERTIES OF THE TRIGGERS worth stating. First, they write only to
-- project_premises and never back to projects.projects, so they cannot fire
-- themselves — no recursion guard is needed. Second, they coexist with 017's
-- trg_projects_history and with update_modified_fields_trigger because all three write
-- to different tables, so their firing order is irrelevant.
--
-- THE SEED IS SILENT UNDER migrate.php. PDO does not surface RAISE NOTICE, so the
-- per-project lines below will not appear; the migration either succeeds or aborts.
-- Sizing says it fits: ~600 projects against ~36M basedata.abp rows is 600 bounded
-- GiST probes, not a 36M-row scan, so the seed belongs in the migration and needs no
-- batching. If it ever needs to be watched, the fallback is to apply this migration
-- with the seed line at the foot commented out, then run
--   SELECT * FROM projects.refresh_project_premises(NULL);
-- in psql, where the notices stream. That call is what sql/project_premises_resync.sql
-- packages.

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

DO $$
BEGIN
    IF to_regclass('projects.project_premises') IS NULL THEN
        RAISE EXCEPTION '057: projects.project_premises is missing — apply 056 first';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'projects' AND table_name = 'project_premises'
                      AND column_name = 'is_valid') THEN
        RAISE EXCEPTION '057: projects.project_premises has no is_valid column — this is the pre-056 table, apply 056 first';
    END IF;
    IF to_regclass('projects.vw_project_ancestors') IS NOT NULL THEN
        RAISE EXCEPTION '057 has already run — projects.vw_project_ancestors exists';
    END IF;
    IF to_regclass('basedata.abp') IS NULL THEN
        RAISE EXCEPTION '057: basedata.abp is missing';
    END IF;
END $$;

-- ── 1. projects.vw_project_ancestors ────────────────────────────────────────
--
-- The lineage relation the validity rule needs, so the descendant test is a join and
-- not a recursive CTE per premise. Every project is its own ancestor at depth 0, which
-- is why the rule tests depth > 0 — strictly above.
--
-- The path array is a cycle guard, matching the hierarchy CTEs already in
-- project_load.php:287-305 and :334-351: the data can contain a self-parent or
-- A -> B -> A, and project_save.php:62-66 blocks only the self-parent case at save
-- time. Row count is projects x average depth, so this view is small.

CREATE VIEW projects.vw_project_ancestors AS
WITH RECURSIVE a AS (
    SELECT project_id, project_id AS ancestor_id, 0 AS depth, ARRAY[project_id] AS path
      FROM projects.projects
    UNION ALL
    SELECT a.project_id, p.parent_project_id, a.depth + 1, a.path || p.parent_project_id
      FROM a
      JOIN projects.projects p ON p.project_id = a.ancestor_id
     WHERE p.parent_project_id IS NOT NULL
       AND NOT (p.parent_project_id = ANY (a.path))
)
SELECT project_id, ancestor_id, depth FROM a;

COMMENT ON VIEW projects.vw_project_ancestors IS
    'Every project paired with each of its ancestors, itself included at depth 0. depth > 0 means strictly above. Cycle-guarded by a path array, because the data can contain a self-parent or an A -> B -> A loop.';

-- ── 2. projects.refresh_project_premises() ──────────────────────────────────
--
-- NULL means every project. p_rescan = false skips the spatial scan and only re-flags,
-- which is the cheap path a parent_project_id-only change takes.
--
-- Two steps, in order, and deliberately NO third pass. An earlier draft stored an
-- overlap count here; it is gone, because contention is a property of a UPRN across
-- every project rather than of one project's rows. That is claimant_count, grouped per
-- UPRN in vw_premise_claimants and recomputed on read.

CREATE FUNCTION projects.refresh_project_premises(
    p_project_id integer DEFAULT NULL,
    p_rescan     boolean DEFAULT true
) RETURNS TABLE (project_id integer, uprn_added integer, uprn_removed integer)
    LANGUAGE plpgsql
    AS $fn$
DECLARE
    v_proj    record;
    v_added   integer;
    v_removed integer;
BEGIN
    -- ── Step 1: resolve and diff containment, per project in scope ──────────
    --
    --   inside, no row            -> INSERT approved, proposed_user/datetime = now
    --   inside, state removed     -> UPDATE back to approved, state_datetime = now,
    --                               proposed_datetime UNTOUCHED so "when did this
    --                               premise first enter this boundary" survives a
    --                               shrink-and-regrow
    --   inside, state approved    -> left alone entirely, no timestamp churn
    --   not inside, not removed   -> UPDATE state removed, state_datetime = now
    --
    -- pending and rejected rows are left alone: this plan never writes them, and the
    -- ON CONFLICT predicate below revives only removed rows on purpose.
    FOR v_proj IN
        SELECT p.project_id AS pid, p.geom AS geom, p.modified_user AS modified_user
          FROM projects.projects p
         WHERE p_project_id IS NULL OR p.project_id = p_project_id
         ORDER BY p.project_id
    LOOP
        v_added   := 0;
        v_removed := 0;

        IF p_rescan AND v_proj.geom IS NOT NULL THEN
            WITH inside AS (
                -- DISTINCT because basedata.abp has no unique index on uprn, and a
                -- duplicate would make ON CONFLICT DO UPDATE hit the same row twice,
                -- which is a hard error rather than a wrong number.
                SELECT DISTINCT a.uprn
                  FROM basedata.abp a
                 WHERE a.uprn IS NOT NULL
                   AND st_intersects(v_proj.geom, a.geom)
            ), ins AS (
                INSERT INTO projects.project_premises AS pp
                       (project_id, uprn, source, state, proposed_user)
                SELECT v_proj.pid, i.uprn, 'boundary', 'approved', v_proj.modified_user
                  FROM inside i
                -- Targeted by constraint name, not by column list: project_id is also
                -- an OUT parameter of this function, and an ON CONFLICT inference
                -- clause is parsed as an expression, so a bare column name there is
                -- exactly where plpgsql would substitute the variable instead.
                ON CONFLICT ON CONSTRAINT project_premises_uq DO UPDATE
                   SET state = 'approved', state_datetime = now()
                 WHERE pp.state = 'removed'
                RETURNING 1
            ), rem AS (
                UPDATE projects.project_premises pp
                   SET state = 'removed', state_datetime = now()
                 WHERE pp.project_id = v_proj.pid
                   AND pp.source     = 'boundary'
                   AND pp.state     <> 'removed'
                   AND NOT EXISTS (SELECT 1 FROM inside i WHERE i.uprn = pp.uprn)
                RETURNING 1
            )
            SELECT (SELECT count(*) FROM ins)::integer,
                   (SELECT count(*) FROM rem)::integer
              INTO v_added, v_removed;

        ELSIF p_rescan THEN
            -- No boundary, so nothing can be inside it. Asked as SQL this would be
            -- st_intersects(NULL, geom) over the whole of basedata.abp, which a
            -- parameterised plan cannot fold away to zero rows.
            WITH rem AS (
                UPDATE projects.project_premises pp
                   SET state = 'removed', state_datetime = now()
                 WHERE pp.project_id = v_proj.pid
                   AND pp.source     = 'boundary'
                   AND pp.state     <> 'removed'
                RETURNING 1
            )
            SELECT (SELECT count(*) FROM rem)::integer INTO v_removed;
        END IF;

        RAISE NOTICE '057/refresh: project % — % added, % removed',
            v_proj.pid, v_added, v_removed;

        project_id   := v_proj.pid;
        uprn_added   := v_added;
        uprn_removed := v_removed;
        RETURN NEXT;
    END LOOP;

    -- ── Step 2: re-flag is_valid across ALL projects, for the affected UPRNs ─
    --
    -- Not just the project being refreshed: a child's boundary edit changes its
    -- parent's flags, so a refresh writes other projects' rows. That is the accepted
    -- cost of storing is_valid rather than deriving it.
    --
    -- The affected set is every UPRN held by a project in scope, in any state. That
    -- covers both halves of the rule — the UPRNs whose rows were just inserted or
    -- changed state, and the UPRNs those projects already held — because a tombstone
    -- is still a row. It is held as a CTE rather than a plpgsql array so the seed,
    -- which touches every UPRN in the table, does not build a million-element array.
    --
    -- THE RULE: a row is valid when no strictly-lower descendant has a live row for
    -- the same UPRN. depth > 0 is the whole of it, and it is depth-agnostic on
    -- purpose — it asks "is anything strictly below me claiming this", not "is my
    -- immediate child". So a ten-deep chain resolves to the deepest project with no
    -- extra work, and SIBLINGS ALL STAY VALID, because a sibling is not a descendant.
    -- There is deliberately NO TIE-BREAK: unrelated overlap is counted in both
    -- projects and flagged, never resolved.
    --
    -- Written as an anti-join rather than the plan's NOT EXISTS expression, which is
    -- the same rule algebraically. A sublink in a SET target list is never pulled into
    -- an anti-join by the planner, so it stays a per-row SubPlan — and each evaluation
    -- would re-scan the recursive vw_project_ancestors, once per row, for the ~1M rows
    -- the seed touches. Joined once, it is evaluated once.
    WITH affected AS MATERIALIZED (
        SELECT DISTINCT pp.uprn
          FROM projects.project_premises pp
         WHERE p_project_id IS NULL OR pp.project_id = p_project_id
    ), live AS (
        -- Every live claim on those UPRNs, from every project, not only those in scope.
        SELECT pp.uprn, pp.project_id
          FROM projects.project_premises pp
          JOIN affected af ON af.uprn = pp.uprn
         WHERE pp.state <> 'removed'
    ), covered AS MATERIALIZED (
        -- (uprn, project) pairs where some STRICT descendant of that project holds a
        -- live row for that uprn. Membership here is exactly what makes a row invalid.
        SELECT DISTINCT l.uprn, anc.ancestor_id AS project_id
          FROM live l
          JOIN projects.vw_project_ancestors anc
            ON anc.project_id = l.project_id
           AND anc.depth > 0
    ), target AS (
        SELECT pp.id, (c.uprn IS NULL) AS new_valid
          FROM projects.project_premises pp
          JOIN affected af ON af.uprn = pp.uprn
          LEFT JOIN covered c
            ON c.uprn = pp.uprn AND c.project_id = pp.project_id
    )
    UPDATE projects.project_premises pp
       SET is_valid = t.new_valid
      FROM target t
     WHERE t.id = pp.id
       AND pp.is_valid IS DISTINCT FROM t.new_valid;

    RETURN;
END;
$fn$;

COMMENT ON FUNCTION projects.refresh_project_premises(integer, boolean) IS
    'Resolves what each project boundary contains into projects.project_premises, then re-flags is_valid across every project holding the affected UPRNs. NULL project means all. p_rescan = false skips the spatial scan and only re-flags, which is the path a parent_project_id change takes. Shared by the triggers, the seed and sql/project_premises_resync.sql.';

-- ── 3. Triggers on projects.projects ────────────────────────────────────────
--
-- proposed_user comes from the row modified_user, which every write path populates:
-- project_save.php:100, map_boundary_update.php:170 via the field list, and
-- project_create_boundary.php:69. No dependency on the app.user_id GUC that 017
-- history triggers read. The triggers are AFTER, so the row is already updated and
-- reading modified_user inside the function gives NEW.modified_user.

CREATE FUNCTION public.fn_project_premises_refresh() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- The WHEN clause on the trigger has already established geom IS NOT NULL.
        PERFORM * FROM projects.refresh_project_premises(NEW.project_id, true);
    ELSIF NEW.geom IS DISTINCT FROM OLD.geom THEN
        PERFORM * FROM projects.refresh_project_premises(NEW.project_id, true);
    ELSE
        -- parent_project_id changed and geom did not: re-parenting changes a whole
        -- lineage's validity with no geometry change at all, so re-flag without a scan.
        PERFORM * FROM projects.refresh_project_premises(NEW.project_id, false);
    END IF;
    RETURN NULL;
END;
$$;

-- Covers project_create_boundary.php:69, which inserts a project with its geometry in
-- one statement.
CREATE TRIGGER trg_projects_premises_refresh_insert
    AFTER INSERT ON projects.projects
    FOR EACH ROW
    WHEN (NEW.geom IS NOT NULL)
    EXECUTE FUNCTION public.fn_project_premises_refresh();

-- The geom half is the recorded decision at improvement-opportunities.md:666: project
-- rows are UPDATEd on every ordinary field save and boundaries change rarely, so the
-- scan must only run on a real geometry change. The parent_project_id half is what
-- storing is_valid obliges.
CREATE TRIGGER trg_projects_premises_refresh_update
    AFTER UPDATE ON projects.projects
    FOR EACH ROW
    WHEN (OLD.geom IS DISTINCT FROM NEW.geom
       OR OLD.parent_project_id IS DISTINCT FROM NEW.parent_project_id)
    EXECUTE FUNCTION public.fn_project_premises_refresh();

-- ── 4. Seed ─────────────────────────────────────────────────────────────────
-- Comment this line out and run the same call in psql if the seed needs watching;
-- see the header.

SELECT * FROM projects.refresh_project_premises(NULL);

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

DO $$
DECLARE
    v_rows      bigint;
    v_projects  bigint;
    v_withgeom  bigint;
    v_badstate  bigint;
BEGIN
    IF to_regclass('projects.vw_project_ancestors') IS NULL THEN
        RAISE EXCEPTION '057: projects.vw_project_ancestors was not created';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_trigger
                    WHERE tgrelid = 'projects.projects'::regclass
                      AND tgname = 'trg_projects_premises_refresh_insert')
       OR NOT EXISTS (SELECT 1 FROM pg_trigger
                       WHERE tgrelid = 'projects.projects'::regclass
                         AND tgname = 'trg_projects_premises_refresh_update') THEN
        RAISE EXCEPTION '057: one or both refresh triggers are missing';
    END IF;

    SELECT count(*), count(DISTINCT pp.project_id)
      INTO v_rows, v_projects
      FROM projects.project_premises pp;

    SELECT count(*) INTO v_badstate
      FROM projects.project_premises pp
     WHERE pp.state <> 'approved';

    IF v_badstate > 0 THEN
        RAISE EXCEPTION '057: the seed produced % row(s) not in state approved', v_badstate;
    END IF;

    -- Not fatal on its own: a deployment can legitimately have no boundaries yet.
    SELECT count(*) INTO v_withgeom
      FROM projects.projects p
     WHERE p.geom IS NOT NULL;

    RAISE NOTICE '057: seeded % premise row(s) across % project(s); % project(s) have a boundary',
        v_rows, v_projects, v_withgeom;
    RAISE NOTICE '057: refresh function and both triggers in place; 058 adds the read views';
END $$;
