-- Pacific Logistics · slice 12 · Vendors + Work Orders + Auto-post
--
-- Rsflow's `vendors` table is already comprehensive (vendor_no, vendor_name,
-- vendor_tin/vrn, address, phones, category, etc.) so we only add grants and
-- a light Pacific-facing UI — no schema changes to vendors.
--
-- Work Order lifecycle:
--     open → in_progress → completed → closed
--                                    └→ cancelled
--
-- On CLOSE the model posts one JE with a debit line per parts category:
--     Dr 5120 Spare Parts        (sum of parts where category='spare_parts')
--     Dr 5130 Tyres              (category='tyres')
--     Dr 5140 Batteries          (etc.)
--     Dr 5150 Lubricants
--     Dr 5160 Workshop Consumables
--     Dr 5110 Repairs & Maintenance   (labour_cost)
--     Dr 5170 External Workshop      (external_cost)
--     Cr 2010 Accounts Payable       (total)
--
-- All lines tagged with truck_id, work_order_id, vendor_id → reports slice
-- maintenance cost per truck / per vendor / per part category cleanly.

CREATE TABLE IF NOT EXISTS work_orders (
    c0d               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id         INT UNSIGNED NOT NULL DEFAULT 1,
    wo_no             VARCHAR(20)   NOT NULL DEFAULT '',   -- WO-00001
    truck_id          INT UNSIGNED  NOT NULL,
    vendor_id         INT UNSIGNED  NULL,                  -- NULL = internal workshop
    kind              VARCHAR(20)   NOT NULL DEFAULT 'planned',
                                                    -- planned | breakdown | callout | inspection | recall
    priority          VARCHAR(20)   NOT NULL DEFAULT 'normal',
                                                    -- low | normal | high | urgent
    status            VARCHAR(20)   NOT NULL DEFAULT 'open',
                                                    -- open | in_progress | completed | closed | cancelled
    -- Schedule / progress
    opened_on         DATE          NOT NULL,
    opened_by         INT UNSIGNED  NULL,
    opened_km         INT UNSIGNED  NULL,
    started_at        DATETIME      NULL,
    started_by        INT UNSIGNED  NULL,
    completed_at      DATETIME      NULL,
    completed_by      INT UNSIGNED  NULL,
    closed_at         DATETIME      NULL,
    closed_by         INT UNSIGNED  NULL,
    cancelled_at      DATETIME      NULL,
    cancelled_by      INT UNSIGNED  NULL,
    cancel_reason     VARCHAR(255)  NULL,
    -- Diagnosis + work done
    fault_description TEXT          NULL,
    work_performed    TEXT          NULL,
    -- Costs
    parts_cost        DECIMAL(14,2) NOT NULL DEFAULT 0.00,   -- computed on close from work_order_parts
    labour_hours      DECIMAL(8,2)  NOT NULL DEFAULT 0.00,
    labour_rate       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    labour_cost       DECIMAL(14,2) NOT NULL DEFAULT 0.00,   -- hours × rate OR manual override
    external_cost     DECIMAL(14,2) NOT NULL DEFAULT 0.00,   -- external workshop invoice
    total_cost        DECIMAL(14,2) NOT NULL DEFAULT 0.00,   -- parts + labour + external
    currency          VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    downtime_hrs      DECIMAL(8,2)  NULL,                    -- computed on close
    payment_method    VARCHAR(30)   NULL,
    payment_reference VARCHAR(80)   NULL,
    notes             TEXT          NULL,
    -- Accounting linkage
    je_id             INT UNSIGNED  NULL,
    void_je_id        INT UNSIGNED  NULL,
    -- Audit
    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_wo_no (wo_no),
    KEY idx_wo_truck   (truck_id, status),
    KEY idx_wo_vendor  (vendor_id),
    KEY idx_wo_status  (status, deleted),
    KEY idx_wo_kind    (kind),
    KEY idx_wo_opened  (opened_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS work_order_parts (
    c0d             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    work_order_id   INT UNSIGNED NOT NULL,
    part_no         VARCHAR(60)  NULL,
    part_name       VARCHAR(160) NOT NULL,
    category        VARCHAR(30)  NOT NULL DEFAULT 'spare_parts',
                                                    -- spare_parts | tyres | batteries | lubricants | consumables
    qty             DECIMAL(10,3) NOT NULL DEFAULT 1.000,
    unit_cost       DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_cost      DECIMAL(14,2) NOT NULL DEFAULT 0.00,   -- qty × unit_cost (recomputed on save)
    gl_account_code VARCHAR(20)   NULL,                    -- auto-derived from category if empty
    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_wop_wo (work_order_id, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — Vendors and WorkOrders
-- ═══════════════════════════════════════════════════════════════════════

-- Vendors (rsflow table, Pacific just adds a UI + grants)
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Administrator (10)
(10, 'Vendors', 'index',  1, NOW()), (10, 'Vendors', 'view',   1, NOW()),
(10, 'Vendors', 'add',    1, NOW()), (10, 'Vendors', 'save',   1, NOW()),
(10, 'Vendors', 'edit',   1, NOW()), (10, 'Vendors', 'update', 1, NOW()),
(10, 'Vendors', 'delete', 1, NOW()),
-- Fleet Manager (22) — owns vendor relationships for workshop/parts
(22, 'Vendors', 'index',  1, NOW()), (22, 'Vendors', 'view',   1, NOW()),
(22, 'Vendors', 'add',    1, NOW()), (22, 'Vendors', 'save',   1, NOW()),
(22, 'Vendors', 'edit',   1, NOW()), (22, 'Vendors', 'update', 1, NOW()),
-- Procurement (7) — full
(7,  'Vendors', 'index',  1, NOW()), (7,  'Vendors', 'view',   1, NOW()),
(7,  'Vendors', 'add',    1, NOW()), (7,  'Vendors', 'save',   1, NOW()),
(7,  'Vendors', 'edit',   1, NOW()), (7,  'Vendors', 'update', 1, NOW()),
-- Ops Manager (20), Accountant (3), Finance Manager (2) — read
(20, 'Vendors', 'index',  1, NOW()), (20, 'Vendors', 'view',   1, NOW()),
(3,  'Vendors', 'index',  1, NOW()), (3,  'Vendors', 'view',   1, NOW()),
(2,  'Vendors', 'index',  1, NOW()), (2,  'Vendors', 'view',   1, NOW()),
-- Executive (1) — read
(1,  'Vendors', 'index',  1, NOW()), (1,  'Vendors', 'view',   1, NOW());

-- WorkOrders
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Administrator (10) — full
(10, 'WorkOrders', 'index',    1, NOW()), (10, 'WorkOrders', 'view',      1, NOW()),
(10, 'WorkOrders', 'add',      1, NOW()), (10, 'WorkOrders', 'save',     1, NOW()),
(10, 'WorkOrders', 'edit',     1, NOW()), (10, 'WorkOrders', 'update',   1, NOW()),
(10, 'WorkOrders', 'start',    1, NOW()), (10, 'WorkOrders', 'complete', 1, NOW()),
(10, 'WorkOrders', 'close',    1, NOW()), (10, 'WorkOrders', 'cancel',   1, NOW()),
(10, 'WorkOrders', 'addPart',  1, NOW()), (10, 'WorkOrders', 'deletePart',1, NOW()),
(10, 'WorkOrders', 'delete',   1, NOW()),
-- Fleet Manager (22) — full owner
(22, 'WorkOrders', 'index',    1, NOW()), (22, 'WorkOrders', 'view',      1, NOW()),
(22, 'WorkOrders', 'add',      1, NOW()), (22, 'WorkOrders', 'save',     1, NOW()),
(22, 'WorkOrders', 'edit',     1, NOW()), (22, 'WorkOrders', 'update',   1, NOW()),
(22, 'WorkOrders', 'start',    1, NOW()), (22, 'WorkOrders', 'complete', 1, NOW()),
(22, 'WorkOrders', 'close',    1, NOW()), (22, 'WorkOrders', 'cancel',   1, NOW()),
(22, 'WorkOrders', 'addPart',  1, NOW()), (22, 'WorkOrders', 'deletePart',1, NOW()),
-- Ops Manager (20) — create + view + start (in case they open a breakdown WO for a stranded driver)
(20, 'WorkOrders', 'index',    1, NOW()), (20, 'WorkOrders', 'view',      1, NOW()),
(20, 'WorkOrders', 'add',      1, NOW()), (20, 'WorkOrders', 'save',     1, NOW()),
(20, 'WorkOrders', 'start',    1, NOW()),
-- Dispatcher (21) — read (workshop status affects dispatch decisions)
(21, 'WorkOrders', 'index',    1, NOW()), (21, 'WorkOrders', 'view',      1, NOW()),
-- Accountant (3) — read
(3,  'WorkOrders', 'index',    1, NOW()), (3,  'WorkOrders', 'view',      1, NOW()),
-- Finance Manager (2) — read
(2,  'WorkOrders', 'index',    1, NOW()), (2,  'WorkOrders', 'view',      1, NOW()),
-- Executive (1) — read
(1,  'WorkOrders', 'index',    1, NOW()), (1,  'WorkOrders', 'view',      1, NOW());
