# Playlist Coverage Check

Python scripts that cross-reference Insights playlist data (Snowflake/Chartmetric) against
the live Spotify API to surface tracklist coverage gaps and data quality issues.
Results are written back to Snowflake and power QA dashboard tiles.

## Setup

```bash
pip install -r requirements.txt
playwright install chromium --with-deps   # for cm_refresh_stale.py; or use system Chrome (see below)
cp .env.shadow .env                        # fill in your credentials
```

All scripts in this repo call `load_dotenv(...)`, so values in `.env` are loaded
automatically when you run them.

### `.env` values needed

| Variable | Description |
|---|---|
| `SNOWFLAKE_ACCOUNT` | `orchard` |
| `SNOWFLAKE_USER` | your `@sonymusic-pde.com` email |
| `SNOWFLAKE_PRIVATE_KEY_PATH` | path to your RSA private key (key-pair auth) |
| `SNOWFLAKE_WAREHOUSE` | default: `DEV_PERFORMANCE_WAREHOUSE` |
| `SPOTIFY_CLIENT_ID` / `SPOTIFY_CLIENT_SECRET` | Spotify API app credentials |
| `CHARTMETRIC_EMAIL` / `CHARTMETRIC_PASSWORD` | CM credentials for `cm_refresh_stale.py` |

### First-time Snowflake table setup

Run each setup SQL file once in Snowflake before running the corresponding script:

```
catalog_tracks_check_setup.sql        → for catalog_tracks_check.py
playlist_metadata_check_setup.sql     → for spotify_metadata_check.py
tracklist_count_check_setup.sql       → for tracklist_count_check.py + cm_refresh_stale.py
```

---

## Scripts

### `tracklist_count_check.py` — ISRC-level tracklist comparison (main check)

Compares ISRCs in our Chartmetric-derived tracklist tables against the live Spotify snapshot,
position by position. Writes a summary row per playlist and a detail row per Spotify position.

**Snowflake tables populated:**
- `FACTS.PROD.PLAYLIST_TRACK_COUNT_CHECK` — one row per playlist
- `FACTS.PROD.PLAYLIST_TRACK_COUNT_CHECK_DETAIL` — one row per Spotify position

```bash
python tracklist_count_check.py                                  # all playlists
python tracklist_count_check.py --source hourly                  # hourly only
python tracklist_count_check.py --source priority --limit 100    # priority, cap at 100
python tracklist_count_check.py --stale-hours 48                 # only re-check after 48h
python tracklist_count_check.py --playlist-id <id> --force       # force recheck one playlist
python tracklist_count_check.py --refresh                        # refresh Insights counts before checking
```

**STATUS values in the detail table:**

| Status | Meaning |
|---|---|
| `MATCH` | Same ISRC at this position in both Spotify and Insights |
| `DIFFERENT_ISRC` | Same track name + artist, different ISRC (e.g. re-release) |
| `WRONG_TRACK` | Different track entirely at this position — Insights is stale |
| `MISSING` | Insights has nothing at this position |

---

### `cm_refresh_stale.py` — Trigger Chartmetric re-ingest for stale playlists

Visits Chartmetric playlist pages for playlists flagged as `CM_STALE` in the dashboard,
triggering CM to queue a re-ingest (same as visiting the page in a browser).

Uses a persistent browser session stored in `.cm_session/`. First run opens a browser.
If `CHARTMETRIC_EMAIL` / `CHARTMETRIC_PASSWORD` are set in `.env`, the script attempts
auto-login and falls back to manual login if needed. Subsequent runs reuse the saved
session silently.

If `playwright install` hangs (corporate network), use system Chrome instead by setting
`channel="chrome"` — already the default in this script.

After visiting each playlist, stamps `CM_REFRESHED_AT` in Snowflake so the same playlist
is not revisited for 23 hours.

```bash
python cm_refresh_stale.py                        # all CM_STALE playlists
python cm_refresh_stale.py --dry-run              # print list without visiting
python cm_refresh_stale.py --limit 50             # cap at 50
python cm_refresh_stale.py --source priority      # priority playlists only
python cm_refresh_stale.py --reset-session        # clear saved session and re-login
```

---

### `catalog_tracks_check.py` — Catalog tracks presence check

Checks whether each playlist has a current tracklist in Insights and whether it contains
any Orchard/AWAL/SME catalog tracks.

**Snowflake table:** `FACTS.PROD.PLAYLIST_CATALOG_TRACKS_CHECK`

```bash
python catalog_tracks_check.py
python catalog_tracks_check.py --stale-hours 48 --limit 100
```

---

### `spotify_metadata_check.py` — Playlist metadata check

Checks whether each playlist has a name and artwork on Spotify.

**Snowflake table:** `FACTS.PROD.PLAYLIST_METADATA_CHECK`

```bash
python spotify_metadata_check.py
python spotify_metadata_check.py --stale-hours 48 --limit 100
```

---

### Standalone utilities

- **`check_empty_playlists.py`** — given a file or list of IDs, checks which have zero tracks on Spotify. No Snowflake dependency. Progress saved to JSON so runs are resumable.
- **`check_playlist_metadata.py`** — same pattern, checks name/artwork.

---

## Dashboard query

`dashboard_widget.sql` — one row per playlist showing what's wrong and why.

Key columns:

| Column | Description |
|---|---|
| `visual_match` | No missing positions; all mismatches are same-track-different-ISRC |
| `spotify_has_repeats` | Spotify has duplicate track IDs (some playlists repeat tracks) |
| `unique_tracks_missing` | Real gap count ignoring repeats |
| `transposed` | More positions wrong than right — Insights has a significantly stale version |
| `has_gaps` | TRUE when there are gap positions (missing slots with later present tracks) |
| `mismatched_count` | Number of `WRONG_TRACK` / `DIFFERENT_ISRC` positions |
| `mismatched_positions` | Array of `{position, isrc_spotify, isrc_insights, same_track_diff_isrc}` |
| `completely_different` | No MATCH or DIFFERENT_ISRC rows at all |
| `insights_matches_cm` | TRUE when gap is fully explained by a CM limitation (not our fault) |
| `discrepancy_type` | Single label: `ISRC_INCONSISTENCY`, `CM_TRACK_CAP`, `CM_STALE`, `UNEXPLAINED`, `UNKNOWN` |
| `truncated_on_insights` | Missing positions start after the last present position in Insights |
| `insights_too_long` | Insights track count exceeds Spotify track count |
| `chartmetric_latest_track_date` | Date CM last recorded a track addition for this playlist |
| `cm_staleness_days` | Days between CM's latest track date and Spotify's latest added date |

---

## Key data notes

- Source tracklist data: `FACTS.PROD.PLAYLISTS_HOURLY_BY_PLAYLIST_CURRENT_TRACKLIST` and `_PRIORITY_`
- These tables have multiple rows per track (one per country) — always deduplicate on `(store_playlist_id, isrc, current_position)`
- Spotify store_id = **286**
- ISRCs are normalised (hyphens stripped) everywhere: `isrc.upper().replace("-", "")`
- `PERSONALIZED`/`ALGORITHMIC`/`STATION`/`RADIO` playlists have no `current_position` — comparison is ISRC-set only, capped at `playlist_track_count`