-- Slice 8b-4a · Service Requisition foundation
-- The float/imprest ladder starts with a Requisition: PC drafts, chain approves, then
-- (later slices) Accountant issues cash, PC spends against categories, Accountant approves retirement.
-- This slice ships the DRAFT → SUBMITTED → APPROVED spine only.
-- Later: ISSUED · RETIRING · RETIRED · CLOSED surfaces.

CREATE TABLE IF NOT EXISTS service_requisitions (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id        INT NOT NULL,
    req_no            VARCHAR(30) NOT NULL,
    title             VARCHAR(200) NOT NULL,
    purpose           TEXT NULL,
    -- DRAFT: PC building.  SUBMITTED: chain running.  APPROVED: chain terminal, awaiting issue.
    -- ISSUED (later): Accountant released cash.  RETIRING (later): PC recording spends.
    -- RETIRED (later): Accountant approved retirement.  CLOSED (later): posted to GL.
    status            ENUM('DRAFT','SUBMITTED','APPROVED','DECLINED','CANCELLED',
                            'ISSUED','RETIRING','RETIRED','CLOSED') NOT NULL DEFAULT 'DRAFT',
    approval_id       INT NOT NULL DEFAULT 0,
    total_amount      DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    currency          VARCHAR(10) NOT NULL DEFAULT 'TZS',
    -- Free-text for now; later slice ties to a callout id, PM visit, etc.
    context_ref       VARCHAR(200) NOT NULL DEFAULT '',
    submitted_on      TIMESTAMP NULL,
    approved_on       TIMESTAMP NULL,
    issued_on         TIMESTAMP NULL,
    retired_on        TIMESTAMP NULL,
    closed_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,
    UNIQUE KEY uq_req_no (req_no),
    KEY ix_project_status (project_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_requisition_lines (
    c0d           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    req_id        INT NOT NULL,
    line_no       INT NOT NULL DEFAULT 0,
    -- Category discriminator so the retirement can enforce spend-by-category.
    category      ENUM('FUEL','TRANSPORT','MEALS','ACCOMMODATION','MATERIALS','LABOUR','MISC') NOT NULL DEFAULT 'MISC',
    description   VARCHAR(500) NOT NULL,
    qty           DECIMAL(20,4) NOT NULL DEFAULT 1.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,
    edited_on     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted       TINYINT(1) NOT NULL DEFAULT 0,
    KEY ix_req (req_id, deleted),
    KEY ix_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
