-- ============================================================
-- DATABASE SCHEMA: Construction Project Contract Management
-- Framework: NEC (New Engineering Contract)
-- ============================================================

-- ------------------------------------------------------------
-- Table: ts_contracts
-- Master list of all vendor contracts in the project.
-- This is the parent table — all other tables link to this.
-- ------------------------------------------------------------
CREATE TABLE ts_contracts (
    contract_number TEXT,        -- Unique contract identifier (e.g. "Andrew Scott Ltd CCI2011...")
    vendor          TEXT,        -- Name of the contractor/vendor company
    contract_owner  TEXT,        -- Person(s) responsible for managing this contract
    contract_value  NUMERIC      -- Total agreed contract value in GBP (£)
);

-- ------------------------------------------------------------
-- Table: ts_categories
-- Lookup/reference table for Early Warning classification types.
-- e.g. "Change to Scope of Work - Design", "Cost Increase"
-- ------------------------------------------------------------
CREATE TABLE ts_categories (
    category_name TEXT PRIMARY KEY  -- Predefined EW category name
);

-- ------------------------------------------------------------
-- Table: ts_early_warnings
-- Records all Early Warnings (EW) raised on the project.
-- An Early Warning is a proactive risk/issue flag raised by
-- the contractor or project team before it becomes a problem.
-- An EW may later be linked to an NCE and/or a Quotation.
-- ------------------------------------------------------------
CREATE TABLE ts_early_warnings (
    record_number        TEXT PRIMARY KEY,  -- Unique EW identifier (e.g. "EW-000001")
    project              TEXT,              -- Project area (e.g. "Meltshop", "Pickle Line")
    contractor           TEXT,              -- Contractor who raised the EW
    area                 TEXT,              -- Sub-area within the project
    contract_number      TEXT REFERENCES ts_contracts(contract_number),  -- Parent contract
    nce_number           TEXT REFERENCES ts_nce(record_number),              -- Linked NCE record number (nullable if not yet raised)
    quotation            TEXT REFERENCES ts_quotations(record_number),              -- Linked Quotation record number (nullable)
    cost                 NUMERIC,           -- Estimated cost impact in GBP (£)
    title                TEXT,              -- Short description of the early warning
    category             TEXT REFERENCES ts_categories(category_name),   -- EW classification type
    comments_added       TEXT,              -- Additional comments or notes
    notification_by      TEXT,              -- Person who raised the EW
    notification_date    DATE,              -- Date EW was raised
    early_warning_type   TEXT,              -- Type of warning (e.g. "Programme", "Cost")
    severity             TEXT,              -- Risk severity: High / Medium / Low
    likelihood           TEXT,              -- Likelihood of occurrence: High / Medium / Low
    priority             TEXT,              -- Priority level derived from severity & likelihood
    status               TEXT,              -- Current status: Open / Closed
    no_days_open         INTEGER,           -- Number of days the EW has been open
    early_warning_closed DATE,              -- Date EW was closed (null if still open)
    mitigation           TEXT,              -- Agreed mitigation action to avoid/reduce the risk
    ew_accepted          TEXT,              -- Whether EW was accepted
    ew_rejected          TEXT,              -- Whether EW was rejected
    under_discussion     TEXT               -- Whether EW is still under discussion
);

-- ------------------------------------------------------------
-- Table: ts_nce
-- Records all NEC Compensation Events (NCE).
-- A Compensation Event is a formal contract change that may
-- entitle the contractor to additional time and/or money.
-- NCEs are raised against a contract and reviewed by the PM.
-- ------------------------------------------------------------
CREATE TABLE ts_nce (
    record_number          TEXT PRIMARY KEY,  -- Unique NCE identifier (e.g. "NCE-000001")
    project                TEXT,              -- Project area
    contractor             TEXT,              -- Contractor involved
    area                   TEXT,              -- Sub-area within the project
    contract_number        TEXT REFERENCES ts_contracts(contract_number),  -- Parent contract
    notification_by        TEXT,              -- Person who raised the NCE
    notification_date      DATE,              -- Date NCE was notified
    title                  TEXT,              -- Short description of the compensation event
    compensation_event_type TEXT,             -- Type of CE (e.g. "Employer instruction")
    reply_due_date         DATE,              -- Deadline for PM to respond
    reply_date             DATE,              -- Actual date PM responded
    reply_by               TEXT,              -- Person who replied
    is_compensation_event  TEXT,              -- PM decision: Yes / No / TBC
    quotation              TEXT REFERENCES ts_quotations(record_number)  -- Linked quotation record number
);

-- ------------------------------------------------------------
-- Table: ts_quotations
-- Records all Quotations submitted against Compensation Events.
-- A Quotation details the cost and time impact of an NCE.
-- The PM can Accept, Reject or request a revised Quotation.
-- ------------------------------------------------------------
CREATE TABLE ts_quotations (
    record_number                   TEXT PRIMARY KEY,  -- Unique Quotation ID (e.g. "QUOTE-000001")
    area                            TEXT,              -- Project sub-area
    contractor                      TEXT,              -- Contractor who submitted the quotation
    contract_number                 TEXT REFERENCES ts_contracts(contract_number),  -- Parent contract
    notification_of_compensation_event TEXT,           -- Reference to the CE being quoted
    instruction                     TEXT,              -- PM instruction that triggered the quotation
    status                          TEXT,              -- Quotation status (e.g. Open, Submitted)
    quotation_due_date              DATE,              -- Deadline for submitting the quotation
    quotation_date                  DATE,              -- Date quotation was submitted
    quotation_by                    TEXT,              -- Person who submitted the quotation
    title                           TEXT,              -- Short description
    change_to_prices                NUMERIC,           -- Cost impact in GBP (£) — can be negative
    change_to_days                  INTEGER,           -- Time impact in days — can be negative
    reply_by                        DATE,              -- PM reply deadline
    quotation_decision              TEXT,              -- PM decision: Accepted / Rejected / Pending
    carried_to_cost_sheet           TEXT,              -- Whether cost has been carried to cost sheet
    impact_carried_to_schedule      TEXT               -- Whether time impact carried to schedule
);

-- ------------------------------------------------------------
-- Cross-table relationships
-- ------------------------------------------------------------

-- Link Early Warning to its NCE (if one has been raised)
ALTER TABLE ts_early_warnings
    ADD CONSTRAINT fk_ew_nce
    FOREIGN KEY (nce_number) REFERENCES ts_nce(record_number);

-- Link Early Warning to its Quotation (if one has been submitted)
ALTER TABLE ts_early_warnings
    ADD CONSTRAINT fk_ew_quotation
    FOREIGN KEY (quotation) REFERENCES ts_quotations(record_number);

-- ============================================================
-- VIEW: vw_weekly_report
-- Mirrors the 'Weekly Report' sheet
-- Shows per-contractor: base value, implemented/unimplemented
-- variations, total commitment and percentage
-- Implemented = Accepted + Accepted_NO_CBS quotations
-- Unimplemented = Submitted/In Review/Assessment/Revised quotations
-- Total Commitment % = Total / Base Contract Value
-- ============================================================
CREATE OR REPLACE VIEW vw_weekly_report AS
SELECT
    c.vendor,
    c.contract_number,
    c.contract_owner,
    c.contract_value AS base_contract_value,

    -- Implemented Variations (Accepted quotations)
    COALESCE(SUM(q.change_to_prices) FILTER (
        WHERE q.status IN ('Accepted', 'Accepted_NO_CBS')
    ), 0) AS implemented_variations,

    -- Unimplemented Variations (in-progress quotations)
    COALESCE(SUM(q.change_to_prices) FILTER (
        WHERE q.status IN ('Submitted', 'Revised_Quotation', 'Assessment', 'Internal Review', 'In_Review')
    ), 0) AS unimplemented_variations,

    -- Total = Base + Implemented + Unimplemented
    c.contract_value
    + COALESCE(SUM(q.change_to_prices) FILTER (
        WHERE q.status IN ('Accepted', 'Accepted_NO_CBS')
    ), 0)
    + COALESCE(SUM(q.change_to_prices) FILTER (
        WHERE q.status IN ('Submitted', 'Revised_Quotation', 'Assessment', 'Internal Review', 'In_Review')
    ), 0) AS total,

    -- Total Commitment % = Total / Base Contract Value
    ROUND(
        (c.contract_value
        + COALESCE(SUM(q.change_to_prices) FILTER (
            WHERE q.status IN ('Accepted', 'Accepted_NO_CBS')
        ), 0)
        + COALESCE(SUM(q.change_to_prices) FILTER (
            WHERE q.status IN ('Submitted', 'Revised_Quotation', 'Assessment', 'Internal Review', 'In_Review')
        ), 0))
        / NULLIF(c.contract_value, 0), 6
    ) AS total_commitment_pct

FROM ts_contracts c
LEFT JOIN ts_quotations q ON q.contract_number = c.contract_number
GROUP BY c.vendor, c.contract_number, c.contract_owner, c.contract_value;


-- ============================================================
-- VIEW: vw_ew_dashboard
-- Mirrors the 'EW_Dash Board' sheet
-- Shows count & % of Early Warnings by Status and by Category
-- ============================================================

-- Part 1: EW counts by Status
CREATE OR REPLACE VIEW vw_ew_status_summary AS
SELECT
    status,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 1.0 / NULLIF(SUM(COUNT(*)) OVER (), 0), 6) AS percentage
FROM ts_early_warnings
GROUP BY status;

-- Part 2: EW counts by Category
CREATE OR REPLACE VIEW vw_ew_category_summary AS
SELECT
    category,
    COUNT(*) AS count_of_ew
FROM ts_early_warnings
GROUP BY category;