-- Slice 8b-4c · Requisition retirement
-- After cash is issued, PC records actual spends per category. Accountant reviews & approves.
-- Variance auto-computed: issue_amount - retired_amount (positive = unspent to return; negative = overspend).

CREATE TABLE IF NOT EXISTS service_requisition_expenses (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    req_id            INT NOT NULL,
    line_no           INT NOT NULL DEFAULT 0,
    -- Same 7-category enum as service_requisition_lines so variance can be computed by category.
    category          ENUM('FUEL','TRANSPORT','MEALS','ACCOMMODATION','MATERIALS','LABOUR','MISC') NOT NULL DEFAULT 'MISC',
    description       VARCHAR(500) NOT NULL,
    amount            DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    -- Receipt reference (voucher / EFD serial / free-text). Not enforced — some spends are receipt-less.
    receipt_ref       VARCHAR(120) NOT NULL DEFAULT '',
    receipt_date      DATE NULL,
    is_receipted      TINYINT(1) NOT NULL DEFAULT 0,
    notes             VARCHAR(500) NOT NULL DEFAULT '',
    created_by        INT NOT NULL DEFAULT 0,
    created_by_name   VARCHAR(120) 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;

-- Retirement lifecycle columns on the header
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE service_requisitions
        ADD COLUMN retirement_submitted_on   TIMESTAMP NULL         AFTER retired_on,
        ADD COLUMN retirement_approved_by    INT NOT NULL DEFAULT 0 AFTER retirement_submitted_on,
        ADD COLUMN retirement_approved_by_name VARCHAR(120) NOT NULL DEFAULT "" AFTER retirement_approved_by,
        ADD COLUMN retired_amount            DECIMAL(20,2) NOT NULL DEFAULT 0.00  AFTER retirement_approved_by_name,
        ADD COLUMN variance_amount           DECIMAL(20,2) NOT NULL DEFAULT 0.00  AFTER retired_amount,
        ADD COLUMN retirement_notes          VARCHAR(500) NOT NULL DEFAULT ""     AFTER variance_amount',
    'SELECT "service_requisitions.retirement_submitted_on already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'service_requisitions' AND column_name = 'retirement_submitted_on');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
