import duckdb
import polars as pl

conn = duckdb.connect("nba.duckdb")

players = ["LeBron James", "Kevin Durant", "Stephen Curry"]
placeholders = ", ".join(f"'{p}'" for p in players)

comparison = conn.sql(f"""
    SELECT
        p.first_name || ' ' || p.last_name AS player,
        COUNT(*) AS games,
        ROUND(AVG(b.pts), 1) AS ppg,
        ROUND(AVG(b.reb), 1) AS rpg,
        ROUND(AVG(b.ast), 1) AS apg,
        ROUND(AVG(b.stl), 1) AS spg,
        ROUND(AVG(b.blk), 1) AS bpg,
        ROUND(AVG(b.tov), 1) AS topg,
        ROUND(AVG(b.plus_minus), 1) AS plus_minus
    FROM fact_box_score_player b
    JOIN dim_player p ON b.player_id = p.player_id
    JOIN dim_game g ON b.game_id = g.game_id
    WHERE (p.first_name || ' ' || p.last_name) IN ({placeholders})
      AND g.season_type = 'Regular Season'
      AND g.season_year = 2024
    GROUP BY 1
    ORDER BY ppg DESC
""").pl()

print(comparison)

trends = conn.sql("""
    SELECT
        p.first_name || ' ' || p.last_name AS player,
        g.season_year,
        COUNT(*) AS games,
        ROUND(AVG(b.pts), 1) AS ppg,
        ROUND(AVG(b.reb), 1) AS rpg,
        ROUND(AVG(b.ast), 1) AS apg
    FROM fact_box_score_player b
    JOIN dim_player p ON b.player_id = p.player_id
    JOIN dim_game g ON b.game_id = g.game_id
    WHERE p.last_name = 'James' AND p.first_name = 'LeBron'
      AND g.season_type = 'Regular Season'
    GROUP BY 1, 2
    ORDER BY g.season_year
""").pl()

print(trends)

shooting = conn.sql(f"""
    SELECT
        p.first_name || ' ' || p.last_name AS player,
        COUNT(*) AS games,
        ROUND(AVG(b.fg_pct) * 100, 1) AS fg_pct,
        ROUND(AVG(b.fg3_pct) * 100, 1) AS fg3_pct,
        ROUND(AVG(b.ft_pct) * 100, 1) AS ft_pct,
        ROUND(SUM(b.pts) / NULLIF(2.0 * (SUM(b.fga) + 0.44 * SUM(b.fta)), 0) * 100, 1) AS ts_pct,
        ROUND(AVG(b.fg3a), 1) AS fg3a_pg
    FROM fact_box_score_player b
    JOIN dim_player p ON b.player_id = p.player_id
    JOIN dim_game g ON b.game_id = g.game_id
    WHERE (p.first_name || ' ' || p.last_name) IN ({placeholders})
      AND g.season_type = 'Regular Season'
      AND g.season_year = 2024
    GROUP BY 1
""").pl()

print(shooting)

clutch = conn.sql(f"""
    SELECT
        p.first_name || ' ' || p.last_name AS player,
        COUNT(*) AS clutch_shots,
        SUM(CASE WHEN pbp.shot_result = 'Made' THEN 1 ELSE 0 END) AS makes,
        ROUND(100.0 * SUM(CASE WHEN pbp.shot_result = 'Made' THEN 1 ELSE 0 END) / COUNT(*), 1) AS fg_pct
    FROM fact_play_by_play pbp
    JOIN dim_player p ON pbp.person_id = p.player_id
    JOIN dim_game g ON pbp.game_id = g.game_id
    WHERE (p.first_name || ' ' || p.last_name) IN ({placeholders})
      AND pbp.period >= 4
      AND pbp.is_field_goal = 1
      AND g.season_type = 'Regular Season'
    GROUP BY 1
    ORDER BY makes DESC
""").pl()

print(clutch)

import matplotlib.pyplot as plt
import numpy as np

categories = ["PPG", "RPG", "APG", "SPG", "BPG", "TS%"]
metrics = ["ppg", "rpg", "apg", "spg", "bpg", "ts_pct"]

radar_source = comparison.join(
    shooting.select(["player", "ts_pct"]),
    on="player",
)

# Normalize each stat to a 0-1 scale relative to this player pool.
def normalize(val, min_val, max_val):
    return (val - min_val) / (max_val - min_val) if max_val > min_val else 0

mins = {metric: radar_source[metric].min() for metric in metrics}
maxs = {metric: radar_source[metric].max() for metric in metrics}

angles = np.linspace(0, 2 * np.pi, len(categories), endpoint=False)
angles = np.concatenate([angles, [angles[0]]])

fig, ax = plt.subplots(figsize=(8, 8), subplot_kw=dict(polar=True))

for row in radar_source.iter_rows(named=True):
    values = [normalize(row[metric], mins[metric], maxs[metric]) for metric in metrics]
    values = np.concatenate([values, [values[0]]])
    ax.plot(angles, values, label=row["player"])
    ax.fill(angles, values, alpha=0.1)

ax.set_xticks(angles[:-1])
ax.set_xticklabels(categories)
ax.legend(loc="upper right", bbox_to_anchor=(1.3, 1.0))
plt.title("Player Comparison Radar")
plt.show()