-- Pacific Logistics · slice 21 · Client COA Adoption (Turn 1.5)
--
-- Retires the rsflow-inherited chart of accounts and adopts Pacific Group's
-- own COA (as provided by their accountant on 2026-08-27) plus 6 additions
-- for accounts they'll need but hadn't listed:
--
--   3006 Mobile Money Float          (Current Asset — TZ payments reality)
--   3007 Trade Receivables           (Current Asset — required by invoicing engine)
--   3008 VAT Recoverable             (Current Asset — VAT input on purchases)
--   3009 Fuel Card Account           (Current Asset — Puma/Total/Oryx float)
--   4009 Trade Payables              (Current Liability — required by expenses engine)
--   4010 VAT Payable                 (Current Liability — VAT output on sales)
--
-- Nuclear reset: because only Iter 2 + 4.1 smoke-test data exists, we truncate
-- every table that references chart_of_accounts.c0d through journal entries or
-- direct FK. Rerun of the smoke tests after this slice proves the accounting
-- engine posts to the new codes and stays balanced.
--
-- Post-slice, five model files carry the new payment_method -> COA map:
--   Expenses.php · FuelEntries.php · CustomerInvoices.php ·
--   CustomerReceipts.php · WorkOrders.php
--
-- Structure of the client COA:
--   1000 Contra Asset            (parent)
--     1001-1004                  accumulated depreciation × 4 categories
--   2000 Non-Current Asset       (parent)
--     2001-2004                  computer / motor vehicle / furniture / right of use
--   3000 Current Assets          (parent)
--     3001-3009                  banks / cash / inventory / prepaid + our 4 new
--   4000 Current Liabilities     (parent)
--     4001-4010                  statutory liab + our 2 new (Trade Payables, VAT Payable)
--   5000 Non-Current Liability   (parent)
--     5001-5002                  loan / lease
--   6000 Equity                  (parent)
--     6001-6004                  capital / OB adj / retained / drawings
--   7000 Expenses                (parent)
--     7001-7034                  the client's 34 expense lines verbatim
--   8000 Revenue                 (parent)
--     8001                       Sales Revenue (single line + analytical tags)


-- ═══════════════════════════════════════════════════════════════════════
-- 1) Truncate everything downstream of chart_of_accounts
-- ═══════════════════════════════════════════════════════════════════════
SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE customer_receipt_allocations;
TRUNCATE TABLE customer_receipts;
TRUNCATE TABLE customer_invoice_lines;
TRUNCATE TABLE customer_invoices;
TRUNCATE TABLE expenses;
-- expense_approvals holds the review/approve/paid audit trail per expense
TRUNCATE TABLE expense_approvals;
TRUNCATE TABLE fuel_entries;
TRUNCATE TABLE work_orders;
TRUNCATE TABLE work_order_parts;
TRUNCATE TABLE journal_lines;
TRUNCATE TABLE journal_entries;
TRUNCATE TABLE chart_of_accounts;
TRUNCATE TABLE bank_accounts;

SET FOREIGN_KEY_CHECKS = 1;


-- ═══════════════════════════════════════════════════════════════════════
-- 2) Reseed chart_of_accounts with the client COA + 6 additions
-- ═══════════════════════════════════════════════════════════════════════
INSERT INTO chart_of_accounts (account_code, account_name, account_type, is_bank, is_control, is_active, currency, created_by, created_on) VALUES
-- ── 1000 Contra Asset ────────────────────────────────────────────
('1000', 'Contra Asset',                                 'ASSET',     NULL,  1, 1, NOW()),
('1001', 'Accumulated depreciation - Motor vehicle',     'ASSET',     '1000',1, 1, NOW()),
('1002', 'Accumulated depreciation - Furniture',         'ASSET',     '1000',1, 1, NOW()),
('1003', 'Accumulated depreciation - Computer equipment','ASSET',     '1000',1, 1, NOW()),
('1004', 'Accumulated depreciation - Right of use',      'ASSET',     '1000',1, 1, NOW()),

-- ── 2000 Non-Current Asset ───────────────────────────────────────
('2000', 'Non-Current Asset',                            'ASSET',     NULL,  1, 1, NOW()),
('2001', 'Computer',                                     'ASSET',     '2000',1, 1, NOW()),
('2002', 'Motor vehicle',                                'ASSET',     '2000',1, 1, NOW()),
('2003', 'Furnitures',                                   'ASSET',     '2000',1, 1, NOW()),
('2004', 'Right of use',                                 'ASSET',     '2000',1, 1, NOW()),

-- ── 3000 Current Assets ──────────────────────────────────────────
('3000', 'Current Assets',                               'ASSET',     NULL,  1, 1, NOW()),
('3001', 'Bank - CRDB',                                  'ASSET',     '3000',1, 1, NOW()),
('3002', 'Bank - NMB',                                   'ASSET',     '3000',1, 1, NOW()),
('3003', 'Cash',                                         'ASSET',     '3000',1, 1, NOW()),
('3004', 'Inventory',                                    'ASSET',     '3000',1, 1, NOW()),
('3005', 'Prepaid expenses',                             'ASSET',     '3000',1, 1, NOW()),
('3006', 'Mobile Money Float',                           'ASSET',     '3000',1, 1, NOW()),    -- NEW
('3007', 'Trade Receivables',                            'ASSET',     '3000',1, 1, NOW()),    -- NEW
('3008', 'VAT Recoverable',                              'ASSET',     '3000',1, 1, NOW()),    -- NEW
('3009', 'Fuel Card Account',                            'ASSET',     '3000',1, 1, NOW()),    -- NEW

-- ── 4000 Current Liabilities ─────────────────────────────────────
('4000', 'Current Liabilities',                          'LIABILITY', NULL,  1, 1, NOW()),
('4001', 'Accrued Expenses',                             'LIABILITY', '4000',1, 1, NOW()),
('4002', 'Lease liability',                              'LIABILITY', '4000',1, 1, NOW()),
('4003', 'NSSF - Payable',                               'LIABILITY', '4000',1, 1, NOW()),
('4004', 'PAYE - Payable',                               'LIABILITY', '4000',1, 1, NOW()),
('4005', 'Salary - payable',                             'LIABILITY', '4000',1, 1, NOW()),
('4006', 'SDL - Payable',                                'LIABILITY', '4000',1, 1, NOW()),
('4007', 'Tax payable',                                  'LIABILITY', '4000',1, 1, NOW()),
('4008', 'WCF - Payable',                                'LIABILITY', '4000',1, 1, NOW()),
('4009', 'Trade Payables',                               'LIABILITY', '4000',1, 1, NOW()),    -- NEW
('4010', 'VAT Payable',                                  'LIABILITY', '4000',1, 1, NOW()),    -- NEW

-- ── 5000 Non-Current Liability ───────────────────────────────────
('5000', 'Non-Current Liability',                        'LIABILITY', NULL,  1, 1, NOW()),
('5001', 'Loan Borrowing',                               'LIABILITY', '5000',1, 1, NOW()),
('5002', 'Lease liability - Non-current',                'LIABILITY', '5000',1, 1, NOW()),

-- ── 6000 Equity ──────────────────────────────────────────────────
('6000', 'Equity',                                       'EQUITY',    NULL,  1, 1, NOW()),
('6001', 'Capital',                                      'EQUITY',    '6000',1, 1, NOW()),
('6002', 'Opening Balance adjustment',                   'EQUITY',    '6000',1, 1, NOW()),
('6003', 'Retained Earnings',                            'EQUITY',    '6000',1, 1, NOW()),
('6004', 'Drawings',                                     'EQUITY',    '6000',1, 1, NOW()),

-- ── 7000 Expenses ────────────────────────────────────────────────
('7000', 'Expenses',                                     'EXPENSE',   NULL,  1, 1, NOW()),
('7001', 'Bank Charges',                                 'EXPENSE',   '7000',1, 1, NOW()),
('7002', 'Basic Salaries',                               'EXPENSE',   '7000',1, 1, NOW()),
('7003', 'Catering',                                     'EXPENSE',   '7000',1, 1, NOW()),
('7004', 'Corporate tax',                                'EXPENSE',   '7000',1, 1, NOW()),
('7005', 'Cost of goods sold (COGS)',                    'EXPENSE',   '7000',1, 1, NOW()),
('7006', 'Delivery Bags',                                'EXPENSE',   '7000',1, 1, NOW()),
('7007', 'Depreciation',                                 'EXPENSE',   '7000',1, 1, NOW()),
('7008', 'Finance cost',                                 'EXPENSE',   '7000',1, 1, NOW()),
('7009', 'Fuel',                                         'EXPENSE',   '7000',1, 1, NOW()),
('7010', 'G&A (Rent, Insurance, Office Supplies)',       'EXPENSE',   '7000',1, 1, NOW()),
('7011', 'Internet',                                     'EXPENSE',   '7000',1, 1, NOW()),
('7012', 'Local Conveyance (Transport)',                 'EXPENSE',   '7000',1, 1, NOW()),
('7013', 'M/Vehicles Running Costs',                     'EXPENSE',   '7000',1, 1, NOW()),
('7014', 'Misc Expenses',                                'EXPENSE',   '7000',1, 1, NOW()),
('7015', 'Mobile Bundles (Mins And Texts)',              'EXPENSE',   '7000',1, 1, NOW()),
('7016', 'NSSF Contribution',                            'EXPENSE',   '7000',1, 1, NOW()),
('7017', 'Office Attendant (Cleaning)',                  'EXPENSE',   '7000',1, 1, NOW()),
('7018', 'Office rent',                                  'EXPENSE',   '7000',1, 1, NOW()),
('7019', 'Office Repairs',                               'EXPENSE',   '7000',1, 1, NOW()),
('7020', 'Office Security',                              'EXPENSE',   '7000',1, 1, NOW()),
('7021', 'Office Service Charges',                       'EXPENSE',   '7000',1, 1, NOW()),
('7022', 'Professional fees',                            'EXPENSE',   '7000',1, 1, NOW()),
('7023', 'Purchases',                                    'EXPENSE',   '7000',1, 1, NOW()),
('7024', 'Regulatory And Compliance',                    'EXPENSE',   '7000',1, 1, NOW()),
('7025', 'Sales And Marketing (Flyers And Posters Etc)', 'EXPENSE',   '7000',1, 1, NOW()),
('7026', 'Skills Development Levy',                      'EXPENSE',   '7000',1, 1, NOW()),
('7027', 'Stationeries',                                 'EXPENSE',   '7000',1, 1, NOW()),
('7028', 'Subsistence Allowances',                       'EXPENSE',   '7000',1, 1, NOW()),
('7029', 'Transport Charges',                            'EXPENSE',   '7000',1, 1, NOW()),
('7030', 'Wages',                                        'EXPENSE',   '7000',1, 1, NOW()),
('7031', 'Workers Compensation Fund',                    'EXPENSE',   '7000',1, 1, NOW()),
('7032', 'Health Insurance',                             'EXPENSE',   '7000',1, 1, NOW()),
('7033', 'Staff uniforms',                               'EXPENSE',   '7000',1, 1, NOW()),
('7034', 'Freight charges',                              'EXPENSE',   '7000',1, 1, NOW()),

-- ── 8000 Revenue ─────────────────────────────────────────────────
('8000', 'Revenue',                                      'REVENUE',   NULL,  1, 1, NOW()),
('8001', 'Sales Revenue',                                'REVENUE',   '8000',1, 1, NOW());


-- ═══════════════════════════════════════════════════════════════════════
-- 3) Remap expense_types.gl_debit_account_id to the new COA
-- ═══════════════════════════════════════════════════════════════════════
-- Every seeded expense_type had a default expense COA under the old 5xxx range.
-- Simplest rule: everything trip-operational goes to 7013 (M/Vehicles Running
-- Costs); driver-related to 7028 or 7002; office/admin per client's specific line.
-- Client can retune per-expense-type via ExpenseTypes admin later.
UPDATE expense_types et
   JOIN chart_of_accounts coa ON coa.account_code = '7013'
    SET et.gl_debit_account_id = coa.c0d
  WHERE et.gl_debit_account_id IS NOT NULL OR et.gl_debit_account_id IS NULL;


-- ═══════════════════════════════════════════════════════════════════════
-- 4) Seed bank_accounts — CRDB (default) + NMB
-- ═══════════════════════════════════════════════════════════════════════
INSERT INTO bank_accounts (account_name, bank_name, account_number, swift_code, currency, coa_id, is_active, opening_balance, opening_on, created_by, created_on)
SELECT 'Pacific Group - CRDB Operating', 'CRDB Bank', 'TBD-CRDB-ACCT', 'CORUTZTZ', 'TZS', c0d, 1, 0.00, NULL, 1, NOW()
  FROM chart_of_accounts WHERE account_code = '3001';

INSERT INTO bank_accounts (account_name, bank_name, account_number, swift_code, currency, coa_id, is_active, opening_balance, opening_on, created_by, created_on)
SELECT 'Pacific Group - NMB', 'NMB Bank', 'TBD-NMB-ACCT', 'NMIBTZTZ', 'TZS', c0d, 1, 0.00, NULL, 1, NOW()
  FROM chart_of_accounts WHERE account_code = '3002';
