4. Building the home dashboard

# app.py
from flask import Flask, render_template, session, redirect, url_for
import sqlite3
from datetime import datetime
import os
from dotenv import load_dotenv

load_dotenv()

app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', 'dev-secret-key-change-in-production')

# Same SQLite file Chapter 16 wrote to.
DATABASE_PATH = 'music_time_machine.db'


def get_db_connection():
    """Open the Music Time Machine database. Returns None on failure."""
    try:
        conn = sqlite3.connect(DATABASE_PATH)
        conn.row_factory = sqlite3.Row  # access columns by name
        return conn
    except sqlite3.Error as e:
        print(f"Database connection error: {e}")
        return None


def _format_month(yyyy_mm):
    """'2026-03' -> 'Mar 2026'."""
    return datetime.strptime(yyyy_mm, '%Y-%m').strftime('%b %Y')


def calculate_taste_stats():
    """
    Compute the three home-dashboard cards from Ch16's snapshot schema.

    Returns a dict with:
      tracks_tracked:        int  -- size of the accumulated catalogue
      turnover_pct:          int|None  -- % of latest snapshot not in previous;
                                          None if fewer than two snapshots exist
      turnover_subtext:      str  -- "of latest snapshot is new", or empty-state hint
      consistent_artist:    str|None  -- artist appearing in the most snapshots
      consistent_subtext:    str  -- "in N of M snapshots", or empty-state hint
    """
    empty = {
        'tracks_tracked': 0,
        'turnover_pct': None,
        'turnover_subtext': 'No snapshots yet -- run a Ch16 snapshot to populate.',
        'consistent_artist': None,
        'consistent_subtext': 'No snapshots yet.',
    }

    conn = get_db_connection()
    if not conn:
        return empty

    try:
        cursor = conn.cursor()

        # 1. Tracks tracked: how many unique tracks have we ever seen?
        cursor.execute("SELECT COUNT(*) FROM tracks")
        tracks_tracked = cursor.fetchone()[0]

        # 2. Track turnover: % of latest snapshot that wasn't in the previous.
        #    All three subqueries filter on time_range so we don't mix
        #    short_term / medium_term / long_term snapshots when comparing.
        cursor.execute("""
            WITH latest AS (
                SELECT track_id FROM snapshots
                WHERE time_range = 'short_term'
                  AND snapshot_date = (
                      SELECT MAX(snapshot_date) FROM snapshots
                      WHERE time_range = 'short_term'
                  )
            ),
            prev AS (
                SELECT track_id FROM snapshots
                WHERE time_range = 'short_term'
                  AND snapshot_date = (
                      SELECT MAX(snapshot_date) FROM snapshots
                      WHERE time_range = 'short_term'
                        AND snapshot_date < (
                            SELECT MAX(snapshot_date) FROM snapshots
                            WHERE time_range = 'short_term'
                        )
                  )
            )
            SELECT
                COUNT(*) AS latest_size,
                SUM(CASE WHEN p.track_id IS NULL THEN 1 ELSE 0 END) AS new_count
            FROM latest l
            LEFT JOIN prev p ON p.track_id = l.track_id
        """)
        row = cursor.fetchone()
        latest_size = row['latest_size'] if row else 0
        new_count = row['new_count'] if row else 0

        # Empty-state: only one snapshot exists, no prev to compare against.
        cursor.execute("""
            SELECT COUNT(DISTINCT snapshot_date)
            FROM snapshots
            WHERE time_range = 'short_term'
        """)
        total_snapshots = cursor.fetchone()[0]

        if total_snapshots < 2 or latest_size == 0:
            turnover_pct = None
            turnover_subtext = 'Take another snapshot to see turnover.'
        else:
            turnover_pct = round(100.0 * new_count / latest_size)
            turnover_subtext = 'of latest snapshot is new'

        # 3. Most-consistent artist: appears in the most distinct snapshot dates.
        cursor.execute("""
            SELECT t.artist_name, COUNT(DISTINCT s.snapshot_date) AS appearances
            FROM tracks t
            JOIN snapshots s ON s.track_id = t.track_id
            WHERE s.time_range = 'short_term'
            GROUP BY t.artist_name
            ORDER BY appearances DESC
            LIMIT 1
        """)
        artist_row = cursor.fetchone()

        if artist_row and total_snapshots > 0:
            consistent_artist = artist_row['artist_name']
            consistent_subtext = (
                f"in {artist_row['appearances']} of {total_snapshots} snapshots"
            )
        else:
            consistent_artist = None
            consistent_subtext = 'Take a snapshot to surface your anchor artist.'

        return {
            'tracks_tracked': tracks_tracked,
            'turnover_pct': turnover_pct,
            'turnover_subtext': turnover_subtext,
            'consistent_artist': consistent_artist,
            'consistent_subtext': consistent_subtext,
        }

    except sqlite3.Error as e:
        print(f"Database query error: {e}")
        return empty
    finally:
        conn.close()


def get_taste_chart_data():
    """
    Chart data from the snapshot schema:
      - discovery rate: count of first-ever appearances per snapshot

    Returns {'mode': 'discoveries'|'empty', 'labels': [...],
             'datasets': [{'label': str, 'data': [...]}, ...]}
    """
    empty = {'mode': 'empty', 'labels': [], 'datasets': []}

    conn = get_db_connection()
    if not conn:
        return empty

    try:
        cursor = conn.cursor()

        cursor.execute("""
            SELECT strftime('%Y-%m', s.snapshot_date) AS month,
                   COUNT(*) AS discoveries
            FROM snapshots s
            WHERE s.time_range = 'short_term'
              AND NOT EXISTS (
                  SELECT 1 FROM snapshots earlier
                  WHERE earlier.track_id = s.track_id
                    AND earlier.snapshot_date < s.snapshot_date
              )
            GROUP BY month
            ORDER BY month
        """)
        rows = cursor.fetchall()
        return {
            'mode': 'discoveries',
            'labels': [_format_month(r['month']) for r in rows],
            'datasets': [
                {'label': 'New tracks', 'data': [r['discoveries'] for r in rows]},
            ],
        }

    except sqlite3.Error as e:
        print(f"Chart data query error: {e}")
        return empty
    finally:
        conn.close()


@app.route('/')
def home():
    """Home dashboard: taste-evolution stats plus discovery-rate chart."""
    if 'access_token' not in session:
        return redirect(url_for('login'))

    stats = calculate_taste_stats()
    chart_data = get_taste_chart_data()

    return render_template('home.html', stats=stats, chart_data=chart_data)


# Stubs for now -- the real routes arrive in later sections. Without these,
# base.html and the redirect above would crash with werkzeug.routing
# exceptions.BuildError because url_for(...) has nothing to resolve to yet.
@app.route('/login')
def login():
    return "Login coming on the next page", 200


@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('home'))


@app.route('/analytics')
def analytics():
    return "Analytics page coming in a later section", 200


@app.route('/playlists')
def playlists():
    return "Playlist Manager coming in a later section", 200


@app.route('/settings')
def settings():
    return "Settings page coming in a later section", 200


if __name__ == '__main__':
    app.run(debug=True)

<!-- templates/home.html -->
{% extends 'base.html' %}

{% block title %}Home - Music Time Machine{% endblock %}

{% block content %}
    <h1>Music Dashboard</h1>
    <p style="color: var(--text-muted); margin-bottom: 2rem;">
        Welcome back! Here's your listening activity at a glance.
    </p>
    
    <!-- Statistics Grid -->
    <div class="stats-grid">
        <div class="stat-card">
            <div class="stat-value">{{ stats.tracks_tracked }}</div>
            <div class="stat-label">Tracks tracked</div>
        </div>
        <div class="stat-card">
            <div class="stat-value">
                {% if stats.turnover_pct is not none %}{{ stats.turnover_pct }}%{% else %}—{% endif %}
            </div>
            <div class="stat-label">Track turnover</div>
            <div class="stat-subtext">{{ stats.turnover_subtext }}</div>
        </div>
        <div class="stat-card">
            <div class="stat-value">
                {{ stats.consistent_artist or '—' }}
            </div>
            <div class="stat-label">Most-consistent artist</div>
            <div class="stat-subtext">{{ stats.consistent_subtext }}</div>
        </div>
    </div>

    <!-- Discovery Timeline Chart -->
    <div class="chart-container">
        {% if chart_data.mode == 'discoveries' %}
            <h2 style="margin-bottom: 1.5rem;">New tracks discovered per snapshot</h2>
        {% else %}
            <h2 style="margin-bottom: 1.5rem;">Snapshot history</h2>
            <p style="color: var(--text-muted);">
                Run a Chapter 16 snapshot to start populating this chart.
            </p>
        {% endif %}
        <canvas id="trajectoryChart"></canvas>
    </div>
    
    <!-- Quick Actions -->
    <div class="card">
        <div class="card-header">Quick Actions</div>
        <div class="card-content">
            <a href="{{ url_for('analytics') }}" class="btn btn-primary" style="margin-right: 1rem;">
                View Detailed Analytics
            </a>
            <a href="{{ url_for('playlists') }}" class="btn btn-secondary">
                Generate Playlists
            </a>
        </div>
    </div>
{% endblock %}

{% block scripts %}
    <script>
        // Pass Python data to JavaScript. chart_data has shape:
        //   { mode: 'discoveries' | 'empty',
        //     labels: [...],
        //     datasets: [{ label, data }, ...] }
        const chartData = {{ chart_data|tojson|safe }};

        // Spotify-green palette for the discovery series.
        const palette = ['#1DB954', '#7C3AED'];

        const datasets = chartData.datasets.map((d, i) => ({
            label: d.label,
            data: d.data,
            borderColor: palette[i % palette.length],
            backgroundColor: palette[i % palette.length] + '1A', // ~10% alpha
            borderWidth: 2,
            fill: chartData.datasets.length === 1, // fill only when single series
            tension: 0.4
        }));

        const ctx = document.getElementById('trajectoryChart').getContext('2d');
        new Chart(ctx, {
            type: 'line',
            data: { labels: chartData.labels, datasets },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: { display: chartData.datasets.length > 1 },
                    tooltip: {
                        backgroundColor: '#282828',
                        titleColor: '#FFFFFF',
                        bodyColor: '#B3B3B3',
                        borderColor: '#404040',
                        borderWidth: 1
                    }
                },
                scales: {
                    y: {
                        beginAtZero: chartData.mode === 'discoveries',
                        ticks: { color: '#B3B3B3' },
                        grid: { color: '#404040' }
                    },
                    x: {
                        ticks: { color: '#B3B3B3' },
                        grid: { color: '#404040' }
                    }
                }
            }
        });
    </script>
{% endblock %}

const chartData = {{ chart_data|tojson|safe }};