-- =========================================================
-- Quiniela Liga MX — Esquema de base de datos (MySQL)
-- =========================================================

-- ---------------------------------------------------------
-- usuarios (participantes y administrador)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre            VARCHAR(120)        NOT NULL,
    email             VARCHAR(190)        NOT NULL,
    google_id         VARCHAR(64)         NULL,               -- sub de Google, si entra con Gmail
    password_hash     VARCHAR(255)        NULL,               -- solo si NO usa Google
    avatar_url        VARCHAR(255)        NULL,               -- foto de perfil (de Google o subida)
    rol               ENUM('admin','participante') NOT NULL DEFAULT 'participante',
    notif_push        TINYINT(1)          NOT NULL DEFAULT 1,
    activo            TINYINT(1)          NOT NULL DEFAULT 1,
    fecha_alta        DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_usuarios_email (email),
    UNIQUE KEY uq_usuarios_google_id (google_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- equipos (catálogo de clubes de Liga MX)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS equipos (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_externo_ligamx     INT UNSIGNED        NULL,            -- idClub tal cual lo expone ligamx.net (null si se dio de alta a mano)
    nombre                VARCHAR(100)        NOT NULL,
    nombre_url            VARCHAR(100)        NOT NULL,        -- slug, ej. "cruz-azul"
    ciudad                VARCHAR(100)        NULL,
    escudo_url            VARCHAR(255)        NULL,            -- ruta local, ej. /uploads/escudos/12566.png
    activo                TINYINT(1)          NOT NULL DEFAULT 1,
    UNIQUE KEY uq_equipos_externo (id_externo_ligamx)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- jornadas
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS jornadas (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    numero                TINYINT UNSIGNED    NOT NULL,
    nombre                VARCHAR(60)         NOT NULL,        -- "Jornada 1"
    estatus               ENUM('cerrada','abierta','comenzada','finalizada') NOT NULL DEFAULT 'cerrada',
    fecha_cierre_captura  DATETIME            NULL,            -- deadline para enviar quiniela
    UNIQUE KEY uq_jornadas_numero (numero)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- partidos
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS partidos (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    jornada_id            INT UNSIGNED        NOT NULL,
    equipo_local_id       INT UNSIGNED        NOT NULL,
    equipo_visita_id      INT UNSIGNED        NOT NULL,
    fecha                 DATE                NOT NULL,
    hora                  TIME                NOT NULL,
    estadio               VARCHAR(100)        NULL,
    marcador_local         TINYINT UNSIGNED    NULL,           -- oficial, null = no jugado
    marcador_visita        TINYINT UNSIGNED    NULL,
    CONSTRAINT fk_partidos_jornada  FOREIGN KEY (jornada_id)       REFERENCES jornadas(id),
    CONSTRAINT fk_partidos_local    FOREIGN KEY (equipo_local_id)  REFERENCES equipos(id),
    CONSTRAINT fk_partidos_visita   FOREIGN KEY (equipo_visita_id) REFERENCES equipos(id),
    KEY idx_partidos_jornada (jornada_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- quinielas (una por usuario y jornada)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS quinielas (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id            INT UNSIGNED        NOT NULL,
    jornada_id            INT UNSIGNED        NOT NULL,
    estatus               ENUM('sin_iniciar','en_captura','registrada') NOT NULL DEFAULT 'sin_iniciar',
    fecha_envio           DATETIME            NULL,
    pago_estatus          ENUM('pendiente','pagada')           NULL,  -- null hasta que está registrada
    CONSTRAINT fk_quinielas_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    CONSTRAINT fk_quinielas_jornada FOREIGN KEY (jornada_id) REFERENCES jornadas(id),
    UNIQUE KEY uq_quinielas_usuario_jornada (usuario_id, jornada_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- pronosticos (un renglón por partido dentro de una quiniela)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS pronosticos (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiniela_id           INT UNSIGNED        NOT NULL,
    partido_id            INT UNSIGNED        NOT NULL,
    marcador_local         TINYINT UNSIGNED    NOT NULL,
    marcador_visita        TINYINT UNSIGNED    NOT NULL,
    puntos_obtenidos      TINYINT UNSIGNED    NULL,            -- se calcula al capturar resultado oficial
    CONSTRAINT fk_pronosticos_quiniela FOREIGN KEY (quiniela_id) REFERENCES quinielas(id),
    CONSTRAINT fk_pronosticos_partido  FOREIGN KEY (partido_id)  REFERENCES partidos(id),
    UNIQUE KEY uq_pronosticos_quiniela_partido (quiniela_id, partido_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- push_subscripciones (suscripciones de notificaciones push
-- de cada dispositivo/navegador donde el participante dio permiso)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS push_subscripciones (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id            INT UNSIGNED        NOT NULL,
    endpoint              VARCHAR(512)        NOT NULL,
    p256dh                VARCHAR(255)        NOT NULL,
    auth                  VARCHAR(255)        NOT NULL,
    fecha_alta            DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_push_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    UNIQUE KEY uq_push_endpoint (endpoint(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =========================================================
-- Catálogo inicial: 18 equipos de Liga MX (Apertura 2026)
-- escudo_url apunta a donde quedará la copia LOCAL descargada
-- por el script download_escudos.php (ver ese archivo).
-- =========================================================
INSERT INTO equipos (id_externo_ligamx, nombre, nombre_url, ciudad, escudo_url) VALUES
(11,    'Pachuca',                       'pachuca',                        'Pachuca',        '/uploads/escudos/11.png'),
(5,     'Tijuana',                       'tijuana',                        'Tijuana',        '/uploads/escudos/5.png'),
(17,    'Toluca',                        'toluca',                         'Toluca',         '/uploads/escudos/17.png'),
(12566, 'Cruz Azul',                     'cruz-azul',                      'Ciudad de México','/uploads/escudos/12566.png'),
(10445, 'Atlas',                         'atlas',                          'Guadalajara',    '/uploads/escudos/10445.png'),
(14,    'Monterrey',                     'monterrey',                      'Monterrey',      '/uploads/escudos/14.png'),
(29,    'Necaxa',                        'necaxa',                         'Aguascalientes', '/uploads/escudos/29.png'),
(1,     'América',                       'america',                        'Ciudad de México','/uploads/escudos/1.png'),
(11550, 'Puebla',                        'puebla',                         'Puebla',         '/uploads/escudos/11550.png'),
(14102, 'Santos Laguna',                 'santos-laguna',                  'Torreón',        '/uploads/escudos/14102.png'),
(9,     'León',                          'leon',                           'León',           '/uploads/escudos/9.png'),
(11220, 'Atlético de San Luis',          'atletico-de-san-luis',           'San Luis Potosí','/uploads/escudos/11220.png'),
(14257, 'Atlante',                       'atlante',                        'Ciudad de México','/uploads/escudos/14257.png'),
(11790, 'FC Juárez',                     'fc-juarez',                      'Ciudad Juárez',  '/uploads/escudos/11790.png'),
(13668, 'Gallos Blancos de Querétaro',   'gallos-blancos-de-queretaro',    'Querétaro',      '/uploads/escudos/13668.png'),
(16,    'Tigres',                        'tigres',                         'San Nicolás',    '/uploads/escudos/16.png'),
(7,     'Guadalajara',                   'guadalajara',                    'Guadalajara',    '/uploads/escudos/7.png'),
(18,    'Pumas',                         'pumas',                          'Ciudad de México','/uploads/escudos/18.png');

-- =========================================================
-- Catálogo inicial: 17 jornadas de fase regular (Apertura 2026)
-- Todas inician en "cerrada" como definimos.
-- =========================================================
INSERT INTO jornadas (numero, nombre, estatus) VALUES
(1,'Jornada 1','cerrada'),(2,'Jornada 2','cerrada'),(3,'Jornada 3','cerrada'),
(4,'Jornada 4','cerrada'),(5,'Jornada 5','cerrada'),(6,'Jornada 6','cerrada'),
(7,'Jornada 7','cerrada'),(8,'Jornada 8','cerrada'),(9,'Jornada 9','cerrada'),
(10,'Jornada 10','cerrada'),(11,'Jornada 11','cerrada'),(12,'Jornada 12','cerrada'),
(13,'Jornada 13','cerrada'),(14,'Jornada 14','cerrada'),(15,'Jornada 15','cerrada'),
(16,'Jornada 16','cerrada'),(17,'Jornada 17','cerrada');
