-- 060_projects_list_top_project.sql
-- Adds top_project_id / top_project_name to projects.vw_projects_list.
--
-- The list already carries parent_project_name, which answers "who is directly above
-- this". It cannot answer "show me everything in this programme": filtering on a parent
-- returns its direct children and stops, so a three-deep estate needs three filters and
-- the user has to know the shape before they can ask. The root of the lineage is one
-- value shared by every project in the tree, so filtering on it returns the whole set.
--
-- A project with no parent is its own top-level project rather than blank. That is what
-- makes the column filterable as a single rule - every row has a value, and a
-- standalone project is a programme of one.
--
-- Derived, not stored: it is a second reading of parent_project_id, and a stored copy
-- would need maintaining on every re-parent for no gain. vw_project_ancestors already
-- carries every project against each of its ancestors with a depth, so the root is the
-- ancestor at the greatest depth - one DISTINCT ON over a view of roughly projects x
-- average depth, computed once per query.
--
-- Cycle behaviour follows vw_project_ancestors: its path array stops A -> B -> A from
-- recursing, so the walk terminates and the deepest ancestor reached becomes the root.
-- Arbitrary in that case, but bounded, and project_save.php blocks the self-parent case
-- at source.
--
-- CREATE OR REPLACE VIEW, so the two columns are appended after in_hierarchy rather
-- than slotted in beside parent_project_name where they would read better. Definition
-- is 058's with only those additions.

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

DO $guard$
BEGIN
    IF to_regclass('projects.vw_projects_list') IS NULL THEN
        RAISE EXCEPTION '060: projects.vw_projects_list is missing';
    END IF;
    IF to_regclass('projects.vw_project_ancestors') IS NULL THEN
        RAISE EXCEPTION '060: projects.vw_project_ancestors is missing — apply 057 first';
    END IF;
    IF 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 '060 has already run — vw_projects_list carries top_project_id';
    END IF;
END $guard$;

-- ── 1. projects.vw_projects_list ────────────────────────────────────────────

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;

-- ── 2. Verify ───────────────────────────────────────────────────────────────

DO $verify$
DECLARE
    v_missing bigint;
    v_selfish bigint;
BEGIN
    PERFORM 1 FROM projects.vw_projects_list LIMIT 1;

    -- Every project resolves to a root, and a project with no parent is its own.
    SELECT count(*) INTO v_missing
      FROM projects.vw_projects_list
     WHERE top_project_id IS NULL;

    SELECT count(*) INTO v_selfish
      FROM projects.vw_projects_list v
      JOIN projects.projects p ON p.project_id = v.project_id
     WHERE p.parent_project_id IS NULL
       AND v.top_project_id IS DISTINCT FROM v.project_id;

    IF v_missing > 0 OR v_selfish > 0 THEN
        RAISE EXCEPTION '060: % row(s) with no top project, % standalone row(s) not their own top',
            v_missing, v_selfish;
    END IF;

    RAISE NOTICE '060: top_project_id / top_project_name added to vw_projects_list';
END $verify$;
