WITH top_league_teams AS (
    SELECT DISTINCT team_id FROM (
        SELECT home_team_id AS team_id
        FROM @blt.ultimate_soccer_dataset.matches
        WHERE season_id IN (
            '6cd6410e-da07-5f45-a4be-89418f53ad2a',  -- Bundesliga
            '4cbe3f9f-0241-5e3d-8430-728850095ccf',  -- Ligue 1
            '4a21bbb0-2621-5742-a5c1-cfdf5b737ae4',  -- La Liga
            'e61fcd02-afce-5d2f-9f00-4ae666eb6903',  -- Serie A
            '9a913a85-6221-5cdf-a353-974d9bde12f9'   -- Premier League
        )
        UNION
        SELECT away_team_id AS team_id
        FROM @blt.ultimate_soccer_dataset.matches
        WHERE season_id IN (
            '6cd6410e-da07-5f45-a4be-89418f53ad2a',
            '4cbe3f9f-0241-5e3d-8430-728850095ccf',
            '4a21bbb0-2621-5742-a5c1-cfdf5b737ae4',
            'e61fcd02-afce-5d2f-9f00-4ae666eb6903',
            '9a913a85-6221-5cdf-a353-974d9bde12f9'
        )
    )
),

paid_transfers AS (
    SELECT
        to_team.country,
        CASE 
            WHEN t.transfer_fee_eur < 100000 THEN '<100k'
            WHEN t.transfer_fee_eur < 500000 THEN '100k-500k'
            WHEN t.transfer_fee_eur < 1000000 THEN '500k-1M'
            WHEN t.transfer_fee_eur < 5000000 THEN '1M-5M'
            WHEN t.transfer_fee_eur < 10000000 THEN '5M-10M'
            WHEN t.transfer_fee_eur < 20000000 THEN '10M-20M'
            WHEN t.transfer_fee_eur < 50000000 THEN '20M-50M'
            ELSE '50M+'
        END AS fee_bin
    FROM
        @blt.ultimate_soccer_dataset.transfers AS t
    JOIN
        @blt.ultimate_soccer_dataset.teams AS to_team
        ON t.to_team_id = to_team.team_id
    JOIN
        top_league_teams AS top
        ON t.to_team_id = top.team_id
    WHERE
        t.transfer_fee_eur IS NOT NULL
        AND t.transfer_fee_eur > 0
        AND EXTRACT(YEAR FROM t.transfer_date) = 2024
),

ucl_wins AS (
    SELECT
        CASE 
            WHEN m.home_score > m.away_score THEN home_team.country
            WHEN m.home_score < m.away_score THEN away_team.country
            ELSE NULL
        END AS winning_country,
        COUNT(*) AS wins
    FROM
        @blt.ultimate_soccer_dataset.matches AS m
    JOIN @blt.ultimate_soccer_dataset.teams AS home_team
        ON m.home_team_id = home_team.team_id
    JOIN @blt.ultimate_soccer_dataset.teams AS away_team
        ON m.away_team_id = away_team.team_id
    WHERE
        m.season_id = '1e23c5ed-5bfa-509e-8830-8032140cddd4'  -- 2024/2025 UCL season
        AND m.home_score IS NOT NULL
        AND m.away_score IS NOT NULL
    GROUP BY
        winning_country
)

SELECT
    pt.country,
    COUNT(CASE WHEN fee_bin = '<100k' THEN 1 END) AS bin_lt_100k,
    COUNT(CASE WHEN fee_bin = '100k-500k' THEN 1 END) AS bin_100k_500k,
    COUNT(CASE WHEN fee_bin = '500k-1M' THEN 1 END) AS bin_500k_1M,
    COUNT(CASE WHEN fee_bin = '1M-5M' THEN 1 END) AS bin_1M_5M,
    COUNT(CASE WHEN fee_bin = '5M-10M' THEN 1 END) AS bin_5M_10M,
    COUNT(CASE WHEN fee_bin = '10M-20M' THEN 1 END) AS bin_10M_20M,
    COUNT(CASE WHEN fee_bin = '20M-50M' THEN 1 END) AS bin_20M_50M,
    COUNT(CASE WHEN fee_bin = '50M+' THEN 1 END) AS bin_50M_plus,
    COALESCE(w.wins, 0) AS ucl_wins
FROM
    paid_transfers AS pt
LEFT JOIN
    ucl_wins AS w
    ON pt.country = w.winning_country
GROUP BY
    pt.country, w.wins
ORDER BY
    pt.country