
CREATE TABLE IF NOT EXISTS `access_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NULL,
  `panel` VARCHAR(40) NOT NULL,
  `tipo` ENUM('login','logout','recuperacion','restablecer','mfa') NOT NULL,
  `identificador` VARCHAR(190) NULL,
  `request_id` CHAR(36) NOT NULL,
  `ip_hash` VARCHAR(128) NULL,
  `user_agent_hash` VARCHAR(128) NULL,
  `resultado` ENUM('permitido','fallido','bloqueado','captcha_invalido','token_invalido','cuenta_inactiva','clave_invalida','sesion_cerrada','mfa_pendiente') NOT NULL,
  `detalle` VARCHAR(255) NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_access_attempts_panel_tipo_creado` (`panel`, `tipo`, `creado_en`),
  KEY `idx_access_attempts_identificador` (`identificador`),
  KEY `idx_access_attempts_usuario` (`usuario_id`),
  KEY `idx_access_attempts_request_id` (`request_id`),
  CONSTRAINT `fk_access_attempts_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mfa_metodos` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `tipo` ENUM('totp','email_code','backup_code_set') NOT NULL DEFAULT 'email_code',
  `nombre_visible` VARCHAR(120) NOT NULL DEFAULT 'Acceso reforzado',
  `secreto_cifrado` TEXT NULL,
  `secreto_nonce` VARCHAR(255) NULL,
  `key_version` VARCHAR(20) NULL,
  `primario` TINYINT(1) NOT NULL DEFAULT 0,
  `estado` ENUM('pendiente','activo','inactivo','revocado') NOT NULL DEFAULT 'pendiente',
  `ultimo_verificado_en` DATETIME NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mfa_usuario_estado` (`usuario_id`, `estado`),
  KEY `idx_mfa_primario` (`primario`),
  CONSTRAINT `fk_mfa_metodos_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trusted_devices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `panel` VARCHAR(40) NOT NULL DEFAULT 'superadmin',
  `device_hash` VARCHAR(128) NOT NULL,
  `nombre_visible` VARCHAR(150) NULL,
  `ip_hash` VARCHAR(128) NULL,
  `user_agent_hash` VARCHAR(128) NULL,
  `verificado_en` DATETIME NULL,
  `ultimo_uso_en` DATETIME NULL,
  `expira_en` DATETIME NULL,
  `revocado_en` DATETIME NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_trusted_devices_usuario_device` (`usuario_id`, `panel`, `device_hash`),
  KEY `idx_trusted_devices_expira` (`expira_en`),
  KEY `idx_trusted_devices_usuario_panel` (`usuario_id`, `panel`),
  CONSTRAINT `fk_trusted_devices_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

