-- 058_project_premises_views.sql
-- Project premises, migration 3 of 3
-- (docs/2026-08-17-project-premises-uprn-table.md, Phase 3)
--
-- The two read views, and the four existing views repointed off the live spatial join
-- onto the table 057 populates. After this migration the numbers on screen come from
-- projects.project_premises. Phase 4 does the same for the endpoint queries.
--
-- In every case the GiST spatial join
--     LEFT JOIN basedata.abp ON st_intersects(p_1.geom, abp.geom)
-- becomes an equality join on abp_uprn_idx
--     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
-- and abp is retained only where cross_reference is needed for the building-type
-- lookup.
--
-- CREATE OR REPLACE VIEW throughout, never DROP + CREATE, so dependent grants and
-- anything referencing these views survive (039's note). That constrains the shape:
-- every existing column must keep its name, type and position, so new columns are
-- APPENDED at the end even where they would read better beside their relatives.
--
-- THE VIEWS MUST STAY FILTERABLE. This is migration 048's lesson, and it decides
-- three places below. A view that aggregates over every row in the table and lets the
-- caller filter afterwards cannot have WHERE project_id = ? pushed into it: Postgres
-- plans a subquery once and never builds a parameterised path for it, so a join qual
-- on the grouping column reaches the aggregate too late. In 048 that cost 598ms of
-- JIT on a 675ms query. Here it would mean a HashAggregate over every UPRN in the
-- database - with an array_agg and a string_agg per group - on every project editor
-- load and every stocklist premise search. So vw_premise_claimants is created as the
-- canonical, documented answer to "who claims this premise" and is used where the
-- read is genuinely estate-wide, while the three consumers that always filter use the
-- correlated form of the same question, which is one index probe per returned row on
-- project_premises_uprn_state_idx. Same rule, same numbers, cost proportional to what
-- is asked for.
--
-- TWO NUMBERS CHANGE ON PURPOSE, both from wrong to right. A project with no premises
-- goes from total_premises = 1 to 0, because count(*) over a LEFT JOIN chain counted
-- the empty row. And an opportunity linked to both a parent and its child sees
-- total_premises fall, because the shared premise stops being counted twice.

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

DO $$
BEGIN
    IF to_regclass('projects.vw_project_ancestors') IS NULL THEN
        RAISE EXCEPTION '058: projects.vw_project_ancestors is missing — apply 057 first';
    END IF;
    IF to_regclass('projects.project_premises') IS NULL THEN
        RAISE EXCEPTION '058: projects.project_premises is missing — apply 056 and 057 first';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM projects.project_premises) THEN
        RAISE EXCEPTION '058: projects.project_premises is empty — 057 seed did not run, and these views would report zero premises for every project';
    END IF;
    IF to_regclass('projects.vw_premise_claimants') IS NOT NULL THEN
        RAISE EXCEPTION '058 has already run — projects.vw_premise_claimants exists';
    END IF;
END $$;

-- ── 1. projects.vw_premise_claimants ────────────────────────────────────────
--
-- One row per UPRN, answering who claims this premise. WHERE is_valid is what makes
-- claimant_count = 1 the normal case: a premise inside a parent and three nested
-- descendants has four rows in the table and ONE valid claimant here. So
-- claimant_count > 1 means genuine unrelated overlap - siblings, or projects in
-- different trees - which is exactly the red-flag condition.
--
-- Estate-wide by construction, so read it for estate-wide questions. A consumer
-- filtering to one project or one stocklist wants the correlated form instead; see
-- the header.

CREATE VIEW projects.vw_premise_claimants AS
SELECT pp.uprn,
       count(*) AS claimant_count,
       -- NULL when contested, ON PURPOSE. There is no tie-break, so a single owner
       -- does not exist to name; forcing NULL makes the ambiguity impossible for a
       -- consumer to ignore, where an arbitrary first() would read as an answer.
       CASE WHEN count(*) = 1 THEN min(pp.project_id) END AS project_id,
       array_agg(pp.project_id ORDER BY pp.project_id)     AS project_ids,
       string_agg(p.project_name::text, ', ' ORDER BY pp.project_id) AS project_names
  FROM projects.project_premises pp
  JOIN projects.projects p ON p.project_id = pp.project_id
 WHERE pp.state = 'approved' AND pp.is_valid
 GROUP BY pp.uprn;

COMMENT ON VIEW projects.vw_premise_claimants IS
    'One row per UPRN: how many projects validly claim it, and which. claimant_count = 1 is normal; > 1 is unrelated overlap, which is flagged and never tie-broken, so project_id is NULL there by design. Aggregates the whole table - a consumer filtering to one project or stocklist should ask the same question correlated instead, or it computes every UPRN in the database and filters afterwards.';

-- ── 2. projects.vw_project_premises ─────────────────────────────────────────
--
-- Premise identity and flags, the live set only.
--
-- Two deliberate omissions. No state column: the view IS the live set, so a state
-- column on it would only ever read approved, and anything wanting tombstones reads
-- the table. And no building-type join: project_load.php joins
-- basedata.toid_building_type_geom while the list views join
-- basedata.toid_building_type, so putting either here would silently move somebody
-- MDU/SDU numbers. Each consumer keeps the join it has today.

CREATE VIEW projects.vw_project_premises AS
SELECT pp.project_id, pp.uprn, pp.is_valid,
       -- Correlated rather than joined to vw_premise_claimants, so WHERE project_id =
       -- ? reaches the base table and this costs one index probe per returned row
       -- instead of aggregating every UPRN in the database. Same definition as that
       -- view: approved and valid claims on this UPRN.
       (SELECT count(*)
          FROM projects.project_premises c
         WHERE c.uprn = pp.uprn AND c.state = 'approved' AND c.is_valid) AS claimant_count,
       -- The viewing project's role for this premise, NULL where no lineage
       -- relationship exists for this UPRN. Parent falls straight out of NOT is_valid,
       -- because the only way to be invalid is for a descendant to hold the premise.
       -- Child needs the ancestor probe, because being valid does not by itself mean
       -- anything above you contains the premise: a standalone project is valid with
       -- no role at all.
       CASE WHEN pp.is_valid AND EXISTS (
                SELECT 1 FROM projects.project_premises a
                  JOIN projects.vw_project_ancestors anc
                       ON anc.project_id = pp.project_id
                      AND anc.ancestor_id = a.project_id
                      AND anc.depth > 0
                 WHERE a.uprn = pp.uprn AND a.state <> 'removed')
            THEN 'Child'
            WHEN NOT pp.is_valid THEN 'Parent'
       END AS hierarchy_role,
       abp.address, abp.postcode, abp.classification_code, abp.class_description,
       abp.cross_reference AS toid_reference, abp.udprn, abp.geom
  FROM projects.project_premises pp
  JOIN basedata.abp abp ON abp.uprn = pp.uprn
 WHERE pp.state = 'approved';

COMMENT ON VIEW projects.vw_project_premises IS
    'The live premise set per project, with both flags: hierarchy_role (Child counted here, Parent counted below, NULL where no lineage relationship exists for the UPRN) and claimant_count (> 1 is unrelated overlap). Filter it by project_id - both derived columns are correlated so the cost follows the rows returned.';

-- ── 3. projects.vw_projects_list ────────────────────────────────────────────
--
-- Definition from 039 with the premise source replaced and six columns appended.
-- count(*) becomes count(pp.uprn), which is what fixes a project with no premises
-- reporting 1 instead of 0. bduk_uprn_tracking_v2 joins pp.uprn directly and needs no
-- abp hop.
--
-- NO COLUMN HERE SUMS CLEANLY ESTATE-WIDE, AND THAT IS THE DESIGN.
-- sum(valid_premises) over-counts by exactly sum(contested_premises), which is the
-- reported size of the error rather than a hidden one, and it goes to zero as
-- overlaps get fixed.

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
   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;

-- ── 4. opportunity.vw_opportunity_list ──────────────────────────────────────
--
-- Definition from 039 with the premise source replaced through
-- opportunity_project_link, and valid_premises appended.
--
-- count(DISTINCT pp.uprn) is the double-counting fix: an opportunity linked to both a
-- parent and its child counts the shared premise once. DISTINCT uprn rather than
-- is_valid is right here, because an opportunity that links only the parent still
-- genuinely covers premises that are not valid AT the parent - and DISTINCT also
-- absorbs unrelated overlap, so an opportunity total is clean even where the
-- underlying projects are contested.
--
-- mdu/sdu/other take DISTINCT for the same reason as the total. The plan names only
-- total_premises, but mdu_premises is the DIVISOR in total_mtr_per_mdu_prem - the
-- money figure the double count was distorting - so leaving it counting rows would
-- fix the total and leave the metric exactly as wrong as before, with the parts no
-- longer summing to the whole.
--
-- The join to projects.projects is gone: its only consumer was st_intersects on
-- p.geom. project_premises hangs off opl.project_id directly, and a link to a project
-- that no longer exists produces no premise rows either way.

CREATE OR REPLACE VIEW opportunity.vw_opportunity_list AS
 WITH r AS (
         SELECT rt.opportunity_id,
            rt.edge,
            rt.shape_length,
            count(*) OVER (PARTITION BY rt.edge, rt.opportunity_id) AS edge_used_count,
            row_number() OVER (PARTITION BY rt.edge, rt.opportunity_id ORDER BY rt.route_id, rt.seq) AS r
           FROM opportunity.opportunity_routes rt
        ), pr AS (
         SELECT o_1.opportunity_id,
            count(DISTINCT opl.project_id) AS project_count,
            count(DISTINCT pp.uprn) AS total_premises,
            count(DISTINCT
                CASE
                    WHEN bt.building_type = 'MDU'::text THEN pp.uprn
                    ELSE NULL::bigint
                END) AS mdu_premises,
            count(DISTINCT
                CASE
                    WHEN bt.building_type = 'SDU'::text THEN pp.uprn
                    ELSE NULL::bigint
                END) AS sdu_premises,
            count(DISTINCT
                CASE
                    WHEN bt.building_type <> ALL (ARRAY['MDU'::text, 'SDU'::text]) THEN pp.uprn
                    ELSE NULL::bigint
                END) AS other_premises,
            count(DISTINCT
                CASE
                    WHEN pp.is_valid THEN pp.uprn
                    ELSE NULL::bigint
                END) AS valid_premises
           FROM opportunity.opportunities o_1
             LEFT JOIN opportunity.opportunity_project_link opl ON o_1.opportunity_id = opl.opportunity_id AND opl.is_deleted = false
             LEFT JOIN projects.project_premises pp ON pp.project_id = opl.project_id AND pp.state = 'approved'
             LEFT JOIN basedata.abp a ON a.uprn = pp.uprn
             LEFT JOIN basedata.toid_building_type bt ON a.cross_reference::text = bt.toid::text
          GROUP BY o_1.opportunity_id
        ), s AS (
         SELECT r.opportunity_id,
            round(sum(r.shape_length)::numeric, 0) AS total_distance,
            round(sum(
                CASE
                    WHEN r.edge_used_count > 1 THEN r.shape_length
                    ELSE 0::double precision
                END)::numeric, 0) AS shared_route_distance,
            round(sum(
                CASE
                    WHEN r.edge_used_count = 1 THEN r.shape_length
                    ELSE 0::double precision
                END)::numeric, 0) AS unique_route_distance
           FROM r
          WHERE r.r = 1
          GROUP BY r.opportunity_id
        )
 SELECT o.opportunity_id,
    o.parent_opportunity_id,
    po.opportunity_name AS parent_opportunity_name,
    o.opportunity_name,
    o.opportunity_status_id,
    st.opportunity_status_desc,
    o.opportunity_manager,
    pm.username AS opportunity_manager_name,
    o.created_datetime,
    to_char(o.created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS created_date,
    o.created_user,
    uc.username AS created_username,
    to_char(o.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    o.modified_user,
    um.username AS modified_username,
    o.company_id,
    c.company_name,
    o.account_id,
    ac.account_name,
    pr.project_count,
    COALESCE(s.total_distance, 0::numeric) AS total_distance,
    COALESCE(s.shared_route_distance, 0::numeric) AS shared_route_distance,
    COALESCE(s.unique_route_distance, 0::numeric) AS unique_route_distance,
    pr.total_premises,
    pr.mdu_premises,
    pr.sdu_premises,
    pr.other_premises,
    COALESCE(round(s.total_distance / NULLIF(pr.mdu_premises, 0)::numeric, 1), 0::numeric) AS total_mtr_per_mdu_prem,
    COALESCE(round(s.unique_route_distance / NULLIF(pr.mdu_premises, 0)::numeric, 1), 0::numeric) AS unique_mtr_per_mdu_prem,
    COALESCE(round(s.shared_route_distance / NULLIF(s.total_distance, 0::numeric) * 100::numeric, 1), 0::numeric) AS shared_meterage_percent,
    o.cover_image_url,
    pr.valid_premises
   FROM opportunity.opportunities o
     LEFT JOIN opportunity.opportunities po ON o.parent_opportunity_id = po.opportunity_id
     LEFT JOIN opportunity.opportunity_status st ON o.opportunity_status_id = st.opportunity_status_id
     LEFT JOIN users.users pm ON o.opportunity_manager = pm.id
     LEFT JOIN users.users uc ON o.created_user = uc.id
     LEFT JOIN users.users um ON o.modified_user = um.id
     LEFT JOIN users.companies c ON o.company_id = c.company_id
     LEFT JOIN accounts.accounts ac ON o.account_id = ac.account_id
     LEFT JOIN pr ON o.opportunity_id = pr.opportunity_id
     LEFT JOIN s ON o.opportunity_id = s.opportunity_id;

-- ── 5. opportunity.vw_opportunity_projects_output ───────────────────────────
--
-- Definition from 025 with the premise source replaced. Rows here are per project, so
-- it mirrors vw_projects_list rather than vw_opportunity_list: count(pp.uprn), plus
-- valid and dependant appended. No DISTINCT - within one project a UPRN appears once.

CREATE OR REPLACE VIEW opportunity.vw_opportunity_projects_output AS
 WITH r AS (
         SELECT o.opportunity_id,
            o.project_id,
            o.route_id,
            o.edge,
            o.shape_length,
            count(*) OVER (PARTITION BY o.edge, o.opportunity_id) AS edge_used_count
           FROM opportunity.opportunity_routes o
        ), pr AS (
         SELECT o.opportunity_id,
            p_1.project_id,
            p_1.project_name,
            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 = 'SDU'::text THEN 1 ELSE NULL::integer END) AS sdu_premises,
            count(CASE WHEN bt.building_type <> ALL (ARRAY['MDU'::text, 'SDU'::text]) THEN 1 ELSE NULL::integer END) AS other_premises,
            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
           FROM opportunity.opportunities o
             LEFT JOIN opportunity.opportunity_project_link opl
                    ON o.opportunity_id = opl.opportunity_id AND opl.is_deleted = false
             LEFT JOIN projects.projects p_1 ON opl.project_id = p_1.project_id
             LEFT JOIN projects.project_premises pp
                    ON pp.project_id = p_1.project_id AND pp.state = 'approved'
             LEFT JOIN basedata.abp a ON a.uprn = pp.uprn
             LEFT JOIN basedata.toid_building_type bt ON a.cross_reference::text = bt.toid::text
          GROUP BY o.opportunity_id, p_1.project_id, p_1.project_name
        ), s AS (
         SELECT r.route_id,
            r.project_id,
            round(sum(r.shape_length)::numeric, 0) AS total_distance,
            round(sum(CASE WHEN r.edge_used_count > 1 THEN r.shape_length ELSE 0::double precision END)::numeric, 0) AS shared_route_distance,
            round(sum(CASE WHEN r.edge_used_count = 1 THEN r.shape_length ELSE 0::double precision END)::numeric, 0) AS unique_route_distance
           FROM r
          GROUP BY r.route_id, r.project_id
        )
 SELECT pr.opportunity_id,
    row_number() OVER (PARTITION BY pr.opportunity_id ORDER BY s.total_distance) AS build_order,
        CASE WHEN s.route_id IS NOT NULL THEN 'Complete'::text ELSE 'Failed'::text END AS route_status,
    s.route_id,
    s.project_id,
    pr.project_name,
    COALESCE(s.total_distance, 0::numeric) AS total_distance,
    COALESCE(s.shared_route_distance, 0::numeric) AS shared_route_distance,
    COALESCE(s.unique_route_distance, 0::numeric) AS unique_route_distance,
    pr.total_premises,
    pr.mdu_premises,
    pr.sdu_premises,
    pr.other_premises,
    COALESCE(round(s.total_distance / NULLIF(pr.mdu_premises, 0)::numeric, 1), 0::numeric) AS total_mtr_per_mdu_prem,
    COALESCE(round(s.unique_route_distance / NULLIF(pr.mdu_premises, 0)::numeric, 1), 0::numeric) AS unique_mtr_per_mdu_prem,
    p.geom,
    pr.valid_premises,
    pr.dependant_premises
   FROM pr
     LEFT JOIN s ON s.project_id = pr.project_id
     LEFT JOIN projects.projects p ON pr.project_id = p.project_id
  ORDER BY s.total_distance;

-- ── 6. stocklists.stocklist_premises_geom ───────────────────────────────────
--
-- ONE ROW PER PREMISE, ALWAYS. Today this view joins projects on
-- st_intersects(p.geom, a.geom), so a premise inside a parent and two nested
-- descendants comes back three times - three points on the map, three rows on the
-- stocklist premise list. The fix is to aggregate the claim rather than tie-break it:
-- one row per premise, naming the project where there is exactly one, and carrying
-- the count and the names where there is more than one.
--
-- LATERAL rather than a join to vw_premise_claimants, because every consumer of this
-- view filters by stocklist_id - stocklist_address_search.php:94,
-- distance_analysis_process.php:357, strategy_stocklist_distance_analysis.php:404 -
-- and a join to the aggregated view would compute every UPRN in the database before
-- applying that filter. See the header.
--
-- style_type NEEDS ITS TEST CHANGED. It keys on p.project_id >= 1 today to mean "in a
-- project". With project_id NULL for contested premises that would style a contested
-- premise as NOT in a project, which is wrong in the one case people are being asked
-- to look at. cl.claimant_count >= 1 is the same question asked of the count.

CREATE OR REPLACE VIEW stocklists.stocklist_premises_geom AS
 SELECT sp.stocklist_id,
    p.project_id,
    a.uprn,
    a.classification_code,
    a.class_description,
    a.address,
    a.postcode,
    a.geom,
        CASE
            WHEN ((cl.claimant_count >= 1) AND (bt.building_type = 'MDU'::text)) THEN 'mdu_project'::text
            WHEN ((cl.claimant_count >= 1) AND (bt.building_type = 'SMDU'::text)) THEN 'smdu_project'::text
            WHEN ((cl.claimant_count >= 1) AND (bt.building_type = 'SDU'::text)) THEN 'sdu_project'::text
            WHEN (bt.building_type = 'MDU'::text) THEN 'mdu_prem'::text
            WHEN (bt.building_type = 'SMDU'::text) THEN 'smdu_prem'::text
            WHEN (bt.building_type = 'SDU'::text) THEN 'sdu_prem'::text
            ELSE 'other'::text
        END AS style_type,
    s.stocklist_name,
    s.account_id,
    ac.account_name,
    s.company_id,
    bt.building_type,
    bt.building_use,
    bt.building_premises,
    p.project_name,
    a.cross_reference AS toid,
    cl.claimant_count,
    cl.project_names
   FROM stocklists.stocklist_premises sp
     JOIN stocklists.stocklists s ON sp.stocklist_id = s.stocklist_id
     JOIN basedata.abp a ON sp.uprn = a.uprn
     LEFT JOIN LATERAL (
        SELECT count(*) AS claimant_count,
               -- NULL when contested, same rule as vw_premise_claimants: there is no
               -- tie-break, so there is no single owner to name.
               CASE WHEN count(*) = 1 THEN min(c.project_id) END AS project_id,
               string_agg(pj.project_name::text, ', ' ORDER BY c.project_id) AS project_names
          FROM projects.project_premises c
          JOIN projects.projects pj ON pj.project_id = c.project_id
         WHERE c.uprn = sp.uprn AND c.state = 'approved' AND c.is_valid
     ) cl ON true
     LEFT JOIN projects.projects p ON p.project_id = cl.project_id
     LEFT JOIN accounts.accounts ac ON s.account_id = ac.account_id
     LEFT JOIN basedata.toid_building_type_geom bt ON a.cross_reference::text = bt.toid::text;

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

DO $$
DECLARE
    v_bad bigint;
BEGIN
    IF to_regclass('projects.vw_premise_claimants') IS NULL
       OR to_regclass('projects.vw_project_premises') IS NULL THEN
        RAISE EXCEPTION '058: one or both read views were not created';
    END IF;

    -- CREATE OR REPLACE validates a definition, but a view can replace cleanly and
    -- still fail on first read (039's note).
    PERFORM 1 FROM projects.vw_premise_claimants LIMIT 1;
    PERFORM 1 FROM projects.vw_project_premises LIMIT 1;
    -- vw_projects_list is not probed here: its prems CTE sits behind a LEFT JOIN, so
    -- LIMIT 1 computes the whole thing anyway and the assertion below already reads it.
    PERFORM 1 FROM opportunity.vw_opportunity_list LIMIT 1;
    PERFORM 1 FROM opportunity.vw_opportunity_projects_output LIMIT 1;
    PERFORM 1 FROM stocklists.stocklist_premises_geom LIMIT 1;

    -- Test 24, asserted here so a shape error cannot reach the list page.
    SELECT count(*) INTO v_bad
      FROM projects.vw_projects_list
     WHERE valid_premises + dependant_premises <> total_premises
        OR contested_premises > valid_premises;
    IF v_bad > 0 THEN
        RAISE EXCEPTION '058: % project row(s) fail valid + dependant = total, or contested > valid', v_bad;
    END IF;

    RAISE NOTICE '058: read views created and four views repointed; the endpoint queries are Phase 4';
END $$;
