-- 0002_reference_data.sql
-- Roles, granular permissions, role mapping, and audit triggers on master data.

-- ---------------------------------------------------------- audit hooks ----

CREATE TRIGGER audit_branches   AFTER INSERT OR UPDATE OR DELETE ON branches
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('branches');
CREATE TRIGGER audit_users      AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('users');
CREATE TRIGGER audit_roles      AFTER INSERT OR UPDATE OR DELETE ON roles
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('roles');
CREATE TRIGGER audit_role_perms AFTER INSERT OR UPDATE OR DELETE ON role_permissions
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('role_permissions');
CREATE TRIGGER audit_user_roles AFTER INSERT OR UPDATE OR DELETE ON user_roles
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('user_roles');
CREATE TRIGGER audit_settings   AFTER INSERT OR UPDATE OR DELETE ON settings
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('settings');
CREATE TRIGGER audit_suppliers  AFTER INSERT OR UPDATE OR DELETE ON suppliers
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('suppliers');
CREATE TRIGGER audit_products   AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('products');
CREATE TRIGGER audit_batches    AFTER INSERT OR UPDATE OR DELETE ON batches
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('batches');
CREATE TRIGGER audit_approvals  AFTER INSERT OR UPDATE OR DELETE ON approvals
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('approvals');

-- ---------------------------------------------------------- permissions ----

INSERT INTO permissions (code, description) VALUES
  ('sale.create',                'Create a sale'),
  ('sale.hold',                  'Hold and resume a sale'),
  ('sale.discount.apply',        'Apply a discount within the allowed limit'),
  ('sale.discount.large',        'Request a discount above the limit'),
  ('sale.price_override',        'Request a price override'),
  ('sale.void',                  'Void or cancel a transaction'),
  ('payment.receive',            'Receive a payment'),
  ('receipt.print',              'Print a receipt'),
  ('receipt.reprint',            'Reprint a receipt'),
  ('product.search',             'Search products'),
  ('product.view',               'View products'),
  ('product.create',             'Create products'),
  ('product.edit',               'Edit products'),
  ('product.price.edit',         'Change selling prices'),
  ('product.purchase_price.view','View purchase prices'),
  ('product.purchase_price.edit','Change purchase prices'),
  ('stock.view',                 'View stock levels'),
  ('stock.receive',              'Receive stock against a purchase'),
  ('stock.adjust',               'Request or make a stock adjustment'),
  ('stock.count',                'Perform stock counts'),
  ('stock.transfer',             'Transfer stock between branches'),
  ('batch.quarantine',           'Quarantine or release a batch'),
  ('recall.manage',              'Manage product recalls'),
  ('rx.view',                    'View prescriptions'),
  ('rx.create',                  'Record prescriptions'),
  ('rx.dispense',                'Dispense prescription medicines'),
  ('rx.dispense_controlled',     'Dispense controlled medicines'),
  ('controlled.register.view',   'View the controlled-drug register and reports'),
  ('controlled.adjust',          'Adjust controlled-drug stock'),
  ('customer.view',              'View customers'),
  ('customer.edit',              'Create and edit customers'),
  ('purchase.create',            'Create purchase orders'),
  ('purchase.approve',           'Approve purchase orders'),
  ('supplier.manage',            'Manage suppliers'),
  ('refund.create',              'Process a refund'),
  ('refund.approve',             'Approve a refund'),
  ('cash.shift.open',            'Open a cashier shift'),
  ('cash.shift.close',           'Close a cashier shift'),
  ('cash.shift.approve_variance','Approve a cash variance'),
  ('report.sales',               'View sales reports'),
  ('report.profit',              'View profit reports'),
  ('report.inventory',           'View inventory reports'),
  ('report.purchases',           'View purchase reports'),
  ('report.financial',           'View financial reports'),
  ('audit.view',                 'View the audit trail'),
  ('approval.decide',            'Approve or reject requests'),
  ('user.manage',                'Manage users'),
  ('role.manage',                'Manage roles and permissions'),
  ('settings.manage',            'Manage system settings');

-- ---------------------------------------------------------------- roles ----

INSERT INTO roles (code, name) VALUES
  ('system_admin',        'System Administrator'),
  ('pharmacy_manager',    'Pharmacy Manager'),
  ('branch_manager',      'Branch Manager'),
  ('pharmacist',          'Pharmacist'),
  ('pharm_technologist',  'Pharmaceutical Technologist'),
  ('cashier',             'Cashier'),
  ('storekeeper',         'Storekeeper'),
  ('procurement_officer', 'Procurement Officer'),
  ('accountant',          'Accountant'),
  ('auditor',             'Auditor');

INSERT INTO role_permissions (role_id, permission_code)
SELECT r.id, p.code
FROM roles r
JOIN permissions p ON p.code = ANY (
  CASE r.code
    WHEN 'system_admin' THEN
      (SELECT array_agg(code) FROM permissions)
    WHEN 'pharmacy_manager' THEN
      (SELECT array_agg(code) FROM permissions WHERE code <> 'role.manage')
    WHEN 'branch_manager' THEN
      (SELECT array_agg(code) FROM permissions WHERE code <> 'role.manage')
    WHEN 'pharmacist' THEN ARRAY[
      'sale.create', 'sale.hold', 'sale.discount.apply', 'payment.receive',
      'receipt.print', 'receipt.reprint', 'product.search', 'product.view',
      'stock.view', 'batch.quarantine', 'rx.view', 'rx.create', 'rx.dispense',
      'rx.dispense_controlled', 'controlled.register.view', 'customer.view',
      'customer.edit', 'refund.create', 'report.inventory']
    WHEN 'pharm_technologist' THEN ARRAY[
      'sale.create', 'sale.hold', 'sale.discount.apply', 'payment.receive',
      'receipt.print', 'receipt.reprint', 'product.search', 'product.view',
      'stock.view', 'rx.view', 'rx.create', 'rx.dispense', 'customer.view',
      'customer.edit']
    WHEN 'cashier' THEN ARRAY[
      'sale.create', 'sale.hold', 'payment.receive', 'receipt.print',
      'product.search', 'product.view', 'customer.view',
      'cash.shift.open', 'cash.shift.close']
    WHEN 'storekeeper' THEN ARRAY[
      'product.search', 'product.view', 'stock.view', 'stock.receive',
      'stock.count', 'stock.transfer', 'report.inventory']
    WHEN 'procurement_officer' THEN ARRAY[
      'product.search', 'product.view', 'product.purchase_price.view',
      'stock.view', 'purchase.create', 'supplier.manage', 'report.purchases',
      'report.inventory']
    WHEN 'accountant' THEN ARRAY[
      'product.view', 'product.purchase_price.view', 'report.sales',
      'report.profit', 'report.inventory', 'report.purchases', 'report.financial']
    WHEN 'auditor' THEN ARRAY[
      'product.view', 'product.purchase_price.view', 'stock.view', 'rx.view',
      'controlled.register.view', 'customer.view', 'report.sales', 'report.profit',
      'report.inventory', 'report.purchases', 'report.financial', 'audit.view']
  END
);