-- 024_opportunity_schema_status.sql
-- Opportunity module alignment, migration 1 of 5
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- Creates the `opportunity` schema and its status table. Nothing else moves yet:
-- migration 025 brings the entity tables and the data, 026 the field meta, 027
-- the seeds, 028 the history triggers.
--
-- WHY A NEW SCHEMA. The module's four tables live in `prospector`, whose other
-- contents are the network planner's spatial data (network_points, network_cables,
-- pia_blockages and so on). Those stay exactly where they are — they are
-- project-planning data and no part of this work touches them. Only the four
-- opportunity tables move.
--
-- DDL IS WRITTEN OUT, NOT COPIED. No CREATE TABLE ... (LIKE projects....), because
-- INCLUDING ALL copies column defaults verbatim: a serial-style column would arrive
-- defaulting to nextval() on the *projects* sequence, quietly sharing one sequence
-- between two modules. Every object here is its own. The projects schema is the
-- reference for shape only.
--
-- FOUR ARTEFACTS IN THE PROJECTS SHAPE ARE DELIBERATELY NOT COPIED (Dave, 2026-08-05).
-- They read as accidents rather than intent, and this is the one chance to not
-- inherit them. In this migration: projects.project_status carries BOTH
-- project_status_pkey (PK) and project_status_un (UNIQUE) on the same single column
-- — two identical indexes maintained on every write. Here there is only the PK.
-- The other three are dealt with in 026.
--
-- NO SEQUENCE ON THE STATUS TABLE, mirroring projects, and that is load-bearing:
-- admin_save.php's statusSave() allocates a new status id as MAX(id) + 5 and inserts
-- it explicitly. A sequence would sit unused and drift out of step with the real
-- maximum, which is worse than not having one. The step of five is a spacing
-- convention so rows can still be slid in by hand.
--
-- WHY 'Created' IS ID 5 AND NOT 105. The projects ids encode a percentage of
-- completion — 100 meant complete, 0 was taken by Cancelled, so Created landed on
-- 105. That convention is retired (Dave, 2026-08-05): ordering is display_order's
-- job since migration 022, the id is never shown to a user, and progress — if it is
-- ever wanted — is the row's position over the count of statuses. Nothing in the
-- opportunity module keys on an id value: "complete" is configured per module in
-- public.dashboard_config. Worth knowing that the old convention IS still
-- load-bearing for projects, in accounts.vw_accounts_list, which hardcodes
-- project_status_id = 100 for its complete-project count. That coupling is exactly
-- what is not being recreated here.
--
-- 'Created' is also the status the entity column will default to (migration 025),
-- which is what makes it undeletable and undeactivatable: statusDefaultId() reads
-- the default from the catalog, so the protection follows the column rather than a
-- hardcoded id.

CREATE SCHEMA IF NOT EXISTS opportunity;

COMMENT ON SCHEMA opportunity IS
    'Opportunity module — entity, field meta, EAV values and history. Moved out of prospector by migrations 024–028 (docs/2026-08-04-opportunity-module-alignment.md).';

-- ── Status table ────────────────────────────────────────────────────────────

CREATE TABLE opportunity.opportunity_status (
    opportunity_status_id            integer      NOT NULL,
    opportunity_status_desc          varchar(100),
    opportunity_status_display_order integer      NOT NULL DEFAULT 0,
    opportunity_status_active        boolean      NOT NULL DEFAULT true,
    opportunity_status_style         varchar(32),
    CONSTRAINT opportunity_status_pk PRIMARY KEY (opportunity_status_id)
);

COMMENT ON COLUMN opportunity.opportunity_status.opportunity_status_id IS
    'Assigned by hand in steps of five — no sequence, matching the other modules. admin_save.php''s statusSave() computes MAX(id) + 5 for new rows.';

COMMENT ON COLUMN opportunity.opportunity_status.opportunity_status_style IS
    'Style token key from statusStyleRegistry() in global_functions.php — NOT a colour. NULL means unstyled and consumers fall back to their existing default.';

INSERT INTO opportunity.opportunity_status
    (opportunity_status_id, opportunity_status_desc,
     opportunity_status_display_order, opportunity_status_active, opportunity_status_style)
VALUES
    (5, 'Created', 1, true, NULL);

-- Guard: migration 025 hardcodes 5 as the opportunity_status_id column default, and
-- statusDefaultId() derives the delete/deactivate protection from that default. If
-- this row is not id 5, that chain silently breaks.
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM opportunity.opportunity_status
                    WHERE opportunity_status_id = 5
                      AND opportunity_status_desc = 'Created') THEN
        RAISE EXCEPTION 'opportunity: Created status did not land on id 5 — migration 025''s column default depends on it';
    END IF;
END $$;
