-- 025_opportunity_tables_and_data.sql
-- Opportunity module alignment, migration 2 of 5
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- The entity tables, the data move, the views, and the renaming of the originals.
-- Requires 024 (schema + status table). Field meta follows in 026.
--
-- THE MODULE IS DOWN FROM THE MOMENT THIS APPLIES until the re-pointed endpoints
-- deploy: the originals are renamed, so opportunity_manage.php, opportunity_process.php,
-- opportunity_create.php, the list page and the QGIS PDF export all error against
-- tables that no longer answer to those names. That is deliberate — a missed reference
-- fails loudly rather than silently reading stale data — but it means this migration
-- and the endpoint deploy want to happen in the same sitting.
--
-- WHAT MOVES, AND WHAT IT BECOMES:
--
--   prospector.opportunity              → opportunity.opportunities
--   prospector.opportunity_access_log   → opportunity.opportunity_access_log
--   prospector.opportunity_project_link → opportunity.opportunity_project_link
--   prospector.opportunity_routes       → opportunity.opportunity_routes
--
-- The entity table gains the seven columns that make it a peer of projects.projects:
-- parent_opportunity_id, opportunity_status_id, opportunity_manager, account_id,
-- stocklist_id, cover_image_url (company_id it already had, from migration 007). Its
-- audit columns are renamed to the common shape — opportunity_created_datetime becomes
-- created_datetime and so on — which is the whole point of the exercise and is why
-- every view and endpoint is rewritten rather than aliased.
--
-- BACKFILL IS DELIBERATELY MINIMAL (Dave, 2026-08-05): opportunity_manager takes the
-- creator, and nothing else is invented. Status is not named in the INSERT, so every
-- existing row takes the 'Created' default from 024. parent, account, stocklist and
-- cover image stay NULL and get filled in as people edit.
--
-- LINK TABLE RESHAPE. opportunity_project_link takes the audit shape used elsewhere:
-- link_active inverts to is_deleted, link_date becomes added_datetime, and added_user
-- is new — the old table never recorded who linked, so it is NULL for every historic
-- row. opportunity_routes is copied verbatim: it is derived pgRouting output,
-- recalculated wholesale, so attribution and a primary key buy nothing.
--
-- 19 ROWS ARE NOT COPIED. opportunity_project_link has 19 rows whose project_id is not
-- in projects.projects (all with link_active = false, i.e. already removed from their
-- opportunity). They cannot be rendered — vw_opportunity_projects inner-joins
-- projects.projects — and they would fail the new foreign key. They are excluded by
-- the INSERT's WHERE clause and reported by RAISE NOTICE. Nothing is destroyed: all
-- 406 rows remain in prospector.opportunity_project_link_migrated_20260804.
--
-- NO FOREIGN KEYS ON company_id / account_id / stocklist_id, mirroring
-- projects.projects, which has none either (Dave, 2026-08-05). Consistency between the
-- two modules matters more here than constraining three columns in one of them.
--
-- Expected volumes, measured on dev 2026-08-05:
--   opportunity 56 · access_log 435 · project_link 406 (387 copied) · routes 12,199

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

DO $$
DECLARE
    v_len integer;
BEGIN
    IF to_regclass('prospector.opportunity') IS NULL THEN
        RAISE EXCEPTION 'prospector.opportunity not found — has this migration already run?';
    END IF;

    IF to_regclass('opportunity.opportunity_status') IS NULL THEN
        RAISE EXCEPTION 'opportunity.opportunity_status not found — apply 024 first';
    END IF;

    -- opportunity_name is varchar(200) in prospector; the projects shape is
    -- varchar(150). Truncating a name silently would be worse than stopping here.
    SELECT max(length(opportunity_name)) INTO v_len FROM prospector.opportunity;
    IF v_len > 150 THEN
        RAISE EXCEPTION 'opportunity_name is up to % characters, which will not fit the projects-shape varchar(150) — widen the column in this migration before applying', v_len;
    END IF;
END $$;

-- ── 1. Entity table ─────────────────────────────────────────────────────────

CREATE SEQUENCE opportunity.opportunities_opportunity_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE opportunity.opportunities (
    opportunity_id        integer      NOT NULL DEFAULT nextval('opportunity.opportunities_opportunity_id_seq'::regclass),
    parent_opportunity_id integer,
    opportunity_name      varchar(150),
    opportunity_status_id integer      NOT NULL DEFAULT 5,
    opportunity_manager   integer,
    geom                  public.geometry,
    created_datetime      timestamp without time zone DEFAULT now(),
    created_user          integer,
    modified_datetime     timestamp without time zone DEFAULT now(),
    modified_user         integer,
    company_id            integer,
    account_id            integer,
    stocklist_id          integer,
    cover_image_url       varchar(100),
    CONSTRAINT opportunities_pk PRIMARY KEY (opportunity_id),
    CONSTRAINT opportunities_parent_opportunity_id_fk FOREIGN KEY (parent_opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id),
    CONSTRAINT opportunities_status_fk FOREIGN KEY (opportunity_status_id)
        REFERENCES opportunity.opportunity_status(opportunity_status_id),
    CONSTRAINT opportunities_manager_fk FOREIGN KEY (opportunity_manager)
        REFERENCES users.users(id),
    CONSTRAINT opportunities_created_user_fk FOREIGN KEY (created_user)
        REFERENCES users.users(id),
    CONSTRAINT opportunities_modified_user_fk FOREIGN KEY (modified_user)
        REFERENCES users.users(id)
);

ALTER SEQUENCE opportunity.opportunities_opportunity_id_seq
    OWNED BY opportunity.opportunities.opportunity_id;

CREATE INDEX opportunities_geom_idx ON opportunity.opportunities USING gist (geom);

COMMENT ON COLUMN opportunity.opportunities.geom IS
    'Unused — no endpoint writes it. The parked idea was a derived opportunity boundary published as a map layer; see docs/2026-08-04-opportunity-module-alignment.md for why it was parked.';

-- Mirrors projects.projects_update_modified_fields(): stamps the modification time on
-- every UPDATE whatever the write path. The user half is commented out there too —
-- modified_user is set by the endpoints, and the history triggers in 028 fall back to
-- it when the app.user_id GUC is absent.
CREATE FUNCTION opportunity.opportunities_update_modified_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.modified_datetime = now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER update_modified_fields_trigger
    BEFORE UPDATE ON opportunity.opportunities
    FOR EACH ROW EXECUTE FUNCTION opportunity.opportunities_update_modified_fields();

-- ── 2. Companion tables ─────────────────────────────────────────────────────

CREATE SEQUENCE opportunity.opportunity_access_log_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_access_log (
    id              bigint NOT NULL DEFAULT nextval('opportunity.opportunity_access_log_id_seq'::regclass),
    opportunity_id  bigint,
    user_id         integer,
    access_type     varchar(50),
    access_datetime timestamp without time zone DEFAULT now(),
    CONSTRAINT opportunity_access_log_pk PRIMARY KEY (id)
);

ALTER SEQUENCE opportunity.opportunity_access_log_id_seq
    OWNED BY opportunity.opportunity_access_log.id;

CREATE SEQUENCE opportunity.opportunity_journal_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

-- New table — the module has never had a journal. file_upload.php's entity map points
-- at prospector.project_journal, a table that has never existed; that write is wrapped
-- in a try/catch which logs and continues, so uploads stored fine and only the note was
-- lost. Pointing the map here (Phase 6) fixes it.
CREATE TABLE opportunity.opportunity_journal (
    id             bigint NOT NULL DEFAULT nextval('opportunity.opportunity_journal_id_seq'::regclass),
    opportunity_id bigint,
    user_id        integer,
    log_datetime   timestamp without time zone,
    log_text       text,
    CONSTRAINT opportunity_journal_pk PRIMARY KEY (id)
);

ALTER SEQUENCE opportunity.opportunity_journal_id_seq
    OWNED BY opportunity.opportunity_journal.id;

CREATE SEQUENCE opportunity.opportunity_project_link_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_project_link (
    id             bigint  NOT NULL DEFAULT nextval('opportunity.opportunity_project_link_id_seq'::regclass),
    opportunity_id bigint,
    project_id     bigint,
    added_user     integer,
    added_datetime timestamp without time zone DEFAULT now(),
    is_deleted     boolean NOT NULL DEFAULT false,
    CONSTRAINT opportunity_project_link_pk PRIMARY KEY (id),
    CONSTRAINT opportunity_project_link_opportunity_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_project_link_project_fk FOREIGN KEY (project_id)
        REFERENCES projects.projects(project_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_project_link_added_user_fk FOREIGN KEY (added_user)
        REFERENCES users.users(id)
);

ALTER SEQUENCE opportunity.opportunity_project_link_id_seq
    OWNED BY opportunity.opportunity_project_link.id;

CREATE INDEX opportunity_project_link_opportunity_idx
    ON opportunity.opportunity_project_link USING btree (opportunity_id, is_deleted);

-- No primary key, matching the original: rows are deleted and re-inserted wholesale
-- when routes are recalculated, and nothing addresses an individual row.
CREATE TABLE opportunity.opportunity_routes (
    opportunity_id bigint,
    project_id     bigint,
    route_id       integer,
    seq            integer,
    path_seq       integer,
    node           bigint,
    edge           bigint,
    cost           double precision,
    agg_cost       double precision,
    shape_length   double precision,
    CONSTRAINT opportunity_routes_opportunity_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_routes_project_fk FOREIGN KEY (project_id)
        REFERENCES projects.projects(project_id) ON UPDATE RESTRICT
);

CREATE INDEX opportunity_routes_opportunity_idx
    ON opportunity.opportunity_routes USING btree (opportunity_id);

-- vw_opportunity_list and vw_opportunity_routes both window over (edge, opportunity_id)
CREATE INDEX opportunity_routes_edge_idx
    ON opportunity.opportunity_routes USING btree (edge, opportunity_id);

-- ── 3. Data ─────────────────────────────────────────────────────────────────

INSERT INTO opportunity.opportunities
    (opportunity_id, opportunity_name, opportunity_manager, geom,
     created_datetime, created_user, modified_datetime, modified_user, company_id)
SELECT opportunity_id,
       opportunity_name,
       opportunity_created_user,          -- manager backfilled from the creator
       geom,
       opportunity_created_datetime,
       opportunity_created_user,
       opportunity_modified_datetime,
       opportunity_modified_user,
       company_id
  FROM prospector.opportunity;

INSERT INTO opportunity.opportunity_access_log
    (id, opportunity_id, user_id, access_type, access_datetime)
SELECT id, opportunity_id, user_id, access_type, access_datetime
  FROM prospector.opportunity_access_log;

INSERT INTO opportunity.opportunity_project_link
    (id, opportunity_id, project_id, added_user, added_datetime, is_deleted)
SELECT l.id,
       l.opportunity_id,
       l.project_id,
       NULL::integer,                                  -- never recorded
       l.link_date,
       l.link_active IS NOT TRUE                       -- active → not deleted; NULL → deleted
  FROM prospector.opportunity_project_link l
 WHERE l.project_id IN (SELECT project_id FROM projects.projects);

INSERT INTO opportunity.opportunity_routes
    (opportunity_id, project_id, route_id, seq, path_seq, node, edge, cost, agg_cost, shape_length)
SELECT opportunity_id, project_id, route_id, seq, path_seq, node, edge, cost, agg_cost, shape_length
  FROM prospector.opportunity_routes;

SELECT setval('opportunity.opportunities_opportunity_id_seq',
              COALESCE((SELECT max(opportunity_id) FROM opportunity.opportunities), 0) + 1, false);
SELECT setval('opportunity.opportunity_access_log_id_seq',
              COALESCE((SELECT max(id) FROM opportunity.opportunity_access_log), 0) + 1, false);
SELECT setval('opportunity.opportunity_project_link_id_seq',
              COALESCE((SELECT max(id) FROM opportunity.opportunity_project_link), 0) + 1, false);
SELECT setval('opportunity.opportunity_journal_id_seq', 1, false);

-- ── 4. Verify before anything is renamed ────────────────────────────────────

DO $$
DECLARE
    v_src   bigint;
    v_dst   bigint;
    v_skip  bigint;
BEGIN
    SELECT count(*) INTO v_src FROM prospector.opportunity;
    SELECT count(*) INTO v_dst FROM opportunity.opportunities;
    IF v_src <> v_dst THEN
        RAISE EXCEPTION 'opportunities: copied % of % rows', v_dst, v_src;
    END IF;

    SELECT count(*) INTO v_src FROM prospector.opportunity_access_log;
    SELECT count(*) INTO v_dst FROM opportunity.opportunity_access_log;
    IF v_src <> v_dst THEN
        RAISE EXCEPTION 'opportunity_access_log: copied % of % rows', v_dst, v_src;
    END IF;

    SELECT count(*) INTO v_src FROM prospector.opportunity_routes;
    SELECT count(*) INTO v_dst FROM opportunity.opportunity_routes;
    IF v_src <> v_dst THEN
        RAISE EXCEPTION 'opportunity_routes: copied % of % rows', v_dst, v_src;
    END IF;

    SELECT count(*) INTO v_src FROM prospector.opportunity_project_link;
    SELECT count(*) INTO v_dst FROM opportunity.opportunity_project_link;
    v_skip := v_src - v_dst;

    -- Only unresolvable project references may be missing. Anything else means the
    -- copy lost rows for a reason nobody has understood yet.
    IF v_skip <> (SELECT count(*) FROM prospector.opportunity_project_link
                   WHERE project_id NOT IN (SELECT project_id FROM projects.projects)) THEN
        RAISE EXCEPTION 'opportunity_project_link: copied % of % rows, which does not match the count of links to missing projects', v_dst, v_src;
    END IF;

    RAISE NOTICE 'opportunity_project_link: % of % rows copied; % skipped (project no longer exists, all previously inactive)',
                 v_dst, v_src, v_skip;
END $$;

-- ── 5. Views ────────────────────────────────────────────────────────────────

-- Rebuilt, not re-pointed. Gains the projects-list columns (status, manager, parent,
-- account, company) and the common audit naming, and fixes the copy-paste join that
-- made opportunity_modified_username show the creator: the um join was on
-- opportunity_created_user. Every metric column the old view exposed is kept.
CREATE 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.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
             LEFT JOIN opportunity.opportunity_project_link opl
                    ON o.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.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
   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;

-- Re-pointed, logic unchanged apart from link_active = true → is_deleted = false and
-- link_date → added_datetime. The network tables stay in prospector and are read
-- cross-schema, which is correct: they are the planner's data, not the module's.
CREATE VIEW opportunity.vw_opportunity_network_points AS
 SELECT o.opportunity_id,
    o.opportunity_name,
    p.project_id,
    p.project_name,
    np.point_id,
    np.point_name,
    np.geom,
    p.company_id,
    np.point_type
   FROM opportunity.opportunities o
     JOIN opportunity.opportunity_project_link opl
       ON o.opportunity_id = opl.opportunity_id AND opl.is_deleted = false
     JOIN projects.projects p ON opl.project_id = p.project_id
     JOIN prospector.network_points np ON p.project_id = np.project_id;

CREATE VIEW opportunity.vw_opportunity_projects AS
 SELECT o.id,
    o.opportunity_id,
    o.added_datetime,
    p.project_id,
    p.project_name,
    p.geom
   FROM opportunity.opportunity_project_link o
     JOIN projects.projects p ON o.project_id = p.project_id
  WHERE o.is_deleted = false;

CREATE 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(*) 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
             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 basedata.abp a ON st_intersects(p_1.geom, a.geom)
             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
   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;

CREATE VIEW opportunity.vw_opportunity_routes AS
 WITH a AS (
         SELECT r.edge,
            round(r.shape_length::numeric, 2) AS duct_section_length,
            count(*) OVER (PARTITION BY r.edge, r.opportunity_id) AS edge_used_count,
            string_agg(p.project_name::text, ', '::text) OVER (PARTITION BY r.edge, r.opportunity_id) AS duct_feeding_projects,
            r.opportunity_id,
            p.company_id,
            d.shape AS geom,
            row_number() OVER (PARTITION BY r.edge, r.opportunity_id ORDER BY r.route_id, r.seq) AS r
           FROM opportunity.opportunity_routes r
             JOIN openreach.duct d ON r.edge = d.objectid
             JOIN projects.projects p ON r.project_id = p.project_id
        )
 SELECT a.opportunity_id,
    a.edge,
    a.duct_section_length,
    a.edge_used_count,
    a.duct_feeding_projects,
        CASE WHEN a.edge_used_count = 1 THEN 'unique'::text ELSE 'shared'::text END AS route_type,
    a.company_id,
    a.geom
   FROM a
  WHERE a.r = 1;

-- New. Mirrors projects.vw_parent_project_links, including the centroid line and
-- distance: geom is unused today so both come back NULL, and they become correct on
-- their own if the boundary idea is ever revisited.
CREATE VIEW opportunity.vw_parent_opportunity_links AS
 SELECT o.opportunity_id,
    o.opportunity_name,
    o.parent_opportunity_id,
    po.opportunity_name AS parent_opportunity_name,
    public.st_makeline(public.st_centroid(o.geom), public.st_centroid(po.geom)) AS geom,
    round((public.st_distance(public.st_centroid(o.geom), public.st_centroid(po.geom)))::numeric, 0) AS distance
   FROM opportunity.opportunities o
     JOIN opportunity.opportunities po ON o.parent_opportunity_id = po.opportunity_id;

-- New. Uploads live in the shared public.file_uploads — projects.project_file_uploads
-- is referenced by no PHP or JS at all, so there is no per-module upload table here.
-- Unlike the projects view this one filters file_is_deleted, as wayleave's does:
-- only wayleave can delete a file today, so the projects omission is latent rather
-- than live, but there is no reason to inherit it.
CREATE VIEW opportunity.vw_file_uploads AS
 SELECT fu.file_id,
    to_char(fu.file_upload_datetime, 'DD mon YYYY HH24:MI'::text) AS file_upload_datetime,
    u.username,
    fu.file_name,
    fu.file_type,
    fu.opportunity_id,
    fu.file_category,
    fu.file_description,
    fu.file_size
   FROM public.file_uploads fu
     LEFT JOIN users.users u ON fu.file_upload_user = u.id
  WHERE fu.entity::text = 'opportunity'::text
    AND fu.file_is_deleted = false
  ORDER BY fu.file_upload_datetime DESC;

-- ── 6. Retire the originals ─────────────────────────────────────────────────

DROP VIEW prospector.vw_opportunity_list;
DROP VIEW prospector.vw_opportunity_network_points;
DROP VIEW prospector.vw_opportunity_projects_output;
DROP VIEW prospector.vw_opportunity_routes;
DROP VIEW prospector.vw_opportunity_projects;

-- Renamed rather than dropped, so a reference missed anywhere in the app, in a script,
-- or in the QGIS project fails loudly instead of quietly reading data that no longer
-- updates. They also hold the 19 uncopied link rows. Drop them once the module has
-- been exercised for a while.
ALTER TABLE prospector.opportunity              RENAME TO opportunity_migrated_20260804;
ALTER TABLE prospector.opportunity_access_log   RENAME TO opportunity_access_log_migrated_20260804;
ALTER TABLE prospector.opportunity_project_link RENAME TO opportunity_project_link_migrated_20260804;
ALTER TABLE prospector.opportunity_routes       RENAME TO opportunity_routes_migrated_20260804;

-- ── 7. Registered map layers ────────────────────────────────────────────────

-- public.map_layers splits schema from table, so only the schema value moves. Keyed on
-- names rather than the layer_id values (45 and 46 on dev) because ids differ between
-- environments.
UPDATE public.map_layers
   SET database_schema = 'opportunity'
 WHERE database_schema = 'prospector'
   AND database_table IN ('vw_opportunity_routes', 'vw_opportunity_network_points');
