-- RBAC: workspace roles, permission modules/actions, role_permissions matrix
-- Portal auth remains on roles (Admin/Staff/Client); app permissions on workspace_roles via app_role_id

CREATE TABLE IF NOT EXISTS workspace_roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL,
  description VARCHAR(500) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_workspace_roles_name (name, deleted_at),
  UNIQUE KEY uq_workspace_roles_slug (slug, deleted_at)
);

CREATE TABLE IF NOT EXISTS permission_modules (
  module_key VARCHAR(64) NOT NULL PRIMARY KEY,
  label VARCHAR(120) NOT NULL,
  module_group VARCHAR(64) NOT NULL DEFAULT 'Core',
  sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS permission_actions (
  action_key VARCHAR(32) NOT NULL PRIMARY KEY,
  label VARCHAR(64) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT NOT NULL,
  module_key VARCHAR(64) NOT NULL,
  action_key VARCHAR(32) NOT NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (role_id, module_key, action_key),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES workspace_roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_module FOREIGN KEY (module_key) REFERENCES permission_modules(module_key) ON DELETE CASCADE,
  CONSTRAINT fk_rp_action FOREIGN KEY (action_key) REFERENCES permission_actions(action_key) ON DELETE CASCADE
);

ALTER TABLE users
  ADD COLUMN app_role_id INT NULL AFTER role_id,
  ADD CONSTRAINT fk_users_app_role FOREIGN KEY (app_role_id) REFERENCES workspace_roles(id);

ALTER TABLE employees
  ADD COLUMN app_role_id INT NULL AFTER status,
  ADD CONSTRAINT fk_employees_app_role FOREIGN KEY (app_role_id) REFERENCES workspace_roles(id);

-- Seed actions
INSERT IGNORE INTO permission_actions (action_key, label, sort_order) VALUES
  ('read', 'Read', 1),
  ('create', 'Create', 2),
  ('update', 'Update', 3),
  ('delete', 'Delete', 4),
  ('export', 'Export', 5);

-- Seed modules
INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('dashboard', 'Dashboard', 'Core', 10),
  ('employees', 'Employees', 'Core', 20),
  ('departments', 'Departments', 'Core', 30),
  ('designations', 'Designations', 'Core', 40),
  ('clients', 'Clients', 'Core', 50),
  ('projects', 'Projects', 'Core', 60),
  ('tasks', 'Tasks', 'Core', 70),
  ('project_chat', 'Project Chat', 'Core', 75),
  ('profile', 'Profile', 'Platform', 80),
  ('web_settings', 'Web Settings', 'Settings', 90),
  ('settings_roles', 'Roles & Permissions', 'Settings', 95),
  ('audit_logs', 'Audit Logs', 'Platform', 100),
  ('recovery_center', 'Recovery Center', 'Platform', 110);

-- Seed workspace roles
INSERT IGNORE INTO workspace_roles (id, name, slug, description, is_system, status) VALUES
  (1, 'Super Admin', 'super-admin', 'Full access to all modules and actions', 1, 'active'),
  (2, 'Employee', 'employee', 'Default staff role with limited access', 0, 'active'),
  (3, 'Client', 'client', 'Client portal access', 1, 'active');

-- Super Admin: all permissions
INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT 1, pm.module_key, pa.action_key, 1
FROM permission_modules pm
CROSS JOIN permission_actions pa;

-- Client: projects/tasks/profile read only
INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed) VALUES
  (3, 'dashboard', 'read', 1),
  (3, 'projects', 'read', 1),
  (3, 'tasks', 'read', 1),
  (3, 'profile', 'read', 1),
  (3, 'profile', 'update', 1);

-- Employee default: dashboard, profile, projects, tasks (read + task update), project_chat read
INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed) VALUES
  (2, 'dashboard', 'read', 1),
  (2, 'profile', 'read', 1),
  (2, 'profile', 'update', 1),
  (2, 'projects', 'read', 1),
  (2, 'tasks', 'read', 1),
  (2, 'tasks', 'update', 1),
  (2, 'project_chat', 'read', 1),
  (2, 'project_chat', 'create', 1);

-- Migrate existing data
UPDATE employees SET app_role_id = 2 WHERE app_role_id IS NULL;

UPDATE users u
JOIN roles r ON r.id = u.role_id
SET u.app_role_id = CASE
  WHEN r.name = 'Admin' THEN 1
  WHEN r.name = 'Client' THEN 3
  ELSE NULL
END
WHERE u.app_role_id IS NULL;

UPDATE users u
JOIN employees e ON e.user_id = u.id
SET u.app_role_id = e.app_role_id
WHERE u.app_role_id IS NULL AND e.app_role_id IS NOT NULL;

UPDATE users u
JOIN roles r ON r.id = u.role_id
SET u.app_role_id = 2
WHERE u.app_role_id IS NULL AND r.name = 'Staff';

ALTER TABLE employees
  MODIFY COLUMN app_role_id INT NOT NULL;