ALTER PROCEDURE [dbo].[ScanFavoriteHorseWeights]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MinValue INT = 1
    DECLARE @MaxValue INT = 3 -- Modifica per range più ampi
    DECLARE @SessionId UNIQUEIDENTIFIER = NEWID()
    DECLARE @ScanDate DATETIME = GETDATE()

    -- Parametri
    DECLARE @Weight_HandicapRating INT
    DECLARE @Weight_CareerWins INT
    DECLARE @Weight_CareerStrikeRate INT
    DECLARE @Weight_JockeyLast100StrikeRate INT
    DECLARE @Weight_TrainerLast100StrikeRate INT
    DECLARE @Weight_BestFixedOdds INT
    DECLARE @Weight_Age INT
    DECLARE @Weight_CareerPlacings INT
    DECLARE @Weight_DryTrackStrikeRate INT
    DECLARE @Weight_ThisTrackStrikeRate INT
    DECLARE @Weight_LastStartFinishPosition INT
    DECLARE @Weight_WeightCarried INT

    DECLARE @TotalRaces INT, @CorrectPredictions INT, @WinPercentage DECIMAL(5,2)
    DECLARE @CombinazioniTotali BIGINT
    DECLARE @CombinazioneCorrente BIGINT = 0

    -- Carica tutti i cavalli validi in memoria
    IF OBJECT_ID('tempdb..#AllHorses') IS NOT NULL DROP TABLE #AllHorses
    SELECT
        Data,
        Meeting,
        Race,
        HorseName,
        HandicapRating,
        CareerWins,
        CareerStrikeRate,
        JockeyLast100StrikeRate,
        TrainerLast100StrikeRate,
        BestFixedOdds,
        Age,
        CareerPlacings,
        DryTrackStrikeRate,
        ThisTrackStrikeRate,
        LastStartFinishPosition,
        WeightCarried,
        FinishResult
    INTO #AllHorses
    FROM Races
    WHERE (FinishResult IS NOT NULL AND FinishResult <> '')

    -- Conta il numero di corse distinte
    SELECT @TotalRaces = COUNT(DISTINCT 
        CONVERT(NVARCHAR(20), Data, 120) + '_' + Meeting + '_' + CAST(Race AS NVARCHAR(10))
    ) 
    FROM #AllHorses

    -- Calcola il numero totale di combinazioni
    SET @CombinazioniTotali = POWER(@MaxValue - @MinValue + 1, 12)

    -- Inizializza la sessione con un record "vuoto"
    INSERT INTO PredictionScanResults (
        SessionId, ScanDate,
        Weight_HandicapRating, Weight_CareerWins, Weight_CareerStrikeRate, Weight_JockeyLast100StrikeRate,
        Weight_TrainerLast100StrikeRate, Weight_BestFixedOdds, Weight_Age, Weight_CareerPlacings,
        Weight_DryTrackStrikeRate, Weight_ThisTrackStrikeRate, Weight_LastStartFinishPosition, Weight_WeightCarried,
        TotalRaces, CorrectPredictions, WinPercentage
    ) VALUES (
        @SessionId, @ScanDate,
        0, 0, 0, 0,
        0, 0, 0, 0,
        0, 0, 0, 0,
        0, 0, 0
    )

    SET @Weight_HandicapRating = @MinValue
    WHILE @Weight_HandicapRating <= @MaxValue
    BEGIN
        SET @Weight_CareerWins = @MinValue
        WHILE @Weight_CareerWins <= @MaxValue
        BEGIN
            SET @Weight_CareerStrikeRate = @MinValue
            WHILE @Weight_CareerStrikeRate <= @MaxValue
            BEGIN
                SET @Weight_JockeyLast100StrikeRate = @MinValue
                WHILE @Weight_JockeyLast100StrikeRate <= @MaxValue
                BEGIN
                    SET @Weight_TrainerLast100StrikeRate = @MinValue
                    WHILE @Weight_TrainerLast100StrikeRate <= @MaxValue
                    BEGIN
                        SET @Weight_BestFixedOdds = @MinValue
                        WHILE @Weight_BestFixedOdds <= @MaxValue
                        BEGIN
                            SET @Weight_Age = @MinValue
                            WHILE @Weight_Age <= @MaxValue
                            BEGIN
                                SET @Weight_CareerPlacings = @MinValue
                                WHILE @Weight_CareerPlacings <= @MaxValue
                                BEGIN
                                    SET @Weight_DryTrackStrikeRate = @MinValue
                                    WHILE @Weight_DryTrackStrikeRate <= @MaxValue
                                    BEGIN
                                        SET @Weight_ThisTrackStrikeRate = @MinValue
                                        WHILE @Weight_ThisTrackStrikeRate <= @MaxValue
                                        BEGIN
                                            SET @Weight_LastStartFinishPosition = @MinValue
                                            WHILE @Weight_LastStartFinishPosition <= @MaxValue
                                            BEGIN
                                                SET @Weight_WeightCarried = @MinValue
                                                WHILE @Weight_WeightCarried <= @MaxValue
                                                BEGIN
                                                    SET @CombinazioneCorrente = @CombinazioneCorrente + 1

                                                    -- Calcolo in memoria: trova il cavallo con punteggio massimo per ogni corsa
                                                    IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
                                                    SELECT
                                                        Data,
                                                        Meeting,
                                                        Race,
                                                        HorseName,
                                                        (
                                                            ISNULL(HandicapRating,0) * @Weight_HandicapRating +
                                                            ISNULL(CareerWins,0) * @Weight_CareerWins +
                                                            ISNULL(CareerStrikeRate,0) * @Weight_CareerStrikeRate +
                                                            ISNULL(JockeyLast100StrikeRate,0) * @Weight_JockeyLast100StrikeRate +
                                                            ISNULL(TrainerLast100StrikeRate,0) * @Weight_TrainerLast100StrikeRate +
                                                            ISNULL(BestFixedOdds,0) * @Weight_BestFixedOdds +
                                                            ISNULL(Age,0) * @Weight_Age +
                                                            ISNULL(CareerPlacings,0) * @Weight_CareerPlacings +
                                                            ISNULL(DryTrackStrikeRate,0) * @Weight_DryTrackStrikeRate +
                                                            ISNULL(ThisTrackStrikeRate,0) * @Weight_ThisTrackStrikeRate +
                                                            (CASE WHEN TRY_CAST(LastStartFinishPosition AS INT) > 0 THEN (1.0 / TRY_CAST(LastStartFinishPosition AS FLOAT)) ELSE 0 END) * @Weight_LastStartFinishPosition +
                                                            ISNULL(WeightCarried,0) * @Weight_WeightCarried
                                                        ) AS Score,
                                                        FinishResult
                                                    INTO #Results
                                                    FROM #AllHorses

                                                    -- Seleziona il cavallo con punteggio massimo per ogni corsa
                                                    ;WITH Ranked AS (
                                                        SELECT *,
                                                            ROW_NUMBER() OVER (PARTITION BY Data, Meeting, Race ORDER BY Score DESC) AS RankByScore
                                                        FROM #Results
                                                    )
                                                    SELECT *
                                                    INTO #TopResults
                                                    FROM Ranked
                                                    WHERE RankByScore = 1

                                                    -- Calcola le statistiche
                                                    SELECT
                                                        @CorrectPredictions = SUM(CASE WHEN ISNUMERIC(FinishResult) = 1 AND CAST(FinishResult AS INT) = 1 THEN 1 ELSE 0 END)
                                                    FROM #TopResults

                                                    -- @TotalRaces già calcolato all'inizio

                                                    IF @TotalRaces > 0
                                                        SET @WinPercentage = CAST(@CorrectPredictions AS DECIMAL(5,2)) * 100.0 / @TotalRaces
                                                    ELSE
                                                        SET @WinPercentage = 0

                                                    -- Aggiorna solo se la percentuale è migliore
                                                    IF EXISTS (
                                                        SELECT 1 FROM PredictionScanResults
                                                        WHERE SessionId = @SessionId
                                                          AND WinPercentage < @WinPercentage
                                                    )
                                                    BEGIN
                                                        UPDATE PredictionScanResults
                                                        SET
                                                            Weight_HandicapRating = @Weight_HandicapRating,
                                                            Weight_CareerWins = @Weight_CareerWins,
                                                            Weight_CareerStrikeRate = @Weight_CareerStrikeRate,
                                                            Weight_JockeyLast100StrikeRate = @Weight_JockeyLast100StrikeRate,
                                                            Weight_TrainerLast100StrikeRate = @Weight_TrainerLast100StrikeRate,
                                                            Weight_BestFixedOdds = @Weight_BestFixedOdds,
                                                            Weight_Age = @Weight_Age,
                                                            Weight_CareerPlacings = @Weight_CareerPlacings,
                                                            Weight_DryTrackStrikeRate = @Weight_DryTrackStrikeRate,
                                                            Weight_ThisTrackStrikeRate = @Weight_ThisTrackStrikeRate,
                                                            Weight_LastStartFinishPosition = @Weight_LastStartFinishPosition,
                                                            Weight_WeightCarried = @Weight_WeightCarried,
                                                            TotalRaces = @TotalRaces,
                                                            CorrectPredictions = @CorrectPredictions,
                                                            WinPercentage = @WinPercentage
                                                        WHERE SessionId = @SessionId
                                                    END

                                                    -- Output avanzamento
                                                    PRINT CONCAT(
                                                        'Combinazione ', @CombinazioneCorrente, '/', @CombinazioniTotali,
                                                        ' (', CAST((@CombinazioneCorrente * 100.0 / @CombinazioniTotali) AS DECIMAL(5,2)), '%) - ',
                                                        'Pesi: ', @Weight_HandicapRating, ',', @Weight_CareerWins, ',', @Weight_CareerStrikeRate, ',',
                                                        @Weight_JockeyLast100StrikeRate, ',', @Weight_TrainerLast100StrikeRate, ',', @Weight_BestFixedOdds, ',',
                                                        @Weight_Age, ',', @Weight_CareerPlacings, ',', @Weight_DryTrackStrikeRate, ',', @Weight_ThisTrackStrikeRate, ',',
                                                        @Weight_LastStartFinishPosition, ',', @Weight_WeightCarried,
                                                        ' - Win%: ', @WinPercentage
                                                    )

                                                    DROP TABLE IF EXISTS #Results
                                                    DROP TABLE IF EXISTS #TopResults

                                                    SET @Weight_WeightCarried = @Weight_WeightCarried + 1
                                                END
                                                SET @Weight_LastStartFinishPosition = @Weight_LastStartFinishPosition + 1
                                            END
                                            SET @Weight_ThisTrackStrikeRate = @Weight_ThisTrackStrikeRate + 1
                                        END
                                        SET @Weight_DryTrackStrikeRate = @Weight_DryTrackStrikeRate + 1
                                    END
                                    SET @Weight_CareerPlacings = @Weight_CareerPlacings + 1
                                END
                                SET @Weight_Age = @Weight_Age + 1
                            END
                            SET @Weight_BestFixedOdds = @Weight_BestFixedOdds + 1
                        END
                        SET @Weight_TrainerLast100StrikeRate = @Weight_TrainerLast100StrikeRate + 1
                    END
                    SET @Weight_JockeyLast100StrikeRate = @Weight_JockeyLast100StrikeRate + 1
                END
                SET @Weight_CareerStrikeRate = @Weight_CareerStrikeRate + 1
            END
            SET @Weight_CareerWins = @Weight_CareerWins + 1
        END
        SET @Weight_HandicapRating = @Weight_HandicapRating + 1
    END
END