import plotly.express as px
import plotly.graph_objects as go
import streamlit as st

from utils.charts import AMBER, MADE_EMERALD, MISS_CRIMSON, apply_arena_theme
from utils.db import query

st.title(":material/trending_up: Era Trends")

tab_scoring, tab_drama, tab_three = st.tabs(
    [
        ":material/sports_basketball: Scoring & Pace",
        ":material/theater_comedy: Game Character",
        ":material/my_location: Three-Point Era",
    ]
)

with tab_scoring:
    pace = query(
        """
        SELECT s.season_display AS season, s.season_start_year,
               round(avg(g.matchup_pace), 1) AS avg_pace,
               round(avg(g.total_points), 1) AS avg_points,
               round(avg(g.home_effective_fg_pct + g.visitor_effective_fg_pct) / 2 * 100, 1) AS avg_efg_pct
        FROM main_intermediate.int_games_enriched g
        JOIN main_marts.dim_seasons s ON g.season_start_year = s.season_start_year
        GROUP BY s.season_display, s.season_start_year
        ORDER BY s.season_start_year
        """
    )

    fig = go.Figure()
    fig.add_trace(
        go.Scatter(
            x=pace["season"],
            y=pace["avg_points"],
            name="Avg Points/Game",
            mode="lines+markers",
            line=dict(color=AMBER, width=3),
        )
    )
    fig.add_trace(
        go.Scatter(
            x=pace["season"],
            y=pace["avg_pace"],
            name="Pace",
            mode="lines+markers",
            yaxis="y2",
            line=dict(color=MADE_EMERALD, width=2, dash="dot"),
        )
    )
    fig.update_layout(
        title="Scoring & Pace Evolution",
        yaxis=dict(title="Avg Points/Game"),
        yaxis2=dict(title="Pace (possessions/48min)", overlaying="y", side="right"),
    )
    apply_arena_theme(fig, height=450)
    st.plotly_chart(fig, width="stretch")

    pace_clean = pace.dropna(subset=["avg_efg_pct"])
    fig_eff = px.area(
        pace_clean,
        x="season",
        y="avg_efg_pct",
        title="League eFG% Trend",
        color_discrete_sequence=[MADE_EMERALD],
    )
    fig_eff.update_layout(yaxis_title="eFG%")
    apply_arena_theme(fig_eff, height=350)
    st.plotly_chart(fig_eff, width="stretch")

with tab_drama:
    drama = query(
        """
        SELECT s.season_display AS season,
               round(100.0 * sum(CASE WHEN g.is_overtime THEN 1 ELSE 0 END) / count(*), 1) AS ot_pct,
               round(100.0 * sum(CASE WHEN g.point_differential <= 5 THEN 1 ELSE 0 END) / count(*), 1) AS clutch_pct,
               round(100.0 * sum(CASE WHEN g.point_differential >= 20 THEN 1 ELSE 0 END) / count(*), 1) AS blowout_pct
        FROM main_intermediate.int_games_enriched g
        JOIN main_marts.dim_seasons s ON g.season_start_year = s.season_start_year
        GROUP BY s.season_display, s.season_start_year
        ORDER BY s.season_start_year
        """
    )
    fig = px.area(
        drama,
        x="season",
        y=["clutch_pct", "blowout_pct", "ot_pct"],
        title="Game Drama: Clutch (≤5pt margin) vs Blowouts (20+) vs Overtime",
        color_discrete_sequence=[MADE_EMERALD, MISS_CRIMSON, AMBER],
    )
    fig.update_layout(yaxis_title="% of Games", legend_title="Category")
    apply_arena_theme(fig, height=450)
    st.plotly_chart(fig, width="stretch")

    home = query(
        """
        SELECT s.season_display AS season,
               round(100.0 * sum(CASE WHEN g.is_home_team_winner THEN 1 ELSE 0 END) / count(*), 1) AS home_win_pct
        FROM main_marts.fct_game_results g
        JOIN main_marts.dim_seasons s ON g.season_key = s.season_key
        GROUP BY s.season_display, s.season_start_year
        ORDER BY s.season_start_year
        """
    )
    fig2 = px.line(
        home,
        x="season",
        y="home_win_pct",
        title="Home Court Advantage Over Time",
        markers=True,
        color_discrete_sequence=[AMBER],
    )
    fig2.update_layout(yaxis_range=[40, 70])
    fig2.add_hline(y=50, line_dash="dash", line_color="gray", annotation_text="50%")
    apply_arena_theme(fig2, height=400)
    st.plotly_chart(fig2, width="stretch")

with tab_three:
    three_pt = query(
        """
        SELECT s.season_display AS season, s.season_start_year,
               round(avg(three_point_rate) * 100, 1) AS avg_three_rate,
               round(avg(CASE WHEN three_point_fg_pct > 0 THEN three_point_fg_pct ELSE NULL END) * 100, 1) AS avg_three_pct,
               round(avg(three_point_attempts), 1) AS avg_3pa
        FROM main_marts.fct_player_game_shooting sh
        JOIN main_marts.dim_seasons s ON sh.season_key = s.season_key
        WHERE sh.fg_attempts >= 5
        GROUP BY s.season_display, s.season_start_year
        ORDER BY s.season_start_year
        """
    )

    if not three_pt.empty:
        fig = go.Figure()
        fig.add_trace(
            go.Bar(
                x=three_pt["season"],
                y=three_pt["avg_three_rate"],
                name="3PA Rate (%)",
                marker_color=AMBER,
                opacity=0.75,
            )
        )
        fig.add_trace(
            go.Scatter(
                x=three_pt["season"],
                y=three_pt["avg_three_pct"],
                name="3P Accuracy (%)",
                mode="lines+markers",
                yaxis="y2",
                line=dict(color=MADE_EMERALD, width=3),
            )
        )
        fig.update_layout(
            title="The Three-Point Revolution: Volume vs Accuracy",
            yaxis=dict(title="3PA Rate (% of all FGA)"),
            yaxis2=dict(title="3P FG%", overlaying="y", side="right", range=[30, 50]),
            barmode="overlay",
        )
        apply_arena_theme(fig, height=450)
        st.plotly_chart(fig, width="stretch")

        fig2 = px.area(
            three_pt,
            x="season",
            y="avg_3pa",
            title="Avg Three-Point Attempts per Player-Game",
            color_discrete_sequence=[AMBER],
        )
        fig2.update_layout(yaxis_title="3PA per Game")
        apply_arena_theme(fig2, height=350)
        st.plotly_chart(fig2, width="stretch")
    else:
        st.info("Three-point shooting data not available.")