dashboard.py

"""Streaming Catalog Analytics Dashboard.

Streamlit front end over the dbt mart layer. All data is synthetic
(generated by scripts/generate_seed.py); artists, albums, and labels
are fictional.

Run after building the database:

    cd streaming_analytics && dbt build --profiles-dir . && cd ..
    streamlit run dashboard.py
"""
import streamlit as st
import duckdb
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from pathlib import Path

# =============================================================================
# PAGE CONFIG
# =============================================================================
st.set_page_config(
    page_title="Streaming Catalog Analytics",
    layout="wide",
    initial_sidebar_state="expanded"
)

# =============================================================================
# DATABASE CONNECTION
# =============================================================================
@st.cache_resource
def get_connection():
    """Create a read-only connection to the DuckDB database."""
    db_path = Path(__file__).parent / "streaming_analytics" / "streaming.duckdb"
    return duckdb.connect(str(db_path), read_only=True)

conn = get_connection()

# =============================================================================
# DATA LOADING (CACHED)
# =============================================================================
@st.cache_data
def load_genres():
    return conn.execute("SELECT * FROM main.dim_genres ORDER BY stream_rank").fetchdf()

@st.cache_data
def load_labels():
    return conn.execute("SELECT * FROM main.rpt_label_market_share ORDER BY stream_rank").fetchdf()

@st.cache_data
def load_artist_tiers():
    return conn.execute("""
        SELECT
            stream_tier,
            count(*) as artist_count,
            sum(total_streams) as total_streams
        FROM main.dim_artists
        GROUP BY stream_tier
    """).fetchdf()

@st.cache_data
def load_yearly_trends():
    return conn.execute("""
        SELECT
            release_year,
            count(*) as tracks,
            count(distinct artist_name) as artists,
            round(avg(popularity), 1) as avg_popularity,
            sum(stream_count) as total_streams
        FROM main.stg_streaming_tracks
        GROUP BY release_year
        ORDER BY release_year
    """).fetchdf()

@st.cache_data
def load_overview_metrics():
    return conn.execute("""
        SELECT
            count(*) as total_tracks,
            count(distinct artist_name) as total_artists,
            sum(stream_count) as total_streams,
            round(avg(popularity), 1) as avg_popularity
        FROM main.stg_streaming_tracks
    """).fetchdf().iloc[0]

@st.cache_data
def load_genre_audio_profiles():
    return conn.execute("""
        SELECT
            genre,
            avg_danceability,
            avg_energy,
            avg_tempo,
            avg_loudness,
            audio_character
        FROM main.dim_genres
        ORDER BY avg_energy DESC
    """).fetchdf()

# =============================================================================
# LOAD DATA
# =============================================================================
genres_df = load_genres()
labels_df = load_labels()
artist_tiers_df = load_artist_tiers()
yearly_df = load_yearly_trends()
metrics = load_overview_metrics()
audio_profiles_df = load_genre_audio_profiles()

# =============================================================================
# SIDEBAR
# =============================================================================
with st.sidebar:
    st.markdown("### About")
    st.markdown("""
    This dashboard visualizes a **dbt project** built on a synthetic
    music-streaming catalog of 85,000 tracks (2015-2025). All artists,
    albums, and labels are fictional.

    **Data Pipeline:**
    - Raw CSV (seed) to Staging to Intermediate to Marts
    - 60 automated tests
    - Full documentation

    **Tech Stack:**
    - dbt-core + DuckDB
    - Streamlit + Plotly
    """)
    st.markdown("---")
    st.markdown("### Navigation")
    page = st.radio("Select View", ["Overview", "Genre Analysis", "Label Analysis", "Trends"])

# =============================================================================
# MAIN CONTENT
# =============================================================================

if page == "Overview":
    st.title("Streaming Catalog Analytics Dashboard")
    st.markdown("*Powered by dbt + DuckDB. Synthetic data; all names fictional.*")

    # KPI Cards
    col1, col2, col3, col4 = st.columns(4)

    with col1:
        st.metric("Total Tracks", f"{metrics['total_tracks']:,}")
    with col2:
        st.metric("Unique Artists", f"{metrics['total_artists']:,}")
    with col3:
        st.metric("Total Streams", f"{metrics['total_streams']/1e9:.1f}B")
    with col4:
        st.metric("Avg Popularity", f"{metrics['avg_popularity']}")

    st.markdown("---")

    # Two column layout
    col_left, col_right = st.columns(2)

    with col_left:
        st.subheader("Genre Market Share")
        fig_genre = px.pie(
            genres_df,
            values='total_streams',
            names='genre',
            hole=0.4,
            color_discrete_sequence=px.colors.qualitative.Set3
        )
        fig_genre.update_layout(
            showlegend=True,
            legend=dict(orientation="h", yanchor="bottom", y=-0.3),
            margin=dict(t=20, b=20, l=20, r=20)
        )
        st.plotly_chart(fig_genre, width="stretch")

    with col_right:
        st.subheader("Artist Distribution by Stream Tier")
        # Order tiers correctly
        tier_order = ['Platinum', 'Gold', 'Silver', 'Bronze']
        artist_tiers_df['stream_tier'] = pd.Categorical(
            artist_tiers_df['stream_tier'],
            categories=tier_order,
            ordered=True
        )
        artist_tiers_df = artist_tiers_df.sort_values('stream_tier')

        fig_tiers = px.bar(
            artist_tiers_df,
            x='stream_tier',
            y='artist_count',
            color='stream_tier',
            color_discrete_map={
                'Platinum': '#E5E4E2',
                'Gold': '#FFD700',
                'Silver': '#C0C0C0',
                'Bronze': '#CD7F32'
            },
            text='artist_count'
        )
        fig_tiers.update_traces(texttemplate='%{text:,}', textposition='outside')
        fig_tiers.update_layout(
            showlegend=False,
            xaxis_title="",
            yaxis_title="Number of Artists",
            margin=dict(t=20, b=20)
        )
        st.plotly_chart(fig_tiers, width="stretch")

elif page == "Genre Analysis":
    st.title("Genre Analysis")

    # Genre market share bar chart
    st.subheader("Market Share by Genre")
    fig_bar = px.bar(
        genres_df.sort_values('stream_market_share_pct', ascending=True),
        x='stream_market_share_pct',
        y='genre',
        orientation='h',
        color='stream_market_share_pct',
        color_continuous_scale='Viridis',
        text='stream_market_share_pct'
    )
    fig_bar.update_traces(texttemplate='%{text:.1f}%', textposition='outside')
    fig_bar.update_layout(
        xaxis_title="Market Share (%)",
        yaxis_title="",
        coloraxis_showscale=False,
        height=500
    )
    st.plotly_chart(fig_bar, width="stretch")

    st.markdown("---")

    # Audio fingerprints radar chart
    st.subheader("Genre Audio Fingerprints")
    st.markdown("*How do genres differ sonically?*")

    selected_genres = st.multiselect(
        "Select genres to compare:",
        options=audio_profiles_df['genre'].tolist(),
        default=['Hip-Hop', 'Classical', 'EDM', 'Country']
    )

    if selected_genres:
        filtered_audio = audio_profiles_df[audio_profiles_df['genre'].isin(selected_genres)]

        fig_radar = go.Figure()

        for _, row in filtered_audio.iterrows():
            fig_radar.add_trace(go.Scatterpolar(
                r=[row['avg_danceability'], row['avg_energy'], row['avg_tempo']/200,
                   (row['avg_loudness']+60)/60],
                theta=['Danceability', 'Energy', 'Tempo (normalized)', 'Loudness (normalized)'],
                fill='toself',
                name=row['genre']
            ))

        fig_radar.update_layout(
            polar=dict(radialaxis=dict(visible=True, range=[0, 1])),
            showlegend=True,
            height=500
        )
        st.plotly_chart(fig_radar, width="stretch")

    # Genre data table
    st.subheader("Genre Metrics Table")
    st.dataframe(
        genres_df[['genre', 'total_tracks', 'unique_artists', 'total_streams',
                   'stream_market_share_pct', 'avg_popularity', 'market_position']],
        width="stretch",
        hide_index=True
    )

elif page == "Label Analysis":
    st.title("Record Label Analysis")
    st.markdown("*All labels are fictional.*")

    # Efficiency vs Market Share scatter
    st.subheader("Label Efficiency vs Market Share")
    st.markdown("*Are labels getting more streams per track than average?*")

    fig_scatter = px.scatter(
        labels_df,
        x='stream_market_share_pct',
        y='efficiency_index',
        size='catalog_size',
        color='competitive_tier',
        hover_name='label',
        color_discrete_map={
            'Major': '#2E86AB',
            'Mid-Tier': '#A23B72',
            'Indie/Boutique': '#F18F01'
        },
        text='label'
    )
    fig_scatter.add_hline(y=100, line_dash="dash", line_color="gray",
                          annotation_text="Market Average")
    fig_scatter.update_traces(textposition='top center')
    fig_scatter.update_layout(
        xaxis_title="Market Share (%)",
        yaxis_title="Efficiency Index (100 = average)",
        height=500
    )
    st.plotly_chart(fig_scatter, width="stretch")

    st.markdown("---")

    # Label comparison
    col1, col2 = st.columns(2)

    with col1:
        st.subheader("Roster Size")
        fig_roster = px.bar(
            labels_df.sort_values('roster_size', ascending=True),
            x='roster_size',
            y='label',
            orientation='h',
            color='competitive_tier',
            color_discrete_map={
                'Major': '#2E86AB',
                'Mid-Tier': '#A23B72',
                'Indie/Boutique': '#F18F01'
            }
        )
        fig_roster.update_layout(
            xaxis_title="Artists on Roster",
            yaxis_title="",
            showlegend=False,
            height=400
        )
        st.plotly_chart(fig_roster, width="stretch")

    with col2:
        st.subheader("Streams per Artist")
        labels_df['streams_per_artist'] = labels_df['total_streams'] / labels_df['roster_size']
        fig_spa = px.bar(
            labels_df.sort_values('streams_per_artist', ascending=True),
            x='streams_per_artist',
            y='label',
            orientation='h',
            color='competitive_tier',
            color_discrete_map={
                'Major': '#2E86AB',
                'Mid-Tier': '#A23B72',
                'Indie/Boutique': '#F18F01'
            }
        )
        fig_spa.update_layout(
            xaxis_title="Avg Streams per Artist",
            yaxis_title="",
            showlegend=False,
            height=400
        )
        st.plotly_chart(fig_spa, width="stretch")

    # Label data table
    st.subheader("Label Metrics Table")
    st.dataframe(
        labels_df[['label', 'competitive_tier', 'stream_market_share_pct',
                   'catalog_size', 'roster_size', 'efficiency_index', 'content_strategy']],
        width="stretch",
        hide_index=True
    )

elif page == "Trends":
    st.title("Release Trends (2015-2025)")

    # Track releases over time
    st.subheader("Annual Track Releases")
    fig_releases = px.area(
        yearly_df,
        x='release_year',
        y='tracks',
        markers=True,
        color_discrete_sequence=['#2E86AB']
    )
    fig_releases.update_layout(
        xaxis_title="Year",
        yaxis_title="Tracks Released",
        height=400
    )
    st.plotly_chart(fig_releases, width="stretch")

    st.markdown("---")

    # Multi-metric trends
    col1, col2 = st.columns(2)

    with col1:
        st.subheader("Unique Artists per Year")
        fig_artists = px.line(
            yearly_df,
            x='release_year',
            y='artists',
            markers=True,
            color_discrete_sequence=['#535353']
        )
        fig_artists.update_layout(
            xaxis_title="Year",
            yaxis_title="Unique Artists",
            height=350
        )
        st.plotly_chart(fig_artists, width="stretch")

    with col2:
        st.subheader("Total Streams by Release Year")
        fig_streams = px.bar(
            yearly_df,
            x='release_year',
            y='total_streams',
            color='total_streams',
            color_continuous_scale='Blues'
        )
        fig_streams.update_layout(
            xaxis_title="Year",
            yaxis_title="Total Streams",
            coloraxis_showscale=False,
            height=350
        )
        st.plotly_chart(fig_streams, width="stretch")

    # Yearly data table
    st.subheader("Year-over-Year Metrics")
    st.dataframe(
        yearly_df.rename(columns={
            'release_year': 'Year',
            'tracks': 'Tracks',
            'artists': 'Artists',
            'avg_popularity': 'Avg Popularity',
            'total_streams': 'Total Streams'
        }),
        width="stretch",
        hide_index=True
    )

# =============================================================================
# FOOTER
# =============================================================================
st.markdown("---")
st.markdown(
    """
    <div style='text-align: center; color: gray;'>
    Built with dbt + DuckDB + Streamlit. Synthetic demo data; all names fictional.
    </div>
    """,
    unsafe_allow_html=True
)