/****** Object:  StoredProcedure [dbo].[GetFavoriteHorses]    Script Date: 17/07/2025 09:09:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[GetFavoriteHorses]
    @Date DATETIME = NULL,
    @Meeting NVARCHAR(100) = NULL,
    @RaceNumber INT = NULL,
    @TopN INT = 3,
    @Weight_HandicapRating DECIMAL(10,2) = 1,
    @Weight_CareerWins DECIMAL(10,2) = 1,
    @Weight_CareerStrikeRate DECIMAL(10,2) = 1,
    @Weight_JockeyLast100StrikeRate DECIMAL(10,2) = 1,
    @Weight_TrainerLast100StrikeRate DECIMAL(10,2) = 1,
    @Weight_BestFixedOdds DECIMAL(10,2) = 1,
    @Weight_Age DECIMAL(10,2) = 1,
    @Weight_CareerPlacings DECIMAL(10,2) = 1,
    @Weight_DryTrackStrikeRate DECIMAL(10,2) = 1,
    @Weight_ThisTrackStrikeRate DECIMAL(10,2) = 1,
    @Weight_LastStartFinishPosition DECIMAL(10,2) = 1,
    @Weight_WeightCarried DECIMAL(10,2) = 1
AS
BEGIN
    SET NOCOUNT ON;

    -- Crea tabella temporanea per i risultati
    CREATE TABLE #Results (
        DataCorsa NVARCHAR(10),
        LuogoCorsa NVARCHAR(100),
        NumeroCorsa INT,
        NomeCavallo NVARCHAR(100),
        Punteggio DECIMAL(18,4),
        RisultatoFinale NVARCHAR(50),
        PosizioneArrivo INT,
        PrevistoVincente BIT
    );

    WITH Scoring AS (
        SELECT
            Data,
            Meeting,
            Race,
            HorseName,
            -- Calcolo del punteggio pesato con le nuove statistiche
            (
                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 +
                -- Per LastStartFinishPosition, valori pił bassi sono migliori, quindi invertiamo il segno
                (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
        FROM Races
        WHERE (FinishResult IS NOT NULL AND FinishResult <> '')
    ),
    Ranked AS (
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY Data, Meeting, Race ORDER BY Score DESC) AS RankByScore
        FROM Scoring
    )
    INSERT INTO #Results
    SELECT
        CONVERT(VARCHAR(10), Data, 120),
        Meeting,
        Race,
        HorseName,
        Score,
        FinishResult,
        CASE WHEN ISNUMERIC(FinishResult) = 1 THEN CAST(FinishResult AS INT) ELSE NULL END,
        CASE WHEN RankByScore = 1 THEN 1 ELSE 0 END
    FROM Ranked
    WHERE RankByScore = 1;

    SELECT * FROM #Results;

    DROP TABLE #Results;
END
