-- =====================================================================
-- Quiniela MB — Esquema de base de datos (SQLite)
-- =====================================================================
-- Idempotente: usa CREATE TABLE IF NOT EXISTS para poder re-ejecutar.

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------
-- Usuarios (jugadores y administradores)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  nombre        TEXT    NOT NULL,
  email         TEXT    NOT NULL UNIQUE,
  password_hash TEXT    NOT NULL,
  rol           TEXT    NOT NULL DEFAULT 'jugador' CHECK (rol IN ('admin', 'jugador')),
  activo        INTEGER NOT NULL DEFAULT 1,
  creado_en     TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------
-- Torneos / eventos
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS torneos (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  nombre              TEXT    NOT NULL,
  precio_quiniela     REAL    NOT NULL DEFAULT 5,
  moneda              TEXT    NOT NULL DEFAULT 'USD',
  pts_marcador_exacto INTEGER NOT NULL DEFAULT 3,
  pts_ganador         INTEGER NOT NULL DEFAULT 1,
  comision_pct        REAL    NOT NULL DEFAULT 0,
  estado              TEXT    NOT NULL DEFAULT 'config'
                        CHECK (estado IN ('config', 'abierto', 'en_curso', 'cerrado')),
  creado_en           TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------
-- Esquema de reparto del premio (configurable por torneo)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reparto_premios (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  torneo_id  INTEGER NOT NULL REFERENCES torneos(id) ON DELETE CASCADE,
  posicion   INTEGER NOT NULL,
  porcentaje REAL    NOT NULL,
  UNIQUE (torneo_id, posicion)
);

-- ---------------------------------------------------------------------
-- Fases del torneo (Grupos J1, Octavos, ...)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS fases (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  torneo_id INTEGER NOT NULL REFERENCES torneos(id) ON DELETE CASCADE,
  nombre    TEXT    NOT NULL,
  orden     INTEGER NOT NULL DEFAULT 0
);

-- ---------------------------------------------------------------------
-- Partidos
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS partidos (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  torneo_id        INTEGER NOT NULL REFERENCES torneos(id) ON DELETE CASCADE,
  fase_id          INTEGER REFERENCES fases(id) ON DELETE SET NULL,
  equipo_local     TEXT    NOT NULL,
  equipo_visitante TEXT    NOT NULL,
  inicia_en        TEXT    NOT NULL,            -- ISO 8601 UTC; deadline de pronóstico
  goles_local      INTEGER,
  goles_visitante  INTEGER,
  estado           TEXT    NOT NULL DEFAULT 'programado'
                     CHECK (estado IN ('programado', 'cerrado', 'finalizado')),
  creado_en        TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------
-- Quinielas (boletos comprados)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS quinielas (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  torneo_id    INTEGER NOT NULL REFERENCES torneos(id) ON DELETE CASCADE,
  usuario_id   INTEGER NOT NULL REFERENCES usuarios(id) ON DELETE CASCADE,
  numero       INTEGER,                          -- correlativo por usuario/torneo (al validar)
  nombre       TEXT,                             -- 'Quiniela N de {Nombre}'
  monto        REAL    NOT NULL DEFAULT 0,
  estado       TEXT    NOT NULL DEFAULT 'pendiente_pago'
                 CHECK (estado IN ('pendiente_pago', 'activa', 'anulada')),
  validada_por INTEGER REFERENCES usuarios(id),
  validada_en  TEXT,
  creado_en    TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------------
-- Pronósticos (un marcador por quiniela y partido)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pronosticos (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  quiniela_id     INTEGER NOT NULL REFERENCES quinielas(id) ON DELETE CASCADE,
  partido_id      INTEGER NOT NULL REFERENCES partidos(id) ON DELETE CASCADE,
  pred_local      INTEGER NOT NULL,
  pred_visitante  INTEGER NOT NULL,
  puntos_obtenidos INTEGER NOT NULL DEFAULT 0,
  actualizado_en  TEXT    NOT NULL DEFAULT (datetime('now')),
  UNIQUE (quiniela_id, partido_id)
);

-- ---------------------------------------------------------------------
-- Índices útiles
-- ---------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_partidos_torneo   ON partidos(torneo_id);
CREATE INDEX IF NOT EXISTS idx_quinielas_torneo  ON quinielas(torneo_id);
CREATE INDEX IF NOT EXISTS idx_quinielas_usuario ON quinielas(usuario_id);
CREATE INDEX IF NOT EXISTS idx_pron_quiniela     ON pronosticos(quiniela_id);
CREATE INDEX IF NOT EXISTS idx_pron_partido      ON pronosticos(partido_id);