-- Pacific Logistics · slice 09 · Accounting Foundation
--
-- Rsflow's chart_of_accounts + journal_entries + journal_lines + JournalPosting
-- model give us a working double-entry engine out of the box. What Pacific adds:
--   1. Analytical tag columns on journal_lines (truck / driver / trip / etc.) —
--      the whole point of the "operations & finance run on one dataset" principle
--   2. A comprehensive TZ-trucking-tuned COA (replacing rsflow's 7-row demo)
--   3. Grants to Admin, Accountant, Finance Manager, Executive
--
-- Rsflow's existing ChartOfAccounts / Journals / Cashbook / FinancialStatements
-- controllers keep working as-is against this same schema.


-- ═══════════════════════════════════════════════════════════════════════
-- 1. Extend journal_lines with Pacific analytical tags
-- ═══════════════════════════════════════════════════════════════════════
ALTER TABLE journal_lines
  ADD COLUMN truck_id       INT UNSIGNED NULL AFTER bank_account_id,
  ADD COLUMN driver_id      INT UNSIGNED NULL AFTER truck_id,
  ADD COLUMN trip_id        INT UNSIGNED NULL AFTER driver_id,
  ADD COLUMN trip_request_id INT UNSIGNED NULL AFTER trip_id,
  ADD COLUMN customer_id    INT UNSIGNED NULL AFTER trip_request_id,
  ADD COLUMN vendor_id      INT UNSIGNED NULL AFTER customer_id,
  ADD COLUMN expense_id     INT UNSIGNED NULL AFTER vendor_id,
  ADD COLUMN fuel_entry_id  INT UNSIGNED NULL AFTER expense_id,
  ADD COLUMN work_order_id  INT UNSIGNED NULL AFTER fuel_entry_id,
  ADD COLUMN cost_centre_id INT UNSIGNED NULL AFTER work_order_id,
  ADD KEY idx_jl_truck    (truck_id),
  ADD KEY idx_jl_driver   (driver_id),
  ADD KEY idx_jl_trip     (trip_id),
  ADD KEY idx_jl_customer (customer_id),
  ADD KEY idx_jl_vendor   (vendor_id),
  ADD KEY idx_jl_expense  (expense_id);


-- ═══════════════════════════════════════════════════════════════════════
-- 2. Clear rsflow's demo COA (7 rows, unused in Pacific)
-- ═══════════════════════════════════════════════════════════════════════
DELETE FROM chart_of_accounts;
ALTER TABLE chart_of_accounts AUTO_INCREMENT = 1;


-- ═══════════════════════════════════════════════════════════════════════
-- 3. Seed the Pacific COA — TZ-tuned trucking chart, ~90 accounts.
--    Two-level: 1nn0 / 2nn0 / etc are control (parent) accounts; the leaves
--    beneath them are what journal_lines actually post to.
-- ═══════════════════════════════════════════════════════════════════════

-- Level-1 control accounts first (parent_id = 0)
INSERT INTO chart_of_accounts (account_code, account_name, account_type, parent_id, is_bank, is_control, is_active, currency) VALUES
-- ── ASSETS (1000-1999) ────────────────────────────────────────────────
('1000', 'Cash & Bank',               'ASSET',    0, 0, 1, 1, 'TZS'),
('1100', 'Accounts Receivable',       'ASSET',    0, 0, 1, 1, 'TZS'),
('1200', 'Inventory',                 'ASSET',    0, 0, 1, 1, 'TZS'),
('1300', 'Fixed Assets',              'ASSET',    0, 0, 1, 1, 'TZS'),
('1400', 'Accumulated Depreciation',  'ASSET',    0, 0, 1, 1, 'TZS'),
-- ── LIABILITIES (2000-2999) ───────────────────────────────────────────
('2000', 'Trade & Other Payables',    'LIABILITY',0, 0, 1, 1, 'TZS'),
('2100', 'Taxes Payable',             'LIABILITY',0, 0, 1, 1, 'TZS'),
('2200', 'Employee-related Liabilities','LIABILITY',0,0,1, 1, 'TZS'),
('2300', 'Loans & Financing',         'LIABILITY',0, 0, 1, 1, 'TZS'),
-- ── EQUITY (3000-3999) ────────────────────────────────────────────────
('3000', 'Equity',                    'EQUITY',   0, 0, 1, 1, 'TZS'),
-- ── REVENUE (4000-4999) ───────────────────────────────────────────────
('4000', 'Freight Revenue',           'REVENUE',  0, 0, 1, 1, 'TZS'),
('4100', 'Other Operating Revenue',   'REVENUE',  0, 0, 1, 1, 'TZS'),
('4900', 'Other Income',              'REVENUE',  0, 0, 1, 1, 'TZS'),
-- ── EXPENSES (5000-5999) ──────────────────────────────────────────────
('5000', 'Direct Operating Costs',    'EXPENSE',  0, 0, 1, 1, 'TZS'),
('5100', 'Vehicle Maintenance',       'EXPENSE',  0, 0, 1, 1, 'TZS'),
('5200', 'Driver Costs',              'EXPENSE',  0, 0, 1, 1, 'TZS'),
('5300', 'Vehicle Insurance & Compliance','EXPENSE',0,0,1, 1, 'TZS'),
('5400', 'Administrative Expenses',   'EXPENSE',  0, 0, 1, 1, 'TZS'),
('5500', 'Depreciation',              'EXPENSE',  0, 0, 1, 1, 'TZS'),
('5900', 'Other / Suspense',          'EXPENSE',  0, 0, 1, 1, 'TZS');


-- Now the leaf accounts, resolving parent_id by control code
INSERT INTO chart_of_accounts (account_code, account_name, account_type, parent_id, is_bank, is_control, is_active, currency)
SELECT * FROM (
  -- ── Cash & Bank leaves ───────────────────────────────────────────
  SELECT '1010' AS ac, 'Cash on Hand'                   AS an, 'ASSET' AS at, (SELECT c0d FROM chart_of_accounts WHERE account_code='1000') AS pi, 0 AS ib, 0 AS ic, 1 AS ia, 'TZS' AS cu
  UNION ALL SELECT '1020', 'Bank - Main Operating',              'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1000'), 1, 0, 1, 'TZS'
  UNION ALL SELECT '1030', 'Bank - Fuel Card Account',           'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1000'), 1, 0, 1, 'TZS'
  UNION ALL SELECT '1040', 'Petty Cash',                         'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1050', 'Mobile Money Float',                 'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1000'), 0, 0, 1, 'TZS'
  -- ── Accounts Receivable leaves ───────────────────────────────────
  UNION ALL SELECT '1110', 'Trade Receivables',                  'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1100'), 0, 1, 1, 'TZS'
  UNION ALL SELECT '1120', 'Driver Advances',                    'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1140', 'Prepaid Insurance',                  'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1150', 'Prepaid Rent',                       'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1160', 'Other Prepayments',                  'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1100'), 0, 0, 1, 'TZS'
  -- ── Inventory leaves ─────────────────────────────────────────────
  UNION ALL SELECT '1210', 'Fuel Stock',                         'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1220', 'Spare Parts',                        'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1230', 'Tyres in Stock',                     'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1240', 'Lubricants',                         'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1200'), 0, 0, 1, 'TZS'
  -- ── Fixed Assets leaves ──────────────────────────────────────────
  UNION ALL SELECT '1310', 'Vehicles - Trucks',                  'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1320', 'Vehicles - Pickups',                 'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1330', 'Trailers',                           'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1340', 'Workshop Equipment',                 'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1350', 'Furniture & Fittings',               'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1360', 'Computer Equipment',                 'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1370', 'Buildings',                          'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1380', 'Land',                               'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1300'), 0, 0, 1, 'TZS'
  -- ── Accumulated Depreciation (contra) leaves ─────────────────────
  UNION ALL SELECT '1410', 'Acc Depn - Trucks',                  'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1420', 'Acc Depn - Pickups',                 'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1430', 'Acc Depn - Trailers',                'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1440', 'Acc Depn - Workshop Equipment',      'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1450', 'Acc Depn - Furniture & Fittings',    'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1460', 'Acc Depn - Computer Equipment',      'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '1470', 'Acc Depn - Buildings',               'ASSET',     (SELECT c0d FROM chart_of_accounts WHERE account_code='1400'), 0, 0, 1, 'TZS'
  -- ── Trade & Other Payables leaves ────────────────────────────────
  UNION ALL SELECT '2010', 'Accounts Payable (Trade)',           'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2000'), 0, 1, 1, 'TZS'
  UNION ALL SELECT '2020', 'Accrued Expenses',                   'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2030', 'Fuel Suppliers Payable',             'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2040', 'Spare Parts Suppliers Payable',      'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2000'), 0, 0, 1, 'TZS'
  -- ── Taxes Payable leaves ─────────────────────────────────────────
  UNION ALL SELECT '2110', 'VAT Output',                         'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2120', 'VAT Input',                          'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2130', 'PAYE Payable',                       'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2140', 'NSSF Payable',                       'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2150', 'WCF Payable',                        'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2160', 'SDL Payable',                        'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2170', 'Income Tax Payable',                 'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2100'), 0, 0, 1, 'TZS'
  -- ── Employee-related leaves ──────────────────────────────────────
  UNION ALL SELECT '2210', 'Salaries Payable',                   'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2220', 'Overtime Payable',                   'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2230', 'Driver Allowances Payable',          'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2200'), 0, 0, 1, 'TZS'
  -- ── Loans leaves ─────────────────────────────────────────────────
  UNION ALL SELECT '2310', 'Bank Loan - Short-term',             'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2320', 'Bank Loan - Long-term',              'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '2330', 'Vehicle Financing',                  'LIABILITY', (SELECT c0d FROM chart_of_accounts WHERE account_code='2300'), 0, 0, 1, 'TZS'
  -- ── Equity leaves ────────────────────────────────────────────────
  UNION ALL SELECT '3010', 'Share Capital',                      'EQUITY',    (SELECT c0d FROM chart_of_accounts WHERE account_code='3000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '3020', 'Retained Earnings',                  'EQUITY',    (SELECT c0d FROM chart_of_accounts WHERE account_code='3000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '3030', 'Current Year Earnings',              'EQUITY',    (SELECT c0d FROM chart_of_accounts WHERE account_code='3000'), 0, 0, 1, 'TZS'
  -- ── Freight Revenue leaves ───────────────────────────────────────
  UNION ALL SELECT '4010', 'Regional Freight Revenue',           'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '4020', 'In-town Freight Revenue',            'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '4030', 'Ad-hoc / Special Freight',           'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4000'), 0, 0, 1, 'TZS'
  -- ── Other Operating Revenue leaves ───────────────────────────────
  UNION ALL SELECT '4110', 'Vehicle Hire Income',                'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '4120', 'Sundry Operating Income',            'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4100'), 0, 0, 1, 'TZS'
  -- ── Other Income leaves ──────────────────────────────────────────
  UNION ALL SELECT '4910', 'Interest Income',                    'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4900'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '4920', 'Gain on Disposal of Asset',          'REVENUE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='4900'), 0, 0, 1, 'TZS'
  -- ── Direct Operating Costs leaves ────────────────────────────────
  UNION ALL SELECT '5010', 'Fuel Expense',                       'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5020', 'Toll Charges',                       'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5030', 'Weighbridge Fees',                   'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5040', 'Loading & Offloading',               'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5050', 'Parking Fees',                       'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5000'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5060', 'Border & Immigration Fees',          'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5000'), 0, 0, 1, 'TZS'
  -- ── Vehicle Maintenance leaves ───────────────────────────────────
  UNION ALL SELECT '5110', 'Repairs & Maintenance',              'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5120', 'Spare Parts Consumed',               'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5130', 'Tyres',                              'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5140', 'Batteries',                          'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5150', 'Lubricants & Oils',                  'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5160', 'Workshop Consumables',               'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5170', 'External Workshop Services',         'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5180', 'Car Wash & Cleaning',                'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5100'), 0, 0, 1, 'TZS'
  -- ── Driver Costs leaves ──────────────────────────────────────────
  UNION ALL SELECT '5210', 'Driver Salaries & Wages',            'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5220', 'Driver Overtime',                    'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5230', 'Driver Per Diem / Allowance',        'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5240', 'Driver Bonuses',                     'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5250', 'Driver Uniforms & PPE',              'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5260', 'Driver Training',                    'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5270', 'Driver Medical',                     'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5200'), 0, 0, 1, 'TZS'
  -- ── Insurance & Compliance leaves ────────────────────────────────
  UNION ALL SELECT '5310', 'Motor Vehicle Insurance',            'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5320', 'Vehicle Licences & Permits',         'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5330', 'Road Tolls (Subscription)',          'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5340', 'Vehicle Inspection Fees',            'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5300'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5350', 'PSV Licence Renewal',                'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5300'), 0, 0, 1, 'TZS'
  -- ── Administrative Expenses leaves ───────────────────────────────
  UNION ALL SELECT '5410', 'Office Salaries & Wages',            'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5420', 'Rent',                               'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5430', 'Utilities (Electricity, Water)',     'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5440', 'Telephone & Internet',               'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5450', 'Office Supplies & Stationery',       'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5460', 'Professional Fees (Legal, Audit)',   'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5470', 'Bank Charges',                       'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5480', 'IT & Software',                      'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5490', 'Travel & Entertainment',             'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5400'), 0, 0, 1, 'TZS'
  -- ── Depreciation leaves ──────────────────────────────────────────
  UNION ALL SELECT '5510', 'Depreciation - Trucks',              'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5500'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5520', 'Depreciation - Other Vehicles',      'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5500'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5530', 'Depreciation - Trailers',            'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5500'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5540', 'Depreciation - Workshop Equipment',  'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5500'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5550', 'Depreciation - Furniture & Fittings','EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5500'), 0, 0, 1, 'TZS'
  -- ── Other / Suspense leaves ──────────────────────────────────────
  UNION ALL SELECT '5910', 'Fuel Variance / Suspense',           'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5900'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5920', 'Fines & Penalties',                  'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5900'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5930', 'Bad Debts Written Off',              'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5900'), 0, 0, 1, 'TZS'
  UNION ALL SELECT '5990', 'Miscellaneous Expenses',             'EXPENSE',   (SELECT c0d FROM chart_of_accounts WHERE account_code='5900'), 0, 0, 1, 'TZS'
) AS leaves;


-- ═══════════════════════════════════════════════════════════════════════
-- 4. Grants — accounting is Admin / Accountant / Finance Manager / Executive
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Administrator (10) — full
(10, 'ChartOfAccounts',    'index', 1, NOW()), (10, 'ChartOfAccounts',    'view',   1, NOW()),
(10, 'ChartOfAccounts',    'add',   1, NOW()), (10, 'ChartOfAccounts',    'save',   1, NOW()),
(10, 'ChartOfAccounts',    'edit',  1, NOW()), (10, 'ChartOfAccounts',    'update', 1, NOW()),
(10, 'Journals',           'index', 1, NOW()), (10, 'Journals',           'view',   1, NOW()),
(10, 'Cashbook',           'index', 1, NOW()),
(10, 'FinancialStatements','index', 1, NOW()), (10, 'FinancialStatements','pnl',    1, NOW()),
(10, 'FinancialStatements','bs',    1, NOW()), (10, 'FinancialStatements','tb',     1, NOW()),
(10, 'FinancialStatements','cf',    1, NOW()),
-- Accountant (3) — full
(3,  'ChartOfAccounts',    'index', 1, NOW()), (3,  'ChartOfAccounts',    'view',   1, NOW()),
(3,  'ChartOfAccounts',    'add',   1, NOW()), (3,  'ChartOfAccounts',    'save',   1, NOW()),
(3,  'ChartOfAccounts',    'edit',  1, NOW()), (3,  'ChartOfAccounts',    'update', 1, NOW()),
(3,  'Journals',           'index', 1, NOW()), (3,  'Journals',           'view',   1, NOW()),
(3,  'Cashbook',           'index', 1, NOW()),
(3,  'FinancialStatements','index', 1, NOW()), (3,  'FinancialStatements','pnl',    1, NOW()),
(3,  'FinancialStatements','bs',    1, NOW()), (3,  'FinancialStatements','tb',     1, NOW()),
(3,  'FinancialStatements','cf',    1, NOW()),
-- Finance Manager (2) — full
(2,  'ChartOfAccounts',    'index', 1, NOW()), (2,  'ChartOfAccounts',    'view',   1, NOW()),
(2,  'ChartOfAccounts',    'add',   1, NOW()), (2,  'ChartOfAccounts',    'save',   1, NOW()),
(2,  'ChartOfAccounts',    'edit',  1, NOW()), (2,  'ChartOfAccounts',    'update', 1, NOW()),
(2,  'Journals',           'index', 1, NOW()), (2,  'Journals',           'view',   1, NOW()),
(2,  'Cashbook',           'index', 1, NOW()),
(2,  'FinancialStatements','index', 1, NOW()), (2,  'FinancialStatements','pnl',    1, NOW()),
(2,  'FinancialStatements','bs',    1, NOW()), (2,  'FinancialStatements','tb',     1, NOW()),
(2,  'FinancialStatements','cf',    1, NOW()),
-- Executive (1) — read-only
(1,  'ChartOfAccounts',    'index', 1, NOW()), (1,  'ChartOfAccounts',    'view', 1, NOW()),
(1,  'Journals',           'index', 1, NOW()), (1,  'Journals',           'view', 1, NOW()),
(1,  'Cashbook',           'index', 1, NOW()),
(1,  'FinancialStatements','index', 1, NOW()), (1,  'FinancialStatements','pnl',  1, NOW()),
(1,  'FinancialStatements','bs',    1, NOW()), (1,  'FinancialStatements','tb',   1, NOW()),
(1,  'FinancialStatements','cf',    1, NOW());
