import plotly.express as px
import streamlit as st
from streamlit_echarts import st_echarts

from utils.charts import apply_arena_theme
from utils.db import get_selected_season, query

SEASON, SEASON_LABEL = get_selected_season()

st.title(f":material/science: Player Lab — {SEASON_LABEL}")

min_games = st.sidebar.slider("Min Games", 5, 60, 20, key="player_min_games")

players = query(
    """
    SELECT player_name, team, count(*) AS games,
           round(avg(points), 1) AS ppg,
           round(avg(assists), 1) AS apg,
           round(avg(total_rebounds), 1) AS rpg,
           round(avg(steals), 1) AS spg,
           round(avg(blocks), 1) AS bpg,
           round(avg(box_plus_minus), 2) AS bpm,
           round(avg(usage_pct), 1) AS usage,
           round(avg(true_shooting_pct) * 100, 1) AS ts_pct,
           round(avg(net_rating), 1) AS net_rtg,
           sum(CASE WHEN is_double_double THEN 1 ELSE 0 END) AS double_doubles,
           sum(CASE WHEN is_triple_double THEN 1 ELSE 0 END) AS triple_doubles
    FROM main_intermediate.int_player_performance
    WHERE season_start_year = ? AND minutes_played >= 20
    GROUP BY player_name, team
    HAVING count(*) >= ?
    ORDER BY bpm DESC
    """,
    [SEASON, min_games],
)

tab_board, tab_compare, tab_archetypes = st.tabs(
    [
        ":material/leaderboard: Leaderboard",
        ":material/compare_arrows: Comparison",
        ":material/hub: Archetypes",
    ]
)

with tab_board:
    if not players.empty:
        fig = px.scatter(
            players,
            x="usage",
            y="ts_pct",
            size="ppg",
            hover_name="player_name",
            color="bpm",
            color_continuous_scale="RdYlGn",
            title="Usage vs True Shooting % (size = PPG, color = BPM)",
        )
        fig.update_layout(xaxis_title="Usage %", yaxis_title="True Shooting %")
        fig.add_hline(y=players["ts_pct"].median(), line_dash="dot", line_color="gray", opacity=0.4)
        fig.add_vline(x=players["usage"].median(), line_dash="dot", line_color="gray", opacity=0.4)
        apply_arena_theme(fig, height=500)
        st.plotly_chart(fig, width="stretch")

        col1, col2 = st.columns(2)
        with col1:
            fig2 = px.scatter(
                players,
                x="ppg",
                y="apg",
                hover_name="player_name",
                color="bpm",
                color_continuous_scale="RdYlGn",
                size="usage",
                title="Scoring vs Playmaking",
            )
            apply_arena_theme(fig2)
            st.plotly_chart(fig2, width="stretch")
        with col2:
            top_scorers = players.nlargest(15, "ppg")
            fig3 = px.bar(
                top_scorers,
                x="ppg",
                y="player_name",
                orientation="h",
                color="ts_pct",
                color_continuous_scale="RdYlGn",
                title="Top 15 Scorers (color = TS%)",
            )
            fig3.update_layout(yaxis=dict(autorange="reversed"))
            apply_arena_theme(fig3, height=450)
            st.plotly_chart(fig3, width="stretch")

        st.dataframe(
            players,
            column_config={
                "ts_pct": st.column_config.ProgressColumn(
                    "TS%", min_value=40, max_value=75, format="%.1f%%"
                ),
                "usage": st.column_config.ProgressColumn(
                    "USG%", min_value=5, max_value=40, format="%.1f%%"
                ),
                "bpm": st.column_config.NumberColumn("BPM", format="%.2f"),
            },
            width="stretch",
            hide_index=True,
        )

with tab_compare:
    player_list = players["player_name"].tolist()
    selected_players = st.multiselect(
        "Select players to compare", player_list, default=player_list[:3]
    )
    if selected_players:
        comp = players[players["player_name"].isin(selected_players)]
        categories = ["ppg", "apg", "rpg", "spg", "bpg", "usage", "ts_pct"]
        labels = ["PPG", "APG", "RPG", "SPG", "BPG", "USG%", "TS%"]

        RADAR_COLORS = [
            "#E09F3E",
            "#10B981",
            "#38BDF8",
            "#F43F5E",
            "#A855F7",
            "#42D4F4",
            "#F032E6",
            "#BFEF45",
            "#FABED4",
            "#469990",
        ]
        radar_indicators = [
            {"name": labels[i], "max": float(players[c].max() * 1.15)}
            for i, c in enumerate(categories)
        ]
        radar_data = [
            {
                "value": [float(row[c]) for c in categories],
                "name": row["player_name"],
                "lineStyle": {"color": RADAR_COLORS[i % len(RADAR_COLORS)], "width": 2.5},
                "areaStyle": {"color": RADAR_COLORS[i % len(RADAR_COLORS)], "opacity": 0.3},
                "itemStyle": {"color": RADAR_COLORS[i % len(RADAR_COLORS)]},
            }
            for i, (_, row) in enumerate(comp.iterrows())
        ]
        echarts_option = {
            "legend": {"data": [d["name"] for d in radar_data], "top": "bottom"},
            "color": RADAR_COLORS[: len(radar_data)],
            "radar": {
                "indicator": radar_indicators,
                "shape": "polygon",
                "splitArea": {
                    "areaStyle": {
                        "color": ["#0B0F17", "#151D2A", "#1E293B", "#0B0F17", "#151D2A"]
                    }
                },
                "axisLine": {"lineStyle": {"color": "rgba(200, 200, 200, 0.3)"}},
                "splitLine": {"lineStyle": {"color": "rgba(200, 200, 200, 0.2)"}},
            },
            "series": [{"type": "radar", "data": radar_data}],
        }
        st_echarts(options=echarts_option, height="500px")

        st.dataframe(
            comp.set_index("player_name")[categories + ["bpm", "net_rtg", "games"]].rename(
                columns=dict(zip(categories, labels))
            ),
            width="stretch",
        )

        st.subheader("Parallel Coordinates (Top 15 BPM)")
        top_parallel = players.nlargest(15, "bpm")
        parallel_dims = ["ppg", "apg", "rpg", "usage", "ts_pct", "bpm", "net_rtg"]
        parallel_labels = ["PPG", "APG", "RPG", "USG%", "TS%", "BPM", "Net Rtg"]
        parallel_option = {
            "parallelAxis": [
                {
                    "dim": i,
                    "name": parallel_labels[i],
                    "min": float(top_parallel[parallel_dims[i]].min()),
                    "max": float(top_parallel[parallel_dims[i]].max()),
                }
                for i in range(len(parallel_dims))
            ],
            "tooltip": {"trigger": "item"},
            "series": [
                {
                    "type": "parallel",
                    "lineStyle": {"width": 2, "opacity": 0.6},
                    "data": [
                        [float(row[c]) for c in parallel_dims]
                        for _, row in top_parallel.iterrows()
                    ],
                }
            ],
        }
        st_echarts(options=parallel_option, height="400px")

with tab_archetypes:
    archetype_players = query(
        """
        WITH player_archetype_counts AS (
            SELECT player_name, team, usage_tier, impact_tier, shooting_efficiency_tier,
                   count(*) AS games_in_archetype
            FROM main_intermediate.int_player_performance
            WHERE season_start_year = ? AND minutes_played >= 20
                  AND usage_tier != 'Insufficient Minutes'
            GROUP BY player_name, team, usage_tier, impact_tier, shooting_efficiency_tier
        ),
        player_modal AS (
            SELECT *, ROW_NUMBER() OVER (
                PARTITION BY player_name, team ORDER BY games_in_archetype DESC
            ) AS rn
            FROM player_archetype_counts
        )
        SELECT player_name, team, usage_tier, impact_tier, shooting_efficiency_tier,
               games_in_archetype
        FROM player_modal
        WHERE rn = 1
        """,
        [SEASON],
    )

    if not archetype_players.empty:
        archetypes = (
            archetype_players.groupby(["usage_tier", "impact_tier", "shooting_efficiency_tier"])
            .agg(players=("player_name", "count"))
            .reset_index()
            .query("players >= 2")
            .sort_values("players", ascending=False)
        )

        fig_arch = px.sunburst(
            archetypes,
            path=["usage_tier", "impact_tier", "shooting_efficiency_tier"],
            values="players",
            title="Player Archetype Hierarchy (click segments, then filter below)",
            color="players",
            color_continuous_scale="Blues",
        )
        apply_arena_theme(fig_arch, height=600)
        st.plotly_chart(fig_arch, width="stretch")

        st.subheader(":material/manage_search: Drill Into Archetype")
        usage_tiers = ["All"] + sorted(archetype_players["usage_tier"].unique().tolist())
        sel_usage = st.selectbox("Usage Tier", usage_tiers, key="arch_usage")

        filtered = archetype_players
        if sel_usage != "All":
            filtered = filtered[filtered["usage_tier"] == sel_usage]

        impact_tiers = ["All"] + sorted(filtered["impact_tier"].unique().tolist())
        sel_impact = st.selectbox("Impact Tier", impact_tiers, key="arch_impact")

        if sel_impact != "All":
            filtered = filtered[filtered["impact_tier"] == sel_impact]

        eff_tiers = ["All"] + sorted(filtered["shooting_efficiency_tier"].unique().tolist())
        sel_eff = st.selectbox("Shooting Efficiency Tier", eff_tiers, key="arch_eff")

        if sel_eff != "All":
            filtered = filtered[filtered["shooting_efficiency_tier"] == sel_eff]

        st.caption(f"Showing {len(filtered)} players matching selection")
        st.dataframe(
            filtered[
                [
                    "player_name",
                    "team",
                    "usage_tier",
                    "impact_tier",
                    "shooting_efficiency_tier",
                    "games_in_archetype",
                ]
            ]
            .sort_values("games_in_archetype", ascending=False)
            .reset_index(drop=True),
            width="stretch",
            hide_index=True,
        )

    st.subheader("Player Specialists")
    specialists = query(
        """
        SELECT player_name, team, count(*) AS games,
               round(avg(points), 1) AS ppg,
               round(avg(box_plus_minus), 2) AS bpm,
               round(100.0 * sum(CASE WHEN is_versatile THEN 1 ELSE 0 END) / count(*), 1) AS pct_versatile,
               round(100.0 * sum(CASE WHEN is_defensive_specialist THEN 1 ELSE 0 END) / count(*), 1) AS pct_def_spec,
               round(100.0 * sum(CASE WHEN is_three_and_d THEN 1 ELSE 0 END) / count(*), 1) AS pct_three_and_d
        FROM main_intermediate.int_player_performance
        WHERE season_start_year = ? AND minutes_played >= 20
        GROUP BY player_name, team
        HAVING count(*) >= ?
        """,
        [SEASON, min_games],
    )

    col1, col2, col3 = st.columns(3)
    with col1:
        versatile = specialists[specialists["pct_versatile"] >= 25].sort_values(
            "pct_versatile", ascending=False
        )
        st.metric("Versatile", len(versatile))
        st.caption("≥25% of games with double-double stats")
        st.dataframe(
            versatile[["player_name", "team", "ppg", "pct_versatile"]],
            hide_index=True,
            width="stretch",
        )
    with col2:
        def_spec = specialists[specialists["pct_def_spec"] >= 25].sort_values(
            "pct_def_spec", ascending=False
        )
        st.metric("Defensive Specialists", len(def_spec))
        st.caption("≥25% of games as defensive specialist")
        st.dataframe(
            def_spec[["player_name", "team", "ppg", "pct_def_spec"]],
            hide_index=True,
            width="stretch",
        )
    with col3:
        three_d = specialists[specialists["pct_three_and_d"] >= 25].sort_values(
            "pct_three_and_d", ascending=False
        )
        st.metric("Three-and-D", len(three_d))
        st.caption("≥25% of games as 3-and-D player")
        st.dataframe(
            three_d[["player_name", "team", "ppg", "pct_three_and_d"]],
            hide_index=True,
            width="stretch",
        )