-- Migration: Add granular permission columns to users table
-- perm_expenses, perm_reports, perm_payroll, perm_dispatch, perm_warehouse,
-- perm_settings, perm_branding, perm_users, perm_logs,
-- perm_costings, perm_assign, perm_customers, perm_invoices, perm_financials, perm_photos

ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `perm_expenses`   TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_reports`    TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_payroll`    TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_dispatch`   TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_warehouse`  TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_settings`   TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_branding`   TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_users`      TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_logs`       TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_costings`   TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_assign`     TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_customers`  TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_invoices`   TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_financials` TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `perm_photos`     TINYINT(1) NOT NULL DEFAULT 0;

-- Grant all permissions to existing admins and managers
UPDATE `users`
SET
  perm_expenses=1, perm_reports=1, perm_payroll=1, perm_dispatch=1,
  perm_warehouse=1, perm_settings=1, perm_branding=1, perm_users=1,
  perm_logs=1, perm_costings=1, perm_assign=1, perm_customers=1,
  perm_invoices=1, perm_financials=1, perm_photos=1
WHERE role IN ('admin', 'manager');