plsql.sql

-- ==============================================
-- PROCEDIMIENTOS ALMACENADOS (3)
-- ==============================================

-- 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 PROCEDURE sp_AnalisisRendimientoPiloto(
    IN p_driver_id INT,
    IN p_year INT
)
BEGIN
    DECLARE v_total_races INT DEFAULT 0;
    DECLARE v_points_total DECIMAL(8,2) DEFAULT 0;
    DECLARE v_championship_position INT DEFAULT 0;
    
    -- Validar que el piloto participó en esa temporada
    SELECT COUNT(*) INTO v_total_races
    FROM results r 
    JOIN races ra ON r.raceId = ra.raceId 
    WHERE r.driverId = p_driver_id AND ra.year = p_year;
    
    IF v_total_races = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Piloto no participó en esa temporada';
    END IF;
    
    -- Crear tabla temporal con resultados detallados
    DROP TEMPORARY TABLE IF EXISTS temp_driver_analysis;
    CREATE TEMPORARY TABLE temp_driver_analysis (
        race_name VARCHAR(100),
        circuit_name VARCHAR(100),
        grid_position INT,
        final_position INT,
        points_earned DECIMAL(8,2),
        fastest_lap_time VARCHAR(20),
        laps_completed INT,
        status VARCHAR(50)
    );
    
    -- Insertar datos detallados
    INSERT INTO temp_driver_analysis
    SELECT 
        ra.name as race_name,
        c.name as circuit_name,
        r.grid as grid_position,
        r.position as final_position,
        r.points as points_earned,
        r.fastestLapTime as fastest_lap_time,
        r.laps as laps_completed,
        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;
    
    -- Obtener posición final en campeonato
    SELECT ds.position INTO v_championship_position
    FROM driver_standings ds
    JOIN races ra ON ds.raceId = ra.raceId
    WHERE ds.driverId = p_driver_id AND ra.year = p_year
    ORDER BY ra.round DESC
    LIMIT 1;
    
    -- Resultados principales
    SELECT 
        CONCAT((SELECT CONCAT(forename, ' ', surname) FROM drivers WHERE driverId = p_driver_id), 
               ' - Temporada ', p_year) as piloto_temporada,
        v_total_races as carreras_disputadas,
        SUM(points_earned) as puntos_totales,
        v_championship_position as posicion_campeonato,
        COUNT(CASE WHEN final_position = 1 THEN 1 END) as victorias,
        COUNT(CASE WHEN final_position <= 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(CASE WHEN final_position IS NOT NULL THEN final_position END) as promedio_llegada,
        COUNT(CASE WHEN status != 'Finished' THEN 1 END) as abandonos
    FROM temp_driver_analysis;
    
    -- Detalle carrera por carrera
    SELECT * FROM temp_driver_analysis;
    
    DROP TEMPORARY TABLE temp_driver_analysis;
END$$

-- PROCEDIMIENTO 2: Gestión inteligente de pit stops con análisis de estrategia
-- Propósito: Analiza y compara estrategias de pit stops en una carrera específica
CREATE PROCEDURE sp_AnalisisEstrategiaPitStops(
    IN p_race_id INT
)
BEGIN
    DECLARE v_race_name VARCHAR(100);
    DECLARE v_total_drivers INT DEFAULT 0;
    
    -- Validar que la carrera existe y tiene datos de pit stops
    SELECT ra.name, COUNT(DISTINCT ps.driverId) 
    INTO v_race_name, v_total_drivers
    FROM races ra
    LEFT JOIN pit_stops ps ON ra.raceId = ps.raceId
    WHERE ra.raceId = p_race_id
    GROUP BY ra.name;
    
    IF v_total_drivers = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No hay datos de pit stops para esta carrera';
    END IF;
    
    -- Análisis por número de paradas
    SELECT 
        'RESUMEN POR ESTRATEGIA' as analisis_tipo,
        num_stops as numero_paradas,
        COUNT(*) as pilotos_con_estrategia,
        AVG(final_position) as posicion_promedio_final,
        MIN(final_position) as mejor_posicion,
        MAX(final_position) as peor_posicion,
        AVG(total_pit_time) as tiempo_promedio_pits
    FROM (
        SELECT 
            ps.driverId,
            COUNT(ps.stop) as num_stops,
            r.position as final_position,
            SUM(ps.milliseconds) as total_pit_time
        FROM pit_stops ps
        JOIN results r ON ps.raceId = r.raceId AND ps.driverId = r.driverId
        WHERE ps.raceId = p_race_id AND r.position IS NOT NULL
        GROUP BY ps.driverId, r.position
    ) strategy_summary
    GROUP BY num_stops
    ORDER BY numero_paradas;
    
    -- Top 10 mejores y peores pit stops
    (SELECT 
        'MEJORES PIT STOPS' as tipo,
        CONCAT(d.forename, ' ', d.surname) as piloto,
        con.name as constructor,
        ps.stop as numero_parada,
        ps.lap as vuelta,
        ps.duration as duracion,
        ps.milliseconds as milisegundos
    FROM pit_stops ps
    JOIN drivers d ON ps.driverId = d.driverId
    JOIN results r ON ps.raceId = r.raceId AND ps.driverId = r.driverId
    JOIN constructors con ON r.constructorId = con.constructorId
    WHERE ps.raceId = p_race_id
    ORDER BY ps.milliseconds ASC
    LIMIT 10)
    UNION ALL
    (SELECT 
        'PEORES PIT STOPS' as tipo,
        CONCAT(d.forename, ' ', d.surname) as piloto,
        con.name as constructor,
        ps.stop as numero_parada,
        ps.lap as vuelta,
        ps.duration as duracion,
        ps.milliseconds as milisegundos
    FROM pit_stops ps
    JOIN drivers d ON ps.driverId = d.driverId
    JOIN results r ON ps.raceId = r.raceId AND ps.driverId = r.driverId
    JOIN constructors con ON r.constructorId = con.constructorId
    WHERE ps.raceId = p_race_id
    ORDER BY ps.milliseconds DESC
    LIMIT 10);
    
END$$

-- PROCEDIMIENTO 3: Simulador de campeonato con proyecciones
-- Propósito: Simula diferentes escenarios de puntuación para proyectar campeonatos
CREATE PROCEDURE sp_SimuladorCampeonato(
    IN p_year INT,
    IN p_races_remaining INT,
    IN p_driver_id INT,
    IN p_points_per_race DECIMAL(8,2)
)
BEGIN
    DECLARE v_current_points DECIMAL(8,2) DEFAULT 0;
    DECLARE v_current_position INT DEFAULT 0;
    DECLARE v_leader_points DECIMAL(8,2) DEFAULT 0;
    DECLARE v_projected_points DECIMAL(8,2) DEFAULT 0;
    
    -- Obtener situación actual del piloto
    SELECT ds.points, ds.position
    INTO v_current_points, v_current_position
    FROM driver_standings ds
    JOIN races r ON ds.raceId = r.raceId
    WHERE ds.driverId = p_driver_id AND r.year = p_year
    ORDER BY r.round DESC
    LIMIT 1;
    
    -- Obtener puntos del líder
    SELECT MAX(ds.points) INTO v_leader_points
    FROM driver_standings ds
    JOIN races r ON ds.raceId = r.raceId
    WHERE r.year = p_year AND r.round = (
        SELECT MAX(round) FROM races WHERE year = p_year
    );
    
    SET v_projected_points = v_current_points + (p_races_remaining * p_points_per_race);
    
    -- Crear escenarios
    DROP TEMPORARY TABLE IF EXISTS temp_scenarios;
    CREATE TEMPORARY TABLE temp_scenarios (
        escenario VARCHAR(50),
        puntos_actuales DECIMAL(8,2),
        puntos_proyectados DECIMAL(8,2),
        diferencia_con_lider DECIMAL(8,2),
        puntos_maximos_posibles DECIMAL(8,2),
        probabilidad_titulo VARCHAR(20)
    );
    
    INSERT INTO temp_scenarios VALUES
    ('Conservador (5 pts/carrera)', v_current_points, v_current_points + (p_races_remaining * 5), 
     (v_current_points + (p_races_remaining * 5)) - v_leader_points, v_current_points + (p_races_remaining * 25), 'Baja'),
    ('Realista (12 pts/carrera)', v_current_points, v_current_points + (p_races_remaining * 12), 
     (v_current_points + (p_races_remaining * 12)) - v_leader_points, v_current_points + (p_races_remaining * 25), 'Media'),
    ('Optimista (20 pts/carrera)', v_current_points, v_current_points + (p_races_remaining * 20), 
     (v_current_points + (p_races_remaining * 20)) - v_leader_points, v_current_points + (p_races_remaining * 25), 'Alta'),
    ('Personalizado', v_current_points, v_projected_points, 
     v_projected_points - v_leader_points, v_current_points + (p_races_remaining * 25), 'Variable');
    
    SELECT * FROM temp_scenarios;
    
    -- Tabla de posiciones actuales top 10
    SELECT 
        ds.position as posicion,
        CONCAT(d.forename, ' ', d.surname) as piloto,
        c.name as constructor,
        ds.points as puntos,
        ds.wins as victorias,
        CASE 
            WHEN ds.driverId = p_driver_id THEN '*** PILOTO ANALIZADO ***'
            ELSE ''
        END AS destacado
    FROM driver_standings ds
    JOIN drivers d ON ds.driverId = d.driverId
    JOIN results r ON ds.raceId = r.raceId AND ds.driverId = r.driverId
    JOIN constructors c ON r.constructorId = c.constructorId
    JOIN races ra ON ds.raceId = ra.raceId
    WHERE ra.year = p_year AND ra.round = (
        SELECT MAX(round) FROM races WHERE year = p_year
    )
    AND ds.position <= 10
    ORDER BY ds.position;
    
    DROP TEMPORARY TABLE temp_scenarios;
END$$

-- ==============================================
-- FUNCIONES (3)
-- ==============================================

-- FUNCIÓN 1: Calculadora de puntos históricos con diferentes sistemas
-- Propósito: Calcula puntos usando diferentes sistemas de puntuación de F1
CREATE FUNCTION fn_CalcularPuntosHistoricos(
    p_position INT,
    p_year INT
) RETURNS DECIMAL(8,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_points DECIMAL(8,2) DEFAULT 0;
    
    -- Sistema actual (2010-presente): 25,18,15,12,10,8,6,4,2,1
    IF p_year >= 2010 THEN
        CASE p_position
            WHEN 1 THEN SET v_points = 25;
            WHEN 2 THEN SET v_points = 18;
            WHEN 3 THEN SET v_points = 15;
            WHEN 4 THEN SET v_points = 12;
            WHEN 5 THEN SET v_points = 10;
            WHEN 6 THEN SET v_points = 8;
            WHEN 7 THEN SET v_points = 6;
            WHEN 8 THEN SET v_points = 4;
            WHEN 9 THEN SET v_points = 2;
            WHEN 10 THEN SET v_points = 1;
            ELSE SET v_points = 0;
        END CASE;
    -- Sistema 2003-2009: 10,8,6,5,4,3,2,1
    ELSEIF p_year BETWEEN 2003 AND 2009 THEN
        CASE p_position
            WHEN 1 THEN SET v_points = 10;
            WHEN 2 THEN SET v_points = 8;
            WHEN 3 THEN SET v_points = 6;
            WHEN 4 THEN SET v_points = 5;
            WHEN 5 THEN SET v_points = 4;
            WHEN 6 THEN SET v_points = 3;
            WHEN 7 THEN SET v_points = 2;
            WHEN 8 THEN SET v_points = 1;
            ELSE SET v_points = 0;
        END CASE;
    -- Sistema 1991-2002: 10,6,4,3,2,1
    ELSE
        CASE p_position
            WHEN 1 THEN SET v_points = 10;
            WHEN 2 THEN SET v_points = 6;
            WHEN 3 THEN SET v_points = 4;
            WHEN 4 THEN SET v_points = 3;
            WHEN 5 THEN SET v_points = 2;
            WHEN 6 THEN SET v_points = 1;
            ELSE SET v_points = 0;
        END CASE;
    END IF;
    
    RETURN v_points;
END$$

-- FUNCIÓN 2: Analizador de consistencia de piloto
-- Propósito: Calcula un índice de consistencia basado en variabilidad de posiciones
CREATE FUNCTION fn_IndiceConsistencia(
    p_driver_id INT,
    p_year INT
) RETURNS DECIMAL(5,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_avg_position DECIMAL(8,2) DEFAULT 0;
    DECLARE v_std_deviation DECIMAL(8,2) DEFAULT 0;
    DECLARE v_races_finished INT DEFAULT 0;
    DECLARE v_consistency_index DECIMAL(5,2) DEFAULT 0;
    
    -- Calcular promedio de posiciones (solo carreras terminadas)
    SELECT 
        AVG(r.position),
        COUNT(*),
        STDDEV(r.position)
    INTO v_avg_position, v_races_finished, v_std_deviation
    FROM results r
    JOIN races ra ON r.raceId = ra.raceId
    WHERE r.driverId = p_driver_id 
    AND ra.year = p_year 
    AND r.position IS NOT NULL
    AND r.statusId = 1; -- Solo terminados
    
    -- Si no hay suficientes datos
    IF v_races_finished < 3 THEN
        RETURN 0;
    END IF;
    
    -- Calcular índice: 100 - (desviación_estándar * factor_ajuste)
    -- Factor de ajuste basado en posición promedio
    SET v_consistency_index = GREATEST(0, 
        100 - (v_std_deviation * 10) - (v_avg_position * 2)
    );
    
    RETURN ROUND(v_consistency_index, 2);
END$$

-- FUNCIÓN 3: Predictor de tiempo de vuelta por condiciones
-- Propósito: Estima tiempo de vuelta basado en patrones históricos del circuito
CREATE FUNCTION fn_PredictorTiempoVuelta(
    p_circuit_id INT,
    p_driver_id INT,
    p_weather_factor DECIMAL(3,2) -- 1.0 = seco, 1.15 = lluvia ligera, 1.3 = lluvia fuerte
) RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_avg_milliseconds INT DEFAULT 0;
    DECLARE v_predicted_time INT DEFAULT 0;
    DECLARE v_minutes INT DEFAULT 0;
    DECLARE v_seconds INT DEFAULT 0;
    DECLARE v_milliseconds INT DEFAULT 0;
    DECLARE v_result VARCHAR(20) DEFAULT '';
    
    -- Obtener promedio histórico del piloto en este circuito
    SELECT AVG(lt.milliseconds)
    INTO v_avg_milliseconds
    FROM lap_times lt
    JOIN races r ON lt.raceId = r.raceId
    WHERE r.circuitId = p_circuit_id
    AND lt.driverId = p_driver_id
    AND r.year >= YEAR(CURDATE()) - 3; -- Últimos 3 años
    
    -- Si no hay datos del piloto, usar promedio del circuito
    IF v_avg_milliseconds IS NULL THEN
        SELECT AVG(lt.milliseconds)
        INTO v_avg_milliseconds
        FROM lap_times lt
        JOIN races r ON lt.raceId = r.raceId
        WHERE r.circuitId = p_circuit_id
        AND r.year >= YEAR(CURDATE()) - 2;
    END IF;
    
    -- Si aún no hay datos, retornar estimación base
    IF v_avg_milliseconds IS NULL THEN
        RETURN '1:30.000';
    END IF;
    
    -- Aplicar factor climático
    SET v_predicted_time = ROUND(v_avg_milliseconds * p_weather_factor);
    
    -- Convertir a formato MM:SS.mmm
    SET v_minutes = FLOOR(v_predicted_time / 60000);
    SET v_seconds = FLOOR((v_predicted_time % 60000) / 1000);
    SET v_milliseconds = v_predicted_time % 1000;
    
    SET v_result = CONCAT(
        v_minutes, ':',
        LPAD(v_seconds, 2, '0'), '.',
        LPAD(v_milliseconds, 3, '0')
    );
    
    RETURN v_result;
END$$

-- ==============================================
-- TRIGGERS (3)
-- ==============================================

-- TRIGGER 1: Auditoría automática de cambios en resultados
-- Propósito: Registra todos los cambios en la tabla results para auditoría
CREATE TABLE audit_results (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    result_id INT,
    race_id INT,
    driver_id INT,
    old_position INT,
    new_position INT,
    old_points DECIMAL(8,2),
    new_points DECIMAL(8,2),
    changed_by VARCHAR(100),
    change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    change_reason TEXT
);

CREATE TRIGGER tr_audit_results_update
    AFTER UPDATE ON results
    FOR EACH ROW
BEGIN
    INSERT INTO audit_results (
        operation_type, result_id, race_id, driver_id,
        old_position, new_position, old_points, new_points,
        changed_by, change_reason
    ) VALUES (
        'UPDATE', NEW.resultId, NEW.raceId, NEW.driverId,
        OLD.position, NEW.position, OLD.points, NEW.points,
        USER(), 'Actualización automática via trigger'
    );
END$$

-- TRIGGER 2: Validador de consistencia de datos de carrera
-- Propósito: Valida que los datos de una carrera sean consistentes antes de insertar
CREATE TRIGGER tr_validate_race_result
    BEFORE INSERT ON results
    FOR EACH ROW
BEGIN
    DECLARE v_race_year INT;
    DECLARE v_driver_active INT DEFAULT 0;
    DECLARE v_position_exists INT DEFAULT 0;
    
    -- Obtener año de la carrera
    SELECT year INTO v_race_year
    FROM races 
    WHERE raceId = NEW.raceId;
    
    -- Validar que la posición no esté duplicada en la misma carrera
    IF NEW.position IS NOT NULL THEN
        SELECT COUNT(*) INTO v_position_exists
        FROM results 
        WHERE raceId = NEW.raceId 
        AND position = NEW.position 
        AND resultId != COALESCE(NEW.resultId, 0);
        
        IF v_position_exists > 0 THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Ya existe un piloto en esa posición para esta carrera';
        END IF;
    END IF;
    
    -- Validar que los puntos sean coherentes con la posición y el año
    IF NEW.position IS NOT NULL AND NEW.points != fn_CalcularPuntosHistoricos(NEW.position, v_race_year) THEN
        SET NEW.points = fn_CalcularPuntosHistoricos(NEW.position, v_race_year);
    END IF;
    
    -- Validar que grid position sea positiva
    IF NEW.grid IS NOT NULL AND NEW.grid <= 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'La posición de parrilla debe ser mayor a 0';
    END IF;
END$$

-- TRIGGER 3: Actualizador automático de estadísticas de constructor
-- Propósito: Mantiene estadísticas agregadas actualizadas automáticamente
CREATE TABLE constructor_stats (
    constructor_id INT PRIMARY KEY,
    total_races INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    total_podiums INT DEFAULT 0,
    total_points DECIMAL(10,2) DEFAULT 0,
    avg_position DECIMAL(8,2) DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (constructor_id) REFERENCES constructors(constructorId)
);

CREATE TRIGGER tr_update_constructor_stats
    AFTER INSERT ON results
    FOR EACH ROW
BEGIN
    DECLARE v_total_races INT DEFAULT 0;
    DECLARE v_total_wins INT DEFAULT 0;
    DECLARE v_total_podiums INT DEFAULT 0;
    DECLARE v_total_points DECIMAL(10,2) DEFAULT 0;
    DECLARE v_avg_position DECIMAL(8,2) DEFAULT 0;
    
    -- Calcular estadísticas actualizadas
    SELECT 
        COUNT(*),
        SUM(CASE WHEN position = 1 THEN 1 ELSE 0 END),
        SUM(CASE WHEN position <= 3 AND position IS NOT NULL THEN 1 ELSE 0 END),
        SUM(points),
        AVG(CASE WHEN position IS NOT NULL THEN position END)
    INTO v_total_races, v_total_wins, v_total_podiums, v_total_points, v_avg_position
    FROM results
    WHERE constructorId = NEW.constructorId;
    
    -- Insertar o actualizar estadísticas
    INSERT INTO constructor_stats (
        constructor_id, total_races, total_wins, total_podiums, 
        total_points, avg_position
    ) VALUES (
        NEW.constructorId, v_total_races, v_total_wins, v_total_podiums,
        v_total_points, v_avg_position
    ) ON DUPLICATE KEY UPDATE
        total_races = v_total_races,
        total_wins = v_total_wins,
        total_podiums = v_total_podiums,
        total_points = v_total_points,
        avg_position = v_avg_position;
END$$

DELIMITER ;