Streamlit_NBA/streamlit_app/app_pages/teams.py

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 get_selected_season, query

SEASON, SEASON_LABEL = get_selected_season()

st.title(f":material/shield: Team Intelligence — {SEASON_LABEL}")

ratings = 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(offensive_rating), 1) AS off_rtg,
           round(avg(defensive_rating), 1) AS def_rtg,
           round(avg(net_rating), 1) AS net_rtg,
           round(avg(pace), 1) AS pace,
           round(avg(effective_fg_pct) * 100, 1) AS efg_pct,
           round(avg(turnover_rate) * 100, 1) AS tov_rate,
           round(avg(offensive_rebound_rate) * 100, 1) AS orb_rate,
           round(avg(free_throw_rate) * 100, 1) AS ft_rate
    FROM main_intermediate.int_team_performance
    WHERE season_start_year = ?
    GROUP BY team ORDER BY net_rtg DESC
    """,
    [SEASON],
)

tab_ratings, tab_factors, tab_drilldown = st.tabs(
    [
        ":material/bar_chart: Ratings & Standings",
        ":material/grid_view: Four Factors & Styles",
        ":material/manage_search: Team Drill-Down",
    ]
)

with tab_ratings:
    fig = px.scatter(
        ratings,
        x="off_rtg",
        y="def_rtg",
        text="team",
        size="win_pct",
        color="net_rtg",
        color_continuous_scale="RdYlGn",
        title="Offensive vs Defensive Rating (size = Win%)",
    )
    fig.update_traces(textposition="top center", marker=dict(sizemin=8))
    avg_off = ratings["off_rtg"].mean()
    avg_def = ratings["def_rtg"].mean()
    fig.add_hline(y=avg_def, line_dash="dash", line_color="gray", opacity=0.4)
    fig.add_vline(x=avg_off, line_dash="dash", line_color="gray", opacity=0.4)
    fig.update_layout(
        xaxis_title="Offensive Rating → (higher = better)",
        yaxis_title="← Defensive Rating (lower = better)",
        yaxis=dict(autorange="reversed"),
    )
    apply_arena_theme(fig, height=550)
    st.plotly_chart(fig, width="stretch")

    win_sorted = ratings.sort_values("win_pct", ascending=True)
    fig_win = px.bar(
        win_sorted,
        x="win_pct",
        y="team",
        orientation="h",
        color="net_rtg",
        color_continuous_scale="RdYlGn",
        title="Win % (color = Net Rating)",
    )
    fig_win.update_layout(yaxis_title="", xaxis_title="Win %")
    fig_win.add_vline(x=50, line_dash="dash", line_color="gray", opacity=0.5)
    apply_arena_theme(fig_win, height=600)
    st.plotly_chart(fig_win, width="stretch")

    st.dataframe(
        ratings,
        column_config={
            "win_pct": st.column_config.ProgressColumn(
                "Win%", min_value=0, max_value=100, format="%.1f%%"
            ),
            "efg_pct": st.column_config.ProgressColumn(
                "eFG%", min_value=45, max_value=60, format="%.1f%%"
            ),
        },
        hide_index=True,
        width="stretch",
    )

with tab_factors:
    st.subheader("Four Factors")
    st.caption(
        "The four key drivers of team success: eFG%, Turnover Rate, Offensive Rebound Rate, Free Throw Rate"
    )

    col1, col2 = st.columns(2)
    with col1:
        top10 = ratings.nlargest(10, "efg_pct")
        fig = px.bar(
            top10,
            x="efg_pct",
            y="team",
            orientation="h",
            color="efg_pct",
            color_continuous_scale="Greens",
            title="eFG% (Top 10)",
        )
        fig.update_layout(yaxis=dict(autorange="reversed"), showlegend=False)
        apply_arena_theme(fig, height=350)
        st.plotly_chart(fig, width="stretch")
    with col2:
        top10 = ratings.nsmallest(10, "tov_rate")
        fig = px.bar(
            top10,
            x="tov_rate",
            y="team",
            orientation="h",
            color="tov_rate",
            color_continuous_scale="Reds_r",
            title="Turnover Rate (Top 10 — lowest)",
        )
        fig.update_layout(yaxis=dict(autorange="reversed"), showlegend=False)
        apply_arena_theme(fig, height=350)
        st.plotly_chart(fig, width="stretch")

    col3, col4 = st.columns(2)
    with col3:
        top10 = ratings.nlargest(10, "orb_rate")
        fig = px.bar(
            top10,
            x="orb_rate",
            y="team",
            orientation="h",
            color="orb_rate",
            color_continuous_scale="Purples",
            title="Offensive Rebound Rate (Top 10)",
        )
        fig.update_layout(yaxis=dict(autorange="reversed"), showlegend=False)
        apply_arena_theme(fig, height=350)
        st.plotly_chart(fig, width="stretch")
    with col4:
        top10 = ratings.nlargest(10, "ft_rate")
        fig = px.bar(
            top10,
            x="ft_rate",
            y="team",
            orientation="h",
            color="ft_rate",
            color_continuous_scale="Oranges",
            title="Free Throw Rate (Top 10)",
        )
        fig.update_layout(yaxis=dict(autorange="reversed"), showlegend=False)
        apply_arena_theme(fig, height=350)
        st.plotly_chart(fig, width="stretch")

    st.subheader("Team Styles")
    col1, col2 = st.columns(2)
    with col1:
        styles = query(
            """
            SELECT shot_selection_style, ball_movement_style, count(*) AS games,
                   round(avg(CASE WHEN game_result = 'W' THEN 1.0 ELSE 0.0 END) * 100, 1) AS win_pct
            FROM main_intermediate.int_team_performance
            WHERE season_start_year = ?
            GROUP BY shot_selection_style, ball_movement_style
            ORDER BY win_pct DESC
            """,
            [SEASON],
        )
        fig = px.sunburst(
            styles,
            path=["shot_selection_style", "ball_movement_style"],
            values="games",
            color="win_pct",
            color_continuous_scale="RdYlGn",
            title="Offense: Shot Selection × Ball Movement",
        )
        apply_arena_theme(fig)
        st.plotly_chart(fig, width="stretch")

    with col2:
        defense = query(
            """
            SELECT defensive_activity, ball_movement_style, count(*) AS games,
                   round(avg(CASE WHEN game_result = 'W' THEN 1.0 ELSE 0.0 END) * 100, 1) AS win_pct
            FROM main_intermediate.int_team_performance
            WHERE season_start_year = ?
            GROUP BY defensive_activity, ball_movement_style
            ORDER BY win_pct DESC
            """,
            [SEASON],
        )
        fig = px.sunburst(
            defense,
            path=["defensive_activity", "ball_movement_style"],
            values="games",
            color="win_pct",
            color_continuous_scale="RdYlGn",
            title="Defense: Activity × Ball Movement",
        )
        apply_arena_theme(fig)
        st.plotly_chart(fig, width="stretch")

with tab_drilldown:
    team_list = ratings["team"].tolist()
    selected_team = st.selectbox("Select Team", team_list)

    trajectory = query(
        """
        SELECT game_date::date::varchar AS game_date,
               round(net_rating, 1) AS net_rtg,
               round(avg(net_rating) OVER (
                   ORDER BY game_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
               ), 1) AS rolling_net_rtg,
               round(avg(offensive_rating) OVER (
                   ORDER BY game_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
               ), 1) AS rolling_off,
               round(avg(defensive_rating) OVER (
                   ORDER BY game_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
               ), 1) AS rolling_def
        FROM main_intermediate.int_team_performance
        WHERE season_start_year = ? AND team = ?
        ORDER BY game_date
        """,
        [SEASON, selected_team],
    )

    if not trajectory.empty:
        fig = go.Figure()
        fig.add_trace(
            go.Scatter(
                x=trajectory["game_date"],
                y=trajectory["rolling_net_rtg"],
                name="Net Rating",
                mode="lines",
                line=dict(color=AMBER, width=3),
            )
        )
        fig.add_trace(
            go.Scatter(
                x=trajectory["game_date"],
                y=trajectory["rolling_off"],
                name="Offense",
                mode="lines",
                line=dict(color=MADE_EMERALD, width=1.5, dash="dot"),
            )
        )
        fig.add_trace(
            go.Scatter(
                x=trajectory["game_date"],
                y=trajectory["rolling_def"],
                name="Defense",
                mode="lines",
                line=dict(color=MISS_CRIMSON, width=1.5, dash="dot"),
            )
        )
        fig.add_hline(y=0, line_dash="dash", line_color="gray", opacity=0.4)
        fig.update_layout(
            title=f"{selected_team} — 10-Game Rolling Ratings",
            xaxis_title="Date",
            yaxis_title="Rating",
        )
        apply_arena_theme(fig, height=400)
        st.plotly_chart(fig, width="stretch")

    st.subheader(f"{selected_team} Game Log")
    game_log = query(
        """
        SELECT game_date::date::varchar AS date, opponent_team AS opp, game_result AS result,
               points AS pts, round(offensive_rating, 1) AS off_rtg,
               round(defensive_rating, 1) AS def_rtg, round(net_rating, 1) AS net_rtg,
               round(pace, 1) AS pace
        FROM main_intermediate.int_team_performance
        WHERE season_start_year = ? AND team = ?
        ORDER BY game_date DESC
        """,
        [SEASON, selected_team],
    )
    st.dataframe(game_log, hide_index=True, width="stretch")