-- =========================================================================
-- 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
-- -------------------------------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- Crear Base de Datos (Si es necesario, recuerde adaptar las credenciales en config/config.php)
-- CREATE DATABASE IF NOT EXISTS `mu_online_cms` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `mu_online_cms`;

-- -------------------------------------------------------------------------
-- 1. TABLA: cms_users (Cuentas Web del CMS desvinculadas de contraseñas de juego)
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_users`;
CREATE TABLE IF NOT EXISTS `cms_users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(32) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL, -- Hasheado usando PASSWORD_BCRYPT (cost: 12)
  `email` VARCHAR(128) NOT NULL UNIQUE,
  `role` ENUM('user', 'moderator', 'admin', 'super_admin') DEFAULT 'user',
  `status` ENUM('pending', 'active', 'banned') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_username` (`username`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Semilla: Administrador Inicial (Contraseña: admin123)
-- Generado con PASSWORD_BCRYPT y costo de 12
INSERT INTO `cms_users` (`id`, `username`, `password_hash`, `email`, `role`, `status`) VALUES
(1, 'admin', '$2y$12$6K8m1hZoxwV7mK0yqG2eOeQ1R3yL7d9N.gQzCg5Ie9xH0R8tB9Y6e', 'admin@muonline.com', 'super_admin', 'active');


-- -------------------------------------------------------------------------
-- 2. TABLA: cms_user_sessions (Gestión de Sesiones Seguras y Anti-Hijacking)
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_user_sessions`;
CREATE TABLE IF NOT EXISTS `cms_user_sessions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `session_token` VARCHAR(64) NOT NULL UNIQUE,
  `ip_address` VARCHAR(45) NOT NULL, -- Compatible con IPv4 e IPv6
  `user_agent` TEXT NOT NULL,
  `last_activity` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `cms_users`(`id`) ON DELETE CASCADE,
  INDEX `idx_token` (`session_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -------------------------------------------------------------------------
-- 3. TABLA: cms_security_logs (Auditoría Forense y Monitoreo de Amenazas)
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_security_logs`;
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)
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_modules`;
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)
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `Character`;
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');

SET FOREIGN_KEY_CHECKS = 1;
-- =========================================================================
-- INSTALACIÓN COMPLETADA EXITOSAMENTE
-- =========================================================================