-- =========================================================
-- MÓDULO SUPER ADMIN / ADMIN + ISO 27001/27002/27018
-- Compatible con instalación nueva y actualización incremental.
-- =========================================================

ALTER TABLE roles ADD COLUMN IF NOT EXISTS nivel INT NOT NULL DEFAULT 50;
ALTER TABLE roles ADD COLUMN IF NOT EXISTS protected TINYINT(1) NOT NULL DEFAULT 0;

ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS last_login_at DATETIME NULL;
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS failed_logins INT NOT NULL DEFAULT 0;
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS locked_until DATETIME NULL;
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS mfa_enabled TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS mfa_secret VARCHAR(120) NULL;
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS must_change_password TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE usuarios ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL;

CREATE TABLE IF NOT EXISTS permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) UNIQUE NOT NULL,
  nombre VARCHAR(160) NOT NULL,
  modulo VARCHAR(80) NOT NULL,
  descripcion TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY(role_id, permission_id),
  FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  event_uuid CHAR(36) UNIQUE NOT NULL,
  usuario_id INT NULL,
  accion VARCHAR(120) NOT NULL,
  modulo VARCHAR(120) NULL,
  detalle TEXT NULL,
  severity ENUM('info','warning','critical') DEFAULT 'info',
  entity_type VARCHAR(120) NULL,
  entity_id VARCHAR(80) NULL,
  ip VARCHAR(60) NULL,
  user_agent VARCHAR(255) NULL,
  metadata_json JSON NULL,
  prev_hash CHAR(64) NOT NULL,
  chain_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_audit_fecha(created_at),
  INDEX idx_audit_accion(accion),
  INDEX idx_audit_usuario(usuario_id),
  FOREIGN KEY(usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS system_backups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  checksum_sha256 CHAR(64) NOT NULL,
  tipo ENUM('manual','programado') DEFAULT 'manual',
  encrypted TINYINT(1) DEFAULT 0,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(created_by) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS iso_controls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  standard VARCHAR(40) NOT NULL,
  codigo VARCHAR(40) UNIQUE NOT NULL,
  nombre VARCHAR(180) NOT NULL,
  objetivo TEXT NULL,
  estado ENUM('pendiente','parcial','implementado') DEFAULT 'pendiente',
  responsable VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS iso_evidences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  control_id INT NOT NULL,
  titulo VARCHAR(180) NOT NULL,
  descripcion TEXT NULL,
  archivo_path VARCHAR(500) NULL,
  estado ENUM('pendiente','validada','rechazada') DEFAULT 'pendiente',
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(control_id) REFERENCES iso_controls(id) ON DELETE CASCADE,
  FOREIGN KEY(created_by) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO roles(slug,nombre,descripcion,nivel,protected) VALUES
('super_admin','SUPER ADMIN','Control total: usuarios, roles, permisos, auditoría, backups y SGSI.',1,1),
('auditor','Auditor / Compliance','Consulta de auditoría, evidencias ISO y reportes sin administración operativa.',20,1);

UPDATE roles SET nivel=5, protected=1 WHERE slug='admin';
UPDATE roles SET nivel=30, protected=1 WHERE slug='staff';

INSERT IGNORE INTO permissions(slug,nombre,modulo,descripcion) VALUES
('users.view','Ver usuarios','Usuarios','Consultar usuarios y estados'),
('users.create','Crear usuarios','Usuarios','Crear usuarios internos o externos'),
('users.edit','Editar usuarios','Usuarios','Modificar datos, rol, sede y estado'),
('users.delete','Eliminar/bloquear usuarios','Usuarios','Bloqueo o eliminación lógica'),
('users.approve','Aprobar/rechazar usuarios','Usuarios','Aprobación administrativa'),
('users.reset_password','Resetear contraseñas','Usuarios','Emisión de contraseña temporal'),
('roles.view','Ver roles','Roles y permisos','Consultar roles y permisos'),
('roles.create','Crear roles','Roles y permisos','Crear roles personalizados'),
('roles.edit','Editar roles/permisos','Roles y permisos','Modificar permisos de rol'),
('roles.delete','Eliminar roles','Roles y permisos','Eliminar roles no protegidos'),
('audit.view','Ver auditoría forense','Auditoría','Consultar bitácora forense'),
('audit.export','Exportar auditoría','Auditoría','Exportar evidencia CSV'),
('backups.view','Ver backups','Backups','Consultar copias de seguridad'),
('backups.create','Crear backups','Backups','Generar backup manual'),
('backups.download','Descargar backups','Backups','Descargar copia SQL'),
('backups.delete','Eliminar backups','Backups','Eliminar backup controlado'),
('iso.view','Ver matriz ISO','SGSI / ISO','Consultar matriz de controles internos'),
('iso.evidence','Registrar evidencias ISO','SGSI / ISO','Registrar evidencias de cumplimiento'),
('iso.export','Exportar matriz ISO','SGSI / ISO','Exportar matriz de cumplimiento'),
('payments.view','Ver pagos','Pagos','Consultar pagos'),
('payments.validate','Validar pagos','Pagos','Aprobar o rechazar pagos'),
('agenda.view','Ver agenda','Agenda','Consultar agenda académica'),
('agenda.edit','Gestionar agenda','Agenda','Crear y editar agenda'),
('notifications.view','Ver notificaciones','Notificaciones','Consultar notificaciones'),
('notifications.create','Crear notificaciones','Notificaciones','Publicar notificaciones'),
('certificates.view','Ver certificados','Certificados','Consultar certificados'),
('certificates.generate','Generar certificados','Certificados','Emitir certificados'),
('qr.scan','Escanear QR','QR','Validar credenciales, tickets y asistencia'),
('qr.use','Usar/registrar QR','QR','Marcar token como usado'),
('reports.export','Exportar reportes','Reportes','Exportar bases operativas');

-- SUPER ADMIN no necesita asignación: bypass por código.
-- ADMIN recibe todos los permisos.
INSERT IGNORE INTO role_permissions(role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p WHERE r.slug='admin';

-- STAFF recibe operación sin gobierno de roles, auditoría sensible ni backups.
INSERT IGNORE INTO role_permissions(role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p WHERE r.slug='staff' AND p.slug IN (
'users.view','users.approve','payments.view','payments.validate','agenda.view','agenda.edit','notifications.view','notifications.create','certificates.view','certificates.generate','qr.scan','qr.use','reports.export'
);

-- AUDITOR recibe consulta y exportación de evidencias.
INSERT IGNORE INTO role_permissions(role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p WHERE r.slug='auditor' AND p.slug IN ('audit.view','audit.export','iso.view','iso.export','reports.export');

INSERT IGNORE INTO usuarios(role_id,nombres,apellidos,institucion,email,telefono,password,estado,must_change_password)
SELECT id,'Super','Administrador','CGE','superadmin@atenea.edu.ec','0980000000','$2y$12$F3OPWHfb.TtsxrArjZmWxO4kW4jRovqX3GpJoEUOJbBs0bAiGhZg6','aprobado',1
FROM roles WHERE slug='super_admin';

INSERT IGNORE INTO iso_controls(standard,codigo,nombre,objetivo,estado,responsable) VALUES
('ISO/IEC 27001','ATN-ISO-001','Gobierno SGSI','Mantener políticas, responsabilidades y mejora continua del SGSI.', 'parcial','SUPER ADMIN'),
('ISO/IEC 27001','ATN-ISO-002','Gestión de riesgos','Identificar, evaluar y tratar riesgos sobre información del congreso.', 'pendiente','ADMIN'),
('ISO/IEC 27002','ATN-ISO-003','Control de acceso RBAC','Aplicar mínimos privilegios por rol y permiso.', 'implementado','SUPER ADMIN'),
('ISO/IEC 27002','ATN-ISO-004','Registro y monitoreo','Conservar logs forenses con trazabilidad e integridad.', 'implementado','SUPER ADMIN'),
('ISO/IEC 27002','ATN-ISO-005','Copias de seguridad','Generar, proteger y verificar backups de la base de datos.', 'parcial','ADMIN'),
('ISO/IEC 27002','ATN-ISO-006','Gestión de identidades','Controlar altas, bajas, bloqueos, contraseñas y MFA.', 'parcial','ADMIN'),
('ISO/IEC 27002','ATN-ISO-007','Seguridad operativa del evento','Proteger QR, certificados, pagos, asistencia y reportes.', 'parcial','STAFF'),
('ISO/IEC 27018','ATN-ISO-008','Protección de PII','Minimizar, proteger y auditar datos personales de participantes.', 'parcial','ADMIN'),
('ISO/IEC 27018','ATN-ISO-009','Privacidad y consentimiento','Registrar evidencias de consentimiento, finalidad y tratamiento de datos.', 'pendiente','ADMIN'),
('ISO/IEC 27018','ATN-ISO-010','Transparencia y auditoría de PII','Mantener evidencia exportable sobre acceso y tratamiento de PII.', 'parcial','AUDITOR');