Script SQL completo
El siguiente script implementa el modelo en MariaDB 10.x/phpMyAdmin.
CREATE DATABASE IF NOT EXISTS fifa2026
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE fifa2026;
SET FOREIGN_KEY_CHECKS = 0;
DROP VIEW IF EXISTS vw_campeon;
DROP VIEW IF EXISTS vw_estadisticas_generales_torneo;
DROP VIEW IF EXISTS vw_resumen_selecciones_torneo;
DROP VIEW IF EXISTS vw_goleadores;
DROP VIEW IF EXISTS vw_tabla_grupos;
DROP TABLE IF EXISTS estadistica_jugador_partido;
DROP TABLE IF EXISTS estadistica_equipo_partido;
DROP TABLE IF EXISTS cambio;
DROP TABLE IF EXISTS tarjeta;
DROP TABLE IF EXISTS gol;
DROP TABLE IF EXISTS alineacion_partido;
DROP TABLE IF EXISTS partido_arbitro_apoyo;
DROP TABLE IF EXISTS partido;
DROP TABLE IF EXISTS arbitro;
DROP TABLE IF EXISTS convocatoria;
DROP TABLE IF EXISTS jugador;
DROP TABLE IF EXISTS posicion_jugador;
DROP TABLE IF EXISTS club;
DROP TABLE IF EXISTS seleccion;
DROP TABLE IF EXISTS director_tecnico;
DROP TABLE IF EXISTS grupo;
DROP TABLE IF EXISTS fase;
DROP TABLE IF EXISTS estadio;
DROP TABLE IF EXISTS ciudad_sede;
DROP TABLE IF EXISTS pais;
DROP TABLE IF EXISTS confederacion;
DROP TABLE IF EXISTS torneo;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE torneo (
torneo_id BIGINT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL UNIQUE,
anio SMALLINT NOT NULL UNIQUE CHECK (anio >= 1900),
fecha_inicio DATE NOT NULL,
fecha_fin DATE NOT NULL,
total_equipos SMALLINT NOT NULL CHECK (total_equipos > 0),
total_partidos SMALLINT NOT NULL CHECK (total_partidos > 0),
CHECK (fecha_fin >= fecha_inicio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE confederacion (
confederacion_id BIGINT AUTO_INCREMENT PRIMARY KEY,
codigo VARCHAR(10) NOT NULL UNIQUE,
nombre VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE pais (
pais_id BIGINT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(80) NOT NULL UNIQUE,
codigo_iso3 CHAR(3) NOT NULL UNIQUE,
codigo_fifa CHAR(3) UNIQUE,
es_sede BOOLEAN NOT NULL DEFAULT FALSE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE ciudad_sede (
ciudad_id BIGINT AUTO_INCREMENT PRIMARY KEY,
pais_id BIGINT NOT NULL,
nombre VARCHAR(80) NOT NULL,
estado_provincia VARCHAR(80),
UNIQUE (pais_id, nombre),
CONSTRAINT fk_ciudad_pais FOREIGN KEY (pais_id) REFERENCES pais(pais_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE estadio (
estadio_id BIGINT AUTO_INCREMENT PRIMARY KEY,
ciudad_id BIGINT NOT NULL,
nombre VARCHAR(120) NOT NULL UNIQUE,
capacidad INTEGER NOT NULL CHECK (capacidad > 0),
direccion TEXT,
activo BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT fk_estadio_ciudad FOREIGN KEY (ciudad_id) REFERENCES ciudad_sede(ciudad_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE fase (
fase_id BIGINT AUTO_INCREMENT PRIMARY KEY,
torneo_id BIGINT NOT NULL,
codigo VARCHAR(20) NOT NULL,
nombre VARCHAR(60) NOT NULL,
orden SMALLINT NOT NULL CHECK (orden > 0),
es_eliminatoria BOOLEAN NOT NULL,
permite_empate BOOLEAN NOT NULL,
UNIQUE (torneo_id, fase_id),
UNIQUE (torneo_id, codigo),
UNIQUE (torneo_id, orden),
CONSTRAINT fk_fase_torneo FOREIGN KEY (torneo_id) REFERENCES torneo(torneo_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE grupo (
grupo_id BIGINT AUTO_INCREMENT PRIMARY KEY,
torneo_id BIGINT NOT NULL,
codigo CHAR(1) NOT NULL,
UNIQUE (torneo_id, grupo_id),
UNIQUE (torneo_id, codigo),
CONSTRAINT fk_grupo_torneo FOREIGN KEY (torneo_id) REFERENCES torneo(torneo_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE director_tecnico (
director_tecnico_id BIGINT AUTO_INCREMENT PRIMARY KEY,
nombres VARCHAR(80) NOT NULL,
apellidos VARCHAR(80) NOT NULL,
nacionalidad_pais_id BIGINT NOT NULL,
CONSTRAINT fk_dt_pais FOREIGN KEY (nacionalidad_pais_id) REFERENCES pais(pais_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE seleccion (
seleccion_id BIGINT AUTO_INCREMENT PRIMARY KEY,
torneo_id BIGINT NOT NULL,
pais_id BIGINT NOT NULL,
confederacion_id BIGINT NOT NULL,
grupo_id BIGINT,
director_tecnico_id BIGINT NOT NULL,
nombre_corto VARCHAR(80) NOT NULL,
codigo_fifa CHAR(3) NOT NULL,
ranking_fifa SMALLINT,
UNIQUE (torneo_id, seleccion_id),
UNIQUE (torneo_id, pais_id),
UNIQUE (torneo_id, codigo_fifa),
UNIQUE (torneo_id, director_tecnico_id),
CONSTRAINT fk_seleccion_torneo FOREIGN KEY (torneo_id) REFERENCES torneo(torneo_id) ON DELETE CASCADE,
CONSTRAINT fk_seleccion_pais FOREIGN KEY (pais_id) REFERENCES pais(pais_id),
CONSTRAINT fk_seleccion_conf FOREIGN KEY (confederacion_id) REFERENCES confederacion(confederacion_id),
CONSTRAINT fk_seleccion_dt FOREIGN KEY (director_tecnico_id) REFERENCES director_tecnico(director_tecnico_id),
CONSTRAINT fk_seleccion_grupo FOREIGN KEY (torneo_id, grupo_id) REFERENCES grupo(torneo_id, grupo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE club (
club_id BIGINT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(120) NOT NULL,
pais_id BIGINT NOT NULL,
ciudad VARCHAR(80),
UNIQUE (nombre, pais_id),
CONSTRAINT fk_club_pais FOREIGN KEY (pais_id) REFERENCES pais(pais_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE posicion_jugador (
posicion_id BIGINT AUTO_INCREMENT PRIMARY KEY,
codigo VARCHAR(10) NOT NULL UNIQUE,
nombre VARCHAR(30) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE jugador (
jugador_id BIGINT AUTO_INCREMENT PRIMARY KEY,
nombres VARCHAR(80) NOT NULL,
apellidos VARCHAR(80) NOT NULL,
fecha_nacimiento DATE,
pais_nacimiento_id BIGINT,
pie_habil VARCHAR(10) CHECK (pie_habil IN ('DERECHO','IZQUIERDO','AMBOS')),
altura_cm DECIMAL(5,2) CHECK (altura_cm IS NULL OR altura_cm > 0),
peso_kg DECIMAL(5,2) CHECK (peso_kg IS NULL OR peso_kg > 0),
CONSTRAINT fk_jugador_pais_nacimiento FOREIGN KEY (pais_nacimiento_id) REFERENCES pais(pais_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE convocatoria (
convocatoria_id BIGINT AUTO_INCREMENT PRIMARY KEY,
torneo_id BIGINT NOT NULL,
seleccion_id BIGINT NOT NULL,
jugador_id BIGINT NOT NULL,
club_id BIGINT,
posicion_id BIGINT NOT NULL,
numero_camiseta SMALLINT NOT NULL CHECK (numero_camiseta BETWEEN 1 AND 99),
es_capitan BOOLEAN NOT NULL DEFAULT FALSE,
fecha_convocatoria DATE,
UNIQUE (torneo_id, jugador_id),
UNIQUE (seleccion_id, jugador_id),
UNIQUE (seleccion_id, numero_camiseta),
CONSTRAINT fk_convocatoria_seleccion FOREIGN KEY (torneo_id, seleccion_id)
REFERENCES seleccion(torneo_id, seleccion_id) ON DELETE CASCADE,
CONSTRAINT fk_convocatoria_jugador FOREIGN KEY (jugador_id) REFERENCES jugador(jugador_id),
CONSTRAINT fk_convocatoria_club FOREIGN KEY (club_id) REFERENCES club(club_id),
CONSTRAINT fk_convocatoria_posicion FOREIGN KEY (posicion_id) REFERENCES posicion_jugador(posicion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE arbitro (
arbitro_id BIGINT AUTO_INCREMENT PRIMARY KEY,
nombres VARCHAR(80) NOT NULL,
apellidos VARCHAR(80) NOT NULL,
nacionalidad_pais_id BIGINT NOT NULL,
CONSTRAINT fk_arbitro_pais FOREIGN KEY (nacionalidad_pais_id) REFERENCES pais(pais_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE partido (
partido_id BIGINT AUTO_INCREMENT PRIMARY KEY,
torneo_id BIGINT NOT NULL,
numero_partido SMALLINT NOT NULL,
fase_id BIGINT NOT NULL,
grupo_id BIGINT,
estadio_id BIGINT NOT NULL,
arbitro_central_id BIGINT,
fecha_hora_inicio DATETIME NOT NULL,
fecha_hora_fin DATETIME NOT NULL,
seleccion_a_id BIGINT NOT NULL,
seleccion_b_id BIGINT NOT NULL,
estado VARCHAR(20) NOT NULL DEFAULT 'PROGRAMADO'
CHECK (estado IN ('PROGRAMADO','EN_JUEGO','FINALIZADO','SUSPENDIDO','CANCELADO')),
hubo_tiempo_extra BOOLEAN NOT NULL DEFAULT FALSE,
hubo_penales BOOLEAN NOT NULL DEFAULT FALSE,
goles_a SMALLINT CHECK (goles_a IS NULL OR goles_a >= 0),
goles_b SMALLINT CHECK (goles_b IS NULL OR goles_b >= 0),
penales_a SMALLINT CHECK (penales_a IS NULL OR penales_a >= 0),
penales_b SMALLINT CHECK (penales_b IS NULL OR penales_b >= 0),
ganador_id BIGINT,
observaciones TEXT,
CHECK (seleccion_a_id <> seleccion_b_id),
CHECK (fecha_hora_fin > fecha_hora_inicio),
UNIQUE (torneo_id, partido_id),
UNIQUE (torneo_id, numero_partido),
CONSTRAINT fk_partido_torneo FOREIGN KEY (torneo_id) REFERENCES torneo(torneo_id) ON DELETE CASCADE,
CONSTRAINT fk_partido_estadio FOREIGN KEY (estadio_id) REFERENCES estadio(estadio_id),
CONSTRAINT fk_partido_arbitro FOREIGN KEY (arbitro_central_id) REFERENCES arbitro(arbitro_id),
CONSTRAINT fk_partido_fase FOREIGN KEY (torneo_id, fase_id) REFERENCES fase(torneo_id, fase_id),
CONSTRAINT fk_partido_grupo FOREIGN KEY (torneo_id, grupo_id) REFERENCES grupo(torneo_id, grupo_id),
CONSTRAINT fk_partido_sel_a FOREIGN KEY (torneo_id, seleccion_a_id) REFERENCES seleccion(torneo_id, seleccion_id),
CONSTRAINT fk_partido_sel_b FOREIGN KEY (torneo_id, seleccion_b_id) REFERENCES seleccion(torneo_id, seleccion_id),
CONSTRAINT fk_partido_ganador FOREIGN KEY (torneo_id, ganador_id) REFERENCES seleccion(torneo_id, seleccion_id),
INDEX idx_partido_estadio_horario (estadio_id, fecha_hora_inicio, fecha_hora_fin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE partido_arbitro_apoyo (
partido_id BIGINT NOT NULL,
arbitro_id BIGINT NOT NULL,
rol_arbitro VARCHAR(15) NOT NULL CHECK (rol_arbitro IN ('ASISTENTE','CUARTO','VAR','AVAR')),
secuencia SMALLINT NOT NULL DEFAULT 1 CHECK (secuencia > 0),
cuarto_unico BIGINT GENERATED ALWAYS AS (CASE WHEN rol_arbitro = 'CUARTO' THEN partido_id ELSE NULL END) VIRTUAL,
var_unico BIGINT GENERATED ALWAYS AS (CASE WHEN rol_arbitro = 'VAR' THEN partido_id ELSE NULL END) VIRTUAL,
PRIMARY KEY (partido_id, rol_arbitro, secuencia),
UNIQUE (partido_id, arbitro_id),
UNIQUE KEY uq_partido_cuarto (cuarto_unico),
UNIQUE KEY uq_partido_var (var_unico),
CONSTRAINT fk_paa_partido FOREIGN KEY (partido_id) REFERENCES partido(partido_id) ON DELETE CASCADE,
CONSTRAINT fk_paa_arbitro FOREIGN KEY (arbitro_id) REFERENCES arbitro(arbitro_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE alineacion_partido (
partido_id BIGINT NOT NULL,
seleccion_id BIGINT NOT NULL,
jugador_id BIGINT NOT NULL,
es_titular BOOLEAN NOT NULL,
es_capitan BOOLEAN NOT NULL DEFAULT FALSE,
posicion_tactica VARCHAR(30),
capitan_unico VARCHAR(64) GENERATED ALWAYS AS (CASE WHEN es_capitan THEN CONCAT(partido_id, '-', seleccion_id) ELSE NULL END) VIRTUAL,
PRIMARY KEY (partido_id, jugador_id),
UNIQUE KEY uq_capitan_por_equipo_partido (capitan_unico),
CONSTRAINT fk_alineacion_partido FOREIGN KEY (partido_id) REFERENCES partido(partido_id) ON DELETE CASCADE,
CONSTRAINT fk_alineacion_convocatoria FOREIGN KEY (seleccion_id, jugador_id)
REFERENCES convocatoria(seleccion_id, jugador_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE gol (
gol_id BIGINT AUTO_INCREMENT PRIMARY KEY,
partido_id BIGINT NOT NULL,
seleccion_id BIGINT NOT NULL,
jugador_id BIGINT NOT NULL,
minuto SMALLINT NOT NULL CHECK (minuto BETWEEN 0 AND 130),
minuto_extra SMALLINT NOT NULL DEFAULT 0 CHECK (minuto_extra BETWEEN 0 AND 59),
es_penal BOOLEAN NOT NULL DEFAULT FALSE,
es_autogol BOOLEAN NOT NULL DEFAULT FALSE,
descripcion TEXT,
CONSTRAINT fk_gol_partido FOREIGN KEY (partido_id) REFERENCES partido(partido_id) ON DELETE CASCADE,
CONSTRAINT fk_gol_jugador FOREIGN KEY (jugador_id) REFERENCES jugador(jugador_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tarjeta (
tarjeta_id BIGINT AUTO_INCREMENT PRIMARY KEY,
partido_id BIGINT NOT NULL,
seleccion_id BIGINT NOT NULL,
jugador_id BIGINT NOT NULL,
minuto SMALLINT NOT NULL CHECK (minuto BETWEEN 0 AND 130),
minuto_extra SMALLINT NOT NULL DEFAULT 0 CHECK (minuto_extra BETWEEN 0 AND 59),
tipo_tarjeta VARCHAR(10) NOT NULL CHECK (tipo_tarjeta IN ('AMARILLA','ROJA')),
es_segunda_amarilla BOOLEAN NOT NULL DEFAULT FALSE,
motivo VARCHAR(150),
CONSTRAINT fk_tarjeta_partido FOREIGN KEY (partido_id) REFERENCES partido(partido_id) ON DELETE CASCADE,
CONSTRAINT fk_tarjeta_jugador FOREIGN KEY (jugador_id) REFERENCES jugador(jugador_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cambio (
cambio_id BIGINT AUTO_INCREMENT PRIMARY KEY,
partido_id BIGINT NOT NULL,
seleccion_id BIGINT NOT NULL,
jugador_sale_id BIGINT NOT NULL,
jugador_entra_id BIGINT NOT NULL,
minuto SMALLINT NOT NULL CHECK (minuto BETWEEN 0 AND 130),
minuto_extra SMALLINT NOT NULL DEFAULT 0 CHECK (minuto_extra BETWEEN 0 AND 59),
motivo VARCHAR(100),
CHECK (jugador_sale_id <> jugador_entra_id),
CONSTRAINT fk_cambio_partido FOREIGN KEY (partido_id) REFERENCES partido(partido_id) ON DELETE CASCADE,
CONSTRAINT fk_cambio_jugador_sale FOREIGN KEY (jugador_sale_id) REFERENCES jugador(jugador_id),
CONSTRAINT fk_cambio_jugador_entra FOREIGN KEY (jugador_entra_id) REFERENCES jugador(jugador_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE estadistica_equipo_partido (
partido_id BIGINT NOT NULL,
seleccion_id BIGINT NOT NULL,
posesion_pct DECIMAL(5,2) CHECK (posesion_pct BETWEEN 0 AND 100),
tiros_totales SMALLINT NOT NULL DEFAULT 0 CHECK (tiros_totales >= 0),
tiros_al_arco SMALLINT NOT NULL DEFAULT 0 CHECK (tiros_al_arco >= 0),
faltas SMALLINT NOT NULL DEFAULT 0 CHECK (faltas >= 0),
tiros_esquina SMALLINT NOT NULL DEFAULT 0 CHECK (tiros_esquina >= 0),
fueras_juego SMALLINT NOT NULL DEFAULT 0 CHECK (fueras_juego >= 0),
pases_totales SMALLINT NOT NULL DEFAULT 0 CHECK (pases_totales >= 0),
precision_pase_pct DECIMAL(5,2) CHECK (precision_pase_pct BETWEEN 0 AND 100),
atajadas SMALLINT NOT NULL DEFAULT 0 CHECK (atajadas >= 0),
PRIMARY KEY (partido_id, seleccion_id),
CHECK (tiros_al_arco <= tiros_totales),
CONSTRAINT fk_eep_partido FOREIGN KEY (partido_id) REFERENCES partido(partido_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE estadistica_jugador_partido (
partido_id BIGINT NOT NULL,
jugador_id BIGINT NOT NULL,
minutos_jugados SMALLINT NOT NULL CHECK (minutos_jugados BETWEEN 0 AND 130),
asistencias SMALLINT NOT NULL DEFAULT 0 CHECK (asistencias >= 0),
tiros_totales SMALLINT NOT NULL DEFAULT 0 CHECK (tiros_totales >= 0),
tiros_al_arco SMALLINT NOT NULL DEFAULT 0 CHECK (tiros_al_arco >= 0),
faltas_cometidas SMALLINT NOT NULL DEFAULT 0 CHECK (faltas_cometidas >= 0),
faltas_recibidas SMALLINT NOT NULL DEFAULT 0 CHECK (faltas_recibidas >= 0),
pases_totales SMALLINT NOT NULL DEFAULT 0 CHECK (pases_totales >= 0),
precision_pase_pct DECIMAL(5,2) CHECK (precision_pase_pct BETWEEN 0 AND 100),
atajadas SMALLINT NOT NULL DEFAULT 0 CHECK (atajadas >= 0),
PRIMARY KEY (partido_id, jugador_id),
CHECK (tiros_al_arco <= tiros_totales),
CONSTRAINT fk_ejp_alineacion FOREIGN KEY (partido_id, jugador_id)
REFERENCES alineacion_partido(partido_id, jugador_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DELIMITER $$
DROP FUNCTION IF EXISTS fn_es_equipo_de_partido$$
CREATE FUNCTION fn_es_equipo_de_partido(p_partido_id BIGINT, p_seleccion_id BIGINT)
RETURNS BOOLEAN
READS SQL DATA
BEGIN
DECLARE v_existe INT DEFAULT 0;
SELECT COUNT(*) INTO v_existe
FROM partido p
WHERE p.partido_id = p_partido_id
AND p_seleccion_id IN (p.seleccion_a_id, p.seleccion_b_id);
RETURN v_existe > 0;
END$$
DROP PROCEDURE IF EXISTS sp_validar_marcador_vs_goles$$
CREATE PROCEDURE sp_validar_marcador_vs_goles(IN p_partido_id BIGINT)
READS SQL DATA
BEGIN
DECLARE v_sel_a BIGINT;
DECLARE v_sel_b BIGINT;
DECLARE v_estado VARCHAR(20);
DECLARE v_reg_a SMALLINT;
DECLARE v_reg_b SMALLINT;
DECLARE v_goles_a SMALLINT DEFAULT 0;
DECLARE v_goles_b SMALLINT DEFAULT 0;
SELECT seleccion_a_id, seleccion_b_id, estado, goles_a, goles_b
INTO v_sel_a, v_sel_b, v_estado, v_reg_a, v_reg_b
FROM partido
WHERE partido_id = p_partido_id;
IF v_estado = 'FINALIZADO' THEN
SELECT COUNT(*) INTO v_goles_a FROM gol WHERE partido_id = p_partido_id AND seleccion_id = v_sel_a;
SELECT COUNT(*) INTO v_goles_b FROM gol WHERE partido_id = p_partido_id AND seleccion_id = v_sel_b;
IF NOT (v_reg_a <=> v_goles_a) OR NOT (v_reg_b <=> v_goles_b) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'El marcador oficial no coincide con el detalle de goles';
END IF;
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_partido_bi$$
CREATE TRIGGER trg_validar_partido_bi
BEFORE INSERT ON partido
FOR EACH ROW
BEGIN
DECLARE v_fase_codigo VARCHAR(20);
DECLARE v_es_eliminatoria BOOLEAN;
DECLARE v_grupo_a BIGINT;
DECLARE v_grupo_b BIGINT;
DECLARE v_traslapes INT DEFAULT 0;
SELECT COUNT(*) INTO v_traslapes
FROM partido p
WHERE p.estadio_id = NEW.estadio_id
AND NEW.fecha_hora_inicio < p.fecha_hora_fin
AND NEW.fecha_hora_fin > p.fecha_hora_inicio;
IF v_traslapes > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El estadio ya tiene un partido en ese horario';
END IF;
SELECT codigo, es_eliminatoria
INTO v_fase_codigo, v_es_eliminatoria
FROM fase
WHERE fase_id = NEW.fase_id AND torneo_id = NEW.torneo_id;
IF v_fase_codigo IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La fase no pertenece al torneo';
END IF;
IF NEW.ganador_id IS NOT NULL AND NEW.ganador_id NOT IN (NEW.seleccion_a_id, NEW.seleccion_b_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador debe ser uno de los dos equipos del partido';
END IF;
IF v_fase_codigo = 'GRUPOS' THEN
IF NEW.grupo_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un partido de grupos debe tener grupo_id';
END IF;
SELECT grupo_id INTO v_grupo_a FROM seleccion WHERE seleccion_id = NEW.seleccion_a_id;
SELECT grupo_id INTO v_grupo_b FROM seleccion WHERE seleccion_id = NEW.seleccion_b_id;
IF NOT (v_grupo_a <=> NEW.grupo_id) OR NOT (v_grupo_b <=> NEW.grupo_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'En fase de grupos, ambos equipos deben pertenecer al grupo del partido';
END IF;
ELSE
IF NEW.grupo_id IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Las fases eliminatorias no deben tener grupo_id';
END IF;
END IF;
IF NEW.estado = 'FINALIZADO' THEN
IF NEW.goles_a IS NULL OR NEW.goles_b IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un partido finalizado debe tener marcador oficial';
END IF;
IF v_es_eliminatoria THEN
IF NEW.ganador_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'En eliminación directa debe existir ganador';
END IF;
IF NEW.goles_a = NEW.goles_b THEN
IF NOT NEW.hubo_penales OR NEW.penales_a IS NULL OR NEW.penales_b IS NULL OR NEW.penales_a = NEW.penales_b THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si el marcador queda empatado en eliminación, debe existir tanda de penales válida';
END IF;
IF NOT NEW.hubo_tiempo_extra THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si hubo penales, se espera también registro de tiempo extra';
END IF;
IF (NEW.penales_a > NEW.penales_b AND NEW.ganador_id <> NEW.seleccion_a_id)
OR (NEW.penales_b > NEW.penales_a AND NEW.ganador_id <> NEW.seleccion_b_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador no coincide con la tanda de penales';
END IF;
ELSE
IF NEW.hubo_penales OR NEW.penales_a IS NOT NULL OR NEW.penales_b IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No debe haber penales si el partido eliminatorio no terminó empatado';
END IF;
IF (NEW.goles_a > NEW.goles_b AND NEW.ganador_id <> NEW.seleccion_a_id)
OR (NEW.goles_b > NEW.goles_a AND NEW.ganador_id <> NEW.seleccion_b_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador no coincide con el marcador oficial';
END IF;
END IF;
ELSE
IF NEW.hubo_tiempo_extra OR NEW.hubo_penales OR NEW.penales_a IS NOT NULL OR NEW.penales_b IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La fase de grupos no debe registrar tiempo extra ni penales';
END IF;
IF NEW.goles_a = NEW.goles_b THEN
IF NEW.ganador_id IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un empate en fase de grupos no debe tener ganador';
END IF;
ELSE
IF (NEW.goles_a > NEW.goles_b AND NOT (NEW.ganador_id <=> NEW.seleccion_a_id))
OR (NEW.goles_b > NEW.goles_a AND NOT (NEW.ganador_id <=> NEW.seleccion_b_id)) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador no coincide con el marcador del partido de grupos';
END IF;
END IF;
END IF;
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_partido_bu$$
CREATE TRIGGER trg_validar_partido_bu
BEFORE UPDATE ON partido
FOR EACH ROW
BEGIN
DECLARE v_fase_codigo VARCHAR(20);
DECLARE v_es_eliminatoria BOOLEAN;
DECLARE v_grupo_a BIGINT;
DECLARE v_grupo_b BIGINT;
DECLARE v_traslapes INT DEFAULT 0;
SELECT COUNT(*) INTO v_traslapes
FROM partido p
WHERE p.partido_id <> OLD.partido_id
AND p.estadio_id = NEW.estadio_id
AND NEW.fecha_hora_inicio < p.fecha_hora_fin
AND NEW.fecha_hora_fin > p.fecha_hora_inicio;
IF v_traslapes > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El estadio ya tiene un partido en ese horario';
END IF;
SELECT codigo, es_eliminatoria
INTO v_fase_codigo, v_es_eliminatoria
FROM fase
WHERE fase_id = NEW.fase_id AND torneo_id = NEW.torneo_id;
IF v_fase_codigo IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La fase no pertenece al torneo';
END IF;
IF NEW.ganador_id IS NOT NULL AND NEW.ganador_id NOT IN (NEW.seleccion_a_id, NEW.seleccion_b_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador debe ser uno de los dos equipos del partido';
END IF;
IF v_fase_codigo = 'GRUPOS' THEN
IF NEW.grupo_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un partido de grupos debe tener grupo_id';
END IF;
SELECT grupo_id INTO v_grupo_a FROM seleccion WHERE seleccion_id = NEW.seleccion_a_id;
SELECT grupo_id INTO v_grupo_b FROM seleccion WHERE seleccion_id = NEW.seleccion_b_id;
IF NOT (v_grupo_a <=> NEW.grupo_id) OR NOT (v_grupo_b <=> NEW.grupo_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'En fase de grupos, ambos equipos deben pertenecer al grupo del partido';
END IF;
ELSE
IF NEW.grupo_id IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Las fases eliminatorias no deben tener grupo_id';
END IF;
END IF;
IF NEW.estado = 'FINALIZADO' THEN
IF NEW.goles_a IS NULL OR NEW.goles_b IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un partido finalizado debe tener marcador oficial';
END IF;
IF v_es_eliminatoria THEN
IF NEW.ganador_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'En eliminación directa debe existir ganador';
END IF;
IF NEW.goles_a = NEW.goles_b THEN
IF NOT NEW.hubo_penales OR NEW.penales_a IS NULL OR NEW.penales_b IS NULL OR NEW.penales_a = NEW.penales_b THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si el marcador queda empatado en eliminación, debe existir tanda de penales válida';
END IF;
IF NOT NEW.hubo_tiempo_extra THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si hubo penales, se espera también registro de tiempo extra';
END IF;
IF (NEW.penales_a > NEW.penales_b AND NEW.ganador_id <> NEW.seleccion_a_id)
OR (NEW.penales_b > NEW.penales_a AND NEW.ganador_id <> NEW.seleccion_b_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador no coincide con la tanda de penales';
END IF;
ELSE
IF NEW.hubo_penales OR NEW.penales_a IS NOT NULL OR NEW.penales_b IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No debe haber penales si el partido eliminatorio no terminó empatado';
END IF;
IF (NEW.goles_a > NEW.goles_b AND NEW.ganador_id <> NEW.seleccion_a_id)
OR (NEW.goles_b > NEW.goles_a AND NEW.ganador_id <> NEW.seleccion_b_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador no coincide con el marcador oficial';
END IF;
END IF;
ELSE
IF NEW.hubo_tiempo_extra OR NEW.hubo_penales OR NEW.penales_a IS NOT NULL OR NEW.penales_b IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La fase de grupos no debe registrar tiempo extra ni penales';
END IF;
IF NEW.goles_a = NEW.goles_b THEN
IF NEW.ganador_id IS NOT NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un empate en fase de grupos no debe tener ganador';
END IF;
ELSE
IF (NEW.goles_a > NEW.goles_b AND NOT (NEW.ganador_id <=> NEW.seleccion_a_id))
OR (NEW.goles_b > NEW.goles_a AND NOT (NEW.ganador_id <=> NEW.seleccion_b_id)) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ganador no coincide con el marcador del partido de grupos';
END IF;
END IF;
END IF;
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_alineacion_bi$$
CREATE TRIGGER trg_validar_alineacion_bi
BEFORE INSERT ON alineacion_partido
FOR EACH ROW
BEGIN
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección no participa en el partido';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_alineacion_bu$$
CREATE TRIGGER trg_validar_alineacion_bu
BEFORE UPDATE ON alineacion_partido
FOR EACH ROW
BEGIN
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección no participa en el partido';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_gol_bi$$
CREATE TRIGGER trg_validar_gol_bi
BEFORE INSERT ON gol
FOR EACH ROW
BEGIN
DECLARE v_torneo_id BIGINT;
DECLARE v_sel_a BIGINT;
DECLARE v_sel_b BIGINT;
DECLARE v_sel_jugador BIGINT;
DECLARE v_oponente BIGINT;
SELECT torneo_id, seleccion_a_id, seleccion_b_id
INTO v_torneo_id, v_sel_a, v_sel_b
FROM partido
WHERE partido_id = NEW.partido_id;
IF v_torneo_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Partido inexistente';
END IF;
IF NEW.seleccion_id NOT IN (v_sel_a, v_sel_b) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección del gol no participa en el partido';
END IF;
SELECT c.seleccion_id INTO v_sel_jugador
FROM convocatoria c
WHERE c.torneo_id = v_torneo_id AND c.jugador_id = NEW.jugador_id
LIMIT 1;
IF v_sel_jugador IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador no está convocado en el torneo';
END IF;
IF v_sel_jugador NOT IN (v_sel_a, v_sel_b) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador no pertenece a ninguna de las selecciones del partido';
END IF;
SET v_oponente = CASE WHEN v_sel_jugador = v_sel_a THEN v_sel_b ELSE v_sel_a END;
IF NOT NEW.es_autogol AND NEW.seleccion_id <> v_sel_jugador THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si no es autogol, la selección acreditada debe ser la del jugador';
END IF;
IF NEW.es_autogol AND NEW.seleccion_id <> v_oponente THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si es autogol, la selección acreditada debe ser la rival';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_gol_bu$$
CREATE TRIGGER trg_validar_gol_bu
BEFORE UPDATE ON gol
FOR EACH ROW
BEGIN
DECLARE v_torneo_id BIGINT;
DECLARE v_sel_a BIGINT;
DECLARE v_sel_b BIGINT;
DECLARE v_sel_jugador BIGINT;
DECLARE v_oponente BIGINT;
SELECT torneo_id, seleccion_a_id, seleccion_b_id
INTO v_torneo_id, v_sel_a, v_sel_b
FROM partido
WHERE partido_id = NEW.partido_id;
IF v_torneo_id IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Partido inexistente';
END IF;
IF NEW.seleccion_id NOT IN (v_sel_a, v_sel_b) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección del gol no participa en el partido';
END IF;
SELECT c.seleccion_id INTO v_sel_jugador
FROM convocatoria c
WHERE c.torneo_id = v_torneo_id AND c.jugador_id = NEW.jugador_id
LIMIT 1;
IF v_sel_jugador IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador no está convocado en el torneo';
END IF;
IF v_sel_jugador NOT IN (v_sel_a, v_sel_b) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador no pertenece a ninguna de las selecciones del partido';
END IF;
SET v_oponente = CASE WHEN v_sel_jugador = v_sel_a THEN v_sel_b ELSE v_sel_a END;
IF NOT NEW.es_autogol AND NEW.seleccion_id <> v_sel_jugador THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si no es autogol, la selección acreditada debe ser la del jugador';
END IF;
IF NEW.es_autogol AND NEW.seleccion_id <> v_oponente THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Si es autogol, la selección acreditada debe ser la rival';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_tarjeta_bi$$
CREATE TRIGGER trg_validar_tarjeta_bi
BEFORE INSERT ON tarjeta
FOR EACH ROW
BEGIN
DECLARE v_torneo_id BIGINT;
DECLARE v_existe INT DEFAULT 0;
SELECT torneo_id INTO v_torneo_id FROM partido WHERE partido_id = NEW.partido_id;
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección no participa en el partido';
END IF;
SELECT COUNT(*) INTO v_existe
FROM convocatoria c
WHERE c.torneo_id = v_torneo_id
AND c.seleccion_id = NEW.seleccion_id
AND c.jugador_id = NEW.jugador_id;
IF v_existe = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La tarjeta debe corresponder a un jugador de la misma selección';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_tarjeta_bu$$
CREATE TRIGGER trg_validar_tarjeta_bu
BEFORE UPDATE ON tarjeta
FOR EACH ROW
BEGIN
DECLARE v_torneo_id BIGINT;
DECLARE v_existe INT DEFAULT 0;
SELECT torneo_id INTO v_torneo_id FROM partido WHERE partido_id = NEW.partido_id;
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección no participa en el partido';
END IF;
SELECT COUNT(*) INTO v_existe
FROM convocatoria c
WHERE c.torneo_id = v_torneo_id
AND c.seleccion_id = NEW.seleccion_id
AND c.jugador_id = NEW.jugador_id;
IF v_existe = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La tarjeta debe corresponder a un jugador de la misma selección';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_cambio_bi$$
CREATE TRIGGER trg_validar_cambio_bi
BEFORE INSERT ON cambio
FOR EACH ROW
BEGIN
DECLARE v_torneo_id BIGINT;
DECLARE v_sale INT DEFAULT 0;
DECLARE v_entra INT DEFAULT 0;
SELECT torneo_id INTO v_torneo_id FROM partido WHERE partido_id = NEW.partido_id;
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección no participa en el partido';
END IF;
SELECT COUNT(*) INTO v_sale FROM convocatoria c
WHERE c.torneo_id = v_torneo_id AND c.seleccion_id = NEW.seleccion_id AND c.jugador_id = NEW.jugador_sale_id;
SELECT COUNT(*) INTO v_entra FROM convocatoria c
WHERE c.torneo_id = v_torneo_id AND c.seleccion_id = NEW.seleccion_id AND c.jugador_id = NEW.jugador_entra_id;
IF v_sale = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador que sale no pertenece a la selección indicada';
END IF;
IF v_entra = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador que entra no pertenece a la selección indicada';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_cambio_bu$$
CREATE TRIGGER trg_validar_cambio_bu
BEFORE UPDATE ON cambio
FOR EACH ROW
BEGIN
DECLARE v_torneo_id BIGINT;
DECLARE v_sale INT DEFAULT 0;
DECLARE v_entra INT DEFAULT 0;
SELECT torneo_id INTO v_torneo_id FROM partido WHERE partido_id = NEW.partido_id;
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La selección no participa en el partido';
END IF;
SELECT COUNT(*) INTO v_sale FROM convocatoria c
WHERE c.torneo_id = v_torneo_id AND c.seleccion_id = NEW.seleccion_id AND c.jugador_id = NEW.jugador_sale_id;
SELECT COUNT(*) INTO v_entra FROM convocatoria c
WHERE c.torneo_id = v_torneo_id AND c.seleccion_id = NEW.seleccion_id AND c.jugador_id = NEW.jugador_entra_id;
IF v_sale = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador que sale no pertenece a la selección indicada';
END IF;
IF v_entra = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El jugador que entra no pertenece a la selección indicada';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_estadistica_equipo_bi$$
CREATE TRIGGER trg_validar_estadistica_equipo_bi
BEFORE INSERT ON estadistica_equipo_partido
FOR EACH ROW
BEGIN
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La estadística de equipo debe corresponder a uno de los equipos del partido';
END IF;
END$$
DROP TRIGGER IF EXISTS trg_validar_estadistica_equipo_bu$$
CREATE TRIGGER trg_validar_estadistica_equipo_bu
BEFORE UPDATE ON estadistica_equipo_partido
FOR EACH ROW
BEGIN
IF NOT fn_es_equipo_de_partido(NEW.partido_id, NEW.seleccion_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La estadística de equipo debe corresponder a uno de los equipos del partido';
END IF;
END$$
DELIMITER ;
CREATE VIEW vw_tabla_grupos AS
WITH base AS (
SELECT
p.torneo_id,
p.grupo_id,
p.seleccion_a_id AS seleccion_id,
1 AS pj,
CASE WHEN p.goles_a > p.goles_b THEN 1 ELSE 0 END AS pg,
CASE WHEN p.goles_a = p.goles_b THEN 1 ELSE 0 END AS pe,
CASE WHEN p.goles_a < p.goles_b THEN 1 ELSE 0 END AS pp,
p.goles_a AS gf,
p.goles_b AS gc,
CASE
WHEN p.goles_a > p.goles_b THEN 3
WHEN p.goles_a = p.goles_b THEN 1
ELSE 0
END AS pts
FROM partido p
JOIN fase f ON f.fase_id = p.fase_id AND f.torneo_id = p.torneo_id
WHERE f.codigo = 'GRUPOS' AND p.estado = 'FINALIZADO'
UNION ALL
SELECT
p.torneo_id,
p.grupo_id,
p.seleccion_b_id AS seleccion_id,
1 AS pj,
CASE WHEN p.goles_b > p.goles_a THEN 1 ELSE 0 END AS pg,
CASE WHEN p.goles_b = p.goles_a THEN 1 ELSE 0 END AS pe,
CASE WHEN p.goles_b < p.goles_a THEN 1 ELSE 0 END AS pp,
p.goles_b AS gf,
p.goles_a AS gc,
CASE
WHEN p.goles_b > p.goles_a THEN 3
WHEN p.goles_b = p.goles_a THEN 1
ELSE 0
END AS pts
FROM partido p
JOIN fase f ON f.fase_id = p.fase_id AND f.torneo_id = p.torneo_id
WHERE f.codigo = 'GRUPOS' AND p.estado = 'FINALIZADO'
),
agg AS (
SELECT
torneo_id,
grupo_id,
seleccion_id,
SUM(pj) AS pj,
SUM(pg) AS pg,
SUM(pe) AS pe,
SUM(pp) AS pp,
SUM(gf) AS gf,
SUM(gc) AS gc,
SUM(gf) - SUM(gc) AS dg,
SUM(pts) AS pts
FROM base
GROUP BY torneo_id, grupo_id, seleccion_id
)
SELECT
a.torneo_id,
g.codigo AS grupo,
s.seleccion_id,
s.nombre_corto AS seleccion,
a.pj, a.pg, a.pe, a.pp, a.gf, a.gc, a.dg, a.pts,
ROW_NUMBER() OVER (
PARTITION BY a.torneo_id, a.grupo_id
ORDER BY a.pts DESC, a.dg DESC, a.gf DESC, s.nombre_corto
) AS posicion
FROM agg a
JOIN grupo g ON g.grupo_id = a.grupo_id
JOIN seleccion s ON s.seleccion_id = a.seleccion_id;
CREATE VIEW vw_goleadores AS
SELECT
p.torneo_id,
j.jugador_id,
CONCAT(j.nombres, ' ', j.apellidos) AS jugador,
s.nombre_corto AS seleccion,
COUNT(*) AS goles
FROM gol gl
JOIN partido p ON p.partido_id = gl.partido_id
JOIN jugador j ON j.jugador_id = gl.jugador_id
JOIN seleccion s ON s.seleccion_id = gl.seleccion_id
GROUP BY p.torneo_id, j.jugador_id, j.nombres, j.apellidos, s.nombre_corto;
CREATE VIEW vw_resumen_selecciones_torneo AS
WITH base AS (
SELECT
p.torneo_id,
p.seleccion_a_id AS seleccion_id,
1 AS pj,
CASE WHEN p.goles_a > p.goles_b THEN 1 ELSE 0 END AS pg,
CASE WHEN p.goles_a = p.goles_b THEN 1 ELSE 0 END AS pe,
CASE WHEN p.goles_a < p.goles_b THEN 1 ELSE 0 END AS pp,
p.goles_a AS gf,
p.goles_b AS gc
FROM partido p
WHERE p.estado = 'FINALIZADO'
UNION ALL
SELECT
p.torneo_id,
p.seleccion_b_id AS seleccion_id,
1 AS pj,
CASE WHEN p.goles_b > p.goles_a THEN 1 ELSE 0 END AS pg,
CASE WHEN p.goles_b = p.goles_a THEN 1 ELSE 0 END AS pe,
CASE WHEN p.goles_b < p.goles_a THEN 1 ELSE 0 END AS pp,
p.goles_b AS gf,
p.goles_a AS gc
FROM partido p
WHERE p.estado = 'FINALIZADO'
)
SELECT
b.torneo_id,
s.seleccion_id,
s.nombre_corto AS seleccion,
SUM(b.pj) AS partidos_jugados,
SUM(b.pg) AS partidos_ganados,
SUM(b.pe) AS partidos_empatados,
SUM(b.pp) AS partidos_perdidos,
SUM(b.gf) AS goles_a_favor,
SUM(b.gc) AS goles_en_contra,
SUM(b.gf) - SUM(b.gc) AS diferencia_goles
FROM base b
JOIN seleccion s ON s.seleccion_id = b.seleccion_id
GROUP BY b.torneo_id, s.seleccion_id, s.nombre_corto;
CREATE VIEW vw_estadisticas_generales_torneo AS
SELECT
p.torneo_id,
SUM(CASE WHEN p.estado = 'FINALIZADO' THEN 1 ELSE 0 END) AS partidos_finalizados,
COALESCE(SUM(COALESCE(p.goles_a,0) + COALESCE(p.goles_b,0)), 0) AS goles_totales,
ROUND(
AVG(CASE WHEN p.estado = 'FINALIZADO' THEN COALESCE(p.goles_a,0) + COALESCE(p.goles_b,0) ELSE NULL END),
2
) AS promedio_goles_por_partido,
SUM(CASE WHEN t.tipo_tarjeta = 'AMARILLA' THEN 1 ELSE 0 END) AS tarjetas_amarillas,
SUM(CASE WHEN t.tipo_tarjeta = 'ROJA' THEN 1 ELSE 0 END) AS tarjetas_rojas
FROM partido p
LEFT JOIN tarjeta t ON t.partido_id = p.partido_id
GROUP BY p.torneo_id;
CREATE VIEW vw_campeon AS
SELECT
p.torneo_id,
s.seleccion_id,
s.nombre_corto AS campeon
FROM partido p
JOIN fase f ON f.fase_id = p.fase_id AND f.torneo_id = p.torneo_id
JOIN seleccion s ON s.seleccion_id = p.ganador_id
WHERE f.codigo = 'FINAL'
AND p.estado = 'FINALIZADO';