-- ============================================================
-- Sistema de Análisis de Apuestas Deportivas
-- Esquema de Base de Datos (MySQL 5.7+/8.0)
-- ============================================================

CREATE DATABASE IF NOT EXISTS hackruzc_apuesta
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hackruzc_apuesta;

-- ------------------------------------------------------------
-- Partidos importados (desde los dos formatos de texto)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS matches (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  sport             VARCHAR(50)  NOT NULL DEFAULT 'Fútbol',
  team_home         VARCHAR(150) NOT NULL,
  team_away         VARCHAR(150) NOT NULL,
  organization      VARCHAR(200) DEFAULT NULL,      -- liga / organización
  match_datetime    DATETIME     NOT NULL,
  market            VARCHAR(150) NOT NULL,          -- "Hándicap de Set", "Gana ADT / Empate Anula", etc.
  selection_label   VARCHAR(200) NOT NULL,          -- "Cameron Norrie (-2.5)"
  odds_current       DECIMAL(6,2) NOT NULL,
  raw_source_text    TEXT,
  created_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Historial de cuotas (antes / durante / después del ticket)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS odds_history (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  match_id     INT NOT NULL,
  odds_value   DECIMAL(6,2) NOT NULL,
  event_type   ENUM('captura_inicial','actualizacion','antes_ticket','despues_ticket','manual') NOT NULL,
  ticket_id    INT DEFAULT NULL,
  recorded_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Proveedores de IA administrables (Gemini, Grok, Claude, DeepSeek, Kimi, etc.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_providers (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(100) NOT NULL,        -- Nombre visible: "Gemini", "Grok"...
  slug           VARCHAR(50)  NOT NULL UNIQUE,  -- gemini, grok, claude, deepseek, kimi...
  api_endpoint   VARCHAR(255) NOT NULL,
  api_key        TEXT NOT NULL,                 -- puede haber varias keys separadas por coma
  model          VARCHAR(150) DEFAULT NULL,
  active         TINYINT(1) NOT NULL DEFAULT 1,
  is_final_analyst TINYINT(1) NOT NULL DEFAULT 0, -- IA seleccionada para dar el resultado final
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Análisis individual de cada partido, por cada IA
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_analysis (
  id                     INT AUTO_INCREMENT PRIMARY KEY,
  match_id               INT NOT NULL,
  ai_provider_id         INT NOT NULL,
  implied_probability    DECIMAL(6,3) NOT NULL, -- 1/cuota
  real_probability       DECIMAL(6,3) NOT NULL, -- estimada por la IA
  projected_probability  DECIMAL(6,3) NOT NULL, -- proyección ponderada
  ev_percent             DECIMAL(7,3) NOT NULL, -- valor esperado %
  rationale              TEXT,                   -- justificación breve de la IA
  raw_response           TEXT,                   -- respuesta cruda para auditoría
  created_at             DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
  FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Jugadas múltiples (combinadas, máximo 2 partidos)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS parlays (
  id                     INT AUTO_INCREMENT PRIMARY KEY,
  name                   VARCHAR(255) NOT NULL,
  ai_provider_id         INT DEFAULT NULL,   -- IA que generó/validó la combinada (NULL = consolidado)
  combined_odds          DECIMAL(8,2) NOT NULL,
  implied_probability    DECIMAL(6,3) NOT NULL,
  real_probability       DECIMAL(6,3) NOT NULL,
  projected_probability  DECIMAL(6,3) NOT NULL,
  ev_percent             DECIMAL(7,3) NOT NULL,
  created_at             DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS parlay_matches (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  parlay_id  INT NOT NULL,
  match_id   INT NOT NULL,
  FOREIGN KEY (parlay_id) REFERENCES parlays(id) ON DELETE CASCADE,
  FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Tickets finales armados por el usuario (simples o combinados)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tickets (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  type             ENUM('simple','multiple') NOT NULL,
  match_id         INT DEFAULT NULL,
  parlay_id        INT DEFAULT NULL,
  stake_amount     DECIMAL(10,2) NOT NULL,
  odds_at_ticket   DECIMAL(8,2) NOT NULL,   -- cuota congelada al armar el ticket (editable)
  potential_return DECIMAL(10,2) NOT NULL,  -- stake * odds_at_ticket
  status           ENUM('pendiente','ganado','perdido','anulado','push') NOT NULL DEFAULT 'pendiente',
  profit_loss      DECIMAL(10,2) DEFAULT NULL,
  final_result_note TEXT,
  checked_by_ai_provider_id INT DEFAULT NULL,
  settled_at       DATETIME DEFAULT NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL,
  FOREIGN KEY (parlay_id) REFERENCES parlays(id) ON DELETE SET NULL,
  FOREIGN KEY (checked_by_ai_provider_id) REFERENCES ai_providers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Configuración general del sistema (pesos del modelo, etc.)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
  setting_key   VARCHAR(100) PRIMARY KEY,
  setting_value TEXT
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
  ('projection_weight_real', '0.7'),      -- peso de la prob. real en la proyectada
  ('projection_weight_implied', '0.3'),   -- peso de la prob. implícita en la proyectada
  ('threshold_green', '68'),              -- % probabilidad real -> verde
  ('threshold_yellow', '50'),             -- % probabilidad real -> amarillo
  ('final_analyst_provider_id', '')       -- IA elegida para el análisis final consolidado
ON DUPLICATE KEY UPDATE setting_key = setting_key;