-- Pacific Logistics · slice 10 · Expense Management with GL auto-post
--
-- Every expense the business incurs — fuel, tolls, driver allowance, rent,
-- office salary, tyres, etc. — flows through this module. The lifecycle:
--
--     draft → submitted → reviewed → approved → paid
--                                  └→ rejected
--                                  └→ cancelled
--
-- On APPROVE the model auto-posts a journal:
--     Dr Expense Account  (per expense_types.gl_debit_account_id)
--     Cr Accounts Payable (2010) — the accrual
--
-- On PAY the model posts a settlement journal:
--     Dr Accounts Payable (2010)
--     Cr Bank / Cash      (per payment_method → GL code map in the model)
--
-- Both journals carry Pacific analytical tags (truck / driver / trip / vendor
-- / cost_centre) so P&L per truck / route / driver is a query away.


-- ═══════════════════════════════════════════════════════════════════════
-- 1. expense_types — the catalog + GL mapping
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS expense_types (
    c0d                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id              INT UNSIGNED NOT NULL DEFAULT 1,
    slug                   VARCHAR(60)  NOT NULL,
    display_name           VARCHAR(120) NOT NULL,
    description            VARCHAR(255) NULL,
    gl_debit_account_id    INT UNSIGNED NULL,       -- FK-in-app to chart_of_accounts (leaf expense account)
    scope                  VARCHAR(30)  NOT NULL DEFAULT 'general',
                                                    -- general | fuel | maintenance | driver | admin | insurance
    requires_truck         TINYINT(1)   NOT NULL DEFAULT 0,
    requires_driver        TINYINT(1)   NOT NULL DEFAULT 0,
    requires_vendor        TINYINT(1)   NOT NULL DEFAULT 0,
    requires_trip          TINYINT(1)   NOT NULL DEFAULT 0,
    active                 TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order             INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_expense_types_slug (tenant_id, slug),
    KEY idx_expense_types_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Seed expense types, resolving GL account_code → c0d via subquery
INSERT INTO expense_types (tenant_id, slug, display_name, gl_debit_account_id, scope, requires_truck, requires_driver, requires_vendor, requires_trip, sort_order) VALUES
-- Direct operating (per-truck / per-trip)
(1, 'fuel',                 'Fuel',                          (SELECT c0d FROM chart_of_accounts WHERE account_code='5010'), 'fuel',        1, 1, 0, 0,  10),
(1, 'toll_charges',         'Toll Charges',                  (SELECT c0d FROM chart_of_accounts WHERE account_code='5020'), 'general',     1, 0, 0, 1,  20),
(1, 'weighbridge_fee',      'Weighbridge Fee',               (SELECT c0d FROM chart_of_accounts WHERE account_code='5030'), 'general',     1, 0, 0, 1,  30),
(1, 'loading_offloading',   'Loading / Offloading',          (SELECT c0d FROM chart_of_accounts WHERE account_code='5040'), 'general',     1, 0, 0, 1,  40),
(1, 'parking_fee',          'Parking Fee',                   (SELECT c0d FROM chart_of_accounts WHERE account_code='5050'), 'general',     1, 0, 0, 0,  50),
(1, 'border_fee',           'Border / Immigration Fee',      (SELECT c0d FROM chart_of_accounts WHERE account_code='5060'), 'general',     1, 0, 0, 1,  60),

-- Maintenance
(1, 'repair_maintenance',   'Repair & Maintenance',          (SELECT c0d FROM chart_of_accounts WHERE account_code='5110'), 'maintenance', 1, 0, 1, 0, 110),
(1, 'spare_parts',          'Spare Parts',                   (SELECT c0d FROM chart_of_accounts WHERE account_code='5120'), 'maintenance', 1, 0, 1, 0, 120),
(1, 'tyres',                'Tyres',                         (SELECT c0d FROM chart_of_accounts WHERE account_code='5130'), 'maintenance', 1, 0, 1, 0, 130),
(1, 'batteries',            'Batteries',                     (SELECT c0d FROM chart_of_accounts WHERE account_code='5140'), 'maintenance', 1, 0, 1, 0, 140),
(1, 'lubricants',           'Lubricants / Oils',             (SELECT c0d FROM chart_of_accounts WHERE account_code='5150'), 'maintenance', 1, 0, 1, 0, 150),
(1, 'workshop_consumables', 'Workshop Consumables',          (SELECT c0d FROM chart_of_accounts WHERE account_code='5160'), 'maintenance', 0, 0, 1, 0, 160),
(1, 'external_workshop',    'External Workshop Service',     (SELECT c0d FROM chart_of_accounts WHERE account_code='5170'), 'maintenance', 1, 0, 1, 0, 170),
(1, 'car_wash',             'Car Wash & Cleaning',           (SELECT c0d FROM chart_of_accounts WHERE account_code='5180'), 'general',     1, 0, 0, 0, 180),

-- Driver costs
(1, 'driver_salary',        'Driver Salary',                 (SELECT c0d FROM chart_of_accounts WHERE account_code='5210'), 'driver',      0, 1, 0, 0, 210),
(1, 'driver_overtime',      'Driver Overtime',               (SELECT c0d FROM chart_of_accounts WHERE account_code='5220'), 'driver',      0, 1, 0, 0, 220),
(1, 'driver_allowance',     'Driver Allowance / Per Diem',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5230'), 'driver',      0, 1, 0, 0, 230),
(1, 'driver_bonus',         'Driver Bonus',                  (SELECT c0d FROM chart_of_accounts WHERE account_code='5240'), 'driver',      0, 1, 0, 0, 240),
(1, 'driver_ppe',           'Driver Uniforms & PPE',         (SELECT c0d FROM chart_of_accounts WHERE account_code='5250'), 'driver',      0, 1, 0, 0, 250),
(1, 'driver_training',      'Driver Training',               (SELECT c0d FROM chart_of_accounts WHERE account_code='5260'), 'driver',      0, 1, 0, 0, 260),
(1, 'driver_medical',       'Driver Medical',                (SELECT c0d FROM chart_of_accounts WHERE account_code='5270'), 'driver',      0, 1, 0, 0, 270),

-- Insurance & compliance
(1, 'vehicle_insurance',    'Vehicle Insurance Premium',     (SELECT c0d FROM chart_of_accounts WHERE account_code='5310'), 'insurance',   1, 0, 1, 0, 310),
(1, 'vehicle_licence',      'Vehicle Licence & Permits',     (SELECT c0d FROM chart_of_accounts WHERE account_code='5320'), 'general',     1, 0, 0, 0, 320),
(1, 'road_toll_subscription','Road Toll Subscription',       (SELECT c0d FROM chart_of_accounts WHERE account_code='5330'), 'general',     1, 0, 0, 0, 330),
(1, 'inspection_fee',       'Vehicle Inspection Fee',        (SELECT c0d FROM chart_of_accounts WHERE account_code='5340'), 'general',     1, 0, 0, 0, 340),
(1, 'psv_licence',          'PSV Licence Renewal',           (SELECT c0d FROM chart_of_accounts WHERE account_code='5350'), 'general',     1, 0, 0, 0, 350),

-- Admin
(1, 'office_salary',        'Office Salaries & Wages',       (SELECT c0d FROM chart_of_accounts WHERE account_code='5410'), 'admin',       0, 0, 0, 0, 410),
(1, 'rent',                 'Rent',                          (SELECT c0d FROM chart_of_accounts WHERE account_code='5420'), 'admin',       0, 0, 1, 0, 420),
(1, 'utilities',            'Utilities',                     (SELECT c0d FROM chart_of_accounts WHERE account_code='5430'), 'admin',       0, 0, 1, 0, 430),
(1, 'telephone_internet',   'Telephone & Internet',          (SELECT c0d FROM chart_of_accounts WHERE account_code='5440'), 'admin',       0, 0, 1, 0, 440),
(1, 'office_supplies',      'Office Supplies & Stationery',  (SELECT c0d FROM chart_of_accounts WHERE account_code='5450'), 'admin',       0, 0, 1, 0, 450),
(1, 'professional_fees',    'Professional Fees',             (SELECT c0d FROM chart_of_accounts WHERE account_code='5460'), 'admin',       0, 0, 1, 0, 460),
(1, 'bank_charges',         'Bank Charges',                  (SELECT c0d FROM chart_of_accounts WHERE account_code='5470'), 'admin',       0, 0, 0, 0, 470),
(1, 'it_software',          'IT & Software',                 (SELECT c0d FROM chart_of_accounts WHERE account_code='5480'), 'admin',       0, 0, 1, 0, 480),
(1, 'travel_entertainment', 'Travel & Entertainment',        (SELECT c0d FROM chart_of_accounts WHERE account_code='5490'), 'admin',       0, 0, 0, 0, 490),

-- Other
(1, 'fines_penalties',      'Fines & Penalties',             (SELECT c0d FROM chart_of_accounts WHERE account_code='5920'), 'general',     0, 0, 0, 0, 920),
(1, 'miscellaneous',        'Miscellaneous',                 (SELECT c0d FROM chart_of_accounts WHERE account_code='5990'), 'general',     0, 0, 0, 0, 990);


-- ═══════════════════════════════════════════════════════════════════════
-- 2. expenses — the main table
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS expenses (
    c0d                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id           INT UNSIGNED NOT NULL DEFAULT 1,
    expense_no          VARCHAR(20)   NOT NULL DEFAULT '',   -- EXP-00001
    expense_type_id     INT UNSIGNED  NOT NULL,
    expense_date        DATE          NOT NULL,
    description         VARCHAR(255)  NULL,
    -- Amounts (VAT is optional; JE posts on gross for simplicity in Iter 2)
    net_amount          DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    vat_amount          DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    gross_amount        DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    currency            VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    fx_rate             DECIMAL(20,6) NOT NULL DEFAULT 1.000000,
    -- Analytical tags — one expense may be tagged to any/all of these
    truck_id            INT UNSIGNED  NULL,
    driver_id           INT UNSIGNED  NULL,
    trip_id             INT UNSIGNED  NULL,
    trip_request_id     INT UNSIGNED  NULL,
    customer_id         INT UNSIGNED  NULL,
    vendor_id           INT UNSIGNED  NULL,
    cost_centre_id      INT UNSIGNED  NULL,
    branch_id           INT UNSIGNED  NULL,
    -- Payment
    payment_method      VARCHAR(30)   NULL,           -- cash | petty_cash | mobile_money | bank_transfer | cheque | fuel_card
    payment_reference   VARCHAR(80)   NULL,
    -- Attachment
    receipt_url         VARCHAR(255)  NULL,
    receipt_file_name   VARCHAR(255)  NULL,
    receipt_file_size   INT UNSIGNED  NULL,
    notes               TEXT          NULL,
    -- Lifecycle
    status              VARCHAR(20)   NOT NULL DEFAULT 'draft',
                                                    -- draft | submitted | reviewed | approved | paid | rejected | cancelled
    submitted_by        INT UNSIGNED  NULL,
    submitted_at        DATETIME      NULL,
    reviewed_by         INT UNSIGNED  NULL,
    reviewed_at         DATETIME      NULL,
    approved_by         INT UNSIGNED  NULL,
    approved_at         DATETIME      NULL,
    paid_by             INT UNSIGNED  NULL,
    paid_at             DATETIME      NULL,
    rejected_by         INT UNSIGNED  NULL,
    rejected_at         DATETIME      NULL,
    rejection_reason    VARCHAR(255)  NULL,
    -- Accounting linkage (set when approve() posts JE, and when pay() posts settlement JE)
    accrual_je_id       INT UNSIGNED  NULL,
    payment_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_expense_no (expense_no),
    KEY idx_expenses_status   (status, deleted),
    KEY idx_expenses_date     (expense_date),
    KEY idx_expenses_type     (expense_type_id),
    KEY idx_expenses_truck    (truck_id),
    KEY idx_expenses_driver   (driver_id),
    KEY idx_expenses_trip     (trip_id),
    KEY idx_expenses_customer (customer_id),
    KEY idx_expenses_vendor   (vendor_id),
    KEY idx_expenses_created  (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- 3. expense_approvals — audit log per lifecycle transition
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS expense_approvals (
    c0d          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    expense_id   INT UNSIGNED NOT NULL,
    from_status  VARCHAR(20)  NULL,
    to_status    VARCHAR(20)  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_ea_expense (expense_id, action_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- 4. Grants — see readme header for the rationale
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Administrator (10) — full
(10, 'Expenses', 'index',   1, NOW()), (10, 'Expenses', 'view',    1, NOW()),
(10, 'Expenses', 'add',     1, NOW()), (10, 'Expenses', 'save',    1, NOW()),
(10, 'Expenses', 'edit',    1, NOW()), (10, 'Expenses', 'update',  1, NOW()),
(10, 'Expenses', 'submit',  1, NOW()), (10, 'Expenses', 'review',  1, NOW()),
(10, 'Expenses', 'approve', 1, NOW()), (10, 'Expenses', 'reject',  1, NOW()),
(10, 'Expenses', 'cancel',  1, NOW()), (10, 'Expenses', 'pay',     1, NOW()),
(10, 'Expenses', 'delete',  1, NOW()),
-- Accountant (3) — full (owns processing)
(3,  'Expenses', 'index',   1, NOW()), (3,  'Expenses', 'view',    1, NOW()),
(3,  'Expenses', 'add',     1, NOW()), (3,  'Expenses', 'save',    1, NOW()),
(3,  'Expenses', 'edit',    1, NOW()), (3,  'Expenses', 'update',  1, NOW()),
(3,  'Expenses', 'submit',  1, NOW()), (3,  'Expenses', 'review',  1, NOW()),
(3,  'Expenses', 'approve', 1, NOW()), (3,  'Expenses', 'reject',  1, NOW()),
(3,  'Expenses', 'cancel',  1, NOW()), (3,  'Expenses', 'pay',     1, NOW()),
-- Finance Manager (2) — full including final approval
(2,  'Expenses', 'index',   1, NOW()), (2,  'Expenses', 'view',    1, NOW()),
(2,  'Expenses', 'add',     1, NOW()), (2,  'Expenses', 'save',    1, NOW()),
(2,  'Expenses', 'edit',    1, NOW()), (2,  'Expenses', 'update',  1, NOW()),
(2,  'Expenses', 'submit',  1, NOW()), (2,  'Expenses', 'review',  1, NOW()),
(2,  'Expenses', 'approve', 1, NOW()), (2,  'Expenses', 'reject',  1, NOW()),
(2,  'Expenses', 'cancel',  1, NOW()), (2,  'Expenses', 'pay',     1, NOW()),
-- Ops Manager (20) — create + edit + submit + cancel own; can't approve
(20, 'Expenses', 'index',   1, NOW()), (20, 'Expenses', 'view',    1, NOW()),
(20, 'Expenses', 'add',     1, NOW()), (20, 'Expenses', 'save',    1, NOW()),
(20, 'Expenses', 'edit',    1, NOW()), (20, 'Expenses', 'update',  1, NOW()),
(20, 'Expenses', 'submit',  1, NOW()), (20, 'Expenses', 'cancel',  1, NOW()),
-- Fleet Manager (22) — create + submit (workshop-side expenses)
(22, 'Expenses', 'index',   1, NOW()), (22, 'Expenses', 'view',    1, NOW()),
(22, 'Expenses', 'add',     1, NOW()), (22, 'Expenses', 'save',    1, NOW()),
(22, 'Expenses', 'edit',    1, NOW()), (22, 'Expenses', 'update',  1, NOW()),
(22, 'Expenses', 'submit',  1, NOW()), (22, 'Expenses', 'cancel',  1, NOW()),
-- Fuel Officer (23) — create + submit (fuel-scope expenses only, enforced in controller if needed)
(23, 'Expenses', 'index',   1, NOW()), (23, 'Expenses', 'view',    1, NOW()),
(23, 'Expenses', 'add',     1, NOW()), (23, 'Expenses', 'save',    1, NOW()),
(23, 'Expenses', 'edit',    1, NOW()), (23, 'Expenses', 'update',  1, NOW()),
(23, 'Expenses', 'submit',  1, NOW()), (23, 'Expenses', 'cancel',  1, NOW()),
-- HR Officer (24) — create + submit (driver-scope expenses)
(24, 'Expenses', 'index',   1, NOW()), (24, 'Expenses', 'view',    1, NOW()),
(24, 'Expenses', 'add',     1, NOW()), (24, 'Expenses', 'save',    1, NOW()),
(24, 'Expenses', 'edit',    1, NOW()), (24, 'Expenses', 'update',  1, NOW()),
(24, 'Expenses', 'submit',  1, NOW()), (24, 'Expenses', 'cancel',  1, NOW()),
-- Dispatcher (21) — create + submit (trip-related expenses)
(21, 'Expenses', 'index',   1, NOW()), (21, 'Expenses', 'view',    1, NOW()),
(21, 'Expenses', 'add',     1, NOW()), (21, 'Expenses', 'save',    1, NOW()),
(21, 'Expenses', 'edit',    1, NOW()), (21, 'Expenses', 'update',  1, NOW()),
(21, 'Expenses', 'submit',  1, NOW()), (21, 'Expenses', 'cancel',  1, NOW()),
-- Executive (1) — read only
(1,  'Expenses', 'index',   1, NOW()), (1,  'Expenses', 'view',    1, NOW());
