-- 008_autocomplete_view_labels.sql
-- Autocomplete label columns (docs/2026-07-15-autopopulate-fields-fix.md, Step 1)
--
-- The editor populate loops fill inputs by matching view column aliases to element
-- ids ($('#'+key).val(value)), so each autocomplete pair's label must be returned
-- under the visible input's element id. vw_projects_list exposes the project
-- manager and company labels only as pm_username / contractor_name (list-page
-- aliases that don't match the form ids), and vw_accounts has no parent-account
-- label at all. Columns are APPENDED — CREATE OR REPLACE VIEW forbids renames,
-- and pm_username / contractor_name are consumed by the list pages
-- (project_list.js, account_list.js, stocklist_list.js, opportunity_edit.js).

-- projects.vw_projects_list: append project_manager_name + company_name.
-- Definition restated from sql/netplanner_schema_20260706.sql unchanged apart
-- from the two appended columns.
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 (public.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
   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)));

-- accounts.vw_accounts: append parent_account_name (new self-join pa).
CREATE OR REPLACE VIEW accounts.vw_accounts AS
 SELECT p.account_id,
    p.parent_account_id,
    p.account_name,
    p.account_status_id,
    p.account_manager,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    u.username AS account_manager_name,
    p.company_id,
    c.company_name,
    COALESCE(pa.account_name, ''::character varying) AS parent_account_name
   FROM (((accounts.accounts p
     LEFT JOIN users.users u ON ((p.account_manager = u.id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts pa ON ((p.parent_account_id = pa.account_id)));
