Procedures.sql

-- PROCEDIMIENTO 1: Análisis completo de rendimiento de piloto por temporada
-- Propósito: Genera estadísticas detalladas de un piloto en una temporada específica
DELIMITER $$
CREATE OR REPLACE PROCEDURE sp_analisis_piloto_temp(
	IN p_driver_id INT,
	IN p_year INT
)
BEGIN
	DECLARE v_total_races INT DEFAULT 0;
	DECLARE v_championship_position INT DEFAULT 0;
	DECLARE v_driver_fullname VARCHAR(100) DEFAULT '';
	
	SELECT COUNT(ra.raceId) INTO v_total_races
	FROM races ra
	JOIN results r ON ra.raceId = r.raceId
	AND r.driverId = p_driver_id
	AND ra.`year` = p_year;
	
	IF v_total_races = 0 THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El piloto no participó en esa temporada';
	END IF;
	
	DROP TEMPORARY TABLE IF EXISTS tbt_analisis_piloto_temp;
	CREATE TEMPORARY TABLE tbt_analisis_piloto_temp (
		race_name VARCHAR(50),
		circuit_name VARCHAR(50),
		laps_completed INT,
		grid_position INT,
		final_position INT,
		points_earned DECIMAL(8,2),
		end_status VARCHAR(50)
	);
	
	INSERT INTO tbt_analisis_piloto_temp
	SELECT ra.`name`, c.`name`, r.laps, r.grid, r.`position`, r.points, s.`status`
	FROM results r
	JOIN races ra ON r.raceId = ra.raceId
	JOIN circuits c ON ra.circuitId = c.circuitId
	JOIN `status` s ON r.statusId = s.statusId
	WHERE r.driverId = p_driver_id
	AND ra.`year` = p_year
	ORDER BY ra.`round`;
	
	SELECT CONCAT(d.forename, ' ', d.surname), ds.`position` 
	INTO v_driver_fullname, v_championship_position
	FROM driver_standings ds
	JOIN races ra ON ds.raceId = ra.raceId
	JOIN drivers d ON ds.driverId = d.driverId
	WHERE ds.driverId = p_driver_id AND ra.`year` = p_year
	ORDER BY ra.round DESC
	LIMIT 1;
	
	SELECT 
        v_driver_fullname AS piloto,
        v_total_races as carreras_disputadas,
        v_championship_position as posicion_campeonato,
        SUM(points_earned) as puntos_totales,
        COUNT(CASE WHEN final_position = 1 THEN 1 END) as victorias,
        COUNT(CASE WHEN final_position BETWEEN 1 AND 3 THEN 1 END) as podios,
        COUNT(CASE WHEN final_position <= 10 THEN 1 END) as puntos_finish,
        AVG(grid_position) as promedio_salida,
        AVG(final_position) as promedio_llegada,
        COUNT(CASE WHEN end_status = 'Finished' THEN 1 END) as Crs_terminadas
    FROM tbt_analisis_piloto_temp;
    
    SELECT * FROM tbt_analisis_piloto_temp;
	
	DROP TEMPORARY TABLE tbt_analisis_piloto_temp;
END$$

CALL sp_analisis_piloto_temp(1, 2009);


-- obtener el podio de una carrera
DELIMITER //

CREATE FUNCTION ObtenerPodio(
    p_raceId INT
) RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE podio VARCHAR(255);
    
    SELECT GROUP_CONCAT(
        CONCAT(d.forename, ' ', d.surname) 
        ORDER BY r.positionOrder SEPARATOR ', '
    ) INTO podio
    FROM results r
    JOIN drivers d ON r.driverId = d.driverId
    WHERE r.raceId = p_raceId
    AND r.positionOrder BETWEEN 1 AND 3;
    
    RETURN podio;
END//
DELIMITER ;

-- 3. FUNCIÓN: Calcular diferencia de tiempo con el ganador
-- PROPÓSITO: Automatizar el cálculo de la diferencia en milisegundos entre un piloto y el ganador de una carrera
DELIMITER //
CREATE FUNCTION calcular_diferencia_ganador(
    p_race_id INT,
    p_driver_id INT
) RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE tiempo_ganador INT DEFAULT 0;
    DECLARE tiempo_piloto INT DEFAULT 0;
    DECLARE diferencia INT DEFAULT 0;
    
    SELECT COALESCE(r.miliseconds, 0)
    INTO tiempo_ganador
    FROM results r
    WHERE r.raceId = p_race_id 
    AND r.position = 1
    LIMIT 1;
   	
    SELECT COALESCE(r.miliseconds, 0)
    INTO tiempo_piloto
    FROM results r
    WHERE r.raceId = p_race_id 
    AND r.driverId = p_driver_id
    LIMIT 1;
    
    IF tiempo_ganador > 0 AND tiempo_piloto > 0 THEN
        SET diferencia = tiempo_piloto - tiempo_ganador;
    ELSE
        SET diferencia = -1;
    END IF;
    
    RETURN diferencia;
END //
DELIMITER ;