sistema_para_juego/shared/database/schema.sql

-- =====================================================
-- SCHEMA DE BASE DE DATOS COMPLETO
-- Sistema de Administración de Bots Discord/Telegram
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- TABLAS COMPARTIDAS
-- =====================================================

-- Tabla de usuarios del panel
CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `nombre_completo` VARCHAR(100),
  `rol_id` INT UNSIGNED,
  `idioma_id` INT UNSIGNED DEFAULT 1,
  `activo` TINYINT(1) DEFAULT 1,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ultimo_acceso` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_username` (`username`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(50) NOT NULL UNIQUE,
  `descripcion` VARCHAR(255),
  `nivel` INT DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar roles por defecto
INSERT INTO `roles` (`nombre`, `descripcion`, `nivel`) VALUES
('Admin', 'Administrador con acceso completo', 100),
('Editor', 'Editor con permisos limitados', 50)
ON DUPLICATE KEY UPDATE `descripcion`=VALUES(`descripcion`);

-- Tabla de permisos
CREATE TABLE IF NOT EXISTS `permisos` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(50) NOT NULL UNIQUE,
  `descripcion` VARCHAR(255),
  `modulo` ENUM('discord', 'telegram', 'global') DEFAULT 'global',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar permisos por defecto
INSERT INTO `permisos` (`nombre`, `descripcion`, `modulo`) VALUES
('crear_mensajes', 'Crear y enviar mensajes', 'global'),
('editar_plantillas', 'Editar plantillas de mensajes', 'global'),
('gestionar_usuarios', 'Gestionar destinatarios', 'global'),
('ver_logs', 'Ver logs del sistema', 'global'),
('gestionar_roles', 'Gestionar roles y permisos', 'global')
ON DUPLICATE KEY UPDATE `descripcion`=VALUES(`descripcion`);

-- Tabla relacional usuarios-permisos
CREATE TABLE IF NOT EXISTS `usuarios_permisos` (
  `usuario_id` INT UNSIGNED NOT NULL,
  `permiso_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`usuario_id`, `permiso_id`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permiso_id`) REFERENCES `permisos`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de idiomas
CREATE TABLE IF NOT EXISTS `idiomas` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `codigo` VARCHAR(10) NOT NULL UNIQUE,
  `nombre` VARCHAR(50) NOT NULL,
  `nombre_nativo` VARCHAR(50),
  `activo` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar idiomas por defecto
INSERT INTO `idiomas` (`codigo`, `nombre`, `nombre_nativo`, `activo`) VALUES
('es', 'Español', 'Español', 1),
('en', 'Inglés', 'English', 1),
('fr', 'Francés', 'Français', 0),
('de', 'Alemán', 'Deutsch', 0),
('pt', 'Portugués', 'Português', 0)
ON DUPLICATE KEY UPDATE `nombre`=VALUES(`nombre`);

-- Tabla de galería (compartida)
CREATE TABLE IF NOT EXISTS `gallery` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(255) NOT NULL,
  `nombre_original` VARCHAR(255) NOT NULL,
  `ruta` VARCHAR(500) NOT NULL,
  `ruta_thumbnail` VARCHAR(500),
  `hash_md5` VARCHAR(32),
  `tipo_mime` VARCHAR(100),
  `tamano` INT UNSIGNED,
  `ancho` INT UNSIGNED,
  `alto` INT UNSIGNED,
  `usuario_id` INT UNSIGNED,
  `fecha_subida` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_hash` (`hash_md5`),
  INDEX `idx_usuario` (`usuario_id`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLAS DE DISCORD
-- =====================================================

-- Destinatarios de Discord
CREATE TABLE IF NOT EXISTS `destinatarios_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `discord_id` VARCHAR(50) NOT NULL UNIQUE,
  `tipo` ENUM('usuario', 'canal', 'grupo', 'rol') DEFAULT 'usuario',
  `nombre` VARCHAR(255),
  `username` VARCHAR(100),
  `discriminador` VARCHAR(10),
  `avatar_url` VARCHAR(500),
  `idioma_detectado` VARCHAR(10),
  `chat_mode` VARCHAR(10) DEFAULT 'bot',
  `activo` TINYINT(1) DEFAULT 1,
  `fecha_registro` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ultima_interaccion` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_discord_id` (`discord_id`),
  INDEX `idx_tipo` (`tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plantillas de Discord
CREATE TABLE IF NOT EXISTS `plantillas_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100) NOT NULL,
  `contenido` TEXT NOT NULL,
  `usuario_id` INT UNSIGNED,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `fecha_modificacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mensajes de Discord
CREATE TABLE IF NOT EXISTS `mensajes_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contenido` TEXT NOT NULL,
  `tipo_envio` ENUM('inmediato', 'programado', 'recurrente') DEFAULT 'inmediato',
  `fecha_envio` TIMESTAMP NULL,
  `canal_id` VARCHAR(50),
  `mensaje_discord_id` VARCHAR(50),
  `usuario_id` INT UNSIGNED,
  `plantilla_id` INT UNSIGNED NULL,
  `estado` ENUM('pendiente', 'enviado', 'fallido', 'deshabilitado') DEFAULT 'pendiente',
  `error` TEXT NULL,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_tipo_envio` (`tipo_envio`),
  INDEX `idx_estado` (`estado`),
  INDEX `idx_fecha_envio` (`fecha_envio`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`plantilla_id`) REFERENCES `plantillas_discord`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Destinatarios de mensajes Discord (relación muchos a muchos)
CREATE TABLE IF NOT EXISTS `mensajes_discord_destinatarios` (
  `mensaje_id` INT UNSIGNED NOT NULL,
  `destinatario_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`mensaje_id`, `destinatario_id`),
  FOREIGN KEY (`mensaje_id`) REFERENCES `mensajes_discord`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`destinatario_id`) REFERENCES `destinatarios_discord`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mensajes recurrentes de Discord
CREATE TABLE IF NOT EXISTS `recurrentes_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mensaje_id` INT UNSIGNED NOT NULL,
  `frecuencia` ENUM('diario', 'semanal', 'mensual') DEFAULT 'diario',
  `hora_envio` TIME DEFAULT '09:00:00',
  `dia_semana` TINYINT NULL COMMENT '1=Lunes, 7=Domingo',
  `dia_mes` TINYINT NULL COMMENT '1-31',
  `activo` TINYINT(1) DEFAULT 1,
  `ultimo_envio` TIMESTAMP NULL,
  `proximo_envio` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`mensaje_id`) REFERENCES `mensajes_discord`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logs de Discord
CREATE TABLE IF NOT EXISTS `logs_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tipo` ENUM('mensaje', 'comando', 'error', 'interaccion', 'usuario') DEFAULT 'mensaje',
  `nivel` ENUM('info', 'warning', 'error') DEFAULT 'info',
  `descripcion` TEXT,
  `datos_json` JSON NULL,
  `usuario_id` INT UNSIGNED NULL,
  `destinatario_id` INT UNSIGNED NULL,
  `fecha` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_tipo` (`tipo`),
  INDEX `idx_nivel` (`nivel`),
  INDEX `idx_fecha` (`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comandos de Discord
CREATE TABLE IF NOT EXISTS `comandos_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `comando` VARCHAR(50) NOT NULL,
  `descripcion` VARCHAR(255),
  `destinatario_id` INT UNSIGNED,
  `plantilla_id` INT UNSIGNED NULL,
  `veces_usado` INT UNSIGNED DEFAULT 0,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ultimo_uso` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_comando` (`comando`),
  FOREIGN KEY (`destinatario_id`) REFERENCES `destinatarios_discord`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plantilla_id`) REFERENCES `plantillas_discord`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuración de bienvenida Discord
CREATE TABLE IF NOT EXISTS `bienvenida_discord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `canal_id` VARCHAR(50) NOT NULL,
  `texto` TEXT,
  `imagen_id` INT UNSIGNED NULL,
  `idiomas_habilitados` JSON NULL,
  `registrar_usuario` TINYINT(1) DEFAULT 1,
  `activo` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`imagen_id`) REFERENCES `gallery`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLAS DE TELEGRAM
-- =====================================================

-- Destinatarios de Telegram
CREATE TABLE IF NOT EXISTS `destinatarios_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` VARCHAR(50) NOT NULL UNIQUE,
  `tipo` ENUM('usuario', 'canal', 'grupo') DEFAULT 'usuario',
  `nombre` VARCHAR(255),
  `username` VARCHAR(100),
  `avatar_url` VARCHAR(500),
  `idioma_detectado` VARCHAR(10),
  `activo` TINYINT(1) DEFAULT 1,
  `fecha_registro` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ultima_interaccion` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_telegram_id` (`telegram_id`),
  INDEX `idx_tipo` (`tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plantillas de Telegram
CREATE TABLE IF NOT EXISTS `plantillas_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100) NOT NULL,
  `comando` VARCHAR(50) UNIQUE,
  `contenido` TEXT NOT NULL,
  `usuario_id` INT UNSIGNED,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `fecha_modificacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_comando` (`comando`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mensajes de Telegram
CREATE TABLE IF NOT EXISTS `mensajes_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contenido` TEXT NOT NULL,
  `tipo_envio` ENUM('inmediato', 'programado', 'recurrente') DEFAULT 'inmediato',
  `fecha_envio` TIMESTAMP NULL,
  `chat_id` VARCHAR(50),
  `mensaje_telegram_id` VARCHAR(50),
  `usuario_id` INT UNSIGNED,
  `plantilla_id` INT UNSIGNED NULL,
  `estado` ENUM('pendiente', 'enviado', 'fallido', 'deshabilitado') DEFAULT 'pendiente',
  `error` TEXT NULL,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_tipo_envio` (`tipo_envio`),
  INDEX `idx_estado` (`estado`),
  INDEX `idx_fecha_envio` (`fecha_envio`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`plantilla_id`) REFERENCES `plantillas_telegram`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Destinatarios de mensajes Telegram (relación muchos a muchos)
CREATE TABLE IF NOT EXISTS `mensajes_telegram_destinatarios` (
  `mensaje_id` INT UNSIGNED NOT NULL,
  `destinatario_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`mensaje_id`, `destinatario_id`),
  FOREIGN KEY (`mensaje_id`) REFERENCES `mensajes_telegram`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`destinatario_id`) REFERENCES `destinatarios_telegram`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mensajes recurrentes de Telegram
CREATE TABLE IF NOT EXISTS `recurrentes_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mensaje_id` INT UNSIGNED NOT NULL,
  `frecuencia` ENUM('diario', 'semanal', 'mensual') DEFAULT 'diario',
  `hora_envio` TIME DEFAULT '09:00:00',
  `dia_semana` TINYINT NULL,
  `dia_mes` TINYINT NULL,
  `activo` TINYINT(1) DEFAULT 1,
  `ultimo_envio` TIMESTAMP NULL,
  `proximo_envio` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`mensaje_id`) REFERENCES `mensajes_telegram`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logs de Telegram
CREATE TABLE IF NOT EXISTS `logs_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tipo` ENUM('mensaje', 'comando', 'error', 'interaccion', 'usuario') DEFAULT 'mensaje',
  `nivel` ENUM('info', 'warning', 'error') DEFAULT 'info',
  `descripcion` TEXT,
  `datos_json` JSON NULL,
  `usuario_id` INT UNSIGNED NULL,
  `destinatario_id` INT UNSIGNED NULL,
  `fecha` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_tipo` (`tipo`),
  INDEX `idx_nivel` (`nivel`),
  INDEX `idx_fecha` (`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Comandos de Telegram
CREATE TABLE IF NOT EXISTS `comandos_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `comando` VARCHAR(50) NOT NULL,
  `descripcion` VARCHAR(255),
  `destinatario_id` INT UNSIGNED,
  `plantilla_id` INT UNSIGNED NULL,
  `veces_usado` INT UNSIGNED DEFAULT 0,
  `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ultimo_uso` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_comando` (`comando`),
  FOREIGN KEY (`destinatario_id`) REFERENCES `destinatarios_telegram`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plantilla_id`) REFERENCES `plantillas_telegram`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuración de bienvenida Telegram
CREATE TABLE IF NOT EXISTS `bienvenida_telegram` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id` VARCHAR(50) NOT NULL,
  `texto` TEXT,
  `imagen_id` INT UNSIGNED NULL,
  `idiomas_habilitados` JSON NULL,
  `registrar_usuario` TINYINT(1) DEFAULT 1,
  `activo` TINYINT(1) DEFAULT 1,
  `boton_unirse_texto` VARCHAR(100) DEFAULT 'Únete al grupo',
  `boton_unirse_url` VARCHAR(500),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`imagen_id`) REFERENCES `gallery`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;