

-- ============================================================
-- ITERATION 003 — ts3_ PREFIXED TABLES (STAGING / VALIDATION)
-- ============================================================
--
-- PURPOSE
-- -------
-- These tables mirror the live ts_ tables but target the
-- "Copy of Early Warning _ Quotations and NCE Data.xlsx" workbook
-- (located at invictusData/data/v2/Commercial Management/).
--
-- The "Copy of" workbook adds two corrected columns:
--   • "Modified Vendor"          — cleaned contractor name
--   • "Modified Contract Number" — cleaned contract reference
-- The loader resolves: use Modified value if non-null/non-empty,
-- otherwise fall back to the original column value.
--
-- ISOLATION STRATEGY
-- ------------------
-- ts_*  → live tables consumed by agents (DO NOT ALTER)
-- ts3_* → staging tables for validation of the new dataset
--
-- Once confidence is established the ts3_ tables will be promoted
-- (data copied back into ts_ tables, ts3_ tables dropped).
--
-- Source script: invictusData/app/1_CommercialManagement/early_warning_003.py
-- Iteration tag: EW_005
--
-- ============================================================
-- BUSINESS MODEL (Updated from client discussion — 09 Apr 2026)
-- ============================================================
--
-- THE NEC CHANGE CHAIN — all cross-table relationships are OPTIONAL:
--
--   EW may exist without NCE.
--   NCE may exist without EW.
--   Quotation may exist without NCE.
--   The direct EW → Quotation shortcut is also optional.
--
-- KEY RULES (from client, 09 Apr 2026):
--   • AW-XXXXXX and EW-XXXXXX record numbers are BOTH treated as Early
--     Warnings — no structural distinction; same table, same columns.
--   • One quotation can have multiple revisions. Each revision is stored
--     as a NEW row with a NEW record_number (e.g. Q1 → Q2 → Q3 all under
--     the same contract_number). Versioning is tracked via:
--       version_number              — 1-based counter (1 = original, 2 = first revision …)
--       previous_version_reference  — record_number of the prior version (NULL for v1)
--       revised_quotation_number    — record_number of the NEXT version that supersedes
--                                     this row (NULL if this is the latest); populated
--                                     from "Revised Q…" strings in the change_to_prices cell
--       is_active                   — TRUE only on the current active version
--   • Unique key for quotations: (contract_number, record_number).
--     Each revision gets a different record_number — no composite version key needed.
--   • Lookup fields (Project, Area, Category, Early Warning Type, etc.) are free-text
--     VARCHAR — valid values are documented in each column comment.
-- ============================================================


-- ============================================================
-- ts3_contracts — Cleaned contract register (v2 workbook)
-- ============================================================
-- vendor and contract_number are the RESOLVED values:
--   vendor          = "Modified Vendor"          if non-empty else "Vendor"
--   contract_number = "Modified Contract Number" if non-empty else "Contract Number"
CREATE TABLE IF NOT EXISTS ts3_contracts (
    id              SERIAL PRIMARY KEY,

    -- Resolved contractor / supplier name
    vendor          VARCHAR(255),

    -- Resolved full contract reference (links to ts3_early_warnings etc.)
    contract_number VARCHAR(255) UNIQUE,

    -- Tata Steel staff responsible for this contract
    contract_owner  VARCHAR(255),

    -- Original contracted value in GBP
    contract_value  NUMERIC(18, 2),

    -- Metadata
    source          VARCHAR(4096) DEFAULT 'direct-sheet', -- e.g., 'direct-sheet', 'alternate-sheet', 'manual-entry', 'api-import'
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- ts3_early_warnings
-- ============================================================
-- Primary uniqueness: (contract_number, record_number).
-- record_number accepts BOTH EW-XXXXXX and AW-XXXXXX prefixes — no structural distinction.
-- All cross-links (nce_number, quotation) are OPTIONAL soft references.
--   contract_number → ts3_contracts.contract_number  (FK enforced)
--   nce_number      → ts3_nce.record_number          (soft — EW may exist without NCE)
--   quotation       → ts3_quotations.record_number   (soft — EW may have no direct quotation)
CREATE TABLE IF NOT EXISTS ts3_early_warnings (
    id                      SERIAL PRIMARY KEY,

    -- Construction package. Lookup values: "Meltshop" | "Pickleline" | "HRP" | "Programme"
    -- Note: "Pickle" and "Pickle Line" in source data are normalised to "Pickleline".
    project                 VARCHAR(100),

    contractor              VARCHAR(150),

    -- Sub-area within the package (e.g. Meltshop sub-areas: "NG Prep", "Scrap Yard Prep").
    area                    VARCHAR(150),

    contract_number         VARCHAR(255),

    -- Unique EW/AW reference within the project.
    -- BOTH EW-XXXXXX and AW-XXXXXX prefixes are accepted and treated identically.
    record_number           VARCHAR(50),

    -- NCE raised from this EW, if any. Soft reference → ts3_nce.record_number.
    -- NULL when no NCE has been raised (EW may exist without NCE).
    nce_number              VARCHAR(50),

    -- Direct quotation linked to this EW, if any. Soft reference → ts3_quotations.record_number.
    -- NULL when no quotation is directly linked (EW may exist without a quotation).
    quotation               VARCHAR(50),

    cost                    NUMERIC(18, 2),
    title                   TEXT,

    -- EW category type (lookup). Values sourced from the "Data List" sheet:
    -- e.g. "Design", "Site Conditions", "Programme", "Third Party", "Statutory", etc.
    category                VARCHAR(255),

    comments_added          TEXT,

    -- Who raised this EW. Lookup values: "Contractor" | "Consultant"
    notification_by         VARCHAR(150),

    notification_date       DATE,

    -- Same as notification_by expressed as a type. Lookup values: "Contractor" | "Consultant"
    early_warning_type      VARCHAR(100),

    -- Risk severity. Lookup values: "Very High" | "High" | "Medium" | "Low"
    severity                VARCHAR(50),

    -- Probability of the risk materialising.
    -- Lookup values: "Occurred" | "Very High" | "High" | "Medium" | "Low"
    -- "Occurred" means the risk has already materialised into a real event.
    likelihood              VARCHAR(50),

    -- Combined risk priority derived from severity × likelihood.
    -- Lookup values: "Very High" | "High" | "Medium" | "Low"
    priority                VARCHAR(50),

    -- Lifecycle status. Lookup values: "Open" | "Submitted" | "Closed"
    status                  VARCHAR(50),

    no_days_open            INTEGER,
    early_warning_closed    DATE,
    mitigation_measure      TEXT,

    -- Outcome tracking — count or flag recording how the EW was resolved.
    ew_accepted             VARCHAR(50),
    ew_rejected             VARCHAR(50),
    under_discussion        VARCHAR(50),

    source_file             VARCHAR(4096),
    sub_sheet               VARCHAR(4096),
    ingested_at             TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ts3_early_warnings_contract_record_uniq UNIQUE (contract_number, record_number),
    CONSTRAINT ts3_ew_contract_fk FOREIGN KEY (contract_number)
        REFERENCES ts3_contracts(contract_number) ON DELETE RESTRICT ON UPDATE CASCADE
);


-- ============================================================
-- ts3_quotations
-- ============================================================
-- Primary uniqueness: (contract_number, record_number).
-- One quotation can have multiple revisions. Each revision is a NEW row with a NEW
-- record_number (e.g. Q1 → Q2 → Q3 all under the same contract). Versioning is tracked
-- via version_number, previous_version_reference, revised_quotation_number, and is_active.
-- All cross-links are optional: Quotation may exist without NCE.
--   contract_number → ts3_contracts.contract_number  (FK enforced)
--   nce_number      → ts3_nce.record_number           (soft circular — applied post-load; optional)
CREATE TABLE IF NOT EXISTS ts3_quotations (
    id                          SERIAL PRIMARY KEY,

    -- Sub-area within the construction package (lookup — see ts3_early_warnings.area).
    area                        VARCHAR(150),
    contractor                  VARCHAR(150),
    contract_number             VARCHAR(255),

    -- Unique quotation reference within the project (e.g. QUOTE-000043).
    -- Each revision of a quotation gets a NEW record_number — versioning is tracked
    -- by the columns below, not by a composite key.
    record_number               VARCHAR(50),

    -- NCE this quotation responds to. Soft reference → ts3_nce.record_number.
    -- NULL when quotation exists independently of an NCE (Quotation may exist without NCE).
    nce_number                  VARCHAR(100),

    instruction                 TEXT,

    -- Current status of this quotation version. Lookup values:
    --   "Accepted"             — accepted by the PM with full cost breakdown
    --   "Accepted_No_CBS"      — accepted without Cost Breakdown Schedule
    --   "Acc_with_Instruction" — accepted alongside a specific PM instruction
    --   "Submitted"            — submitted by contractor, awaiting PM response
    --   "Revised_Quotation"    — PM requested a revised version (a new version row is added)
    --   "Assessment"           — PM is making an independent assessment of value
    --   "Internal_Review"      — under Tata Steel internal review
    --   "In_Review"            — under general review
    --   "Terminated"           — process terminated; quotation will not proceed
    status                      VARCHAR(100),

    quotation_due_date          DATE,
    quotation_date              DATE,
    quotation_by                VARCHAR(150),
    title                       TEXT,
    change_to_prices            NUMERIC(18, 2),

    -- ── Versioning columns (quotation revision chain) ──────────────────────────
    -- When a quotation is revised, a NEW row is inserted with a NEW record_number.
    -- The revision chain is maintained through these four columns.

    -- 1-based revision counter: 1 = original submission; 2 = first revision; etc.
    version_number              INTEGER NOT NULL DEFAULT 1,

    -- record_number of the PREVIOUS version in this chain (NULL for the first version).
    -- e.g. if Q2 is a revision of Q1: Q2.previous_version_reference = 'QUOTE-000001'
    previous_version_reference  VARCHAR(50),

    -- record_number of the NEXT version that supersedes this row (NULL if this is the latest).
    -- Populated from "Revised Q###" strings found in the change_to_prices Excel cell.
    -- change_to_prices is set to NULL when this field is populated.
    revised_quotation_number    VARCHAR(100),

    -- TRUE only for the current active version in a revision chain.
    -- All superseded versions have is_active = FALSE.
    is_active                   BOOLEAN NOT NULL DEFAULT TRUE,
    -- ── End versioning columns ─────────────────────────────────────────────────

    change_to_days              INTEGER,
    reply_by                    VARCHAR(150),
    quotation_decision          TEXT,
    carried_to_cost_sheet       VARCHAR(50),
    impact_carried_to_schedule  VARCHAR(50),
    source_file                 VARCHAR(4096),
    sub_sheet                   VARCHAR(4096),
    ingested_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ts3_quotations_contract_record_uniq  UNIQUE (contract_number, record_number),
    CONSTRAINT ts3_quot_contract_fk FOREIGN KEY (contract_number)
        REFERENCES ts3_contracts(contract_number) ON DELETE RESTRICT ON UPDATE CASCADE
    -- ts3_quot_nce_fk (nce_number → ts3_nce.record_number) applied post-load via _apply_circular_fks()
    -- This link is optional — Quotation may exist without NCE
);


-- ============================================================
-- ts3_nce
-- ============================================================
-- Primary uniqueness: (contract_number, record_number).
-- All cross-links are optional: NCE may exist without EW; NCE may exist without Quotation.
--   contract_number  → ts3_contracts.contract_number  (FK enforced)
--   quotation_number → ts3_quotations.record_number   (soft circular — applied post-load; optional)
CREATE TABLE IF NOT EXISTS ts3_nce (
    id                          SERIAL PRIMARY KEY,

    -- Construction package. Lookup values: "Meltshop" | "Pickleline" | "HRP" | "Programme"
    -- Note: "Pickle" and "Pickle Line" in source data are normalised to "Pickleline".
    project                     VARCHAR(100),

    contractor                  VARCHAR(150),
    area                        VARCHAR(150),
    contract_number             VARCHAR(255),
    record_number               VARCHAR(50),  -- NCE number (e.g. NCE-000001)
    notification_by             VARCHAR(150),
    notification_date           DATE,
    title                       TEXT,

    -- NEC contract clause under which this compensation event is raised. Lookup values:
    --   "60.1(1) An instruction to change the Scope"       — most common; design/scope changes
    --   "60.1(2) No access to or use of the Site"           — site access denied
    --   "60.1(3) Something not provided / omitted"          — information, materials not supplied
    --   "60.1(7) A change to a previous decision"           — client reverses earlier instruction
    --   "60.1(12) Unexpected physical conditions"           — ground conditions, contamination, etc.
    --   ... other 60.1 sub-clauses may appear
    compensation_event_type     VARCHAR(255),

    reply_due_date              DATE,
    reply_date                  DATE,
    reply_by                    VARCHAR(150),

    -- PM's formal decision on whether this is a valid Compensation Event. Lookup values:
    --   "Event is a compensation event and quotation is instructed" — CE accepted; contractor submits Q
    --   "Quotation is not instructed"                               — CE accepted; PM assesses directly
    --   NULL                                                        — decision still pending
    is_compensation_event       TEXT,

    -- Quotation submitted for this NCE, if any. Soft reference → ts3_quotations.record_number.
    -- NULL when no quotation has been raised (NCE may exist without a quotation).
    quotation_number            VARCHAR(50),

    source_file                 VARCHAR(4096),
    sub_sheet                   VARCHAR(4096),
    ingested_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ts3_nce_contract_record_uniq UNIQUE (contract_number, record_number),
    CONSTRAINT ts3_nce_contract_fk FOREIGN KEY (contract_number)
        REFERENCES ts3_contracts(contract_number) ON DELETE RESTRICT ON UPDATE CASCADE
    -- ts3_nce_quotation_fk (quotation_number → ts3_quotations.record_number)
    -- applied post-load via _apply_circular_fks(); this link is optional (NCE may have no quotation)
);


-- ============================================================
-- FOREIGN KEY — circular dependency (added after data load)
-- ============================================================
-- ts3_quotations.nce_number → ts3_nce.record_number
-- ts3_nce.quotation_number  → ts3_quotations.record_number
-- Both applied via ALTER TABLE in _apply_circular_fks() after all tables are populated.
-- They are NOT added here to avoid FK violations during the initial load.


-- ============================================================
-- ts3_categories — (stub — populated in a later pass)
-- ============================================================
CREATE TABLE IF NOT EXISTS ts3_categories (
    id            SERIAL PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL UNIQUE,
    source_file   VARCHAR(4096),
    sub_sheet     VARCHAR(4096),
    ingested_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- CROSS-TABLE RELATIONSHIPS (ts3_ layer)
-- ============================================================
--
-- All cross-table links between EW, NCE, and Quotation are OPTIONAL:
--   EW may exist without NCE.
--   NCE may exist without EW.
--   Quotation may exist without NCE.
--   The direct EW → Quotation shortcut is also optional.
--
-- FK-ENFORCED (contract ownership — always required):
-- ts3_early_warnings.contract_number  → ts3_contracts.contract_number  (FK enforced)
-- ts3_nce.contract_number             → ts3_contracts.contract_number  (FK enforced)
-- ts3_quotations.contract_number      → ts3_contracts.contract_number  (FK enforced)
--
-- POST-LOAD CIRCULAR FKs (applied via _apply_circular_fks() after full data load):
-- ts3_nce.quotation_number            → ts3_quotations.record_number   (circular — post-load; optional)
-- ts3_quotations.nce_number           → ts3_nce.record_number          (circular — post-load; optional)
--
-- SOFT REFERENCES (not DB-enforced — optional business links):
-- ts3_early_warnings.nce_number       → ts3_nce.record_number          (optional — EW may have no NCE)
-- ts3_early_warnings.quotation        → ts3_quotations.record_number   (optional — EW direct shortcut to Q)
-- ts3_early_warnings.category         → ts3_categories.category_name   (optional — soft lookup)
--
-- QUOTATION SELF-REFERENTIAL VERSION CHAIN (within ts3_quotations):
-- ts3_quotations.previous_version_reference → ts3_quotations.record_number  (soft — prior version)
-- ts3_quotations.revised_quotation_number   → ts3_quotations.record_number  (soft — next version)
