-- 1
DELIMITER $$
CREATE FUNCTION FN_JUGADOR_EQUIPO (id_jugador INT)
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
	DECLARE nombre_equipo VARCHAR(100);
    SELECT e.nombre_equipo
    INTO nombre_equipo
    FROM jugadores j
    JOIN Equipos e
		ON j.codigo_equipo = e.codigo_equipo
	WHERE j.codigo_jugador = id_jugador;
    RETURN nombre_equipo;
END $$
DELIMITER ;

-- 2
DELIMITER $$
CREATE PROCEDURE ListadoEquiposJugadores()
BEGIN
	SELECT e.nombre_equipo, j.nombre
    FROM Equipos e
    JOIN Jugadores j
		ON e.codigo_equipo = j.codigo_equipo
	ORDER BY e.nombre_equipo ASC, j.nombre ASC;
END $$
DELIMITER ;

-- 3
DELIMITER $$
CREATE PROCEDURE ClasificacionLiga()
BEGIN 
	SELECT e.nombre_equipo,
		SUM(
			CASE
				WHEN (e.codigo_equipo = p.equipo_local AND p.goles_local > p.goles_visitante) OR (e.codigo_equipo = p.equipo_visitante AND p.goles_visitante > p.goles_local) THEN 3
                WHEN p.goles_local = p.goles_visitante THEN 1
                ELSE 0
			END
		) AS puntos
	FROM Equipos e
	JOIN Partidos p 
		ON e.codigo_equipo = p.equipo_local OR e.codigo_equipo = p.equipo_visitante
	GROUP BY e.codigo_equipo
    ORDER BY puntos DESC;
END $$
DELIMITER ;

-- 4
DELIMITER $$
CREATE FUNCTION FN_Equipo_Ganador()
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
    DECLARE equipo_ganador VARCHAR(100);
    SELECT nombre_equipo INTO equipo_ganador
    FROM (
        SELECT e.nombre_equipo,
            SUM(
                CASE 
                    WHEN (e.codigo_equipo = p.equipo_local AND p.goles_local > p.goles_visitante) OR (e.codigo_equipo = p.equipo_visitante AND p.goles_visitante > p.goles_local) THEN 3
                    WHEN p.goles_local = p.goles_visitante THEN 1
                    ELSE 0
                END
            ) AS puntos
        FROM Equipos e
        LEFT JOIN Partidos p ON e.codigo_equipo = p.equipo_local OR e.codigo_equipo = p.equipo_visitante
        GROUP BY e.codigo_equipo
        ORDER BY puntos DESC
        LIMIT 1
    ) AS clasificacion;
    RETURN equipo_ganador;
END $$
DELIMITER ;

-- 5
DELIMITER $$
CREATE PROCEDURE ListadoCiudadesEstadios()
BEGIN
	SELECT ciudad, nombre_estadio
    FROM Equipos
    ORDER BY ciudad;
END $$
DELIMITER ;

-- 6
SELECT ciudad,
	CASE
		WHEN COUNT(nombre_estadio) = 0 THEN 'Sin estadios'
        ELSE COUNT(nombre_estadio)
	END AS estadios
FROM Equipos
GROUP BY ciudad;

-- 7 
DELIMITER $$
CREATE PROCEDURE JugadorMaxMinGOles(IN id_equipo INT)
BEGIN
	SELECT j.nombre AS jugador_max_goles, COUNT(g.codigo_gol) AS goles
	FROM Jugadores j
    JOIN GOles g
		ON j.codigo_jugador = g.codigo_jugador
	WHERE j.codigo_equipo = id_equipo 
    GROUP BY j.codigo_jugador
    ORDER BY goles DESC
    LIMIT 1;
    
    SELECT j.nombre AS jugador_min_goles, COUNT(g.codigo_gol) as goles
	FROM Jugadores j
    JOIN Goles g 
		ON j.codigo_jugador = g.codigo_jugador
	WHERE j.codigo_equipo = id_equipo
    GROUP BY j.codigo_jugador
    ORDER BY goles ASC
    LIMIT 1;
END $$
DELIMITER ;