-- 039_list_views_cover_image_url.sql
-- Adds cover_image_url to projects.vw_projects_list and
-- opportunity.vw_opportunity_list.
--
-- NOT A WAYLEAVE MIGRATION. It fixes projects and opportunity, and is here because
-- the wayleave realignment surfaced it (Dave, 2026-08-12).
--
-- THE BUG. Both editors read their static row from these views and populate the form
-- by iterating the row's keys, with a branch that calls serveImage() when the key is
-- cover_image_url. Neither view selects that column, so the key never arrived, the
-- branch never matched, and the cover image was never requested — no failed request,
-- no console error, just nothing. The column has been written correctly by
-- image_upload.php the whole time; only the read path was missing.
--
-- wayleave.vw_wayleave_list already carries it (migration 036), which is why the
-- wayleave editor was the one module where the image displayed.
--
-- CREATE OR REPLACE VIEW, not DROP + CREATE: replacing keeps dependent grants and
-- anything referencing the view. It requires every existing column in its existing
-- order and position, with new ones appended — so cover_image_url is LAST in both,
-- not slotted in beside the other entity columns where it would read more naturally.
-- Reordering would mean dropping the views, and data_get.php serves both to live list
-- pages.
--
-- Definitions below are pg_get_viewdef() output from dev, 2026-08-12, with only the
-- final column added. They are NOT reconstructed from
-- sql/netplanner_schema_20260706.sql: migrations 007, 008 and 009 have each altered
-- vw_projects_list since that baseline was taken.

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

DO $$
BEGIN
    IF to_regclass('projects.vw_projects_list') IS NULL THEN
        RAISE EXCEPTION 'projects.vw_projects_list is missing';
    END IF;
    IF to_regclass('opportunity.vw_opportunity_list') IS NULL THEN
        RAISE EXCEPTION 'opportunity.vw_opportunity_list is missing — apply 025 first';
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'projects' AND table_name = 'vw_projects_list'
                  AND column_name = 'cover_image_url')
       AND EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'opportunity' AND table_name = 'vw_opportunity_list'
                      AND column_name = 'cover_image_url') THEN
        RAISE EXCEPTION '039 has already run — both views carry cover_image_url';
    END IF;
END $$;

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

CREATE OR REPLACE VIEW projects.vw_projects_list AS
 WITH prems AS (
         SELECT p_1.project_id,
            count(*) 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
           FROM projects.projects p_1
             LEFT JOIN basedata.abp ON st_intersects(p_1.geom, abp.geom)
             LEFT JOIN basedata.toid_building_type bt ON abp.cross_reference::text = bt.toid::text
             LEFT JOIN adhoc.bduk_uprn_tracking_v2 bd ON abp.uprn = bd.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
   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;

-- ── 2. opportunity.vw_opportunity_list ──────────────────────────────────────

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(*) 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
           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.projects p ON opl.project_id = p.project_id
             LEFT JOIN basedata.abp a ON st_intersects(p.geom, a.geom)
             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
   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;

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

DO $$
DECLARE
    v_missing text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_missing
      FROM (VALUES ('projects', 'vw_projects_list'),
                   ('opportunity', 'vw_opportunity_list')) AS t(sch, rel),
           LATERAL (SELECT t.sch || '.' || t.rel AS v) x
     WHERE NOT EXISTS (
        SELECT 1 FROM information_schema.columns c
         WHERE c.table_schema = t.sch AND c.table_name = t.rel
           AND c.column_name = 'cover_image_url');

    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION '039: cover_image_url missing from: %', v_missing;
    END IF;

    -- Both must still be readable: CREATE OR REPLACE validates the definition, but a
    -- view can replace cleanly and still fail on first read.
    PERFORM 1 FROM projects.vw_projects_list LIMIT 1;
    PERFORM 1 FROM opportunity.vw_opportunity_list LIMIT 1;

    RAISE NOTICE '039: cover_image_url added to both list views';
END $$;
