-- ============================================================
-- Permissions Schema Migration
-- Database: res_erp
-- Description: RBAC matrix for ops_digi. Two tables — permissions
--              (catalog of gateable features) and role_permissions
--              (role -> permission grants). Role 1 (admin) is NEVER
--              materialised; PHP helper short-circuits it as wildcard.
-- Date: 2026-05-25
-- ============================================================

-- 1) Catalog of every gateable feature/page.
--    permission_key is what PHP code passes to user_can('saved_logs.view')
CREATE TABLE IF NOT EXISTS `permissions` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `permission_key` VARCHAR(64)  NOT NULL,
  `label`          VARCHAR(128) NOT NULL,
  `category`       VARCHAR(64)  NOT NULL,
  `description`    TEXT NULL,
  `sort_order`     INT NOT NULL DEFAULT 0,
  `is_system`      TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permission_key` (`permission_key`),
  KEY `idx_category` (`category`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Role -> Permission grants. Absence of a row = denied.
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id`       INT NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `granted_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `granted_by`    VARCHAR(64) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_perm` (`role_id`, `permission_id`),
  KEY `idx_role` (`role_id`),
  CONSTRAINT `fk_rp_permission`
    FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed the permission catalog (idempotent via UPSERT semantics).
INSERT INTO `permissions` (`permission_key`, `label`, `category`, `description`, `sort_order`, `is_system`) VALUES
  ('saved_logs.view',   'View Saved Logs',           'Log Sheets',     'Open the Saved Logs admin table.', 10, 1),
  ('saved_logs.delete', 'Delete Saved Logs',         'Log Sheets',     'Show the delete column / icon in Saved Logs and accept POSTs to delete_entry.php.', 20, 1),
  ('create_log',        'Create New Logsheet',       'Log Sheets',     'Open index.php and initialise a new logsheet from a template tile.', 30, 1),
  ('process_slip',      'Generate Process Slip',     'Log Sheets',     'Access process_slip_generator.php and save process slips.', 40, 1),
  ('dashboard',         'View Dashboard',            'Reports',        'Open dashboard.php (admin dashboard with counts and recent logs).', 50, 1),
  ('battery_report',    'Battery Production Report', 'Reports',        'Open report_batteries.php and run/export production queries.', 60, 1),
  ('workstations',      'Workstation Mapping',       'Configuration',  'Open and edit admin_workstations.php.', 70, 1),
  ('batteries',         'Battery Code Mapping',      'Configuration',  'Open and edit admin_batteries.php.',    80, 1),
  ('technicians',       'Manage Technicians',        'Configuration',  'Open and edit admin_technicians.php.',  90, 1),
  ('customizations',    'Customizations Dashboard',  'Configuration',  'Open admin_customizations.php landing.', 100, 1),
  ('hp_types',          'Custom HP Types',           'Configuration',  'Open and edit admin_custom_hp_types.php.', 110, 1),
  ('users',             'User Management',           'Administration', 'Open and edit manage_users.php.',  120, 1),
  ('templates',         'Manage Templates',          'Administration', 'Open and edit manage_templates.php.', 130, 1),
  ('changelog',         'Change Log',                'Administration', 'Open admin_changelog.php audit history.', 140, 1),
  ('permissions',       'Manage Permissions',        'Administration', 'Open manage_permissions.php to edit this matrix.', 150, 1),
  ('preferences',       'Personal Preferences',      'Personal',       'Open user_preferences.php to toggle browser automation.', 160, 1)
ON DUPLICATE KEY UPDATE
  `label`       = VALUES(`label`),
  `category`    = VALUES(`category`),
  `description` = VALUES(`description`),
  `sort_order`  = VALUES(`sort_order`),
  `is_system`   = VALUES(`is_system`);

-- Seed initial grants. Role 1 (admin) is the implicit super-admin and is
-- intentionally NOT inserted — the PHP helper user_can() returns true for
-- role 1 regardless of grants, which prevents admins from locking themselves
-- out by un-checking everything in the UI.
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, `id` FROM `permissions` WHERE `permission_key` IN (
  'saved_logs.view','create_log','process_slip','dashboard','battery_report',
  'workstations','batteries','technicians','customizations','hp_types','preferences'
);

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 3, `id` FROM `permissions` WHERE `permission_key` IN (
  'saved_logs.view','create_log','dashboard','battery_report','preferences'
);

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 5, `id` FROM `permissions` WHERE `permission_key` IN (
  'saved_logs.view','create_log','process_slip','dashboard','preferences'
);
