-- ============================================================
-- SISTEMA DE APUESTAS DEPORTIVAS - PING PONG
-- Script SQL completo - MySQL 8.0+
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

CREATE DATABASE IF NOT EXISTS `hackruzc_pinpon`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `hackruzc_pinpon`;

-- ------------------------------------------------------------
-- TABLA: usuarios
-- ------------------------------------------------------------
CREATE TABLE `usuarios` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nombre`        VARCHAR(100) NOT NULL,
  `email`         VARCHAR(180) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `rol`           ENUM('admin','usuario') NOT NULL DEFAULT 'usuario',
  `activo`        TINYINT(1) NOT NULL DEFAULT 1,
  `token_reset`   VARCHAR(100) DEFAULT NULL,
  `creado_en`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ultimo_login`  DATETIME DEFAULT NULL
) ENGINE=InnoDB;

-- Administrador por defecto (password: Admin1234!)
INSERT INTO `usuarios` (`nombre`, `email`, `password_hash`, `rol`) VALUES
('Administrador', 'admin@pingpong.local',
 '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- bcrypt de "Admin1234!"
 'admin');

-- ------------------------------------------------------------
-- TABLA: apis
-- Almacena todas las claves externas: deportivas e IA
-- ------------------------------------------------------------
CREATE TABLE `apis` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nombre`           VARCHAR(80)  NOT NULL,
  `tipo`             ENUM('deportiva','gemini','groq','otra') NOT NULL,
  `api_key`          VARCHAR(512) NOT NULL,
  `endpoint_base`    VARCHAR(300) DEFAULT NULL,
  `cuota_diaria`     INT UNSIGNED DEFAULT NULL  COMMENT 'llamadas/día permitidas',
  `cuota_mensual`    INT UNSIGNED DEFAULT NULL  COMMENT 'llamadas/mes permitidas',
  `consumo_hoy`      INT UNSIGNED NOT NULL DEFAULT 0,
  `consumo_mes`      INT UNSIGNED NOT NULL DEFAULT 0,
  `fecha_renovacion` DATE DEFAULT NULL,
  `estado`           ENUM('activa','inactiva','agotada') NOT NULL DEFAULT 'activa',
  `prioridad`        TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'menor = más prioritaria',
  `notas`            TEXT DEFAULT NULL,
  `creado_en`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: api_log
-- Registro de cada llamada a API externa
-- ------------------------------------------------------------
CREATE TABLE `api_log` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `id_api`       INT UNSIGNED NOT NULL,
  `endpoint`     VARCHAR(500) NOT NULL,
  `http_status`  SMALLINT UNSIGNED DEFAULT NULL,
  `duracion_ms`  INT UNSIGNED DEFAULT NULL,
  `error`        TEXT DEFAULT NULL,
  `creado_en`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`id_api`) REFERENCES `apis`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: partidos
-- Partidos de ping pong del día importados de la API deportiva
-- ------------------------------------------------------------
CREATE TABLE `partidos` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `id_externo`        VARCHAR(100) NOT NULL UNIQUE COMMENT 'ID dado por la API deportiva',
  `id_api`            INT UNSIGNED NOT NULL,
  `fecha`             DATE NOT NULL,
  `hora`              TIME DEFAULT NULL,
  `torneo`            VARCHAR(200) DEFAULT NULL,
  `jugador_local`     VARCHAR(150) NOT NULL,
  `jugador_visitante` VARCHAR(150) NOT NULL,
  `marcador_local`    TINYINT UNSIGNED DEFAULT NULL,
  `marcador_visitante`TINYINT UNSIGNED DEFAULT NULL,
  `estado`            ENUM('programado','en_curso','finalizado','cancelado') NOT NULL DEFAULT 'programado',
  `cuotas_iniciales`  JSON DEFAULT NULL  COMMENT '{"local":1.80,"visitante":2.10,"over":1.95}',
  `cuotas_actuales`   JSON DEFAULT NULL,
  `stats_basicas`     JSON DEFAULT NULL  COMMENT 'datos crudos de la API deportiva',
  `json_raw`          LONGTEXT DEFAULT NULL COMMENT 'respuesta original completa de la API',
  `analizado`         TINYINT(1) NOT NULL DEFAULT 0,
  `resultado_final`   VARCHAR(20) DEFAULT NULL COMMENT 'local | visitante | empate',
  `creado_en`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`id_api`) REFERENCES `apis`(`id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: prompts_sistema
-- Prompts configurables para cada IA
-- ------------------------------------------------------------
CREATE TABLE `prompts_sistema` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nombre`           VARCHAR(80) NOT NULL UNIQUE COMMENT 'ej: analisis_gemini, analisis_groq',
  `descripcion`      VARCHAR(300) DEFAULT NULL,
  `prompt_texto`     LONGTEXT NOT NULL,
  `variables`        JSON DEFAULT NULL COMMENT 'lista de variables disponibles para el prompt',
  `activo`           TINYINT(1) NOT NULL DEFAULT 1,
  `modificado_por`   INT UNSIGNED DEFAULT NULL,
  `creado_en`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_en`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`modificado_por`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Prompts por defecto
INSERT INTO `prompts_sistema` (`nombre`, `descripcion`, `prompt_texto`, `variables`) VALUES
('analisis_gemini',
 'Prompt para análisis con Google Gemini',
 'Eres un analista experto en ping pong (tenis de mesa) con más de 10 años de experiencia.\n\nAnaliza el siguiente partido y proporciona una predicción detallada:\n\n**PARTIDO:** {{jugador_local}} vs {{jugador_visitante}}\n**TORNEO:** {{torneo}}\n**FECHA:** {{fecha}} {{hora}}\n\n**ESTADÍSTICAS Y DATOS:**\n{{stats_json}}\n\n**CUOTAS ACTUALES:**\n{{cuotas_json}}\n\nProporciona tu respuesta ÚNICAMENTE en formato JSON con esta estructura exacta:\n{\n  "ganador_predicho": "nombre del jugador",\n  "probabilidad_local": 0-100,\n  "probabilidad_visitante": 0-100,\n  "marcador_sugerido": "3-1",\n  "total_puntos_estimado": numero,\n  "over_under_linea": 80.5,\n  "prediccion_over_under": "over|under",\n  "confianza": 0-100,\n  "handicap_sugerido": "-1.5 local",\n  "factores_clave": ["factor1", "factor2"],\n  "explicacion": "análisis detallado en 3-5 oraciones"\n}',
 '["jugador_local","jugador_visitante","torneo","fecha","hora","stats_json","cuotas_json"]'
),
('analisis_groq',
 'Prompt para análisis con Groq/Llama',
 'Eres un sistema experto de análisis predictivo de ping pong (tenis de mesa).\n\nPartido a analizar:\n- Local: {{jugador_local}}\n- Visitante: {{jugador_visitante}}\n- Torneo: {{torneo}}\n- Fecha/hora: {{fecha}} {{hora}}\n\nDatos estadísticos disponibles:\n{{stats_json}}\n\nCuotas de apuesta:\n{{cuotas_json}}\n\nResponde SOLO con JSON válido, sin texto adicional:\n{\n  "ganador_predicho": "nombre",\n  "probabilidad_local": numero,\n  "probabilidad_visitante": numero,\n  "marcador_sugerido": "X-Y",\n  "total_puntos_estimado": numero,\n  "prediccion_over_under": "over|under",\n  "confianza": numero,\n  "factores_decisivos": ["f1","f2","f3"],\n  "valor_apuesta": "descripcion de la apuesta con mejor valor",\n  "explicacion": "justificacion del analisis"\n}',
 '["jugador_local","jugador_visitante","torneo","fecha","hora","stats_json","cuotas_json"]'
);

-- ------------------------------------------------------------
-- TABLA: analisis_gemini
-- ------------------------------------------------------------
CREATE TABLE `analisis_gemini` (
  `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `id_partido`           INT UNSIGNED NOT NULL,
  `ganador_predicho`     VARCHAR(150) DEFAULT NULL,
  `probabilidad_local`   TINYINT UNSIGNED DEFAULT NULL,
  `probabilidad_visitante` TINYINT UNSIGNED DEFAULT NULL,
  `marcador_sugerido`    VARCHAR(20) DEFAULT NULL,
  `total_puntos_estimado` SMALLINT UNSIGNED DEFAULT NULL,
  `prediccion_over_under` ENUM('over','under') DEFAULT NULL,
  `confianza`            TINYINT UNSIGNED DEFAULT NULL,
  `explicacion`          TEXT DEFAULT NULL,
  `json_respuesta`       JSON DEFAULT NULL,
  `tokens_usados`        INT UNSIGNED DEFAULT NULL,
  `id_api_usada`         INT UNSIGNED DEFAULT NULL,
  `fecha_analisis`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`id_partido`) REFERENCES `partidos`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`id_api_usada`) REFERENCES `apis`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: analisis_groq
-- ------------------------------------------------------------
CREATE TABLE `analisis_groq` (
  `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `id_partido`           INT UNSIGNED NOT NULL,
  `ganador_predicho`     VARCHAR(150) DEFAULT NULL,
  `probabilidad_local`   TINYINT UNSIGNED DEFAULT NULL,
  `probabilidad_visitante` TINYINT UNSIGNED DEFAULT NULL,
  `marcador_sugerido`    VARCHAR(20) DEFAULT NULL,
  `total_puntos_estimado` SMALLINT UNSIGNED DEFAULT NULL,
  `prediccion_over_under` ENUM('over','under') DEFAULT NULL,
  `confianza`            TINYINT UNSIGNED DEFAULT NULL,
  `explicacion`          TEXT DEFAULT NULL,
  `json_respuesta`       JSON DEFAULT NULL,
  `tokens_usados`        INT UNSIGNED DEFAULT NULL,
  `id_api_usada`         INT UNSIGNED DEFAULT NULL,
  `fecha_analisis`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`id_partido`) REFERENCES `partidos`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`id_api_usada`) REFERENCES `apis`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: consenso_analisis
-- Resultado combinado de Gemini + Groq + cuotas
-- ------------------------------------------------------------
CREATE TABLE `consenso_analisis` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `id_partido`        INT UNSIGNED NOT NULL,
  `apuesta_sugerida`  VARCHAR(200) NOT NULL COMMENT 'ej: Gana jugador X, Over 80.5',
  `tipo_apuesta`      ENUM('ganador','over','under','handicap','marcador') NOT NULL,
  `stake_sugerido`    TINYINT UNSIGNED NOT NULL COMMENT '1-10',
  `roi_esperado`      DECIMAL(6,2) DEFAULT NULL COMMENT 'porcentaje',
  `confianza_consenso` TINYINT UNSIGNED DEFAULT NULL,
  `justificacion`     TEXT DEFAULT NULL,
  `json_completo`     JSON DEFAULT NULL,
  `creado_en`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`id_partido`) REFERENCES `partidos`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: apuestas_realizadas
-- ------------------------------------------------------------
CREATE TABLE `apuestas_realizadas` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `id_usuario`    INT UNSIGNED NOT NULL,
  `id_partido`    INT UNSIGNED NOT NULL,
  `tipo_apuesta`  ENUM('ganador','over','under','handicap','marcador','otro') NOT NULL,
  `seleccion`     VARCHAR(200) NOT NULL COMMENT 'descripción de la selección',
  `cuota`         DECIMAL(6,2) NOT NULL,
  `stake`         DECIMAL(10,2) NOT NULL,
  `fecha_apuesta` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resultado`     ENUM('pendiente','ganada','perdida','nula') NOT NULL DEFAULT 'pendiente',
  `ganancia_neta` DECIMAL(10,2) DEFAULT NULL,
  `notas`         VARCHAR(500) DEFAULT NULL,
  `eliminado`     TINYINT(1) NOT NULL DEFAULT 0,
  `eliminado_en`  DATETIME DEFAULT NULL,
  `eliminado_por` INT UNSIGNED DEFAULT NULL,
  FOREIGN KEY (`id_usuario`)    REFERENCES `usuarios`(`id`),
  FOREIGN KEY (`id_partido`)    REFERENCES `partidos`(`id`),
  FOREIGN KEY (`eliminado_por`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: log_eliminaciones
-- Auditoría de eliminaciones de apuestas
-- ------------------------------------------------------------
CREATE TABLE `log_eliminaciones` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tabla`         VARCHAR(60) NOT NULL,
  `id_registro`   INT UNSIGNED NOT NULL,
  `datos_previos` JSON DEFAULT NULL,
  `motivo`        VARCHAR(300) DEFAULT NULL,
  `eliminado_por` INT UNSIGNED NOT NULL,
  `creado_en`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`eliminado_por`) REFERENCES `usuarios`(`id`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLA: error_log
-- Registro de errores del sistema
-- ------------------------------------------------------------
CREATE TABLE `error_log` (
  `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nivel`      ENUM('info','warning','error','critical') NOT NULL DEFAULT 'error',
  `contexto`   VARCHAR(100) DEFAULT NULL,
  `mensaje`    TEXT NOT NULL,
  `traza`      TEXT DEFAULT NULL,
  `creado_en`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET foreign_key_checks = 1;