-- Pacific Logistics · slice 14 · Work Requests (field-to-workshop pipeline)
--
-- The gap this closes: today a Work Order can only be opened by Fleet Manager
-- or Ops Manager. That doesn't fit "driver says the battery is dead" — driver
-- has no way to initiate, and there's no approval chain before parts are ordered.
--
-- Flow:
--     draft (driver preparing)
--      ↓ submit
--     submitted (with Ops Manager for approval)
--      ↓ approve                          ↓ reject
--     approved                            rejected
--      ↓ start sourcing (Procurement)
--     sourcing
--      ↓ mark parts ready
--     ready (Fleet Manager to schedule)
--      ↓ convert to WO
--     wo_created (WR now linked to a WO)
--      ↓ (WO close triggers this)
--     completed
--
--     cancelled — any pre-terminal state can be cancelled
--
-- On CONVERT-TO-WO the model creates a work_orders row using the WR's truck +
-- vendor + urgency (→ kind mapping), copies each WR item to work_order_parts,
-- and links back (wr.wo_id ← WO id, wo.source_wr_id ← WR id). Closing the WO
-- then auto-completes the WR.

CREATE TABLE IF NOT EXISTS work_requests (
    c0d                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id          INT UNSIGNED NOT NULL DEFAULT 1,
    wr_no              VARCHAR(20)   NOT NULL DEFAULT '',   -- WR-00001
    truck_id           INT UNSIGNED  NOT NULL,
    driver_id          INT UNSIGNED  NULL,                  -- who reported (may not be assigned driver)
    urgency            VARCHAR(20)   NOT NULL DEFAULT 'normal', -- low | normal | high | breakdown
    -- Report
    reported_at        DATETIME      NULL,
    reported_by        INT UNSIGNED  NULL,
    truck_odometer     INT UNSIGNED  NULL,
    fault_location     VARCHAR(160)  NULL,                  -- where the truck is / where the fault appears
    symptom            TEXT          NULL,                  -- what's wrong
    -- Estimated + approved amounts
    estimated_cost     DECIMAL(14,2) NOT NULL DEFAULT 0.00, -- computed from items or manually set
    approved_amount    DECIMAL(14,2) NULL,                  -- Ops Manager may cap
    currency           VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    -- Approval / rejection
    approved_by        INT UNSIGNED  NULL,
    approved_at        DATETIME      NULL,
    approval_notes     VARCHAR(255)  NULL,
    rejected_by        INT UNSIGNED  NULL,
    rejected_at        DATETIME      NULL,
    reject_reason      VARCHAR(255)  NULL,
    -- Procurement / sourcing
    sourcing_by        INT UNSIGNED  NULL,
    sourcing_started_at DATETIME     NULL,
    sourcing_notes     VARCHAR(255)  NULL,
    sourced_vendor_id  INT UNSIGNED  NULL,                  -- primary vendor if single-source
    ready_at           DATETIME      NULL,
    ready_by           INT UNSIGNED  NULL,
    -- Linked WO once workshop takes it
    wo_id              INT UNSIGNED  NULL,
    -- Terminal states
    completed_at       DATETIME      NULL,
    completed_by       INT UNSIGNED  NULL,
    cancelled_at       DATETIME      NULL,
    cancelled_by       INT UNSIGNED  NULL,
    cancel_reason      VARCHAR(255)  NULL,
    -- Lifecycle
    status             VARCHAR(30)   NOT NULL DEFAULT 'draft',
                                                    -- draft | submitted | approved | sourcing | ready |
                                                    -- wo_created | completed | rejected | cancelled
    notes              TEXT          NULL,
    -- Submitted / audit
    submitted_by       INT UNSIGNED  NULL,
    submitted_at       DATETIME      NULL,
    created_by         INT UNSIGNED  NULL,
    created_on         DATETIME      NULL,
    edited_by          INT UNSIGNED  NULL,
    edited_on          DATETIME      NULL,
    deleted            TINYINT(1)    NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_wr_no (wr_no),
    KEY idx_wr_truck   (truck_id, status),
    KEY idx_wr_driver  (driver_id),
    KEY idx_wr_status  (status, deleted),
    KEY idx_wr_urgency (urgency),
    KEY idx_wr_reported (reported_at),
    KEY idx_wr_wo      (wo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS work_request_items (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    work_request_id  INT UNSIGNED NOT NULL,
    seq_no           INT          NOT NULL DEFAULT 1,
    item_type        VARCHAR(20)  NOT NULL DEFAULT 'part',   -- part | labour | external_service
    description      VARCHAR(255) NOT NULL,
    category         VARCHAR(30)  NOT NULL DEFAULT 'spare_parts',
                                                    -- same set as work_order_parts: spare_parts | tyres |
                                                    -- batteries | lubricants | consumables (plus 'labour')
    qty              DECIMAL(10,3) NOT NULL DEFAULT 1.000,
    uom              VARCHAR(20)   NOT NULL DEFAULT 'unit',
    unit_cost_est    DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_est        DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    -- Procurement info (set during sourcing)
    vendor_id        INT UNSIGNED  NULL,
    actual_cost      DECIMAL(14,2) NULL,
    sourcing_status  VARCHAR(30)   NOT NULL DEFAULT 'not_started',
                                                    -- not_started | in_stock | sourced | delivered
    notes            VARCHAR(255)  NULL,
    created_by       INT UNSIGNED  NULL,
    created_on       DATETIME      NULL,
    deleted          TINYINT(1)    NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    KEY idx_wri_wr (work_request_id, deleted, seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS work_request_transitions (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    work_request_id  INT UNSIGNED NOT NULL,
    from_status      VARCHAR(30)  NULL,
    to_status        VARCHAR(30)  NOT NULL,
    actor_id         INT UNSIGNED NULL,
    actor_name       VARCHAR(120) NULL,
    action_at        DATETIME     NOT NULL,
    note             VARCHAR(255) NULL,
    PRIMARY KEY (c0d),
    KEY idx_wrt_wr (work_request_id, action_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Link work_orders back to the source WR so WO close can auto-complete the WR.
-- Placed at end of table (no AFTER clause) — the original write specified
-- AFTER trip_request_id which doesn't exist on work_orders, so the whole ALTER
-- silently failed the first time.
ALTER TABLE work_orders ADD COLUMN source_wr_id INT UNSIGNED NULL;
ALTER TABLE work_orders ADD KEY idx_wo_source_wr (source_wr_id);


-- ═══════════════════════════════════════════════════════════════════════
-- Grants
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Administrator (10) — full
(10, 'WorkRequests', 'index',        1, NOW()), (10, 'WorkRequests', 'view',           1, NOW()),
(10, 'WorkRequests', 'add',          1, NOW()), (10, 'WorkRequests', 'save',           1, NOW()),
(10, 'WorkRequests', 'edit',         1, NOW()), (10, 'WorkRequests', 'update',         1, NOW()),
(10, 'WorkRequests', 'submit',       1, NOW()), (10, 'WorkRequests', 'approve',        1, NOW()),
(10, 'WorkRequests', 'reject',       1, NOW()), (10, 'WorkRequests', 'cancel',         1, NOW()),
(10, 'WorkRequests', 'startSourcing',1, NOW()), (10, 'WorkRequests', 'markReady',      1, NOW()),
(10, 'WorkRequests', 'convertToWo',  1, NOW()),
(10, 'WorkRequests', 'addItem',      1, NOW()), (10, 'WorkRequests', 'deleteItem',     1, NOW()),
(10, 'WorkRequests', 'delete',       1, NOW()),
-- Ops Manager (20) — full (owns approval)
(20, 'WorkRequests', 'index',        1, NOW()), (20, 'WorkRequests', 'view',           1, NOW()),
(20, 'WorkRequests', 'add',          1, NOW()), (20, 'WorkRequests', 'save',           1, NOW()),
(20, 'WorkRequests', 'edit',         1, NOW()), (20, 'WorkRequests', 'update',         1, NOW()),
(20, 'WorkRequests', 'submit',       1, NOW()), (20, 'WorkRequests', 'approve',        1, NOW()),
(20, 'WorkRequests', 'reject',       1, NOW()), (20, 'WorkRequests', 'cancel',         1, NOW()),
(20, 'WorkRequests', 'addItem',      1, NOW()), (20, 'WorkRequests', 'deleteItem',     1, NOW()),
-- Fleet Manager (22) — full incl. convert-to-WO
(22, 'WorkRequests', 'index',        1, NOW()), (22, 'WorkRequests', 'view',           1, NOW()),
(22, 'WorkRequests', 'add',          1, NOW()), (22, 'WorkRequests', 'save',           1, NOW()),
(22, 'WorkRequests', 'edit',         1, NOW()), (22, 'WorkRequests', 'update',         1, NOW()),
(22, 'WorkRequests', 'submit',       1, NOW()), (22, 'WorkRequests', 'cancel',         1, NOW()),
(22, 'WorkRequests', 'markReady',    1, NOW()), (22, 'WorkRequests', 'convertToWo',    1, NOW()),
(22, 'WorkRequests', 'addItem',      1, NOW()), (22, 'WorkRequests', 'deleteItem',     1, NOW()),
-- Procurement (7) — sourcing owner
(7,  'WorkRequests', 'index',        1, NOW()), (7,  'WorkRequests', 'view',           1, NOW()),
(7,  'WorkRequests', 'startSourcing',1, NOW()), (7,  'WorkRequests', 'markReady',      1, NOW()),
(7,  'WorkRequests', 'addItem',      1, NOW()), (7,  'WorkRequests', 'deleteItem',     1, NOW()),
-- Driver (25) — create + submit (used by mobile in Iter 3, web fallback here)
(25, 'WorkRequests', 'index',        1, NOW()), (25, 'WorkRequests', 'view',           1, NOW()),
(25, 'WorkRequests', 'add',          1, NOW()), (25, 'WorkRequests', 'save',           1, NOW()),
(25, 'WorkRequests', 'submit',       1, NOW()), (25, 'WorkRequests', 'cancel',         1, NOW()),
-- Dispatcher (21) — read (workshop status affects dispatch)
(21, 'WorkRequests', 'index',        1, NOW()), (21, 'WorkRequests', 'view',           1, NOW()),
-- Accountant (3) / Finance Manager (2) / Executive (1) — read
(3,  'WorkRequests', 'index', 1, NOW()), (3,  'WorkRequests', 'view', 1, NOW()),
(2,  'WorkRequests', 'index', 1, NOW()), (2,  'WorkRequests', 'view', 1, NOW()),
(1,  'WorkRequests', 'index', 1, NOW()), (1,  'WorkRequests', 'view', 1, NOW());
