-- ============================================================
-- Commercial Management — Annotated Business Schema
-- Project: Tata Steel UK — New EAF Steel Plant, Port Talbot, Wales
-- Source:  Early_Warning___Quotations_and_NCE_Data.xlsx
-- ============================================================
--
-- BUSINESS CONTEXT
-- ----------------
-- This dataset covers the NEC (New Engineering Contract) change management
-- process for the Tata Steel UK new steel plant construction project.
--
-- NEC is the standard contract framework used on UK major infrastructure
-- projects. When something changes on a construction project — a design
-- change, an unexpected site condition, an instruction from the client —
-- a formal process is followed to record the change and agree the cost
-- and schedule impact. This dataset captures that entire process.
--
-- THE NEC CHANGE CHAIN
-- --------------------
-- Step 1 — EARLY WARNING (EW)
--   Either the Contractor or the Consultant (Project Manager) spots
--   something that MIGHT cause a problem. They raise an Early Warning
--   to get it on record and start managing it proactively.
--   → Table: ts_early_warnings
--
-- Step 2 — NOTIFICATION OF COMPENSATION EVENT (NCE)
--   If the potential problem becomes a real event that entitles the
--   contractor to more money or time under the NEC contract, a formal
--   NCE is raised. The NEC clause (60.1 sub-clause) defines the legal
--   basis for the entitlement.
--   → Table: ts_nce
--
-- Step 3 — QUOTATION
--   The contractor submits a Quotation — a formal costed proposal
--   showing how much extra money and how many extra days they need.
--   The Project Manager (PM) reviews and either accepts, asks for a
--   revised quote, or makes their own assessment.
--   → Table: ts_quotations
--
-- MASTER REFERENCE
-- ----------------
-- ts_contracts — the register of all 30 contracts (vendors, values, owners)
-- ts_categories — reference lookup of Early Warning category names (15 types)
--
-- HOW THE TABLES LINK
-- -------------------
--   ts_contracts.contract_number
--       ← ts_early_warnings.contract_number    (which contract the EW is on)
--       ← ts_nce.contract_number               (which contract the NCE is on)
--       ← ts_quotations.contract_number        (which contract the quote is on)
--
--   ts_early_warnings.nce_number
--       → ts_nce.record_number                 (the NCE raised from this EW)
--
--   ts_nce.quotation_reference
--       → ts_quotations.record_number          (the quotation raised for this NCE)
--
--   ts_early_warnings.quotation
--       → ts_quotations.record_number          (direct EW → quotation shortcut)
--
--   ts_early_warnings.category
--       → ts_categories.category_name          (category reference lookup)
--
--   ts_nce.project 
--       → ts_quotations.area                   (project-project reference )
--
-- CONSTRUCTION AREAS
-- ------------------
-- The project is built across several construction areas:
--   • Meltshop     — the main electric arc furnace building (largest area)
--   • Pickle Line  — acid pickling / cleaning line for rolled steel
--   • HRP          — Hot Rolling Plant
--   • Programme    — cross-project / programme-level items
-- Sub-areas within Meltshop include "NG Prep" (natural gas preparation)
-- and "Scrap Yard Prep" (scrap yard preparation works).
--
-- SCALE OF DATA
-- -------------
-- Contracts:       30 vendors, total portfolio ~£280M
--   Top vendors:   CISDI £50.6M, Jaso Cranes £48.1M, Clecim/ABB £37.3M,
--                  Sir Robert McAlpine (SRM) £26.2M, ABB Power £14.9M
-- Early Warnings:  884 total (519 Open, 335 Closed, 30 Submitted)
-- NCEs:            590 total
-- Quotations:      1,151 total
--   Accepted financial impact:
--     Meltshop:    £24.5M price increase + 2,396 days schedule impact
--     Pickle Line: £8.4M price increase + 270 days schedule impact
--     Programme:   -£254K saving + 730 days schedule impact
--
-- CURRENCY: All monetary values are in GBP (British pounds sterling).
--   change_to_prices and contract_value are stored as NUMERIC(18,2)
--   (actual pound values — e.g. 24518766.53 = £24.5M).
-- ============================================================


-- ============================================================
-- 1. ts_contracts — Master register of all project contracts
-- ============================================================
-- 30 live contracts across the project. The contract_number field
-- is the key used to link Early Warnings, NCEs and Quotations back
-- to the relevant contract and vendor.
CREATE TABLE IF NOT EXISTS ts_contracts (
    id              SERIAL PRIMARY KEY,

    -- Contractor / supplier common name
    -- e.g. "CISDI (Crop Shear & Coil Box)", "SRM Civils & Structure"
    vendor          VARCHAR(255),

    -- Full contract reference — unique identifier string in format:
    -- "{Vendor short code} {WBS code}"
    -- e.g. "CISDI Uk 2.2.1.1 3701.00025.CIS.400000.PR.CON.CD.00001"
    -- This exact string appears in ts_early_warnings, ts_nce, ts_quotations
    -- as the contract_number join key.
    contract_number VARCHAR(255),

    -- Name(s) of the Tata Steel staff responsible for this contract
    -- e.g. "Richard Chapman", "Stuart Lloyd - Dave Murray"
    contract_owner  VARCHAR(255),

    -- Original contracted value in GBP.
    -- Null for some framework contracts where value is not fixed upfront.
    contract_value  NUMERIC(18, 2),

    -- Metadata (added at import time)
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- 2. ts_early_warnings — NEC Clause 16 Early Warning Notices
-- ============================================================
-- An Early Warning (EW) is a proactive notification — either from the
-- Contractor or the Project Manager's Consultant — flagging something
-- that may increase cost, delay completion, or impair the works.
--
-- The key purpose is to start discussions early before a problem escalates.
-- Many EWs are raised at a stage where the cost impact is not yet known.
--
-- Status flow: Open → Submitted → Closed
--
-- Volume: 884 EWs total
--   519 Open — still being managed / not yet resolved
--   335 Closed — resolved or no longer at risk
--   30 Submitted — formally submitted to the PM, awaiting decision
--
-- Key observation: 811 of 884 EWs are for Meltshop (the main package).
-- EW-000001 through EW-000003 are the oldest open items (272 days open).
CREATE TABLE IF NOT EXISTS ts_early_warnings (
    id              SERIAL PRIMARY KEY,

    -- Construction package: "Meltshop" | "Pickle" | "HRP" | "Programme"
    project         VARCHAR(100),

    -- Contractor name (the vendor subject to or raising this EW)
    contractor      VARCHAR(150),

    -- Sub-area within the package
    -- e.g. "NG Prep" (natural gas prep), "Scrap Yard Prep"
    -- NULL for the main Meltshop package items
    area            VARCHAR(150),

    -- Full contract reference — links to ts_contracts.contract_number
    contract_number VARCHAR(255),

    -- Unique EW reference across the project. Format: "EW-000001"
    record_number   VARCHAR(50),

    -- If this EW escalated to a formal Compensation Event, this is the
    -- NCE reference it generated. Links to ts_nce.record_number.
    nce_number      VARCHAR(50),

    -- If a quotation was obtained directly for this EW, this is the
    -- quotation reference. Links to ts_quotations.record_number.
    quotation       VARCHAR(50),

    -- Estimated cost impact in GBP. Often NULL at EW stage — the point
    -- of an EW is to flag the issue before the cost is fully known.
    cost            NUMERIC(18, 2),

    -- Short description of the risk/issue being flagged
    -- e.g. "EWN T512/001 - Key Dates", "Drainage Design"
    title           TEXT,

    -- Category of the EW — references ts_categories.category_name
    -- e.g. "Design", "Site Conditions", "Programme", "Third Party"
    category        VARCHAR(255),

    comments_added  TEXT,

    -- Who raised this EW:
    --   "Contractor" — raised by the contractor on the contract
    --   "Consultant" — raised by the PM / consultant team
    notification_by VARCHAR(150),

    notification_date DATE,

    -- Same as notification_by but as a type field:
    -- "Contractor" | "Consultant"
    early_warning_type VARCHAR(100),

    -- Severity of potential impact:
    -- "Very High" | "High" | "Medium" | "Low"
    severity        VARCHAR(50),

    -- Probability that the risk will materialise:
    -- "Occurred"  — the risk has already happened (it is now a real event)
    -- "Very High" | "High" | "Medium" | "Low" — still a future risk
    likelihood      VARCHAR(50),

    -- Combined priority rating (derived from severity × likelihood)
    priority        VARCHAR(50),

    -- Current status: "Open" | "Submitted" | "Closed"
    status          VARCHAR(50),

    -- Number of calendar days since the EW was first raised
    no_days_open    INTEGER,

    -- Date the EW was formally closed (NULL if still open)
    early_warning_closed DATE,

    -- Proposed mitigation action to manage or reduce the risk
    mitigation_measure TEXT,

    -- How the EW was resolved (counts of outcomes)
    ew_accepted     VARCHAR(50),  -- Count of accepted outcomes
    ew_rejected     VARCHAR(50),  -- Count of rejected outcomes
    under_discussion VARCHAR(50), -- Count still under discussion

    -- Metadata (added at import time)
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- 3. ts_nce — Notification of Compensation Events (NEC Clause 61)
-- ============================================================
-- A Compensation Event (CE) is a formal NEC mechanism by which a
-- contractor becomes entitled to additional time and/or money when
-- something happens that is the client's risk under the contract.
--
-- The Project Manager (PM) notifies the contractor of a CE, or the
-- contractor notifies the PM, citing which NEC clause 60.1 applies.
--
-- NEC Clause 60.1 sub-clauses (most common in this project):
--   60.1(1)  — An instruction to change the Scope  (most common, 207+)
--              Covers scope changes, design changes, or new instructions
--   60.1(3)  — Something not provided / omitted from the contract (94)
--              e.g. information, access, or materials not supplied on time
--   60.1(7)  — A change to a previous decision (53)
--              Client / PM reverses an earlier instruction
--   60.1(2)  — No access to or use of the Site (20)
--              Contractor could not get onto the site as needed
--   60.1(12) — Unexpected physical conditions (18)
--              e.g. ground conditions, existing services, contamination
--
-- Volume: 590 NCEs total
-- Most NCEs result in a quotation being requested from the contractor.
CREATE TABLE IF NOT EXISTS ts_nce (
    id              SERIAL PRIMARY KEY,

    -- Construction package: "Meltshop" | "Pickle" | "HRP" | "Programme"
    project         VARCHAR(100),

    contractor      VARCHAR(150),
    area            VARCHAR(150),

    -- Links to ts_contracts.contract_number
    contract_number VARCHAR(255),

    -- Unique NCE reference. Format typically mirrors the EW series.
    record_number   VARCHAR(50),

    -- Who issued the NCE notification
    notification_by VARCHAR(150),

    notification_date DATE,

    -- Short description of the compensation event
    title           TEXT,

    -- The NEC clause 60.1 sub-clause that applies to this CE
    -- e.g. "60.1(1) An instruction to change the Scope"
    compensation_event_type VARCHAR(255),

    -- Date by which the PM must reply to the NCE notification
    reply_due_date  DATE,

    -- Date the PM actually replied
    reply_date      DATE,

    -- Person who provided the PM's reply
    reply_by        VARCHAR(150),

    -- PM's decision on whether this is a valid Compensation Event:
    --   "Event is a compensation event and quotation is instructed"
    --     — CE accepted; contractor must now submit a quotation
    --   "Quotation is not instructed"
    --     — CE accepted but PM won't ask for a quotation (e.g. assessed directly)
    --   NULL — decision pending
    is_compensation_event TEXT,

    -- Reference to the quotation raised for this NCE.
    -- Links to ts_quotations.record_number.
    quotation_reference VARCHAR(50),

    -- Metadata (added at import time)
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- 4. ts_quotations — NEC Clause 62/63 Contractor Quotations
-- ============================================================
-- After a Compensation Event is accepted, the contractor submits a
-- Quotation — a formal proposal stating:
--   • How much additional money they require (change_to_prices)
--   • How many extra calendar days they need (change_to_days)
--
-- The PM can: accept the quote, ask for a revised quote, make their
-- own assessment of value, or terminate the process.
--
-- Volume: 1,151 quotations
-- Total accepted financial impact:
--   Meltshop:    ~£24.5M price increases, 2,396 days delay
--   Pickle Line: ~£8.4M price increases, 270 days delay
--   Programme:   ~-£254K saving, 730 days delay
--
-- IMPORTANT — accepted status values (use for financial totals):
--   "Accepted"            — accepted with full cost breakdown
--   "Accepted_No_CBS"     — accepted without Cost Breakdown Schedule
--   "Acc_with_Instruction"— accepted alongside a PM instruction
CREATE TABLE IF NOT EXISTS ts_quotations (
    id              SERIAL PRIMARY KEY,

    -- Construction area: "Meltshop" | "Pickle Line" | "Programme"
    area            VARCHAR(150),

    contractor      VARCHAR(150),

    -- Links to ts_contracts.contract_number
    contract_number VARCHAR(255),

    -- Unique quotation reference
    record_number   VARCHAR(50),

    -- The NCE this quotation responds to. Links to ts_nce.record_number.
    nce_reference   VARCHAR(100),

    -- PM instruction text that triggered this quotation
    instruction     TEXT,

    -- Current status of the quotation:
    --   "Accepted"            — fully accepted by the PM
    --   "Accepted_No_CBS"     — accepted without cost breakdown schedule (most common)
    --   "Acc_with_Instruction"— accepted with a specific PM instruction
    --   "Submitted"           — submitted by contractor, awaiting PM response
    --   "Revised_Quotation"   — PM asked for a revised version
    --   "Assessment"          — PM is making their own independent assessment
    --   "Internal_Review"     — under Tata Steel internal review
    --   "In_Review"           — being reviewed
    --   "Terminated"          — process terminated / quotation not proceeding
    status          VARCHAR(100),

    quotation_due_date DATE,  -- Date contractor must submit by
    quotation_date     DATE,  -- Date contractor actually submitted

    -- Name of person who submitted the quotation on behalf of the contractor
    quotation_by    VARCHAR(150),

    -- Short description of what this quotation covers
    title           TEXT,

    -- Net price change in GBP (positive = cost increase to client, negative = saving)
    -- e.g. 450000.00 = £450K additional cost
    -- This is a NUMERIC(18,2) — actual pound value, NOT divided by 1,000,000
    change_to_prices NUMERIC(18, 2),

    -- Schedule impact in calendar days (positive = delay to the programme)
    change_to_days  INTEGER,

    -- Name of PM representative who replied
    reply_by        VARCHAR(150),

    -- PM's formal written decision text
    quotation_decision TEXT,

    -- "Yes" | "No" — whether this cost change was recorded on the cost sheet
    carried_to_cost_sheet      VARCHAR(50),

    -- "Yes" | "No" — whether the schedule impact was added to the programme
    impact_carried_to_schedule VARCHAR(50),

    -- Metadata (added at import time)
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- 5. ts_categories — Early Warning category reference list
-- ============================================================
-- 15 standard category names used to classify what type of issue
-- an Early Warning relates to (e.g. Design, Programme, Site Conditions).
-- Referenced by ts_early_warnings.category.
CREATE TABLE IF NOT EXISTS ts_categories (
    id            SERIAL PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL UNIQUE,

    -- Metadata (added at import time)
    source_file     VARCHAR(4096),
    sub_sheet       VARCHAR(4096),
    ingested_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ============================================================
-- CROSS-TABLE RELATIONSHIPS (for reference — not enforced as FKs
-- due to data quality, but valid for JOIN queries)
-- ============================================================
--
-- ts_early_warnings.contract_number  → ts_contracts.contract_number
-- ts_nce.contract_number             → ts_contracts.contract_number
-- ts_quotations.contract_number      → ts_contracts.contract_number
-- ts_early_warnings.nce_number       → ts_nce.record_number
-- ts_nce.quotation_reference         → ts_quotations.record_number
-- ts_early_warnings.quotation        → ts_quotations.record_number
-- ts_early_warnings.category         → ts_categories.category_name


-- ============================================================
-- KEY QUERY PATTERNS (for agent reference)
-- ============================================================

-- 1. Total accepted financial variations by area:
--    SELECT area,
--           SUM(change_to_prices) / 1e6 AS price_change_gbp_millions,
--           SUM(change_to_days) AS total_days_impact
--    FROM ts_quotations
--    WHERE status IN ('Accepted', 'Accepted_No_CBS', 'Acc_with_Instruction')
--    GROUP BY area ORDER BY price_change_gbp_millions DESC;

-- 2. Open Early Warnings by severity (highest risk first):
--    SELECT severity, likelihood, COUNT(*) AS ews, AVG(no_days_open) AS avg_days_open
--    FROM ts_early_warnings
--    WHERE status = 'Open'
--    GROUP BY 1, 2 ORDER BY ews DESC;

-- 3. Full NEC change chain for a contract:
--    SELECT e.record_number AS ew_ref,
--           n.record_number AS nce_ref,
--           q.record_number AS quote_ref,
--           q.change_to_prices,
--           q.change_to_days,
--           q.status
--    FROM ts_early_warnings e
--    LEFT JOIN ts_nce n       ON e.nce_number = n.record_number
--    LEFT JOIN ts_quotations q ON n.quotation_reference = q.record_number
--    WHERE e.contract_number = '...'
--    ORDER BY e.record_number;

-- 4. NCE breakdown by compensation event type:
--    SELECT compensation_event_type,
--           COUNT(*) AS total_nces,
--           SUM(CASE WHEN is_compensation_event ILIKE '%quotation is instructed%'
--               THEN 1 ELSE 0 END) AS with_quotation_instructed
--    FROM ts_nce
--    GROUP BY 1 ORDER BY total_nces DESC;

-- 5. Contract variation exposure vs original value:
--    SELECT c.vendor,
--           c.contract_value,
--           SUM(q.change_to_prices) AS total_variations,
--           ROUND(100.0 * SUM(q.change_to_prices) / c.contract_value, 1) AS variation_pct
--    FROM ts_contracts c
--    LEFT JOIN ts_quotations q
--           ON q.contract_number = c.contract_number
--          AND q.status IN ('Accepted', 'Accepted_No_CBS', 'Acc_with_Instruction')
--    WHERE c.contract_value IS NOT NULL
--    GROUP BY c.vendor, c.contract_value
--    ORDER BY total_variations DESC NULLS LAST;

-- 6. EW conversion rate — how many EWs became NCEs:
--    SELECT COUNT(*) AS total_ews,
--           COUNT(nce_number) AS progressed_to_nce,
--           ROUND(100.0 * COUNT(nce_number) / NULLIF(COUNT(*), 0), 1) AS conversion_pct
--    FROM ts_early_warnings;
