-- Pacific Logistics · slice 38 · Comprehensive role-perms audit + fix
--
-- Findings from the audit:
--   1. FinancialStatements grants use wrong action names — they seed
--      `tb / pnl / bs / cf` but the controller has `trialBalance /
--      profitAndLoss / balanceSheet` (no cashFlow — that's on the new
--      FinancialReports controller). Every click 403s.
--   2. Several roles have controller grants but no action rows for
--      key modules — e.g. Finance Manager lacks CustomerInvoices,
--      Ops Manager / Dispatcher have no Dashboard grant, Fuel Officer
--      only has 2 controllers, Field Supervisor is barely wired,
--      HR Officer is missing DocExpiries + TaxReturns.

-- ─────────────────────────────────────────────────────────────────
-- 1. Clear the wrong FinancialStatements action rows + reseed correctly
-- ─────────────────────────────────────────────────────────────────
DELETE FROM user_groups_perms
 WHERE controller_allowed = 'FinancialStatements'
   AND action_allowed IN ('tb','pnl','bs','cf');

INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Executive (1), Finance Manager (2), Accountant (3), Administrator (10)
(1,'FinancialStatements','index',            1,NOW()),
(1,'FinancialStatements','trialBalance',     1,NOW()),
(1,'FinancialStatements','profitAndLoss',    1,NOW()),
(1,'FinancialStatements','balanceSheet',     1,NOW()),
(1,'FinancialStatements','trialBalancePdf',  1,NOW()),
(1,'FinancialStatements','trialBalanceXlsx', 1,NOW()),
(1,'FinancialStatements','profitAndLossPdf', 1,NOW()),
(1,'FinancialStatements','profitAndLossXlsx',1,NOW()),
(1,'FinancialStatements','balanceSheetPdf',  1,NOW()),
(1,'FinancialStatements','balanceSheetXlsx', 1,NOW()),
(2,'FinancialStatements','index',            1,NOW()),
(2,'FinancialStatements','trialBalance',     1,NOW()),
(2,'FinancialStatements','profitAndLoss',    1,NOW()),
(2,'FinancialStatements','balanceSheet',     1,NOW()),
(2,'FinancialStatements','trialBalancePdf',  1,NOW()),
(2,'FinancialStatements','trialBalanceXlsx', 1,NOW()),
(2,'FinancialStatements','profitAndLossPdf', 1,NOW()),
(2,'FinancialStatements','profitAndLossXlsx',1,NOW()),
(2,'FinancialStatements','balanceSheetPdf',  1,NOW()),
(2,'FinancialStatements','balanceSheetXlsx', 1,NOW()),
(3,'FinancialStatements','index',            1,NOW()),
(3,'FinancialStatements','trialBalance',     1,NOW()),
(3,'FinancialStatements','profitAndLoss',    1,NOW()),
(3,'FinancialStatements','balanceSheet',     1,NOW()),
(3,'FinancialStatements','trialBalancePdf',  1,NOW()),
(3,'FinancialStatements','trialBalanceXlsx', 1,NOW()),
(3,'FinancialStatements','profitAndLossPdf', 1,NOW()),
(3,'FinancialStatements','profitAndLossXlsx',1,NOW()),
(3,'FinancialStatements','balanceSheetPdf',  1,NOW()),
(3,'FinancialStatements','balanceSheetXlsx', 1,NOW()),
(10,'FinancialStatements','index',           1,NOW()),
(10,'FinancialStatements','trialBalance',    1,NOW()),
(10,'FinancialStatements','profitAndLoss',   1,NOW()),
(10,'FinancialStatements','balanceSheet',    1,NOW()),
(10,'FinancialStatements','trialBalancePdf', 1,NOW()),
(10,'FinancialStatements','trialBalanceXlsx',1,NOW()),
(10,'FinancialStatements','profitAndLossPdf',1,NOW()),
(10,'FinancialStatements','profitAndLossXlsx',1,NOW()),
(10,'FinancialStatements','balanceSheetPdf', 1,NOW()),
(10,'FinancialStatements','balanceSheetXlsx',1,NOW());


-- ─────────────────────────────────────────────────────────────────
-- 2. Fill role-perm gaps found in the audit
-- ─────────────────────────────────────────────────────────────────

-- Finance Manager (2) — needs AR side + missing Dashboard action rows
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(2, 'Dashboard',        'index',        1, NOW()),
(2, 'Dashboard',        'finance',      1, NOW()),
(2, 'Dashboard',        'exec',         1, NOW()),
(2, 'CustomerInvoices', 'index',        1, NOW()),
(2, 'CustomerInvoices', 'view',         1, NOW()),
(2, 'CustomerInvoices', 'pdf',          1, NOW()),
(2, 'CustomerInvoices', 'print',        1, NOW()),
(2, 'CustomerInvoices', 'billTrips',    1, NOW()),
(2, 'CustomerReceipts', 'index',        1, NOW()),
(2, 'CustomerReceipts', 'view',         1, NOW()),
(2, 'CustomerReceipts', 'record',       1, NOW()),
(2, 'CustomerReceipts', 'save',         1, NOW()),
(2, 'AgedReceivables',  'index',        1, NOW()),
(2, 'CompanyProfile',   'index',        1, NOW()),
(2, 'CompanyProfile',   'saveAj',       1, NOW()),
(2, 'DocNumbers',       'index',        1, NOW()),
(2, 'DocNumbers',       'saveAj',       1, NOW());

-- Accountant (3) — same AR gap
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(3, 'Dashboard',        'index',        1, NOW()),
(3, 'Dashboard',        'finance',      1, NOW()),
(3, 'CustomerInvoices', 'index',        1, NOW()),
(3, 'CustomerInvoices', 'view',         1, NOW()),
(3, 'CustomerInvoices', 'pdf',          1, NOW()),
(3, 'CustomerInvoices', 'print',        1, NOW()),
(3, 'CustomerReceipts', 'index',        1, NOW()),
(3, 'CustomerReceipts', 'view',         1, NOW()),
(3, 'CustomerReceipts', 'record',       1, NOW()),
(3, 'CustomerReceipts', 'save',         1, NOW()),
(3, 'AgedReceivables',  'index',        1, NOW());

-- Executive (1) — dashboard actions
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(1, 'Dashboard', 'index',   1, NOW()),
(1, 'Dashboard', 'exec',    1, NOW()),
(1, 'Dashboard', 'finance', 1, NOW()),
(1, 'Dashboard', 'fleet',   1, NOW()),
(1, 'Dashboard', 'ops',     1, NOW()),
(1, 'Dashboard', 'hr',      1, NOW()),
(1, 'Dashboard', 'fuel',    1, NOW()),
(1, 'Dashboard', 'procurement', 1, NOW());

-- Fleet Manager (22) — Dashboard + AP visibility (garage parts pipeline)
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(22, 'Dashboard',      'index',   1, NOW()),
(22, 'Dashboard',      'fleet',   1, NOW()),
(22, 'Suppliers',      'index',   1, NOW()),
(22, 'Suppliers',      'view',    1, NOW()),
(22, 'SupplierBills',  'index',   1, NOW()),
(22, 'SupplierBills',  'view',    1, NOW()),
(22, 'DocExpiries',    'index',   1, NOW()),
(22, 'CustomerReceipts','index',  1, NOW());

-- Ops Manager (20) — Dashboard
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(20, 'Dashboard',      'index',   1, NOW()),
(20, 'Dashboard',      'ops',     1, NOW()),
(20, 'Dashboard',      'fleet',   1, NOW()),
(20, 'DocExpiries',    'index',   1, NOW());

-- Dispatcher (21) — Dashboard + fleet dashboard access
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(21, 'Dashboard',      'index',   1, NOW()),
(21, 'Dashboard',      'ops',     1, NOW()),
(21, 'DocExpiries',    'index',   1, NOW());

-- HR Officer (24) — was severely under-granted
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(24, 'Dashboard',      'index',   1, NOW()),
(24, 'Dashboard',      'hr',      1, NOW()),
(24, 'Payroll',        'index',   1, NOW()),
(24, 'Payroll',        'view',    1, NOW()),
(24, 'Payroll',        'add',     1, NOW()),
(24, 'Payroll',        'save',    1, NOW()),
(24, 'Payroll',        'compute', 1, NOW()),
(24, 'Payroll',        'approve', 1, NOW()),
(24, 'Payroll',        'pay',     1, NOW()),
(24, 'Payroll',        'payslip', 1, NOW()),
(24, 'Payroll',        'payslipPdf',    1, NOW()),
(24, 'Payroll',        'payslipsBatch', 1, NOW()),
(24, 'Payroll',        'p9',      1, NOW()),
(24, 'Payroll',        'p9Batch', 1, NOW()),
(24, 'TaxReturns',     'index',      1, NOW()),
(24, 'TaxReturns',     'paye',       1, NOW()),
(24, 'TaxReturns',     'vat',        1, NOW()),
(24, 'TaxReturns',     'statutory',  1, NOW()),
(24, 'TaxReturns',     'payeCsv',    1, NOW()),
(24, 'DocExpiries',    'index',   1, NOW()),
(24, 'DriverAssignments','index', 1, NOW()),
(24, 'DriverAssignments','view',  1, NOW()),
(24, 'Notifications',  'index',   1, NOW()),
(24, 'Drivers',        'staffId', 1, NOW()),
(24, 'Drivers',        'uploadDoc', 1, NOW()),
(24, 'Drivers',        'deleteDoc', 1, NOW());

-- Fuel Officer (23) — only had 2 controllers before
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(23, 'Dashboard',      'index',   1, NOW()),
(23, 'Dashboard',      'fuel',    1, NOW()),
(23, 'Trucks',         'index',   1, NOW()),
(23, 'Trucks',         'view',    1, NOW()),
(23, 'Drivers',        'index',   1, NOW()),
(23, 'Drivers',        'view',    1, NOW()),
(23, 'Trips',          'index',   1, NOW()),
(23, 'Trips',          'view',    1, NOW()),
(23, 'FuelEntries',    'add',     1, NOW()),
(23, 'FuelEntries',    'save',    1, NOW()),
(23, 'FuelEntries',    'view',    1, NOW()),
(23, 'GpsDashboard',   'index',   1, NOW()),
(23, 'Notifications',  'index',   1, NOW());

-- Procurement (7) — SupplierPayments + AP aging + statements
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(7,  'Dashboard',          'index',      1, NOW()),
(7,  'Dashboard',          'procurement',1, NOW()),
(7,  'SupplierPayments',   'index',      1, NOW()),
(7,  'SupplierPayments',   'view',       1, NOW()),
(7,  'AgedPayables',       'index',      1, NOW()),
(7,  'WorkOrders',         'index',      1, NOW()),
(7,  'WorkOrders',         'view',       1, NOW());

-- Field Supervisor (6) — supervises maintenance & drivers on the ground
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(6, 'Dashboard',        'index',    1, NOW()),
(6, 'Dashboard',        'fleet',    1, NOW()),
(6, 'Trucks',           'index',    1, NOW()),
(6, 'Trucks',           'view',     1, NOW()),
(6, 'Drivers',          'index',    1, NOW()),
(6, 'Drivers',          'view',     1, NOW()),
(6, 'WorkOrders',       'index',    1, NOW()),
(6, 'WorkOrders',       'view',     1, NOW()),
(6, 'PmDashboard',      'index',    1, NOW()),
(6, 'PmDashboard',      'view',     1, NOW()),
(6, 'FuelEntries',      'index',    1, NOW()),
(6, 'DocExpiries',      'index',    1, NOW()),
(6, 'Notifications',    'index',    1, NOW());


-- ─────────────────────────────────────────────────────────────────
-- Verify
-- ─────────────────────────────────────────────────────────────────
SELECT ug.user_group_name AS role,
       COUNT(DISTINCT ugp.controller_allowed) AS controllers,
       COUNT(*) AS action_rows
  FROM user_groups_perms ugp
  JOIN user_groups ug ON ug.c0d = ugp.user_group_id
 WHERE ug.deleted = 0
 GROUP BY ug.c0d, ug.user_group_name
 ORDER BY ug.c0d;
