
-- ============================================================
-- BUSINESS MODEL 
-- ============================================================
--
-- 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:
--   • 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)
--   • Unique key for quotations: (contract_number, record_number).
--   • Lookup fields are free-text VARCHAR — valid values documented per column.
-- ============================================================


-- ============================================================
-- ts4_contracts — Expanded contract register 
-- ============================================================
CREATE TABLE IF NOT EXISTS ts4_contracts (
    id              SERIAL PRIMARY KEY,

    -- Construction package this contract belongs to.
    -- Lookup values: "Meltshop" | "Pickleline" | "HRP" | "Programme"
    project         VARCHAR(100),

    -- Contractor / supplier name
    vendor          VARCHAR(255),

    -- Comma-separated abbreviations used for this vendor in ts4_early_warnings,
    -- ts4_nce, and ts4_quotations (those tables store shorter aliases in `contractor`).
    -- NULL when the child tables use the same name as vendor (no alias needed).
    -- Use this column to resolve contractor name mismatches across tables.
    -- E.g. vendor 'AtkinsRéalis' → 'Atkins Realis - A, Atkins Realis - E, AtkinsRealis'
    --      vendor 'Andrew Scott'  → 'ASL, ASL NG Prep, ASL P Field'
    --      vendor 'Andritz'       → 'Isra'  (trades as Isra Parsytec GmbH on this contract)
    vendor_aliases  VARCHAR(500),

    -- Full contract reference (links to ts4_early_warnings etc.)
    contract_number VARCHAR(255) UNIQUE,

    -- NEC contract form used. e.g. "NEC ECC Option A", "NEC ECC Option C"
    contract_type   VARCHAR(100),

    -- Tata Steel staff member responsible for managing this contract.
    contract_manager VARCHAR(255),

    -- Vendor ERP / supplier system code (e.g. "Wernick Buildings Limited/3354997")
    supplier_contractor_code VARCHAR(255),

    -- Secondary contract classification. e.g. "Supplies", "Works", "Services"
    contract_type2  VARCHAR(100),

    -- Full descriptive title of the contract scope
    contract_title  TEXT,

    -- Contract commencement date
    start_of_contract DATE,

    -- Planned contract completion date
    end_of_contract DATE,

    -- Currency code. Expected value: "GBP"
    currency        VARCHAR(10) DEFAULT 'GBP',

    -- Original value at contract award, in the stated currency
    initial_contract_value  NUMERIC(18, 2),

    -- Metadata
    -- source = 'direct-sheet'   — row read directly from the Excel sheet
    -- source = 'alternate-sheet' — auto-inserted stub from EW / NCE / Quotation sheet
    source          VARCHAR(4096) DEFAULT 'direct-sheet',
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- ts4_early_warnings : Early Warning Notification, once the EW is notfied 
-- ============================================================
-- 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 → ts4_contracts.contract_number  (FK enforced)
--   nce_number      → ts4_nce.record_number          (soft — EW may exist without NCE)
--   quotation       → ts4_quotations.record_number   (soft — EW may have no direct quotation)
CREATE TABLE IF NOT EXISTS ts4_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 / supplier name.
    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 Contract.
    -- 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 → ts4_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 → ts4_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,

    -- Person who raised the EW notification (e.g. "John Smith").
    notification_by         VARCHAR(150),
    -- Date when the EW was formally notified to the PM / Tata Steel (not the date when the issue was first identified).
    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"
        -- Submitted - EW has been formally submitted to PM.
        -- Open - If a Risk is meterialised of this EW then the status will be changed to Open. EW under observation or monitoring will also be kept in Open status.
        -- Closed - EW has been closed after acceptance or rejection.
    status                  VARCHAR(50),

    no_days_open            INTEGER,
    -- Date when the EW was closed
    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 ts4_early_warnings_contract_record_uniq UNIQUE (contract_number, record_number),
    CONSTRAINT ts4_ew_contract_fk FOREIGN KEY (contract_number)
        REFERENCES ts4_contracts(contract_number) ON DELETE RESTRICT ON UPDATE CASCADE
);


-- ============================================================
-- ts4_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, and revised_quotation_number.
-- All cross-links are optional: Quotation may exist without NCE.
--   contract_number → ts4_contracts.contract_number  (FK enforced)
--   nce_number      → ts4_nce.record_number           (soft circular — applied post-load; optional)
CREATE TABLE IF NOT EXISTS ts4_quotations (
    id                          SERIAL PRIMARY KEY,

    -- Area here is the Project (FK to ts4_contracts.project) (e.g. "Meltshop", "Pickleline", "HRP", "Programme")
    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 → ts4_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,
    -- Date when the quotation was formally submitted to the PM / Tata Steel.
    quotation_date              DATE,
    quotation_by                VARCHAR(150),
    title                       TEXT,
    -- Quoted price for the change as stated in this version of the quotation.
    change_to_prices            NUMERIC(18, 2),
    -- Quotation to change the schedule by no of days. 
    change_to_days              INTEGER,

    -- ── 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).
    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),

    -- ── End versioning columns ─────────────────────────────────────────────────
    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 ts4_quotations_contract_record_uniq  UNIQUE (contract_number, record_number),
    CONSTRAINT ts4_quot_contract_fk FOREIGN KEY (contract_number)
        REFERENCES ts4_contracts(contract_number) ON DELETE RESTRICT ON UPDATE CASCADE
    -- ts4_quot_nce_fk (nce_number → ts4_nce.record_number) applied post-load via _apply_circular_fks()
);


-- ============================================================
-- ts4_nce
-- ============================================================
-- Primary uniqueness: (contract_number, record_number).
-- All cross-links are optional: NCE may exist without EW; NCE may exist without Quotation.
--   contract_number  → ts4_contracts.contract_number  (FK enforced)
--   quotation_number → ts4_quotations.record_number   
CREATE TABLE IF NOT EXISTS ts4_nce (
    id                          SERIAL PRIMARY KEY,

    -- Construction package. Lookup values: "Meltshop" | "Pickleline" | "HRP" | "Programme"
    project                     VARCHAR(100),

    contractor                  VARCHAR(150),
    area                        VARCHAR(150),
    contract_number             VARCHAR(255),
    record_number               VARCHAR(50),  -- NCE number (e.g. NCE-000001)
    -- Person who raised the NCE
    notification_by             VARCHAR(150),
    -- Date when the NCE was formally notified to the PM / Tata Steel
    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"
    --   "60.1(2) No access to or use of the Site"
    --   "60.1(3) Something not provided / omitted"
    --   "60.1(7) A change to a previous decision"
    --   "60.1(12) Unexpected physical conditions"
    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"
    --   "Quotation is not instructed"
    --   NULL — decision still pending
    is_compensation_event       TEXT,

    -- Quotation submitted for this NCE, if any. Soft reference → ts4_quotations.record_number.
    quotation_number            VARCHAR(50),

    source_file                 VARCHAR(4096),
    sub_sheet                   VARCHAR(4096),
    ingested_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ts4_nce_contract_record_uniq UNIQUE (contract_number, record_number),
    CONSTRAINT ts4_nce_contract_fk FOREIGN KEY (contract_number)
        REFERENCES ts4_contracts(contract_number) ON DELETE RESTRICT ON UPDATE CASCADE
    -- ts4_nce_quotation_fk (quotation_number → ts4_quotations.record_number)
    -- applied post-load via _apply_circular_fks()
);


-- ============================================================
-- FOREIGN KEY — circular dependency (added after data load)
-- ============================================================
-- ts4_quotations.nce_number → ts4_nce.record_number
-- ts4_nce.quotation_number  → ts4_quotations.record_number
-- Both applied via ALTER TABLE in _apply_circular_fks() after all tables are populated.


-- ============================================================
-- ts4_categories — (populated in a later pass)
-- ============================================================
CREATE TABLE IF NOT EXISTS ts4_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 (ts4_ layer)
-- ============================================================
--
-- FK-ENFORCED (contract ownership — always required):
-- ts4_early_warnings.contract_number  → ts4_contracts.contract_number  (FK enforced)
-- ts4_nce.contract_number             → ts4_contracts.contract_number  (FK enforced)
-- ts4_quotations.contract_number      → ts4_contracts.contract_number  (FK enforced)
--
-- POST-LOAD CIRCULAR FKs (applied via _apply_circular_fks() after full data load):
-- ts4_nce.quotation_number            → ts4_quotations.record_number   (circular — post-load; optional)
-- ts4_quotations.nce_number           → ts4_nce.record_number          (circular — post-load; optional)
--
-- SOFT REFERENCES (not DB-enforced — optional business links):
-- ts4_early_warnings.nce_number       → ts4_nce.record_number          (optional)
-- ts4_early_warnings.quotation        → ts4_quotations.record_number   (optional)
-- ts4_early_warnings.category         → ts4_categories.category_name   (optional)
--
-- QUOTATION SELF-REFERENTIAL VERSION CHAIN:
-- ts4_quotations.previous_version_reference → ts4_quotations.record_number  (soft)
-- ts4_quotations.revised_quotation_number   → ts4_quotations.record_number  (soft)
