# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## What this is

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.
~7000 playlists across hourly and priority sources (Spotify store_id = 286).

## Setup

```bash
pip install -r requirements.txt
playwright install chromium --with-deps   # or use system Chrome (channel="chrome" already set)
cp .env.shadow .env
```

## The four scripts

### 1. Catalog tracks presence (`catalog_tracks_check.py`)

Checks whether each playlist has a current tracklist in Insights and any catalog tracks.
Populates `DEV_ENGINEERING.MSTEAD.NON_PRIORITY_PLAYLIST_CATALOG_TRACKS_CHECK`.
Run setup SQL first: `catalog_tracks_check_setup.sql`

### 2. Metadata check (`spotify_metadata_check.py`)

Checks whether each playlist has name and artwork on Spotify.
Populates `FACTS.PROD.PLAYLIST_METADATA_CHECK`.
Run setup SQL first: `playlist_metadata_check_setup.sql`

### 3. ISRC-level tracklist comparison (`tracklist_count_check.py`)

Compares ISRCs in our tracklist tables against the live Spotify snapshot, position by position.
Populates `FACTS.PROD.PLAYLIST_TRACK_COUNT_CHECK` (summary) and `FACTS.PROD.PLAYLIST_TRACK_COUNT_CHECK_DETAIL` (per position).
Run setup SQL first: `tracklist_count_check_setup.sql`

```bash
python tracklist_count_check.py
python tracklist_count_check.py --source hourly --limit 100 --stale-hours 48
python tracklist_count_check.py --playlist-id <id> --force   # force recheck one playlist
python tracklist_count_check.py --refresh                    # refresh insights counts before checking
```

### 4. Chartmetric refresh (`cm_refresh_stale.py`)

Visits CM playlist pages for CM_STALE playlists to trigger re-ingest.
Uses a persistent browser session (`.cm_session/`). Attempts auto-login via password on first run (manual fallback if needed).
Stamps `CM_REFRESHED_AT` in Snowflake — skips playlists refreshed within the last 23 hours.

```bash
python cm_refresh_stale.py
python cm_refresh_stale.py --dry-run
python cm_refresh_stale.py --limit 50 --source priority
python cm_refresh_stale.py --reset-session   # force re-login
```

## Standalone utilities

- **`check_empty_playlists.py`** — checks which IDs have zero Spotify tracks. No Snowflake dependency.
- **`check_playlist_metadata.py`** — checks name/artwork. No Snowflake dependency.

## Key Snowflake tables (FACTS.PROD)

| Table | Populated by |
|---|---|
| `PLAYLIST_CATALOG_TRACKS_CHECK` | `catalog_tracks_check.py` |
| `PLAYLIST_METADATA_CHECK` | `spotify_metadata_check.py` |
| `PLAYLIST_TRACK_COUNT_CHECK` | `tracklist_count_check.py` |
| `PLAYLIST_TRACK_COUNT_CHECK_DETAIL` | `tracklist_count_check.py` |

## Critical rules

### Tracklist filter (matches ows-playlist)

```sql
WHERE last_added_on_date IS NOT NULL
  AND (removed_on IS NULL OR removed_on < last_added_on_date)
  AND (
      playlist_type IN ('PERSONALIZED', 'ALGORITHMIC', 'STATION', 'RADIO')
      OR (current_position IS NOT NULL AND current_position <= playlist_track_count)
  )
```

For PERSONALIZED/ALGORITHMIC/STATION/RADIO: cap result at `playlist_track_count` to match ows-playlist
behaviour (use `LEAST(COUNT(...), MAX(playlist_track_count))` or `QUALIFY row_num <= playlist_track_count`).

### ISRC normalisation

Always strip hyphens: `isrc.upper().replace("-", "")`. Spotify returns e.g. `GB-SMU-24-67828`.

### STATUS values in PLAYLIST_TRACK_COUNT_CHECK_DETAIL

- `MATCH` — same ISRC at this position
- `DIFFERENT_ISRC` — same track name+artist, different ISRC (re-release)
- `WRONG_TRACK` — different track entirely at this position (Insights is stale)
- `MISSING` — Insights has nothing at this position

### Stale detail rows

`SPOTIFY_CHECKED_AT IS NULL` = pre-migration rows with no status data. Always filter these out.

### INSIGHTS_LATEST_TRACK_DATE column name

Despite the name, this is Chartmetric's latest track date (MAX of `last_added_on_date` from
`l_spotify_playlist.start_sys_period`). It is NOT Insights' own check date.
In dashboard queries it is aliased as `chartmetric_latest_track_date`.

### Dashboard query

See `dashboard_widget.sql`. One row per playlist. Never return one row per track/position.
Use `ARRAY_COMPACT` not `ARRAY_AGG IGNORE NULLS` (Snowflake syntax).