-- Slice 8c-Spine · Works framework schema
-- BOQ + Phase + Consolidated Request + Variation Order. Framework only — no surfaces yet.
-- 7 pillars: Preliminaries · Manpower · Materials · Equipment · Tools · Sub-contracts · Overheads.

/* ────────────────────────────────────────────────────────────────────────
   BOQ (contract Bill of Quantities — one per project, snapshotted from
   tender pricing on contract approval)
   ──────────────────────────────────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS works_boqs (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id             INT NOT NULL,
    title                  VARCHAR(200) NOT NULL DEFAULT '',
    currency               VARCHAR(10)  NOT NULL DEFAULT 'TZS',
    contract_value         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    variation_value        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    -- What produced the BOQ. Snapshot links back to the tender pricing version.
    snapshot_source        VARCHAR(60)  NOT NULL DEFAULT 'TENDER_PRICING',
    snapshot_pricing_version_id INT NOT NULL DEFAULT 0,
    -- BOQ status stays ACTIVE for the life of the project; CLOSED at handover.
    status                 ENUM('ACTIVE','CLOSED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    notes                  TEXT NULL,
    created_by             INT NOT NULL DEFAULT 0,
    created_by_name        VARCHAR(120) NOT NULL DEFAULT '',
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by              INT NOT NULL DEFAULT 0,
    edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_project (project_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_boq_items (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    boq_id                 INT NOT NULL,
    project_id             INT NOT NULL,
    item_no                VARCHAR(30) NOT NULL DEFAULT '',
    section                VARCHAR(120) NOT NULL DEFAULT '',
    description            VARCHAR(500) NOT NULL,
    unit                   VARCHAR(20) NOT NULL DEFAULT '',
    -- Contract values (immutable at signing; variations append new rows, not amend these).
    contract_qty           DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    contract_rate          DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    contract_amount        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    -- Live cumulative certified quantity (updated by IPC evaluations later).
    cumulative_qty_done    DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    cumulative_value_done  DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    -- Remeasurable = qty may change with actual measurement. Lump-sum = qty fixed at 1.
    remeasurable           TINYINT(1) NOT NULL DEFAULT 1,
    origin                 ENUM('CONTRACT','VARIATION') NOT NULL DEFAULT 'CONTRACT',
    source_variation_id    INT NOT NULL DEFAULT 0,
    notes                  TEXT NULL,
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_boq (boq_id, deleted),
    KEY ix_project (project_id),
    KEY ix_origin (origin, source_variation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ────────────────────────────────────────────────────────────────────────
   Phases (stable containers of work — Mobilisation, Earthworks, Base…)
   ──────────────────────────────────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS works_phases (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id             INT NOT NULL,
    phase_no               INT NOT NULL DEFAULT 0,
    code                   VARCHAR(40) NOT NULL DEFAULT '',
    title                  VARCHAR(200) NOT NULL,
    description            TEXT NULL,
    planned_start          DATE NULL,
    planned_end            DATE NULL,
    actual_start           DATE NULL,
    actual_end             DATE NULL,
    -- PLANNED: still in design, no CR yet.
    -- ACTIVE:  has an ACTIVE Consolidated Request; site is executing.
    -- CLOSED:  handed over, no more work.
    status                 ENUM('PLANNED','ACTIVE','CLOSED','CANCELLED') NOT NULL DEFAULT 'PLANNED',
    created_by             INT NOT NULL DEFAULT 0,
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by              INT NOT NULL DEFAULT 0,
    edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_project_phase (project_id, phase_no),
    KEY ix_project_status (project_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Which BOQ items this phase touches, and how much of each item is planned within this phase.
-- Sum of planned_qty across phases for a given BOQ item ≤ contract_qty (soft-enforced in model).
CREATE TABLE IF NOT EXISTS works_phase_boq_items (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    phase_id               INT NOT NULL,
    boq_item_id            INT NOT NULL,
    planned_qty            DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    notes                  VARCHAR(500) NOT NULL DEFAULT '',
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_phase_boq_item (phase_id, boq_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The actual tasks the site will do to deliver this phase's BOQ output.
CREATE TABLE IF NOT EXISTS works_phase_activities (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    phase_id               INT NOT NULL,
    activity_no            INT NOT NULL DEFAULT 0,
    code                   VARCHAR(40) NOT NULL DEFAULT '',
    title                  VARCHAR(200) NOT NULL,
    description            TEXT NULL,
    unit                   VARCHAR(20) NOT NULL DEFAULT '',
    planned_qty            DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    -- Optional link back to the BOQ item this activity primarily fulfils.
    linked_boq_item_id     INT NOT NULL DEFAULT 0,
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_phase (phase_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ────────────────────────────────────────────────────────────────────────
   Consolidated Requests (the phase execution package + budget across 7 pillars)
   ──────────────────────────────────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS works_crs (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id             INT NOT NULL,
    phase_id               INT NOT NULL,
    cr_no                  VARCHAR(40) NOT NULL DEFAULT '',
    title                  VARCHAR(200) NOT NULL,
    version                INT NOT NULL DEFAULT 1,
    -- Chain of revisions on the same phase: v2 supersedes v1 when it goes ACTIVE.
    supersedes_id          INT NOT NULL DEFAULT 0,
    -- DRAFT: PC building it. SUBMITTED: chain in progress.
    -- APPROVED: chain terminal APPROVED but not yet activated (edge state).
    -- ACTIVE: currently the executing package for its phase. Only one ACTIVE per phase at a time.
    -- SUPERSEDED: a newer revision replaced this one.
    -- CLOSED: phase finished, this CR retired.
    status                 ENUM('DRAFT','SUBMITTED','APPROVED','ACTIVE','SUPERSEDED','CLOSED','DECLINED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    approval_id            INT NOT NULL DEFAULT 0,
    -- Pillar rollups. Persisted for cheap reads; recomputed by the model on line change.
    total_preliminaries    DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    total_manpower         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    total_materials        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    total_equipment        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    total_tools            DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    total_subcontracts     DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    total_overheads        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    grand_total            DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    notes                  TEXT NULL,
    submitted_on           TIMESTAMP NULL,
    approved_on            TIMESTAMP NULL,
    activated_on           TIMESTAMP NULL,
    superseded_on          TIMESTAMP NULL,
    created_by             INT NOT NULL DEFAULT 0,
    created_by_name        VARCHAR(120) NOT NULL DEFAULT '',
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by              INT NOT NULL DEFAULT 0,
    edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_project_phase (project_id, phase_id),
    KEY ix_status (status),
    UNIQUE KEY uq_cr_no (cr_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activities the CR commits to executing this cycle. Copied from phase activities at draft time
-- (frozen snapshot) so the CR remains stable even if phase activities change later.
CREATE TABLE IF NOT EXISTS works_cr_activities (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id                  INT NOT NULL,
    activity_no            INT NOT NULL DEFAULT 0,
    linked_phase_activity_id INT NOT NULL DEFAULT 0,
    linked_boq_item_id     INT NOT NULL DEFAULT 0,
    title                  VARCHAR(200) NOT NULL,
    description            TEXT NULL,
    unit                   VARCHAR(20) NOT NULL DEFAULT '',
    planned_qty            DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ── 7 Pillar line tables ───────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS works_cr_preliminaries (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    description   VARCHAR(500) NOT NULL,
    amount        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_cr_manpower (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    role          VARCHAR(120) NOT NULL,
    persons       INT NOT NULL DEFAULT 1,
    days          DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    rate          DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    total         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_cr_materials (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    description   VARCHAR(500) NOT NULL,
    qty           DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    unit          VARCHAR(20) NOT NULL DEFAULT '',
    unit_cost     DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    total         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    -- Nullable link to a future Stores catalogue item. Free-text for now.
    stores_item_id INT NOT NULL DEFAULT 0,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_cr_equipment (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    description   VARCHAR(500) NOT NULL,
    -- Nullable link to the Assets register once wired.
    asset_id      INT NOT NULL DEFAULT 0,
    hours         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    rate          DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    total         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    in_house_flag TINYINT(1) NOT NULL DEFAULT 1,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_cr_tools (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    description   VARCHAR(500) NOT NULL,
    qty           DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    unit          VARCHAR(20) NOT NULL DEFAULT '',
    unit_cost     DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    total         DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_cr_subcontracts (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    subcontractor_name VARCHAR(200) NOT NULL,
    scope         VARCHAR(500) NOT NULL DEFAULT '',
    lump_sum      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_cr_overheads (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cr_id         INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    -- Category = HSE / SITE_OFFICE / TESTING / INSURANCE / OTHER
    category      VARCHAR(40) NOT NULL DEFAULT 'OTHER',
    description   VARCHAR(500) NOT NULL,
    amount        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    notes         VARCHAR(500) NOT NULL DEFAULT '',
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_cr (cr_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ────────────────────────────────────────────────────────────────────────
   Variation Orders — client-instructed additions to the BOQ.
   Approved variations append new rows to works_boq_items with origin='VARIATION'.
   ──────────────────────────────────────────────────────────────────────── */

CREATE TABLE IF NOT EXISTS works_variations (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id             INT NOT NULL,
    boq_id                 INT NOT NULL,
    vo_no                  VARCHAR(40) NOT NULL DEFAULT '',
    title                  VARCHAR(200) NOT NULL,
    description            TEXT NULL,
    engineer_ref           VARCHAR(120) NOT NULL DEFAULT '',
    engineer_instruction_date DATE NULL,
    status                 ENUM('DRAFT','PRICED','SUBMITTED','APPROVED','DECLINED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    approval_id            INT NOT NULL DEFAULT 0,
    total_value            DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    submitted_on           TIMESTAMP NULL,
    approved_on            TIMESTAMP NULL,
    -- Once applied, the new lines have been written into works_boq_items.
    applied_on             TIMESTAMP NULL,
    notes                  TEXT NULL,
    created_by             INT NOT NULL DEFAULT 0,
    created_by_name        VARCHAR(120) NOT NULL DEFAULT '',
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by              INT NOT NULL DEFAULT 0,
    edited_on              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_vo_no (vo_no),
    KEY ix_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS works_variation_boq_items (
    c0d                    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    variation_id           INT NOT NULL,
    item_no                VARCHAR(30) NOT NULL DEFAULT '',
    section                VARCHAR(120) NOT NULL DEFAULT '',
    description            VARCHAR(500) NOT NULL,
    unit                   VARCHAR(20) NOT NULL DEFAULT '',
    qty                    DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    rate                   DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
    amount                 DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    remeasurable           TINYINT(1) NOT NULL DEFAULT 1,
    notes                  TEXT NULL,
    created_on             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted                TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_variation (variation_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
