-- =========================================================================
-- WEBENGINE CMS v1.2.6 - BASE DE DATOS (ESTRUCTURA DE TABLAS Y SEMILLAS)
-- Desarrollado originalmente por Lautaro Angelico
-- Adaptado con Prácticas de Alta Seguridad (Bcrypt, PDO, Sanitización)
-- =========================================================================

-- -------------------------------------------------------------------------
-- CONFIGURACIÓN DEL ENTORNO DE BASE DE DATOS
-- -------------------------------------------------------------------------


-- -------------------------------------------------------------------------
-- 3. TABLA: cms_security_logs (Auditoría Forense y Monitoreo de Amenazas)
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cms_security_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NULL, -- NULL para ataques anónimos (ej: Brute-Force)
  `event_type` VARCHAR(64) NOT NULL, -- Ej: 'CSRF_ATTEMPT', 'FAILED_LOGIN', 'MODULE_TOGGLE'
  `severity` ENUM('info', 'warning', 'critical') DEFAULT 'info',
  `description` TEXT NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `cms_users`(`id`) ON DELETE SET NULL,
  INDEX `idx_event` (`event_type`),
  INDEX `idx_ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Datos de ejemplo para el Auditor de Logs del AdminCP
INSERT INTO `cms_security_logs` (`user_id`, `event_type`, `severity`, `description`, `ip_address`, `user_agent`) VALUES
(NULL, 'FAILED_LOGIN', 'warning', 'Intento de login fallido para la cuenta \'administrator\'', '190.224.120.45', 'Mozilla/5.0...'),
(1, 'MODULE_TOGGLE', 'info', 'Módulo WebShop cambiado a estado: Inactivo', '127.0.0.1', 'Mozilla/5.0...'),
(NULL, 'CSRF_ATTEMPT', 'critical', 'Intento de alteración de formulario sin Token CSRF en /register/submit', '185.112.144.12', 'Mozilla/5.0...');


-- -------------------------------------------------------------------------
-- 4. TABLA: cms_modules (Configuración Modular del Motor de WebEngine)
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cms_modules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(32) NOT NULL UNIQUE,
  `title` VARCHAR(64) NOT NULL,
  `version` VARCHAR(16) NOT NULL DEFAULT '1.2.6',
  `active` TINYINT(1) NOT NULL DEFAULT 1, -- 1: Activo, 0: Inactivo
  `directory` VARCHAR(64) NOT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuración de Módulos iniciales de WebEngine 1.2.6
INSERT INTO `cms_modules` (`id`, `name`, `title`, `version`, `active`, `directory`) VALUES
(1, 'ranking', 'Ranking de Personajes', '1.2.6', 1, 'ranking'),
(2, 'register', 'Registro Seguro', '1.1.0', 1, 'register'),
(3, 'news', 'Gestor de Noticias', '1.0.5', 1, 'news'),
(4, 'donation', 'Módulo de Créditos / WebShop', '2.0.1', 0, 'donation');


-- -------------------------------------------------------------------------
-- 5. TABLA DE JUEGO: Character (Simulación de la DB de MuOnline)
-- -------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `Character` (
  `Name` VARCHAR(10) NOT NULL PRIMARY KEY,
  `Class` TINYINT NOT NULL, -- 0: DW, 1: SM, 2: GM | 16: DK, 17: BK, 18: BM | 32: FE, 33: ME, 34: HE | 48: MG, 49: DM | 64: DL, 65: LE
  `cLevel` INT NOT NULL DEFAULT 1,
  `Resets` INT NOT NULL DEFAULT 0,
  `Gremio` VARCHAR(32) NULL,
  `Status` ENUM('Online', 'Offline') DEFAULT 'Offline',
  `ResetsWeekly` INT NOT NULL DEFAULT 0,
  INDEX `idx_resets_level` (`Resets` DESC, `cLevel` DESC),
  INDEX `idx_class` (`Class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Semillas de Personajes para Popular el Ranking
INSERT INTO `Character` (`Name`, `Class`, `cLevel`, `Resets`, `Gremio`, `Status`) VALUES
('Odin_DL', 64, 400, 198, 'Asgard', 'Online'),
('ConanTheBK', 16, 400, 185, 'Valhalla', 'Offline'),
('Zeus_SM', 0, 400, 152, 'Olympus', 'Online'),
('LegolasGirl', 32, 390, 115, 'Lothlorien', 'Offline'),
('Thor_BK', 17, 380, 95, 'Asgard', 'Offline'),
('Elena_ME', 33, 400, 92, 'Olympus', 'Online'),
('Gandalf_SM', 1, 350, 80, 'Valhalla', 'Offline'),
('Kratos_BK', 18, 400, 75, 'Olympus', 'Online');

-- =========================================================================
-- INSTALACIÓN COMPLETADA EXITOSAMENTE
-- =========================================================================
