SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid_publico` CHAR(36) NOT NULL,
  `nombre` VARCHAR(180) NOT NULL,
  `correo` VARCHAR(150) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `tipo_usuario` ENUM('superadmin','staff','distribuidor','contador','cliente') NOT NULL DEFAULT 'superadmin',
  `estado` ENUM('pendiente','activo','suspendido','bloqueado_seguridad','cambio_clave_obligatorio') NOT NULL DEFAULT 'pendiente',
  `mfa_requerido` TINYINT(1) NOT NULL DEFAULT 0,
  `ultimo_login_en` DATETIME NULL,
  `ultimo_login_ip_hash` VARCHAR(128) NULL,
  `ultimo_login_user_agent_hash` VARCHAR(128) NULL,
  `clave_cambiada_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`),
  UNIQUE KEY `uq_usuarios_uuid_publico` (`uuid_publico`),
  UNIQUE KEY `uq_usuarios_correo` (`correo`),
  KEY `idx_usuarios_tipo_estado` (`tipo_usuario`, `estado`),
  KEY `idx_usuarios_mfa` (`mfa_requerido`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sesiones_acceso` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `panel` VARCHAR(40) NOT NULL,
  `request_id` CHAR(36) NOT NULL,
  `session_id_hash` VARCHAR(128) NOT NULL,
  `ip_hash` VARCHAR(128) NULL,
  `user_agent_hash` VARCHAR(128) NULL,
  `device_hash` VARCHAR(128) NULL,
  `trusted_device_id` BIGINT UNSIGNED NULL,
  `estado` ENUM('activa','cerrada','invalidada','expirada') NOT NULL DEFAULT 'activa',
  `mfa_requerida` TINYINT(1) NOT NULL DEFAULT 0,
  `mfa_verificada_en` DATETIME NULL,
  `iniciada_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ultima_actividad_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cerrada_en` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sesiones_acceso_session_hash` (`session_id_hash`),
  KEY `idx_sesiones_acceso_usuario_panel` (`usuario_id`, `panel`),
  KEY `idx_sesiones_acceso_estado` (`estado`),
  KEY `idx_sesiones_acceso_request` (`request_id`),
  KEY `idx_sesiones_acceso_trusted_device` (`trusted_device_id`),
  CONSTRAINT `fk_sesiones_acceso_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 `recuperacion_acceso` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `panel` VARCHAR(40) NOT NULL DEFAULT 'superadmin',
  `token_hash` VARCHAR(128) NOT NULL,
  `request_id` CHAR(36) NOT NULL,
  `ip_hash` VARCHAR(128) NULL,
  `user_agent_hash` VARCHAR(128) NULL,
  `estado` ENUM('pendiente','usado','expirado','cancelado') NOT NULL DEFAULT 'pendiente',
  `forzado` TINYINT(1) NOT NULL DEFAULT 0,
  `expira_en` DATETIME NOT NULL,
  `usado_en` DATETIME NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_recuperacion_token_hash` (`token_hash`),
  KEY `idx_recuperacion_usuario_estado` (`usuario_id`, `estado`),
  KEY `idx_recuperacion_expira_en` (`expira_en`),
  CONSTRAINT `fk_recuperacion_acceso_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 `remember_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `panel` VARCHAR(40) NOT NULL,
  `selector` VARCHAR(32) NOT NULL,
  `token_hash` VARCHAR(128) NOT NULL,
  `ip_hash` VARCHAR(128) NULL,
  `user_agent_hash` VARCHAR(128) NULL,
  `estado` ENUM('activo','revocado','expirado','usado') NOT NULL DEFAULT 'activo',
  `expira_en` DATETIME NOT NULL,
  `ultimo_uso_en` DATETIME NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_remember_selector` (`selector`),
  KEY `idx_remember_usuario_panel` (`usuario_id`, `panel`),
  KEY `idx_remember_estado` (`estado`),
  CONSTRAINT `fk_remember_tokens_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 `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 `invitaciones_acceso` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` BIGINT UNSIGNED NOT NULL,
  `panel` VARCHAR(40) NOT NULL DEFAULT 'superadmin',
  `correo` VARCHAR(150) NOT NULL,
  `token_hash` VARCHAR(128) NOT NULL,
  `estado` ENUM('pendiente','aceptada','cancelada','expirada') NOT NULL DEFAULT 'pendiente',
  `expira_en` DATETIME NOT NULL,
  `enviado_en` DATETIME NULL,
  `aceptada_en` DATETIME NULL,
  `creado_en` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_invitaciones_token_hash` (`token_hash`),
  KEY `idx_invitaciones_usuario_estado` (`usuario_id`, `estado`),
  CONSTRAINT `fk_invitaciones_acceso_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 `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;

SET FOREIGN_KEY_CHECKS = 1;


-- Bootstrap manual opcional para la primera cuenta Super Admin
-- Use este bloque solo si todavía no existe una cuenta de acceso.
-- Clave temporal del ejemplo: Cambiar-Temporal-2026

INSERT INTO `usuarios` (
  `uuid_publico`, `nombre`, `correo`, `password_hash`, `tipo_usuario`, `estado`, `mfa_requerido`, `creado_en`, `actualizado_en`
) VALUES (
  '11111111-1111-4111-8111-111111111111',
  'Super Admin',
  'superadmin@empresa.com',
  '$2y$12$WIQwtl62xbCT.GQqbghtAu7i4teOdt359bUqnGWsj7XEK99omao2i',
  'superadmin',
  'cambio_clave_obligatorio',
  1,
  NOW(),
  NOW()
);