-- 059_project_premises_refresh_subtree.sql
-- Project premises, corrective migration
-- (docs/2026-08-17-project-premises-uprn-table.md)
--
-- BUG: giving a project a parent left stale is_valid flags on premises held by its
-- DESCENDANTS, which surfaced as a phantom overlap two levels up.
--
-- projects.refresh_project_premises() re-flags is_valid for an affected set of UPRNs.
-- That set was the UPRNs held by the project being refreshed. For a geometry change
-- that is right - only what the boundary gained or lost can move. For a parent change
-- it is not, because a project's parent decides the lineage of its ENTIRE SUBTREE, and
-- every UPRN claimed anywhere in that subtree can change validity.
--
-- Found while building a chain the ordinary way, bottom up (Dave, 2026-08-28). Three
-- standalone projects 4, 5 and 6, each with a boundary, then:
--
--     in 6, set 5 as parent   -> trigger fires for 6, scope = UPRNs held by 6.
--                                Correct: 5 goes invalid wherever 6 claims.
--     in 5, set 4 as parent   -> trigger fires for 5, scope = UPRNs held by 5.
--                                4 has just become an ancestor of 6 for the first
--                                time, but 6's UPRNs are not in that scope.
--
-- So a premise inside 4 and 6 but NOT 5 was never recomputed. It kept is_valid = true
-- at 4, set while 4 and 6 were unrelated, while 6 was validly claiming it too - two
-- valid claimants for one UPRN, which is the definition of unrelated overlap, so an
-- amber chip appeared on a premise that is simply delivered by a grandchild. Direct
-- children of 4 never showed it, because setting each of their parents re-flagged
-- their own UPRNs.
--
-- Note this is not specific to MOVING a project. The trigger condition is
-- OLD.parent_project_id IS DISTINCT FROM NEW.parent_project_id, which fires on
-- NULL -> value exactly as it does on a move, and the first-time case above is the one
-- that actually bit.
--
-- THE FIX is one predicate: the affected set becomes the UPRNs held by the project OR
-- ANY OF ITS DESCENDANTS. vw_project_ancestors already carries each project at depth 0,
-- so ancestor_id = p_project_id is exactly "this project and everything under it".
--
-- Old ancestors need no separate handling. The set is UPRNs, and the re-flag spans
-- every project holding one of them, so a project that stops being an ancestor is
-- recomputed on the same pass as the one that starts being one.
--
-- The function is otherwise unchanged from 057 and is repeated in full, because
-- CREATE OR REPLACE FUNCTION has no partial form.
--
-- THE REPAIR runs at the foot: refresh_project_premises(NULL, false) re-flags every
-- UPRN in the table with p_rescan = false, so it fixes every stale flag left by the old
-- scope without touching containment and without a single spatial query. Nothing else
-- is needed - no resync, no reseed.

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

DO $guard$
BEGIN
    IF to_regprocedure('projects.refresh_project_premises(integer, boolean)') IS NULL THEN
        RAISE EXCEPTION '059: projects.refresh_project_premises() is missing — apply 057 first';
    END IF;
    IF to_regclass('projects.vw_project_ancestors') IS NULL THEN
        RAISE EXCEPTION '059: projects.vw_project_ancestors is missing — apply 057 first';
    END IF;
END $guard$;

-- ── 1. Replace the function ─────────────────────────────────────────────────

CREATE OR REPLACE 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.
    --
    -- THE SET IS THE SUBTREE, NOT THE PROJECT. Corrected by migration 059: it used to
    -- read pp.project_id = p_project_id, which is wrong for a parent change, because a
    -- project's parent decides the lineage of everything beneath it too. Setting 5's
    -- parent to 4, where 6 is already a child of 5, fires the trigger for 5 only - so
    -- only UPRNs that 5 holds were re-flagged, and a premise inside 4 and 6 but NOT 5
    -- was never recomputed. It kept is_valid = true at 4 from while 4 and 6 were
    -- unrelated, while 6 was valid too, so it read as a two-project overlap that did
    -- not exist (Dave, 2026-08-28). See the file header for the full sequence.
    --
    -- vw_project_ancestors includes each project at depth 0, so filtering on
    -- ancestor_id = p_project_id returns the project itself plus every descendant.
    -- The OLD ancestors need no special handling: the set is UPRNs, and step 2 re-flags
    -- every project holding one of them, whatever its position now.
    WITH affected AS MATERIALIZED (
        SELECT DISTINCT pp.uprn
          FROM projects.project_premises pp
         WHERE p_project_id IS NULL
            OR pp.project_id IN (SELECT anc.project_id
                                   FROM projects.vw_project_ancestors anc
                                  WHERE anc.ancestor_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$;

-- ── 2. Repair the stale flags ───────────────────────────────────────────────
-- p_rescan = false, so this re-flags is_valid across every UPRN and does no spatial
-- work at all. Containment cannot change.

SELECT * FROM projects.refresh_project_premises(NULL, false);

-- ── 3. Verify ───────────────────────────────────────────────────────────────

DO $verify$
DECLARE
    v_src  text;
    v_fwd  bigint;
    v_back bigint;
BEGIN
    SELECT pg_get_functiondef('projects.refresh_project_premises(integer, boolean)'::regprocedure)
      INTO v_src;
    IF v_src NOT LIKE '%anc.ancestor_id = p_project_id%' THEN
        RAISE EXCEPTION '059: the replaced function does not carry the subtree predicate';
    END IF;

    -- The rule, both directions, across every row. Nothing may be valid while a strict
    -- descendant also claims it, and nothing may be invalid without one.
    SELECT count(*) INTO v_fwd
      FROM projects.project_premises a
      JOIN projects.vw_project_ancestors anc
           ON anc.ancestor_id = a.project_id AND anc.depth > 0
      JOIN projects.project_premises d
           ON d.project_id = anc.project_id AND d.uprn = a.uprn
     WHERE a.state = 'approved' AND d.state = 'approved' AND a.is_valid;

    SELECT count(*) INTO v_back
      FROM projects.project_premises a
     WHERE a.state = 'approved' AND NOT a.is_valid
       AND NOT EXISTS (SELECT 1
                         FROM projects.vw_project_ancestors anc
                         JOIN projects.project_premises d
                              ON d.project_id = anc.project_id
                             AND d.uprn = a.uprn
                             AND d.state = 'approved'
                        WHERE anc.ancestor_id = a.project_id AND anc.depth > 0);

    IF v_fwd > 0 OR v_back > 0 THEN
        RAISE EXCEPTION '059: is_valid is still inconsistent — % valid with a claiming descendant, % invalid with none',
            v_fwd, v_back;
    END IF;

    RAISE NOTICE '059: refresh scope widened to the subtree, and every is_valid flag re-checked clean';
END $verify$;
