with fifa_club_world_cup_matches as (
select home_teams.name as home_team, away_teams.name as away_team, matches.*, odds.*
from @blt.ultimate_soccer_dataset.matches matches
inner join @blt.ultimate_soccer_dataset.teams home_teams ON (matches.home_team_id = home_teams.team_id)
inner join @blt.ultimate_soccer_dataset.teams away_teams ON (matches.away_team_id = away_teams.team_id)
inner join @blt.ultimate_soccer_dataset.seasons s ON (matches.season_id = s.season_id)
inner join @blt.ultimate_soccer_dataset.competitions c ON (s.competition_id = c.competition_id)
inner join @blt.ultimate_soccer_dataset.match_betting_odds odds ON (odds.match_id = matches.match_id)
where 1=1
      and c.name = 'FIFA Club World Cup'
      and year(s.start_date) = 2025      
      and market = 'Match Winner'
      and matches.status = 'Match Finished'
),
pre_match AS (  -- 1. keep only pre-match “Match Winner” odds
    SELECT
        match_id,
        outcome,                           -- 'Home', 'Draw', 'Away'
        AVG(odds) AS avg_odds              -- average across bookmakers
    FROM fifa_club_world_cup_matches
    WHERE market     = 'Match Winner'
      AND odds_type  = 'pre_match'
    GROUP BY match_id, outcome
),
pivot1 AS (         -- 2. put the three outcomes on one row per match
    SELECT
        match_id,
        MIN(CASE WHEN outcome = 'Home' THEN avg_odds END) AS home_odds,
        MIN(CASE WHEN outcome = 'Draw' THEN avg_odds END) AS draw_odds,
        MIN(CASE WHEN outcome = 'Away' THEN avg_odds END) AS away_odds
    FROM pre_match
    GROUP BY match_id
),
prob AS (          -- 3. convert odds → implied probabilities (raw)
    SELECT
        p.match_id,
        1 / p.home_odds  AS raw_home_prob,
        1 / p.draw_odds  AS raw_draw_prob,
        1 / p.away_odds  AS raw_away_prob,
        m.home_team,
        m.away_team,
        m.date,
        m.home_score,
        m.away_score
    FROM pivot1 p
    JOIN fifa_club_world_cup_matches m USING (match_id)
    GROUP BY
        p.match_id, p.home_odds, p.draw_odds, p.away_odds,
        m.home_team, m.away_team, m.date, m.home_score, m.away_score
),
normalised AS (    -- 4. normalise so the three probs sum to 1
    SELECT
        *,
        raw_home_prob + raw_draw_prob + raw_away_prob AS total_p
    FROM prob
),
final AS (         -- 5. map actual result → its pre-match probability
    SELECT
        *,
        raw_home_prob / total_p AS home_prob,
        raw_draw_prob / total_p AS draw_prob,
        raw_away_prob / total_p AS away_prob,
        CASE
            WHEN home_score > away_score THEN 'home'
            WHEN home_score < away_score THEN 'away'
            ELSE 'draw'
        END AS actual_outcome
    FROM normalised
)
SELECT
    home_team || ' - ' || away_team as teams,
    --home_team,
    --away_team,
    strftime(date, '%Y-%m-%d') as match_date,
    home_score,
    away_score,
    --round(home_prob,2) as home_probability,
    --round(draw_prob,2) as draw_probability,
    --round(away_prob,2) as away_probability,
    CASE
            WHEN home_prob >= draw_prob AND home_prob >= away_prob THEN 'home'
            WHEN away_prob >= draw_prob AND away_prob >  home_prob THEN 'away'
            ELSE 'draw'                                        -- ties favour draw
    END AS favourite,
    round(greatest(home_prob, draw_prob, away_prob), 2) as favourite_probability, 
    actual_outcome,
    round(CASE
        WHEN actual_outcome = 'home' THEN home_prob
        WHEN actual_outcome = 'away' THEN away_prob
        ELSE draw_prob
    END, 2) AS actual_probability,                -- probability bookmakers gave to what happened
    CASE WHEN favourite_probability = actual_probability THEN 'Yes' ELSE 'No' END as correct_odds,
FROM final
ORDER BY actual_probability ASC