-- =============================================================================
-- Base de datos: gestión de campeonatos deportivos
-- Motor: MySQL 8.0+ (utf8mb4)
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS campeonato_db;
CREATE DATABASE campeonato_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE campeonato_db;

-- -----------------------------------------------------------------------------
-- Perfiles y usuarios
-- -----------------------------------------------------------------------------
CREATE TABLE perfiles (
  id            TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  codigo        VARCHAR(32)  NOT NULL COMMENT 'Identificador único en código',
  nombre        VARCHAR(80)  NOT NULL,
  descripcion   VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_perfiles_codigo (codigo)
) ENGINE=InnoDB COMMENT='Roles del sistema (admin, árbitro, etc.)';

CREATE TABLE usuarios (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email           VARCHAR(191) NOT NULL,
  password_hash   VARCHAR(255) NOT NULL COMMENT 'Hash (bcrypt/argon2)',
  nombre          VARCHAR(80)  NOT NULL,
  apellidos       VARCHAR(120) NULL,
  telefono        VARCHAR(40)  NULL,
  activo          TINYINT(1)   NOT NULL DEFAULT 1,
  creado_en       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en  DATETIME     NULL ON UPDATE CURRENT_TIMESTAMP,
  ultimo_acceso   DATETIME     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_usuarios_email (email),
  KEY idx_usuarios_activo (activo)
) ENGINE=InnoDB COMMENT='Cuentas de acceso al sistema';

CREATE TABLE usuario_perfil (
  usuario_id  INT UNSIGNED NOT NULL,
  perfil_id   TINYINT UNSIGNED NOT NULL,
  asignado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (usuario_id, perfil_id),
  CONSTRAINT fk_up_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_up_perfil FOREIGN KEY (perfil_id) REFERENCES perfiles (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Un usuario puede tener varios perfiles';

-- -----------------------------------------------------------------------------
-- Catálogos
-- -----------------------------------------------------------------------------
CREATE TABLE estadios (
  id          SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre      VARCHAR(120) NOT NULL,
  direccion   VARCHAR(255) NULL,
  ciudad      VARCHAR(80)  NULL,
  capacidad   INT UNSIGNED NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------
-- Campeonatos
-- -----------------------------------------------------------------------------
CREATE TABLE campeonatos (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre           VARCHAR(160) NOT NULL,
  temporada        VARCHAR(40)  NULL COMMENT 'Ej. 2024-2025',
  descripcion      TEXT         NULL,
  fecha_inicio     DATE         NOT NULL,
  fecha_fin        DATE         NOT NULL,
  estado           ENUM('borrador','inscripciones','en_curso','finalizado','cancelado')
                   NOT NULL DEFAULT 'borrador',
  formato          ENUM('liga','liga_grupos','eliminacion','amistoso')
                   NOT NULL DEFAULT 'liga',
  puntos_victoria  TINYINT UNSIGNED NOT NULL DEFAULT 3,
  puntos_empate    TINYINT UNSIGNED NOT NULL DEFAULT 1,
  puntos_derrota   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  desempate_notas  VARCHAR(255) NULL COMMENT 'Criterios: puntos, DG, GF...',
  logo_url         VARCHAR(512) NULL,
  creado_en        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_campeonatos_estado (estado),
  KEY idx_campeonatos_fechas (fecha_inicio, fecha_fin)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------
-- Equipos (clubes reutilizables entre campeonatos)
-- -----------------------------------------------------------------------------
CREATE TABLE equipos (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nombre       VARCHAR(120) NOT NULL,
  abreviatura  VARCHAR(16)  NULL,
  escudo_url   VARCHAR(512) NULL,
  ciudad       VARCHAR(80)  NULL,
  fundado      YEAR         NULL,
  activo       TINYINT(1)   NOT NULL DEFAULT 1,
  creado_en    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_equipos_nombre (nombre)
) ENGINE=InnoDB;

-- Inscripción de un equipo a un campeonato
CREATE TABLE campeonato_equipo (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campeonato_id   INT UNSIGNED NOT NULL,
  equipo_id       INT UNSIGNED NOT NULL,
  grupo_codigo    VARCHAR(16)  NULL COMMENT 'A, B, etc. si hay fase de grupos',
  orden_lista     SMALLINT UNSIGNED NULL,
  estado          ENUM('inscrito','activo','eliminado','retirado')
                  NOT NULL DEFAULT 'inscrito',
  PRIMARY KEY (id),
  UNIQUE KEY uq_ce_campeonato_equipo (campeonato_id, equipo_id),
  KEY idx_ce_campeonato (campeonato_id),
  KEY idx_ce_equipo (equipo_id),
  CONSTRAINT fk_ce_campeonato FOREIGN KEY (campeonato_id) REFERENCES campeonatos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ce_equipo FOREIGN KEY (equipo_id) REFERENCES equipos (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Relación N:M campeonato-equipo con metadatos';

-- Usuario ligado a un club (p. ej. delegado general del equipo)
CREATE TABLE usuario_equipo (
  usuario_id  INT UNSIGNED NOT NULL,
  equipo_id   INT UNSIGNED NOT NULL,
  PRIMARY KEY (usuario_id, equipo_id),
  KEY idx_ue_equipo (equipo_id),
  CONSTRAINT fk_ue_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ue_equipo FOREIGN KEY (equipo_id) REFERENCES equipos (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Staff o delegado del club';

-- Delegación por torneo (mismo usuario puede ser delegado de distinto equipo en otro campeonato)
CREATE TABLE usuario_campeonato_equipo (
  usuario_id    INT UNSIGNED NOT NULL,
  campeonato_id INT UNSIGNED NOT NULL,
  equipo_id     INT UNSIGNED NOT NULL,
  PRIMARY KEY (usuario_id, campeonato_id, equipo_id),
  KEY idx_uce_campeonato (campeonato_id),
  KEY idx_uce_equipo (equipo_id),
  CONSTRAINT fk_uce_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_uce_campeonato FOREIGN KEY (campeonato_id) REFERENCES campeonatos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_uce_equipo FOREIGN KEY (equipo_id) REFERENCES equipos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_uce_inscripcion FOREIGN KEY (campeonato_id, equipo_id)
    REFERENCES campeonato_equipo (campeonato_id, equipo_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Rol del usuario sobre un equipo inscrito en un campeonato';

-- -----------------------------------------------------------------------------
-- Jugadores y plantilla por campeonato
-- -----------------------------------------------------------------------------
CREATE TABLE jugadores (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  documento       VARCHAR(40)  NULL,
  nombre          VARCHAR(80)  NOT NULL,
  apellidos       VARCHAR(120) NOT NULL,
  fecha_nacimiento DATE        NULL,
  nacionalidad    VARCHAR(80)  NULL,
  foto_url        VARCHAR(512) NULL,
  creado_en       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_jugadores_doc (documento),
  KEY idx_jugadores_nombre (apellidos, nombre)
) ENGINE=InnoDB;

CREATE TABLE plantilla (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campeonato_equipo_id INT UNSIGNED NOT NULL,
  jugador_id           INT UNSIGNED NOT NULL,
  dorsal               TINYINT UNSIGNED NULL,
  posicion             VARCHAR(40)  NULL COMMENT 'Portero, defensa, medio, delantero...',
  estado               ENUM('activo','lesionado','sancionado','baja')
                       NOT NULL DEFAULT 'activo',
  fecha_alta           DATE         NOT NULL,
  fecha_baja           DATE         NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_plantilla_ce_jugador (campeonato_equipo_id, jugador_id),
  KEY idx_plantilla_jugador (jugador_id),
  CONSTRAINT fk_plantilla_ce FOREIGN KEY (campeonato_equipo_id) REFERENCES campeonato_equipo (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_plantilla_jugador FOREIGN KEY (jugador_id) REFERENCES jugadores (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Jugadores habilitados por equipo en cada campeonato';

-- -----------------------------------------------------------------------------
-- Partidos, goles y tarjetas
-- -----------------------------------------------------------------------------
CREATE TABLE partidos (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campeonato_id      INT UNSIGNED NOT NULL,
  jornada            TINYINT UNSIGNED NULL,
  ronda              VARCHAR(40)  NULL COMMENT 'Octavos, semifinal...',
  equipo_local_id    INT UNSIGNED NOT NULL,
  equipo_visitante_id INT UNSIGNED NOT NULL,
  fecha_programada   DATETIME     NOT NULL,
  fecha_real         DATETIME     NULL COMMENT 'Inicio real del encuentro',
  estadio_id         SMALLINT UNSIGNED NULL,
  estado             ENUM('programado','en_juego','finalizado','suspendido','aplazado','wo')
                     NOT NULL DEFAULT 'programado',
  goles_local        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  goles_visitante    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  notas              VARCHAR(500) NULL,
  registrado_por     INT UNSIGNED NULL COMMENT 'Usuario que cerró el acta',
  actualizado_en     DATETIME     NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_partidos_campeonato (campeonato_id),
  KEY idx_partidos_fecha (fecha_programada),
  KEY idx_partidos_estado (estado),
  KEY idx_partidos_local (equipo_local_id),
  KEY idx_partidos_visitante (equipo_visitante_id),
  CONSTRAINT fk_partidos_campeonato FOREIGN KEY (campeonato_id) REFERENCES campeonatos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_partidos_local FOREIGN KEY (equipo_local_id) REFERENCES equipos (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_partidos_visitante FOREIGN KEY (equipo_visitante_id) REFERENCES equipos (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_partidos_estadio FOREIGN KEY (estadio_id) REFERENCES estadios (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_partidos_registrador FOREIGN KEY (registrado_por) REFERENCES usuarios (id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT chk_partidos_distintos CHECK (equipo_local_id <> equipo_visitante_id),
  CONSTRAINT chk_partidos_goles CHECK (goles_local <= 99 AND goles_visitante <= 99)
) ENGINE=InnoDB;

CREATE TABLE goles_partido (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  partido_id   INT UNSIGNED NOT NULL,
  jugador_id   INT UNSIGNED NOT NULL,
  minuto       TINYINT UNSIGNED NULL,
  en_contra    TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Autogol',
  notas        VARCHAR(200) NULL,
  PRIMARY KEY (id),
  KEY idx_gp_partido (partido_id),
  KEY idx_gp_jugador (jugador_id),
  CONSTRAINT fk_gp_partido FOREIGN KEY (partido_id) REFERENCES partidos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_gp_jugador FOREIGN KEY (jugador_id) REFERENCES jugadores (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tarjetas_partido (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  partido_id      INT UNSIGNED NOT NULL,
  jugador_id      INT UNSIGNED NOT NULL,
  tipo            ENUM('amarilla','roja','segunda_amarilla') NOT NULL,
  minuto          TINYINT UNSIGNED NULL,
  motivo          VARCHAR(300) NULL,
  registrado_por  INT UNSIGNED NULL,
  creado_en       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tp_partido (partido_id),
  KEY idx_tp_jugador (jugador_id),
  KEY idx_tp_tipo (tipo),
  CONSTRAINT fk_tp_partido FOREIGN KEY (partido_id) REFERENCES partidos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_tp_jugador FOREIGN KEY (jugador_id) REFERENCES jugadores (id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_tp_usuario FOREIGN KEY (registrado_por) REFERENCES usuarios (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Estadísticas agregadas por jugador en un campeonato (opcional, alimentar por triggers o job)
CREATE TABLE estadisticas_jugador_campeonato (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campeonato_id        INT UNSIGNED NOT NULL,
  jugador_id           INT UNSIGNED NOT NULL,
  equipo_id            INT UNSIGNED NOT NULL,
  partidos_jugados     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  goles                SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  tarjetas_amarillas   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  tarjetas_rojas       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ejc_cam_jug_eq (campeonato_id, jugador_id, equipo_id),
  KEY idx_ejc_jugador (jugador_id),
  CONSTRAINT fk_ejc_campeonato FOREIGN KEY (campeonato_id) REFERENCES campeonatos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ejc_jugador FOREIGN KEY (jugador_id) REFERENCES jugadores (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ejc_equipo FOREIGN KEY (equipo_id) REFERENCES equipos (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Resumen deportivo por jugador; puede calcularse o mantenerse';

-- -----------------------------------------------------------------------------
-- Auditoría simple de cambios en partidos
-- -----------------------------------------------------------------------------
CREATE TABLE auditoria_partidos (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  partido_id     INT UNSIGNED NOT NULL,
  usuario_id     INT UNSIGNED NULL,
  accion         VARCHAR(40) NOT NULL,
  detalle        JSON NULL,
  creado_en      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ap_partido (partido_id),
  CONSTRAINT fk_ap_partido FOREIGN KEY (partido_id) REFERENCES partidos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ap_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
