Streamlit_NBA/streamlit_app/app_pages/overview.py

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

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

SEASON, SEASON_LABEL = get_selected_season()

st.title(f":material/dashboard: League Pulse — {SEASON_LABEL}")

# --- KPIs with delta vs prior season ---
summary = query(
    """
    SELECT count(*) AS total_games,
           sum(CASE WHEN is_overtime THEN 1 ELSE 0 END) AS ot_games,
           round(avg(total_points), 1) AS avg_pts,
           round(avg(point_differential), 1) AS avg_margin,
           round(100.0 * sum(CASE WHEN is_home_team_winner THEN 1 ELSE 0 END) / count(*), 1) AS home_win_pct,
           min(game_date) AS first_game,
           max(game_date) AS last_game
    FROM main_marts.fct_game_results
    WHERE season_key IN (SELECT season_key FROM main_marts.dim_seasons WHERE season_start_year = ?)
    """,
    [SEASON],
)

prior = query(
    """
    SELECT round(avg(total_points), 1) AS avg_pts,
           round(avg(point_differential), 1) AS avg_margin,
           round(100.0 * sum(CASE WHEN is_home_team_winner THEN 1 ELSE 0 END) / count(*), 1) AS home_win_pct
    FROM main_marts.fct_game_results
    WHERE season_key IN (SELECT season_key FROM main_marts.dim_seasons WHERE season_start_year = ?)
    """,
    [SEASON - 1],
)

has_prior = not prior.empty and prior["avg_pts"][0] is not None
c1, c2, c3, c4, c5 = st.columns(5)
c1.metric("Total Games", f"{summary['total_games'][0]:,}")
c2.metric(
    "Avg Points/Game",
    summary["avg_pts"][0],
    delta=f"{summary['avg_pts'][0] - prior['avg_pts'][0]:+.1f}" if has_prior else None,
)
c3.metric(
    "Avg Margin",
    summary["avg_margin"][0],
    delta=f"{summary['avg_margin'][0] - prior['avg_margin'][0]:+.1f}" if has_prior else None,
)
c4.metric(
    "Home Win %",
    f"{summary['home_win_pct'][0]}%",
    delta=f"{summary['home_win_pct'][0] - prior['home_win_pct'][0]:+.1f}%" if has_prior else None,
)
c5.metric("OT Games", f"{summary['ot_games'][0]:,}")

st.caption(f"Season: {summary['first_game'][0]} → {summary['last_game'][0]} | Deltas vs prior season")

# --- Row 2: Competitiveness donut + Top standings ---
col1, col2 = st.columns(2)

with col1:
    comp = query(
        """
        SELECT game_competitiveness_tier AS tier, count(*) AS games
        FROM main_marts.fct_game_results
        WHERE season_key IN (SELECT season_key FROM main_marts.dim_seasons WHERE season_start_year = ?)
        GROUP BY tier ORDER BY games DESC
        """,
        [SEASON],
    )
    fig = px.pie(
        comp,
        names="tier",
        values="games",
        title="Game Competitiveness",
        hole=0.4,
        color_discrete_sequence=px.colors.qualitative.Set2,
    )
    fig.update_traces(textinfo="percent+label")
    apply_arena_theme(fig)
    st.plotly_chart(fig, width="stretch")

with col2:
    standings = query(
        """
        SELECT team, count(*) AS games,
               sum(CASE WHEN game_result = 'W' THEN 1 ELSE 0 END) AS wins,
               sum(CASE WHEN game_result = 'L' THEN 1 ELSE 0 END) AS losses,
               round(100.0 * sum(CASE WHEN game_result = 'W' THEN 1 ELSE 0 END) / count(*), 1) AS win_pct,
               round(avg(net_rating), 1) AS net_rtg
        FROM main_intermediate.int_team_performance
        WHERE season_start_year = ?
        GROUP BY team ORDER BY win_pct DESC LIMIT 8
        """,
        [SEASON],
    )
    st.subheader(":material/emoji_events: Top Teams")
    st.dataframe(
        standings,
        column_config={
            "win_pct": st.column_config.ProgressColumn(
                "Win%", min_value=0, max_value=100, format="%.1f%%"
            ),
        },
        hide_index=True,
        width="stretch",
    )

# --- Row 3: Weekly scoring trend ---
weekly = query(
    """
    SELECT date_trunc('week', game_date)::date::varchar AS week,
           count(*) AS games,
           round(avg(total_points), 1) AS avg_pts,
           round(avg(point_differential), 1) AS avg_margin
    FROM main_marts.fct_game_results
    WHERE season_key IN (SELECT season_key FROM main_marts.dim_seasons WHERE season_start_year = ?)
    GROUP BY week ORDER BY week
    """,
    [SEASON],
)
fig_weekly = px.area(
    weekly,
    x="week",
    y="avg_pts",
    title="Weekly Scoring Trend",
    color_discrete_sequence=[AMBER],
)
fig_weekly.update_layout(xaxis_title="Week", yaxis_title="Avg Total Points")
apply_arena_theme(fig_weekly, height=350)
st.plotly_chart(fig_weekly, width="stretch")

# --- Row 4: Calendar heatmap + Season highlights ---
col1, col2 = st.columns([2, 1])

with col1:
    cal_data = query(
        """
        SELECT game_date::varchar AS day, count(*) AS games
        FROM main_marts.fct_game_results
        WHERE season_key IN (SELECT season_key FROM main_marts.dim_seasons WHERE season_start_year = ?)
        GROUP BY game_date ORDER BY game_date
        """,
        [SEASON],
    )
    if not cal_data.empty:
        cal_list = cal_data.values.tolist()
        cal_option = {
            "tooltip": {"position": "top", "formatter": "{c0} games on {b0}"},
            "visualMap": {
                "min": 0,
                "max": int(cal_data["games"].max()),
                "calculable": True,
                "orient": "horizontal",
                "left": "center",
                "top": "top",
                "inRange": {"color": ["#151D2A", "#9be9a8", "#40c463", "#30a14e", "#10B981"]},
            },
            "calendar": {
                "range": [cal_list[0][0][:7], cal_list[-1][0][:7]],
                "cellSize": ["auto", 15],
            },
            "series": [{"type": "heatmap", "coordinateSystem": "calendar", "data": cal_list}],
        }
        st_echarts(options=cal_option, height="200px")

with col2:
    st.subheader(":material/local_fire_department: Top Performances")
    highlights = query(
        """
        SELECT player_name AS player, team,
               game_date::date::varchar AS date, points AS pts,
               assists AS ast, total_rebounds AS reb
        FROM main_intermediate.int_player_performance
        WHERE season_start_year = ?
        ORDER BY points DESC LIMIT 5
        """,
        [SEASON],
    )
    st.dataframe(highlights, hide_index=True, width="stretch")

# --- Row 5: Recent games ---
st.subheader("Recent Games")
games = query(
    """
    SELECT g.game_date, ht.team_abbr AS home, vt.team_abbr AS away,
           g.home_points, g.visitor_points, wt.team_abbr AS winner,
           g.point_differential AS margin, g.game_competitiveness_tier AS type
    FROM main_marts.fct_game_results g
    LEFT JOIN main_marts.dim_teams ht ON g.home_team_key = ht.team_key
    LEFT JOIN main_marts.dim_teams vt ON g.visitor_team_key = vt.team_key
    LEFT JOIN main_marts.dim_teams wt ON g.winning_team_key = wt.team_key
    WHERE g.season_key IN (SELECT season_key FROM main_marts.dim_seasons WHERE season_start_year = ?)
    ORDER BY g.game_date DESC LIMIT 25
    """,
    [SEASON],
)
st.dataframe(games, hide_index=True, width="stretch")