# Playlist Status Check

Check the current state of Spotify priority playlists by comparing Spotify against Insights — ISRCs, positions, track counts, and freshness.

## Modes

- **Single playlist**: `/playlist-status-check 37i9dQZF1DWSTeI2WWFaia` — full tracklist diff with per-track position analysis
- **Bulk (all priority playlists)**: `/playlist-status-check` with no argument — full ISRC and position comparison across all 663 priority playlists, generates an HTML report

## Bulk Mode Scripts

Two persisted scripts live in the skill folder (`~/.claude/skills/playlist-status-check/`):

- **`bulk_check.py`** — fetches all 663 priority playlists from Snowflake + Spotify, compares them, and writes results to `/tmp/bulk_results.json`. Run with `python3 ~/.claude/skills/playlist-status-check/bulk_check.py` (must be the interpreter that has `snowflake-connector-python` and `requests` installed — use `pyenv which python3` to confirm).
- **`gen_report.py`** — reads `/tmp/bulk_results.json` and writes the HTML report to `~/Downloads/priority_playlist_status_report.html`. Run with the same interpreter.
- **`gen_slack.py`** — reads `/tmp/bulk_results.json` and generates a Slack-formatted summary. Copies it to the clipboard and saves it to `~/Downloads/priority_playlist_slack_summary.txt`. Run with the same interpreter.

Run `bulk_check.py` first, then `gen_report.py`. Run `gen_slack.py` if the user asks for a Slack summary or wants to share results with the team. Expected runtime for the bulk check is ~10 minutes for 663 playlists (or near-instant if the Spotify cache is warm).

**Spotify result cache**: `bulk_check.py` caches Spotify API responses for 15 minutes in `~/.claude/skills/playlist-status-check/.spotify_cache.json`. Immediate reruns (e.g. to regenerate the report with fresh Snowflake data) skip all Spotify calls. To force a full refetch, pass `--refresh`:

```bash
python3 ~/.claude/skills/playlist-status-check/bulk_check.py --refresh
```

---

## Prerequisites: Spotify Credentials

1. Check `~/.claude/skills/playlist-status-check/.env` for `SPOTIFY_CLIENT_ID` and `SPOTIFY_CLIENT_SECRET`. If present, use it.
2. Otherwise ask the user:

   > I need Spotify API credentials to continue.
   > - **[A]** Provide a path to an existing `.env` file containing `SPOTIFY_CLIENT_ID` and `SPOTIFY_CLIENT_SECRET`
   > - **[B]** Enter them now — I'll save to `~/.claude/skills/playlist-status-check/.env`

Once resolved, get a token:

```bash
SPOTIFY_CLIENT_ID=$(grep SPOTIFY_CLIENT_ID <env_path> | cut -d= -f2)
SPOTIFY_CLIENT_SECRET=$(grep SPOTIFY_CLIENT_SECRET <env_path> | cut -d= -f2)

TOKEN=$(curl -s -X POST "https://accounts.spotify.com/api/token" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=client_credentials&client_id=$SPOTIFY_CLIENT_ID&client_secret=$SPOTIFY_CLIENT_SECRET" \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['access_token'])")
```

---

## Spotify Tracklist Fetcher (used by both modes)

Use this Python function to fetch the full tracklist for one playlist, returning valid tracks only:

```python
import requests, time

def fetch_spotify_tracklist(playlist_id, token):
    tracks = []
    unavailable = 0
    null_isrc = 0
    latest_added_at = None
    url = f"https://api.spotify.com/v1/playlists/{playlist_id}/tracks"
    params = {"fields": "items(added_at,track(name,artists(name),external_ids(isrc))),next,total", "limit": 100}
    headers = {"Authorization": f"Bearer {token}"}
    position = 1
    while url:
        r = requests.get(url, params=params, headers=headers)
        if r.status_code == 429:
            time.sleep(int(r.headers.get("Retry-After", 5)))
            continue
        data = r.json()
        for item in data.get("items", []):
            added_at = item.get("added_at")
            if latest_added_at is None or (added_at and added_at > latest_added_at):
                latest_added_at = added_at
            t = item.get("track")
            if t is None:
                unavailable += 1
            elif not t.get("external_ids", {}).get("isrc"):
                null_isrc += 1
            else:
                tracks.append({
                    "position": position,
                    "isrc": t["external_ids"]["isrc"].upper(),
                    "name": t["name"],
                    "artist": t["artists"][0]["name"] if t.get("artists") else "",
                    "added_at": added_at,
                })
            position += 1
        url = data.get("next")
        params = {}  # next URL has params embedded
    return tracks, unavailable, null_isrc, latest_added_at
```

Respect rate limits: Spotify allows ~100 requests/30s. In bulk mode, after every 80 playlist fetches (accounting for pagination), pause 10 seconds.

---

## Insights Tracklist Query

For a single playlist:
```sql
SELECT isrc, current_position, chartmetric_track_name, chartmetric_artist_name,
       last_added_on_date, days_on_playlist, playlist_name,
       playlist_track_count, playlist_follower_count
FROM FACTS.PROD.V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST
WHERE store_playlist_id = '<playlist_id>'
  AND store_id = 286
  AND streams_country IS NULL
  AND last_added_on_date IS NOT NULL
  AND (removed_on IS NULL OR removed_on < last_added_on_date)
  AND current_position IS NOT NULL
ORDER BY current_position
```

For bulk mode, run once for all 663 playlists (pass all IDs in the IN clause):
```sql
SELECT store_playlist_id, isrc, current_position, chartmetric_track_name,
       last_added_on_date, playlist_track_count, playlist_follower_count
FROM FACTS.PROD.V_PLAYLISTS_BY_PLAYLIST_CURRENT_TRACKLIST
WHERE store_id = 286
  AND streams_country IS NULL
  AND last_added_on_date IS NOT NULL
  AND (removed_on IS NULL OR removed_on < last_added_on_date)
  AND current_position IS NOT NULL
  AND store_playlist_id IN (<all 663 IDs>)
ORDER BY store_playlist_id, current_position
```

Group results by `store_playlist_id` in Python before comparing.

---

## Comparison Logic (per playlist)

Given `spotify_tracks` (list of {position, isrc, name, artist, added_at}) and `insights_tracks` (list of {isrc, current_position, name}):

```python
spotify_isrcs = {t["isrc"] for t in spotify_tracks}
insights_isrcs = {t["isrc"].upper() for t in insights_tracks}

matched     = spotify_isrcs & insights_isrcs
only_spotify  = spotify_isrcs - insights_isrcs   # missing from Insights
only_insights = insights_isrcs - spotify_isrcs   # extra in Insights

match_pct = len(matched) / max(len(spotify_isrcs), len(insights_isrcs)) * 100 if spotify_isrcs or insights_isrcs else 0

# Position mismatches for matched ISRCs
spotify_pos  = {t["isrc"]: t["position"] for t in spotify_tracks}
insights_pos = {t["isrc"].upper(): t["current_position"] for t in insights_tracks}
position_mismatches = [
    {"isrc": isrc, "spotify": spotify_pos[isrc], "insights": insights_pos[isrc],
     "diff": insights_pos[isrc] - spotify_pos[isrc]}
    for isrc in matched if spotify_pos.get(isrc) != insights_pos.get(isrc)
]
```

Freshness delta = `spotify_latest_added_at − insights_latest_update` (in minutes).

**Status classification** (per playlist):

| Condition | Status |
|-----------|--------|
| Spotify API returns 404 | ❌ Not on Spotify (playlist deleted or unpublished) |
| Spotify API returns any other non-200 | ❓ Spotify error |
| No Insights data | ❌ Not in Insights |
| ISRC diff > 5 | ❌ Significant mismatch |
| ISRC diff 1–5 | ⚠️ Small difference (investigate — may be null-ISRC or unavailable tracks) |
| ISRC diff = 0, position mismatches > 0 | ⚠️ Position drift |
| ISRC diff = 0, no position mismatches | ✅ Up to date |

Note: there is no staleness check. ChartMetric only scrapes when something genuinely changes, so timestamp differences without ISRC/position differences are not meaningful. The "Last updated (Insights)" column is shown for any playlist that fails other checks.

**Spotify fetch return values**: `fetch_spotify` returns `("not_found", 0, 0, None)` on 404, `(None, 0, 0, None)` on other errors, and `(tracks_list, unavailable, null_isrc, latest_added_at)` on success. Map `"not_found"` → status `not_on_spotify` before calling `compare()`.

**Position numbering**: Track positions in `tracks_list` use a ChartMetric-adjusted counter that skips both unavailable slots (`item.track is None`) and null-ISRC tracks. ChartMetric excludes both from its feed and renumbers accordingly. This avoids false position drift caused by geo-unavailable or unresolvable tracks.

---

## Single Playlist Mode — Output

Look up the playlist in PRIORITY_PLAYLISTS first (Step 1), then run the comparison, then display:

```
Playlist Status — {playlist_name}
Spotify ID: {playlist_id}
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Priority playlist: Yes / No

Track count
  Spotify:  {N} valid tracks  ({unavailable} unavailable, {null_isrc} null-ISRC)
  Insights: {N} tracks  (declared: {declared})

Freshness
  Spotify latest added_at:   {datetime} UTC
  Insights latest update:    {datetime} UTC
  Delta:                     {X min / Xh Ym}  ✅ / ⚠️ / ❌

Tracklist match: {pct}%  ✅ / ⚠️ / ❌
  Matched: {N} / {max} ISRCs

  Missing from Insights ({N}):
    #{pos}  {ISRC}  {track} — {artist}  [added {added_at}]
    ...

  Extra in Insights ({N}):
    #{pos}  {ISRC}  {track} — {artist}
    ...

Position mismatches ({N} matched tracks):
  #{spotify_pos} → #{insights_pos} ({±diff})  {ISRC}  {track} — {artist}
  ...

Possible areas to investigate:
  - <context-specific suggestions based on what was found — suggestions only, not conclusions>
```

---

## Bulk Mode — Output

Write a self-contained HTML report to `~/Downloads/priority_playlist_status_report.html`.

**HTML report structure:**
- Header: title, generation timestamp, total checked
- Summary cards: counts per status category (✅ Up to date / ✅ ISRCs match / ⚠️ Small difference / ⚠️ Position drift / ⚠️ Stale / ❌ Significant mismatch / ❌ Not in Insights / ❌ Not on Spotify / ❓ Spotify error)
- Note below ⚠️ Small difference card: *"Differences of 1–5 tracks may be due to null-ISRC or geo-unavailable tracks — requires investigation."*
- **Sortable, filterable table** with columns:
  - Playlist Name (linked to `https://open.spotify.com/playlist/{id}`)
  - Status badge (colour-coded)
  - Spotify Tracks
  - Insights Tracks
  - Diff (coloured: green=0, amber=1–5, red=>5)
  - Missing from Insights
  - Extra in Insights
  - Position Mismatches
  - Freshness delta (Insights latest update vs Spotify latest added_at where available)
- Filter buttons by status category
- Sort by clicking column headers (default: sort by status severity desc, then playlist name)
- Colour scheme: green (✅), amber (⚠️), red (❌)

After writing, confirm: `Report written to ~/Downloads/priority_playlist_status_report.html — {N} playlists checked ({N} ✅, {N} ⚠️, {N} ❌).`