-- =========================================================================
-- WEBENGINE CMS v1.2.6 - BASE DE DATOS SQLITE (ESTRUCTURA Y SEMILLAS)
-- Adaptado para Ejecución Portable y Segura
-- =========================================================================

PRAGMA foreign_keys = OFF;

-- -------------------------------------------------------------------------
-- 1. TABLA: cms_users
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_users`;
CREATE TABLE IF NOT EXISTS `cms_users` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `username` VARCHAR(32) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `email` VARCHAR(128) NOT NULL UNIQUE,
  `role` VARCHAR(16) DEFAULT 'user',
  `status` VARCHAR(16) DEFAULT 'active',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Semilla: Administrador Inicial (Contraseña: admin123)
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
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_user_sessions`;
CREATE TABLE IF NOT EXISTS `cms_user_sessions` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER NOT NULL,
  `session_token` VARCHAR(64) NOT NULL UNIQUE,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT NOT NULL,
  `last_activity` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `cms_users`(`id`) ON DELETE CASCADE
);

-- -------------------------------------------------------------------------
-- 3. TABLA: cms_security_logs
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_security_logs`;
CREATE TABLE IF NOT EXISTS `cms_security_logs` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `severity` VARCHAR(16) DEFAULT 'info',
  `description` TEXT NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` TEXT NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `cms_users`(`id`) ON DELETE SET NULL
);

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
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_modules`;
CREATE TABLE IF NOT EXISTS `cms_modules` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` VARCHAR(32) NOT NULL UNIQUE,
  `title` VARCHAR(64) NOT NULL,
  `version` VARCHAR(16) NOT NULL DEFAULT '1.2.6',
  `active` TINYINT NOT NULL DEFAULT 1,
  `directory` VARCHAR(64) NOT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

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
-- -------------------------------------------------------------------------
DROP TABLE IF EXISTS `Character`;
CREATE TABLE IF NOT EXISTS `Character` (
  `Name` VARCHAR(10) NOT NULL PRIMARY KEY,
  `Class` TINYINT NOT NULL,
  `cLevel` INT NOT NULL DEFAULT 1,
  `Resets` INT NOT NULL DEFAULT 0,
  `Gremio` VARCHAR(32) NULL,
  `Status` VARCHAR(16) DEFAULT 'Offline',
  `ResetsWeekly` INT NOT NULL DEFAULT 0
);

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');

PRAGMA foreign_keys = ON;