-- 061_project_soft_delete.sql
-- Project delete & restore, migration 1 of 1
-- (docs/2026-08-28-project-delete-restore.md, Phase 0)
--
-- Adds projects.projects.is_deleted and makes the three things that must notice it
-- notice it: the history trigger, the premises refresh, and the list view. Inert on its
-- own — nothing sets the flag until the endpoint arrives in Phase 2, so applying this
-- changes no behaviour and no row.
--
-- ONE COLUMN, NOT THREE. There is deliberately no deleted_user / deleted_datetime pair.
-- A delete is an ordinary UPDATE, so projects_update_modified_fields() stamps
-- modified_datetime and the endpoint sets modified_user in its own statement, exactly as
-- project_save.php and map_boundary_update.php already do. Those two values stay the
-- delete's values for as long as the project remains deleted, because the endpoint phase
-- refuses every write path against a deleted project. The user-visible record is a
-- projects.project_journal row written by the same transaction; projects_history is the
-- authority if the two ever disagree (Dave, 2026-08-29).
--
-- THE HISTORY FUNCTION IS THE SHARP EDGE of this migration, and it fails silently.
-- fn_projects_history() opens with a no-op suppression block comparing every column it
-- knows about, and modified_user / modified_datetime are excluded from that compare on
-- purpose — they bump on every update, so including them would defeat the suppression
-- entirely. In a delete, is_deleted is therefore the ONLY changed column the compare
-- could ever see. Without it in that list the function returns early and the delete
-- writes no history row at all: the feature works, the audit is empty, and nothing
-- errors. Section 3 adds it to the compare, and to the INSERT column and VALUES lists so
-- the flag is actually stored.
--
-- THE PREMISES TRIGGER NEEDS TWO CHANGES, NOT ONE. Adding is_deleted to the trigger's
-- WHEN clause is necessary but not sufficient: fn_project_premises_refresh() branches on
-- what changed, and a delete alters neither geom nor parent_project_id, so it would fall
-- through to the ELSE — the re-parent branch — and be refreshed with p_rescan = false.
-- That re-flags is_valid and touches containment not at all, so the deleted project would
-- keep every one of its approved premise rows and go on counting as a claimant. Section 4
-- puts is_deleted alongside the geom test so a delete and a restore both rescan.
--
-- HOW THE TOMBSTONING ACTUALLY HAPPENS: refresh_project_premises() already has a branch
-- for a project with no boundary, which removes every boundary row without running a
-- spatial query. Section 5 selects the project's geom through
-- CASE WHEN p.is_deleted THEN NULL ELSE p.geom END, so a deleted project takes that
-- branch and its rows go to state = 'removed'. Step 2's is_valid re-flag then runs over
-- the affected UPRNs exactly as it does for any other change, so a parent that was
-- showing a premise as invalid because its now-deleted child claimed it goes valid again
-- on the same pass.
--
-- Restore is the same mechanism in reverse and needs no separate code path. geom comes
-- back, the rows revive from 'removed' through the existing ON CONFLICT clause, and
-- proposed_datetime is untouched — so "when did this premise first enter this boundary"
-- survives an archive and a restore, as it already survives a shrink and a regrow.
--
-- The backstops come along for free. sql/project_premises_resync.sql and step 4 of
-- sql/abp_sync.sql both call refresh_project_premises(NULL), so an address sync cannot
-- resurrect a deleted project's premises.
--
-- NO REPAIR PASS. Nothing is deleted at migration time, so there is no existing state to
-- correct and no seed to run. 057 and 059 each end with a full refresh; this one has
-- nothing to refresh.
--
-- Three functions and a view are repeated in full because neither CREATE OR REPLACE
-- FUNCTION nor CREATE OR REPLACE VIEW has a partial form, and a trigger's WHEN clause
-- cannot be altered — it has to be dropped and recreated. Everything outside the marked
-- additions is verbatim from 017, 057, 059 and 060.

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

DO $guard$
BEGIN
    IF to_regclass('projects.projects') IS NULL THEN
        RAISE EXCEPTION '061: projects.projects is missing';
    END IF;
    IF to_regclass('projects.projects_history') IS NULL THEN
        RAISE EXCEPTION '061: projects.projects_history is missing';
    END IF;
    IF to_regproc('public.fn_projects_history') IS NULL THEN
        RAISE EXCEPTION '061: public.fn_projects_history() is missing — apply 017 first';
    END IF;
    IF to_regproc('public.fn_project_premises_refresh') IS NULL THEN
        RAISE EXCEPTION '061: public.fn_project_premises_refresh() is missing — apply 057 first';
    END IF;
    IF to_regprocedure('projects.refresh_project_premises(integer, boolean)') IS NULL THEN
        RAISE EXCEPTION '061: projects.refresh_project_premises() is missing — apply 057 first';
    END IF;
    IF to_regclass('projects.vw_projects_list') IS NULL THEN
        RAISE EXCEPTION '061: projects.vw_projects_list is missing';
    END IF;
    -- 060 must be in: this replaces its view definition and would otherwise silently
    -- drop top_project_id back off it.
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'projects' AND table_name = 'vw_projects_list'
                      AND column_name = 'top_project_id') THEN
        RAISE EXCEPTION '061: vw_projects_list has no top_project_id — apply 060 first';
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'projects' AND table_name = 'projects'
                  AND column_name = 'is_deleted') THEN
        RAISE EXCEPTION '061 has already run — projects.projects carries is_deleted';
    END IF;
END $guard$;

-- ── 1. The column ───────────────────────────────────────────────────────────

ALTER TABLE projects.projects
    ADD COLUMN is_deleted boolean NOT NULL DEFAULT false;

-- Nullable on the history mirror, with no default: rows written before this migration
-- genuinely do not know what the flag was, and false would be a claim rather than a
-- fact. Every row written from here on carries the real value.
ALTER TABLE projects.projects_history
    ADD COLUMN is_deleted boolean;

COMMENT ON COLUMN projects.projects.is_deleted IS
    'Soft delete. true hides the project from every list, picker, map layer and count, '
    'and refuses every write against it; the row, its journal, its history, its '
    'attachments and its premises tombstones all stay. Set only by '
    'fn/project_archive.php, which requires the projects_approve permission and refuses '
    'a project that is part of a hierarchy. Never a form field: it is absent from '
    '$staticFields in project_save.php on purpose, because a saveable is_deleted would '
    'be a delete with no permission check, no blocker checks and no journal entry.';

COMMENT ON COLUMN projects.projects_history.is_deleted IS
    'Mirror of projects.projects.is_deleted. NULL on rows written before migration 061.';

-- ── 2. Index ────────────────────────────────────────────────────────────────
--
-- Partial, deliberately, and unlike wayleaves_is_deleted_idx (032), which indexes the
-- whole column. The predicate that will appear in a dozen queries is is_deleted = false,
-- which matches very nearly every row and will never use an index whatever shape it is.
-- The only selective question is "show me the archive", and a partial index answers
-- exactly that while staying a few pages wide however large the estate grows.

CREATE INDEX projects_is_deleted_idx ON projects.projects (is_deleted) WHERE is_deleted;

-- ── 3. public.fn_projects_history() ─────────────────────────────────────────
--
-- 017's function with is_deleted added in three places, all marked. trg_projects_history
-- keeps pointing here; only the body changes.

CREATE OR REPLACE FUNCTION public.fn_projects_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    projects.projects;
    v_user   integer;
BEGIN
    -- No-op suppression: nothing meaningful changed (modified_* always bumps
    -- via update_modified_fields_trigger, so it is excluded from the compare)
    IF TG_OP = 'UPDATE'
       AND NEW.project_id        IS NOT DISTINCT FROM OLD.project_id
       AND NEW.parent_project_id IS NOT DISTINCT FROM OLD.parent_project_id
       AND NEW.project_name      IS NOT DISTINCT FROM OLD.project_name
       AND NEW.project_status_id IS NOT DISTINCT FROM OLD.project_status_id
       AND NEW.project_manager   IS NOT DISTINCT FROM OLD.project_manager
       AND NEW.geom              IS NOT DISTINCT FROM OLD.geom
       AND NEW.created_datetime  IS NOT DISTINCT FROM OLD.created_datetime
       AND NEW.created_user      IS NOT DISTINCT FROM OLD.created_user
       AND NEW.company_id        IS NOT DISTINCT FROM OLD.company_id
       AND NEW.account_id        IS NOT DISTINCT FROM OLD.account_id
       AND NEW.stocklist_id      IS NOT DISTINCT FROM OLD.stocklist_id
       AND NEW.cover_image_url   IS NOT DISTINCT FROM OLD.cover_image_url
       -- 061: without this line a delete is a no-op to this function and writes no
       -- history row at all, because is_deleted is the only column it changes.
       AND NEW.is_deleted        IS NOT DISTINCT FROM OLD.is_deleted
    THEN
        RETURN NEW;
    END IF;

    v_action := TG_OP;

    -- For DELETE, capture the old row; otherwise capture the new row
    IF TG_OP = 'DELETE' THEN
        v_row := OLD;
    ELSE
        v_row := NEW;
    END IF;

    -- Actor: app.user_id GUC (set per request by www/fn/db.php), falling back
    -- to the row's modified_user; malformed values degrade, never abort
    IF current_setting('app.user_id', true) ~ '^\d+$' THEN
        v_user := current_setting('app.user_id', true)::integer;
    ELSE
        v_user := v_row.modified_user;
    END IF;

    INSERT INTO projects.projects_history (
        project_id,
        parent_project_id,
        project_name,
        project_status_id,
        project_manager,
        geom,
        created_datetime,
        created_user,
        modified_datetime,
        modified_user,
        company_id,
        account_id,
        stocklist_id,
        cover_image_url,
        is_deleted,           -- 061
        history_action,
        history_datetime,
        history_user
    ) VALUES (
        v_row.project_id,
        v_row.parent_project_id,
        v_row.project_name,
        v_row.project_status_id,
        v_row.project_manager,
        v_row.geom,
        v_row.created_datetime,
        v_row.created_user,
        v_row.modified_datetime,
        v_row.modified_user,
        v_row.company_id,
        v_row.account_id,
        v_row.stocklist_id,
        v_row.cover_image_url,
        v_row.is_deleted,     -- 061
        v_action,
        now(),
        v_user
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

-- ── 4. The premises trigger ─────────────────────────────────────────────────
--
-- 057's function with is_deleted joined to the geom test, so a delete and a restore both
-- take the rescan branch. Left in the ELSE it would get p_rescan = false, which re-flags
-- validity and never touches containment — see the header.

CREATE OR REPLACE 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
       -- 061: a delete resolves the project's geom to NULL inside the refresh, so this
       -- branch tombstones its boundary rows; a restore rescans and revives them.
       OR NEW.is_deleted IS DISTINCT FROM OLD.is_deleted 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;
$$;

-- A trigger's WHEN clause cannot be altered, so the update trigger is dropped and
-- recreated. The insert trigger is untouched: a project is never created deleted.
DROP TRIGGER IF EXISTS trg_projects_premises_refresh_update ON projects.projects;

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
       OR OLD.is_deleted IS DISTINCT FROM NEW.is_deleted)
    EXECUTE FUNCTION public.fn_project_premises_refresh();

-- ── 5. projects.refresh_project_premises() ──────────────────────────────────
--
-- 059's function with one changed expression, marked below: the loop reads the project's
-- geom through a CASE that resolves to NULL for a deleted project, which routes it into
-- the existing no-boundary branch. Everything else is verbatim.

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.
    --
    -- 061: a deleted project reports no boundary, so it takes the ELSIF branch below and
    -- every boundary row it holds is tombstoned. Restoring it puts the real geometry
    -- back and the same branch structure revives them. This is also what makes
    -- sql/project_premises_resync.sql and step 4 of sql/abp_sync.sql safe — both call
    -- this function, so neither can resurrect a deleted project's premises.
    FOR v_proj IN
        SELECT p.project_id AS pid,
               CASE WHEN p.is_deleted THEN NULL ELSE p.geom END AS geom,   -- 061
               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 059's 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$;

-- ── 6. projects.vw_projects_list ────────────────────────────────────────────
--
-- 060's definition with one added predicate, marked below.
--
-- NOTHING ELSE IN IT NEEDS THE FLAG, and adding it would be redundant rather than
-- harmless:
--
--   * the prems CTE and its inline cl claimant subquery both filter state = 'approved',
--     and a deleted project's premise rows are tombstoned to 'removed' by section 5, so
--     it already contributes nothing to any count. Per 060's own note that CTE sits
--     behind a LEFT JOIN where no filter can be pushed anyway.
--   * the pp (parent name), tp (top project) and in_hierarchy EXISTS all reach
--     projects.projects through a hierarchy link — and a project must be free of every
--     hierarchy link before it can be deleted, so none of them can ever resolve to a
--     deleted project.
--
-- projects.vw_project_premises and projects.vw_premise_claimants need no change for the
-- same tombstone reason. Do not add a join to projects.projects there: vw_premise_claimants
-- aggregates every UPRN in the database, and 059's repair exists because of what that
-- costs.

CREATE OR REPLACE VIEW projects.vw_projects_list AS
 WITH prems AS (
         SELECT p_1.project_id,
            count(pp.uprn) AS total_premises,
            count(
                CASE
                    WHEN bt.building_type = 'MDU'::text THEN 1
                    ELSE NULL::integer
                END) AS mdu_premises,
            count(
                CASE
                    WHEN bt.building_type = 'SMDU'::text THEN 1
                    ELSE NULL::integer
                END) AS smdu_premises,
            count(
                CASE
                    WHEN bt.building_type = 'SDU'::text THEN 1
                    ELSE NULL::integer
                END) AS sdu_premises,
            count(
                CASE
                    WHEN bd.uprn IS NOT NULL THEN 1
                    ELSE NULL::integer
                END) AS bduk_vouchers,
            count(
                CASE
                    WHEN bd.uprn IS NOT NULL AND bt.building_type = 'MDU'::text THEN 1
                    ELSE NULL::integer
                END) AS bduk_vouchers_mdu,
            count(
                CASE
                    WHEN pp.is_valid THEN 1
                    ELSE NULL::integer
                END) AS valid_premises,
            count(
                CASE
                    WHEN NOT pp.is_valid THEN 1
                    ELSE NULL::integer
                END) AS dependant_premises,
            count(
                CASE
                    WHEN pp.is_valid AND cl.claimant_count > 1 THEN 1
                    ELSE NULL::integer
                END) AS contested_premises,
            count(
                CASE
                    WHEN pp.is_valid AND bt.building_type = 'MDU'::text THEN 1
                    ELSE NULL::integer
                END) AS valid_mdu_premises,
            count(
                CASE
                    WHEN pp.is_valid AND bt.building_type = 'SDU'::text THEN 1
                    ELSE NULL::integer
                END) AS valid_sdu_premises
           FROM projects.projects p_1
             LEFT JOIN projects.project_premises pp
                    ON pp.project_id = p_1.project_id AND pp.state = 'approved'
             LEFT JOIN basedata.abp ON abp.uprn = pp.uprn
             LEFT JOIN basedata.toid_building_type bt ON abp.cross_reference::text = bt.toid::text
             LEFT JOIN adhoc.bduk_uprn_tracking_v2 bd ON pp.uprn = bd.uprn
             -- The claimant count only, aggregated inline rather than joined from
             -- vw_premise_claimants: this CTE sits behind a LEFT JOIN so no filter can
             -- ever be pushed into it and it is always computed in full, which makes
             -- set-based right here and a correlated probe wrong. Only the count is
             -- wanted, and materialising that view's array_agg and string_agg for every
             -- UPRN in the database to reach it would be pure waste.
             LEFT JOIN (
                SELECT c.uprn, count(*) AS claimant_count
                  FROM projects.project_premises c
                 WHERE c.state = 'approved' AND c.is_valid
                 GROUP BY c.uprn
             ) cl ON cl.uprn = pp.uprn
          GROUP BY p_1.project_id
        )
 SELECT p.project_id,
    COALESCE(p.parent_project_id::text, ''::text) AS parent_project_id,
    COALESCE(pp.project_name::text, ''::text) AS parent_project_name,
    p.project_name,
    p.project_status_id,
    COALESCE(ps.project_status_desc, ''::character varying) AS project_status_desc,
    p.project_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    to_char(p.created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS created_date,
    p.created_user,
    COALESCE(uc.username, ''::character varying) AS created_username,
    to_char(p.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    p.modified_user,
    um.username AS modified_username,
    COALESCE(c.company_name, ''::character varying) AS contractor_name,
    p.company_id,
    p.account_id,
    COALESCE(a.account_name, ''::character varying) AS account_name,
    prems.total_premises,
    prems.mdu_premises,
    prems.smdu_premises,
    prems.sdu_premises,
    prems.bduk_vouchers,
    prems.bduk_vouchers_mdu,
    COALESCE(upm.username, ''::character varying) AS project_manager_name,
    COALESCE(c.company_name, ''::character varying) AS company_name,
    p.cover_image_url,
    prems.valid_premises,
    prems.dependant_premises,
    prems.contested_premises,
    prems.valid_mdu_premises,
    prems.valid_sdu_premises,
    -- Drives amber-vs-red on the overlap flag: a project in a hierarchy shows amber,
    -- a standalone project red, because giving the standalone one a parent resolves
    -- the overlap through the validity rule and costs one field.
    (p.parent_project_id IS NOT NULL
     OR EXISTS (SELECT 1 FROM projects.projects ch
                 WHERE ch.parent_project_id = p.project_id)) AS in_hierarchy,
    -- The root of this project's lineage, which for a project with no parent is
    -- itself. Filtering a list on it returns a whole programme in one go, which
    -- parent_project_name cannot do: that only ever finds direct children.
    rt.top_project_id,
    COALESCE(tp.project_name::text, ''::text) AS top_project_name
   FROM projects.projects p
     LEFT JOIN users.users um ON p.modified_user = um.id
     LEFT JOIN users.users uc ON p.created_user = uc.id
     LEFT JOIN users.users upm ON p.project_manager = upm.id
     LEFT JOIN projects.project_status ps ON p.project_status_id = ps.project_status_id
     LEFT JOIN projects.projects pp ON p.parent_project_id = pp.project_id
     LEFT JOIN users.companies c ON p.company_id = c.company_id
     LEFT JOIN accounts.accounts a ON a.account_id = p.account_id
     LEFT JOIN prems ON p.project_id = prems.project_id
     -- The ancestor at the greatest depth is the root. vw_project_ancestors carries
     -- every project at depth 0 as its own ancestor, so a project with no parent
     -- resolves to itself and the column is never empty.
     LEFT JOIN (
        SELECT DISTINCT ON (anc.project_id)
               anc.project_id,
               anc.ancestor_id AS top_project_id
          FROM projects.vw_project_ancestors anc
         ORDER BY anc.project_id, anc.depth DESC
     ) rt ON rt.project_id = p.project_id
     LEFT JOIN projects.projects tp ON tp.project_id = rt.top_project_id
  WHERE p.is_deleted = false;   -- 061

-- ── 7. Verify ───────────────────────────────────────────────────────────────

DO $verify$
DECLARE
    v_src   text;
    v_when  text;
    v_count bigint;
BEGIN
    -- Columns and index.
    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 '061: projects.projects.is_deleted was not added';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'projects' AND table_name = 'projects_history'
                      AND column_name = 'is_deleted') THEN
        RAISE EXCEPTION '061: projects.projects_history.is_deleted was not added';
    END IF;
    IF to_regclass('projects.projects_is_deleted_idx') IS NULL THEN
        RAISE EXCEPTION '061: projects_is_deleted_idx was not created';
    END IF;

    -- Nothing is deleted yet: this migration must not have changed a single row.
    SELECT count(*) INTO v_count FROM projects.projects WHERE is_deleted;
    IF v_count > 0 THEN
        RAISE EXCEPTION '061: % project(s) came out of this migration flagged deleted', v_count;
    END IF;

    -- The history function must carry is_deleted in BOTH lists. The compare is the one
    -- that fails silently, so it is checked by its exact text rather than by presence.
    SELECT pg_get_functiondef('public.fn_projects_history()'::regprocedure) INTO v_src;
    IF v_src NOT LIKE '%NEW.is_deleted        IS NOT DISTINCT FROM OLD.is_deleted%' THEN
        RAISE EXCEPTION '061: fn_projects_history() does not compare is_deleted — a delete would write no history row';
    END IF;
    IF v_src NOT LIKE '%v_row.is_deleted%' THEN
        RAISE EXCEPTION '061: fn_projects_history() does not store is_deleted';
    END IF;

    -- The premises trigger function must rescan on an is_deleted change, not fall to
    -- the ELSE branch.
    SELECT pg_get_functiondef('public.fn_project_premises_refresh()'::regprocedure) INTO v_src;
    IF v_src NOT LIKE '%OR NEW.is_deleted IS DISTINCT FROM OLD.is_deleted THEN%' THEN
        RAISE EXCEPTION '061: fn_project_premises_refresh() would not rescan on a delete';
    END IF;

    -- And the trigger must actually fire for it.
    SELECT pg_get_triggerdef(oid) INTO v_when
      FROM pg_trigger
     WHERE tgrelid = 'projects.projects'::regclass
       AND tgname  = 'trg_projects_premises_refresh_update';
    IF v_when IS NULL OR v_when NOT LIKE '%is_deleted%' THEN
        RAISE EXCEPTION '061: trg_projects_premises_refresh_update does not fire on is_deleted';
    END IF;

    -- The refresh must resolve a deleted project to a NULL boundary.
    SELECT pg_get_functiondef('projects.refresh_project_premises(integer, boolean)'::regprocedure)
      INTO v_src;
    IF v_src NOT LIKE '%CASE WHEN p.is_deleted THEN NULL ELSE p.geom END%' THEN
        RAISE EXCEPTION '061: refresh_project_premises() does not blank a deleted project boundary';
    END IF;

    -- The view still works, still carries 060's columns, and now filters.
    PERFORM 1 FROM projects.vw_projects_list LIMIT 1;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'projects' AND table_name = 'vw_projects_list'
                      AND column_name = 'top_project_id') THEN
        RAISE EXCEPTION '061: vw_projects_list lost top_project_id — 060 was overwritten';
    END IF;
    IF pg_get_viewdef('projects.vw_projects_list'::regclass) NOT LIKE '%is_deleted%' THEN
        RAISE EXCEPTION '061: vw_projects_list does not filter is_deleted';
    END IF;

    -- Every project is still listed, since none is deleted.
    SELECT count(*) INTO v_count
      FROM projects.projects p
     WHERE NOT EXISTS (SELECT 1 FROM projects.vw_projects_list v
                        WHERE v.project_id = p.project_id);
    IF v_count > 0 THEN
        RAISE EXCEPTION '061: % project(s) dropped out of vw_projects_list', v_count;
    END IF;

    RAISE NOTICE '061: is_deleted added; history, premises refresh and list view updated';
END $verify$;
