import { useState } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
import { BarChart, Bar, XAxis, YAxis, Tooltip, ResponsiveContainer, CartesianGrid, Cell } from "recharts";

const N = (v) => (v != null ? Number(v) : 0);

const CSV = "read_csv_auto('https://us.data.motherduck.com/spotify_tracks/spotify_tracks.csv')";

const CustomTooltip = ({ active, payload }) => {
  if (!active || !payload?.length) return null;
  const d = payload[0].payload;
  return (
    <div style={{ background: "#fff", padding: "10px 14px", fontSize: 11, fontFamily: "inherit", border: "none", boxShadow: "0 2px 12px rgba(0,0,0,0.12)" }}>
      <p style={{ fontWeight: 700, margin: 0, letterSpacing: "0.02em" }}>{N(d.tempo_bucket)}–{N(d.tempo_bucket) + 10} BPM</p>
      <p style={{ margin: "4px 0 0", color: "#888" }}>{N(d.cnt)} tracks</p>
      <p style={{ margin: "2px 0 0", color: "#888" }}>dance {N(d.avg_dance).toFixed(2)} · energy {N(d.avg_energy).toFixed(2)}</p>
    </div>
  );
};

const Kpi = ({ value, label, loading }) => (
  <div>
    {loading ? (
      <div style={{ height: 40, width: 80, background: "#f0f0f0", marginBottom: 4 }} className="animate-pulse" />
    ) : (
      <p style={{ fontSize: 36, fontWeight: 800, color: "#111", margin: 0, lineHeight: 1, letterSpacing: "-0.03em" }}>{value}</p>
    )}
    <p style={{ fontSize: 10, color: "#999", margin: "6px 0 0", textTransform: "uppercase", letterSpacing: "0.12em", fontWeight: 500 }}>{label}</p>
  </div>
);

export default function SpotifyExplorer() {
  const [genre, setGenre] = useState("jazz");
  const [tempoMin, setTempoMin] = useState(60);
  const [tempoMax, setTempoMax] = useState(200);

  const genresQuery = useSQLQuery(`SELECT DISTINCT track_genre FROM ${CSV} ORDER BY track_genre`);
  const genres = Array.isArray(genresQuery.data) ? genresQuery.data.map(r => r.track_genre) : [];

  const summaryQuery = useSQLQuery(`SELECT COUNT(*) as cnt, ROUND(AVG(tempo),1) as avg_tempo, ROUND(AVG(danceability),3) as avg_dance, ROUND(AVG(energy),3) as avg_energy FROM ${CSV} WHERE track_genre = '${genre}'`);
  const summary = Array.isArray(summaryQuery.data) && summaryQuery.data.length > 0 ? summaryQuery.data[0] : null;

  const histQuery = useSQLQuery(`SELECT CAST(FLOOR(tempo / 10) * 10 AS INTEGER) as tempo_bucket, COUNT(*) as cnt, ROUND(AVG(danceability), 3) as avg_dance, ROUND(AVG(energy), 3) as avg_energy, ROUND(AVG(popularity), 1) as avg_pop FROM ${CSV} WHERE track_genre = '${genre}' AND tempo > 0 GROUP BY 1 ORDER BY 1`);
  const histData = (Array.isArray(histQuery.data) ? histQuery.data : []).map(r => ({
    ...r,
    cnt: N(r.cnt),
    tempo_bucket: N(r.tempo_bucket),
    inRange: N(r.tempo_bucket) >= tempoMin && N(r.tempo_bucket) < tempoMax,
  }));

  const tracksQuery = useSQLQuery(`SELECT track_name, artists, tempo, popularity, danceability, energy, valence FROM ${CSV} WHERE track_genre = '${genre}' AND tempo BETWEEN ${tempoMin} AND ${tempoMax} ORDER BY popularity DESC LIMIT 10`);
  const tracks = Array.isArray(tracksQuery.data) ? tracksQuery.data : [];

  return (
    <div style={{ background: "#fff", minHeight: "100vh", fontFamily: "'Helvetica Neue', Helvetica, Arial, sans-serif" }}>
      <div style={{ padding: "32px 28px" }}>
        <div style={{ marginBottom: 32 }}>
          <h1 style={{ fontSize: 42, fontWeight: 900, color: "#111", margin: 0, lineHeight: 1, letterSpacing: "-0.04em" }}>
            Spotify Tracks
          </h1>
          <p style={{ fontSize: 12, color: "#999", margin: "8px 0 0", textTransform: "uppercase", letterSpacing: "0.14em", fontWeight: 500 }}>
            114,000 tracks · 114 genres
          </p>
        </div>

        <div style={{ height: 3, background: "#111", marginBottom: 28 }} />

        <div style={{ display: "flex", gap: 32, alignItems: "flex-end", marginBottom: 32, flexWrap: "wrap" }}>
          <div>
            <label style={{ display: "block", fontSize: 10, textTransform: "uppercase", letterSpacing: "0.12em", color: "#999", marginBottom: 6, fontWeight: 500 }}>Genre</label>
            <select
              value={genre}
              onChange={e => setGenre(e.target.value)}
              style={{
                fontSize: 13,
                fontWeight: 600,
                padding: "8px 32px 8px 12px",
                border: "2px solid #111",
                borderRadius: 0,
                background: "#fff",
                color: "#111",
                appearance: "none",
                cursor: "pointer",
                minWidth: 180,
                fontFamily: "inherit",
              }}
            >
              {genres.length > 0 ? genres.map(g => <option key={g} value={g}>{g}</option>) : <option value={genre}>{genre}</option>}
            </select>
          </div>
          <div>
            <label style={{ display: "block", fontSize: 10, textTransform: "uppercase", letterSpacing: "0.12em", color: "#999", marginBottom: 6, fontWeight: 500 }}>Min BPM</label>
            <div style={{ display: "flex", alignItems: "center", gap: 10 }}>
              <input
                type="range" min={0} max={240} step={10} value={tempoMin}
                onChange={e => setTempoMin(Math.min(Number(e.target.value), tempoMax - 10))}
                style={{ width: 120, accentColor: "#111" }}
              />
              <span style={{ fontSize: 14, fontWeight: 700, color: "#111", fontVariantNumeric: "tabular-nums", minWidth: 28 }}>{tempoMin}</span>
            </div>
          </div>
          <div>
            <label style={{ display: "block", fontSize: 10, textTransform: "uppercase", letterSpacing: "0.12em", color: "#999", marginBottom: 6, fontWeight: 500 }}>Max BPM</label>
            <div style={{ display: "flex", alignItems: "center", gap: 10 }}>
              <input
                type="range" min={0} max={250} step={10} value={tempoMax}
                onChange={e => setTempoMax(Math.max(Number(e.target.value), tempoMin + 10))}
                style={{ width: 120, accentColor: "#111" }}
              />
              <span style={{ fontSize: 14, fontWeight: 700, color: "#111", fontVariantNumeric: "tabular-nums", minWidth: 28 }}>{tempoMax}</span>
            </div>
          </div>
        </div>

        <div style={{ display: "grid", gridTemplateColumns: "repeat(4, 1fr)", gap: 24, marginBottom: 36 }}>
          <Kpi value={summary ? N(summary.cnt).toLocaleString() : "—"} label="Tracks" loading={summaryQuery.isLoading} />
          <Kpi value={summary ? N(summary.avg_tempo).toFixed(1) : "—"} label="Avg Tempo" loading={summaryQuery.isLoading} />
          <Kpi value={summary ? N(summary.avg_dance).toFixed(2) : "—"} label="Danceability" loading={summaryQuery.isLoading} />
          <Kpi value={summary ? N(summary.avg_energy).toFixed(2) : "—"} label="Energy" loading={summaryQuery.isLoading} />
        </div>

        <div style={{ marginBottom: 32 }}>
          <p style={{ fontSize: 10, textTransform: "uppercase", letterSpacing: "0.12em", color: "#999", margin: "0 0 12px", fontWeight: 500 }}>Tempo Distribution</p>
          {histQuery.isLoading ? (
            <div style={{ height: 170, background: "#f8f8f8" }} className="animate-pulse" />
          ) : (
            <ResponsiveContainer width="100%" height={170}>
              <BarChart data={histData} barCategoryGap="12%">
                <CartesianGrid strokeDasharray="0" stroke="#f0f0f0" vertical={false} />
                <XAxis dataKey="tempo_bucket" fontSize={9} tickFormatter={v => v} axisLine={{ stroke: "#111", strokeWidth: 1 }} tickLine={false} tick={{ fill: "#999" }} />
                <YAxis fontSize={9} axisLine={false} tickLine={false} tick={{ fill: "#999" }} />
                <Tooltip content={<CustomTooltip />} cursor={{ fill: "rgba(0,0,0,0.03)" }} />
                <Bar dataKey="cnt" radius={0}>
                  {histData.map((entry, i) => (
                    <Cell key={i} fill={entry.inRange ? "#111" : "#e0e0e0"} />
                  ))}
                </Bar>
              </BarChart>
            </ResponsiveContainer>
          )}
        </div>

        <div style={{ height: 1, background: "#e0e0e0", marginBottom: 20 }} />

        <p style={{ fontSize: 10, textTransform: "uppercase", letterSpacing: "0.12em", color: "#999", margin: "0 0 10px", fontWeight: 500 }}>
          Top Tracks ({tracks.length})
        </p>
        {tracksQuery.isLoading ? (
          <div className="animate-pulse" style={{ display: "flex", flexDirection: "column", gap: 8 }}>
            {[1,2,3,4,5].map(i => <div key={i} style={{ height: 16, background: "#f0f0f0", width: i % 2 === 0 ? "60%" : "80%" }} />)}
          </div>
        ) : (
          <div style={{ overflowX: "auto" }}>
            <table style={{ width: "100%", borderCollapse: "collapse", fontSize: 12, fontFamily: "inherit" }}>
              <thead>
                <tr style={{ borderBottom: "2px solid #111" }}>
                  {["Track", "Artist", "BPM", "Pop", "Dance", "Energy"].map(h => (
                    <th key={h} style={{ textAlign: "left", padding: "6px 8px", fontSize: 9, textTransform: "uppercase", letterSpacing: "0.1em", color: "#999", fontWeight: 600 }}>{h}</th>
                  ))}
                </tr>
              </thead>
              <tbody>
                {tracks.map((t, i) => (
                  <tr key={i} style={{ borderBottom: "1px solid #f0f0f0" }}>
                    <td style={{ padding: "7px 8px", fontWeight: 600, color: "#111", maxWidth: 200, overflow: "hidden", textOverflow: "ellipsis", whiteSpace: "nowrap" }}>{t.track_name}</td>
                    <td style={{ padding: "7px 8px", color: "#888" }}>{t.artists}</td>
                    <td style={{ padding: "7px 8px", color: "#111", fontVariantNumeric: "tabular-nums" }}>{N(t.tempo).toFixed(0)}</td>
                    <td style={{ padding: "7px 8px", color: "#111", fontVariantNumeric: "tabular-nums" }}>{N(t.popularity)}</td>
                    <td style={{ padding: "7px 8px", color: "#111", fontVariantNumeric: "tabular-nums" }}>{N(t.danceability).toFixed(2)}</td>
                    <td style={{ padding: "7px 8px", color: "#111", fontVariantNumeric: "tabular-nums" }}>{N(t.energy).toFixed(2)}</td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        )}
      </div>
    </div>
  );
}

Interactive explorer for 114k Spotify tracks. Filter by genre and tempo range to see tempo distribution, audio features, and top tracks.

Spotify Tracks Explorer

Comments