-- Additive RBAC upgrade. Existing super users retain unrestricted access.
CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_key VARCHAR(100) NOT NULL,
  PRIMARY KEY(role_id,permission_key),
  CONSTRAINT fk_rp_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS admin_roles (
  admin_id INT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(admin_id,role_id),
  CONSTRAINT fk_ar_admin FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE CASCADE,
  CONSTRAINT fk_ar_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS admin_permission_overrides (
  admin_id INT UNSIGNED NOT NULL,
  permission_key VARCHAR(100) NOT NULL,
  effect ENUM('allow','deny') NOT NULL,
  PRIMARY KEY(admin_id,permission_key),
  CONSTRAINT fk_apo_admin FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE CASCADE
);
INSERT INTO roles(name,description,is_system) VALUES
 ('Administrator','Full operational access except user and system administration',1),
 ('Registration Staff','Registration, roster, participant, and submission access',1),
 ('Communications','Authors, surveys, email, and participant communications',1),
 ('Read Only','Read-only access to operational records',1)
ON DUPLICATE KEY UPDATE name=VALUES(name);