database/migrate-add-admin-roles.sql

-- Migration: Add Admin Roles System
-- Date: 2026-04-18
-- Purpose: Implement role-based access control for administrators

CREATE TABLE IF NOT EXISTS admin_roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  role_name VARCHAR(50) NOT NULL UNIQUE,
  display_label VARCHAR(100) NOT NULL,
  description TEXT NULL,
  permissions_json JSON NOT NULL DEFAULT ('{}'),
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_roles_active (is_active),
  INDEX idx_roles_name (role_name)
) ENGINE=InnoDB;

ALTER TABLE administrators
ADD COLUMN IF NOT EXISTS role_id INT UNSIGNED NULL DEFAULT 1 AFTER is_active,
ADD COLUMN IF NOT EXISTS last_login_at DATETIME NULL DEFAULT NULL AFTER updated_at,
ADD INDEX idx_admin_role (role_id);

INSERT INTO admin_roles (role_name, display_label, description, permissions_json)
VALUES
  ('super_admin', 'Super Administrator', 'Full system access including admin management', JSON_OBJECT(
    'admin_management', true,
    'doctor_management', true,
    'patient_management', true,
    'billing_management', true,
    'inventory_management', true,
    'admissions_management', true,
    'audit_logs_view', true,
    'settings_edit', true,
    'analytics_view', true,
    'notifications_send', true,
    'system_configuration', true
  )),
  ('admin', 'Administrator', 'Can manage doctors, patients, and view analytics', JSON_OBJECT(
    'admin_management', false,
    'doctor_management', true,
    'patient_management', true,
    'billing_management', true,
    'inventory_management', false,
    'admissions_management', true,
    'audit_logs_view', true,
    'settings_edit', false,
    'analytics_view', true,
    'notifications_send', false,
    'system_configuration', false
  )),
  ('manager', 'Manager', 'Can view reports and manage operations', JSON_OBJECT(
    'admin_management', false,
    'doctor_management', false,
    'patient_management', true,
    'billing_management', true,
    'inventory_management', true,
    'admissions_management', true,
    'audit_logs_view', true,
    'settings_edit', false,
    'analytics_view', true,
    'notifications_send', false,
    'system_configuration', false
  )),
  ('viewer', 'Viewer', 'Read-only access to dashboards and reports', JSON_OBJECT(
    'admin_management', false,
    'doctor_management', false,
    'patient_management', false,
    'billing_management', false,
    'inventory_management', false,
    'admissions_management', false,
    'audit_logs_view', true,
    'settings_edit', false,
    'analytics_view', true,
    'notifications_send', false,
    'system_configuration', false
  ))
ON DUPLICATE KEY UPDATE
  display_label = VALUES(display_label),
  description = VALUES(description),
  permissions_json = VALUES(permissions_json),
  is_active = 1;

UPDATE administrators
SET role_id = COALESCE(role_id, 1)
WHERE id = 'ADM001' OR email = 'administrator@carepeople.com';