-- 007_company_scoping.sql
-- Company scoping hardening (follow-up to docs/2026-07-14-Dynamic-Navigation.md):
-- 1. prospector.opportunity gains company_id, backfilled from the creator's company
-- 2. wayleave.agreements.company_id backfilled from the creator's company
-- 3. The three list views expose company_id so data_get.php can apply the
--    company predicate (previously only the projects views were filtered)
-- Rows whose creator can't be resolved keep company_id NULL and are visible
-- only to company 1 (super) under the strict predicate.

ALTER TABLE prospector.opportunity ADD COLUMN IF NOT EXISTS company_id integer;

UPDATE prospector.opportunity o
SET company_id = u.company
FROM users.users u
WHERE o.opportunity_created_user = u.id
  AND o.company_id IS NULL;

UPDATE wayleave.agreements a
SET company_id = u.company
FROM users.users u
WHERE a.created_user = u.id
  AND a.company_id IS NULL;

-- ── accounts.vw_accounts_list: original definition + company_id ──────────────

CREATE OR REPLACE VIEW accounts.vw_accounts_list AS
 WITH projects AS (
         SELECT p_1.account_id,
            count(*) AS total_projects,
            count(
                CASE
                    WHEN (p_1.project_status_id = 100) THEN 1
                    ELSE NULL::integer
                END) AS complete_projects
           FROM projects.projects p_1
          WHERE (p_1.account_id IS NOT NULL)
          GROUP BY p_1.account_id
        ), stocklists AS (
         SELECT s.account_id,
            count(*) AS total_stocklists
           FROM stocklists.stocklists s
          WHERE (s.account_id IS NOT NULL)
          GROUP BY s.account_id
        )
 SELECT p.account_id,
    COALESCE((p.parent_account_id)::text, ''::text) AS parent_account_id,
    COALESCE((pp.account_name)::text, ''::text) AS parent_account_name,
    p.account_name,
    p.account_status_id,
    COALESCE(ps.account_status_desc, ''::character varying) AS account_status_desc,
    p.account_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    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,
    projects.total_projects,
    projects.complete_projects,
    stocklists.total_stocklists,
    p.company_id
   FROM (((((((accounts.accounts 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.account_manager = upm.id)))
     LEFT JOIN accounts.account_status ps ON ((p.account_status_id = ps.account_status_id)))
     LEFT JOIN accounts.accounts pp ON ((p.parent_account_id = pp.account_id)))
     LEFT JOIN projects ON ((p.account_id = projects.account_id)))
     LEFT JOIN stocklists ON ((p.account_id = stocklists.account_id)));

-- ── stocklists.vw_stocklists_list: original definition + company_id ──────────

CREATE OR REPLACE VIEW stocklists.vw_stocklists_list AS
 WITH prems AS (
         SELECT sp.stocklist_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
           FROM ((stocklists.stocklist_premises sp
             JOIN basedata.abp ON ((sp.uprn = abp.uprn)))
             JOIN basedata.toid_building_type bt ON (((abp.cross_reference)::text = (bt.toid)::text)))
          GROUP BY sp.stocklist_id
        )
 SELECT p.stocklist_id,
    COALESCE((p.parent_stocklist_id)::text, ''::text) AS parent_stocklist_id,
    COALESCE((pp.stocklist_name)::text, ''::text) AS parent_stocklist_name,
    p.stocklist_name,
    p.stocklist_status_id,
    COALESCE(ps.stocklist_status_desc, ''::character varying) AS stocklist_status_desc,
    p.stocklist_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    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,
    a.account_id,
    a.account_name,
    prems.total_premises,
    prems.mdu_premises,
    prems.smdu_premises,
    prems.sdu_premises,
    p.company_id
   FROM (((((((stocklists.stocklists 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.stocklist_manager = upm.id)))
     LEFT JOIN stocklists.stocklist_status ps ON ((p.stocklist_status_id = ps.stocklist_status_id)))
     LEFT JOIN stocklists.stocklists pp ON ((p.parent_stocklist_id = pp.stocklist_id)))
     LEFT JOIN accounts.accounts a ON ((a.account_id = p.account_id)))
     LEFT JOIN prems ON ((p.stocklist_id = prems.stocklist_id)));

-- ── prospector.vw_opportunity_list: original definition + company_id ─────────

CREATE OR REPLACE VIEW prospector.vw_opportunity_list AS
 WITH r AS (
         SELECT o_1.opportunity_id,
            o_1.project_id,
            o_1.route_id,
            o_1.seq,
            o_1.path_seq,
            o_1.node,
            o_1.edge,
            o_1.cost,
            o_1.agg_cost,
            o_1.shape_length,
            count(*) OVER (PARTITION BY o_1.edge, o_1.opportunity_id) AS edge_used_count,
            row_number() OVER (PARTITION BY o_1.edge, o_1.opportunity_id ORDER BY o_1.route_id, o_1.seq) AS r
           FROM prospector.opportunity_routes o_1
          ORDER BY o_1.seq
        ), 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 ((((prospector.opportunity o_1
             LEFT JOIN prospector.opportunity_project_link opl ON (((o_1.opportunity_id = opl.opportunity_id) AND (opl.link_active = true))))
             LEFT JOIN projects.projects p ON ((opl.project_id = p.project_id)))
             LEFT JOIN basedata.abp a ON (public.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.opportunity_name,
    to_char(o.opportunity_created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS opportunity_created_datetime,
    o.opportunity_created_user,
    to_char(o.opportunity_modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS opportunity_modified_datetime,
    o.opportunity_modified_user,
    uc.username AS opportunity_created_username,
    um.username AS opportunity_modified_username,
    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.company_id
   FROM ((((prospector.opportunity o
     LEFT JOIN users.users uc ON ((o.opportunity_created_user = uc.id)))
     LEFT JOIN users.users um ON ((o.opportunity_created_user = um.id)))
     LEFT JOIN pr ON ((o.opportunity_id = pr.opportunity_id)))
     LEFT JOIN s ON ((o.opportunity_id = s.opportunity_id)));
