# CLAUDE.md

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

## Overview

Python replacement for the .NET playlist duplication tool (`Sony.Filtr.AdminAPI` + `Sony.Filtr.Worker`). Consists of a **FastAPI web API** and a **Celery worker**.

## Commands

All commands must be run from the **repo root** (where `pyproject.toml` lives) using `uv`.

```sh
# Install dependencies
uv sync

# Run the API server (dev)
uv run python dev.py

# Run the Celery worker
uv run celery -A worker.tasks.celery_app worker --loglevel=info

# Run all tests
uv run pytest

# Run a single test
uv run pytest tests/integration/test_api.py::test_health_check -v

# Run a specific test layer
uv run pytest tests/security/ -v
uv run pytest tests/resilience/ -v
uv run pytest tests/contract/ -v
uv run pytest tests/e2e/ -v

# Run load tests (requires uvicorn running on :8000)
pip install locust
locust -f tests/load/locustfile.py --host http://localhost:8000

# Build and start the full stack (API + MySQL + Redis + worker) with Docker
docker compose up --build

# Start E2E test stack (MySQL pre-loaded with data from database/data/, port 8001)
docker compose -f docker-compose.test.yml up -d
E2E_API_URL=http://localhost:8001 uv run pytest tests/e2e/ -v -m e2e
docker compose -f docker-compose.test.yml down -v

# Lint
uv run ruff check playlist_sync worker tests

# Type check
uv run mypy playlist_sync worker
```

## Database Initialisation

```sh
# Fresh schema
mysql -u <user> -p <database> < database/00_init.sql

# Incremental migrations (run in order on existing DBs)
mysql -u root -p <local_db> < database/migrations/001_widen_image_columns.sql
mysql -u root -p <local_db> < database/migrations/002_add_id_column.sql
```

Migration 002 (`add_id_column`) is **optional** — the Python service uses a composite PK `(sync_id, time)` on `tblPlaylistSynchronizationLog` and does not require a single-column PK.

When using `docker-compose.test.yml`, MySQL auto-seeds from `database/data/` — no manual restore needed.

## Architecture

The API is a single `FastAPI` app defined in `playlist_sync/api.py`. The `get_session` dependency from `playlist_sync/services/database.py` is injected into every route via `Depends(get_session)`.

Services (`SyncTaskService`, `SyncLogService`, `ServiceAccountService`, `ApplicationService`) are instantiated per-request — not singletons. Exception: `ApplicationService` maintains a **class-level 5-minute in-memory cache** of `tblApplicationInstance` rows (mirrors Redis caching in the .NET stack).

The Celery worker (`worker/tasks.py`) is a separate process with two tasks:
- `execute_single_sync_task` — loads sync+account, fetches source playlist, routes to the correct synchronizer, writes result log and updates sync row.
- `periodic_sync_sweep` — dispatches individual tasks for all active syncs with an optional account-ID file filter and a second pass.

### Sync Flow

```
execute_single_sync_task(sync_id)
  → Load PlaylistSynchronization + ServiceAccount from DB
  → Validate application_id match
  → Fetch source (from_service_type=0 → Spotify) → GenericPlaylist
  → Route by account.music_service_id:
      1 (Spotify)    → SpotifySynchronizer   (SequenceMatcher-based diff, preserves duplicates)
      2 (Deezer)     → DeezerSynchronizer    (ISRC cache → diff)
      3 (YouTube)    → YoutubeSynchronizer   (video search + scoring)
      4 (SoundCloud) → SoundCloudSynchronizer (ISRC search)
  → update_sync_result() → add_sync_log()
```

### Platform Synchronizers

| Synchronizer | File | Algorithm |
|---|---|---|
| Spotify | `synchronizers/spotify.py` | Strip local tracks, then `difflib.SequenceMatcher` insert/delete edit script over the ordered URI sequence (preserves source duplicates) |
| Deezer | `synchronizers/deezer.py` | ISRC→DeezerId cache (7-day), diff, batch remove/add, order all |
| YouTube | `synchronizers/youtube.py` | ISRC cache + video search with official-channel scoring, blacklist/whitelist channels |
| SoundCloud | `synchronizers/soundcloud.py` | ISRC search (exact ISRC preferred), replace track list, update metadata |

### Duplicate-track handling

Spotify-target syncs **preserve source-track multiplicity**: if the source playlist has the same track twice, the target will too. This matches the deployed legacy production behaviour the user verified empirically; it is a deliberate divergence from the `Sony.Filtr.PlaylistSynchronization` .NET source code under `/Users/boris.babiy.sme/projects/filtr-api`, which uses `DistinctBy(t => t.SpotifyUri)`. The reference implementation that matches prod is `apollo-playlists-sync` (see `/Users/boris.babiy.sme/projects/apollo-playlists-sync/src/synchronizer/dsp/spotify.py`).

Deezer, YouTube, and SoundCloud targets continue to dedupe (those APIs/codepaths don't represent duplicates faithfully). When the source has duplicates, they log a `WARNING` via `playlist_sync.synchronizers.base.warn_if_source_has_duplicates(...)` so operators know the target is not a 1:1 copy of the source.

### Spotify diff algorithm

The Spotify synchronizer uses `difflib.SequenceMatcher` over the ordered URI sequences (`source_uris` vs `target_uris` after local-track removal). The `compare_sequences` helper in `playlist_sync/synchronizers/spotify.py` yields `(action, t1, t2, s1, s2, offset)` tuples for `insert`, `delete`, and `replace` opcodes; each opcode is applied directly via `spotify_client.add_tracks_at_position` / `delete_playlist_tracks_by_position`. There is no separate LCS reorder pass — `SequenceMatcher` already produces the minimal edit script.

### API Routes

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/health` | Health check |
| `GET` | `/apollo-api/app-markets/` | List all application markets |
| `GET` | `/PlaylistSync/playlists` | List all syncs (active and inactive) |
| `GET` | `/PlaylistSync/{country_code}/playlists/` | List syncs for a market with latest log data |
| `GET` | `/PlaylistSync/{country_code}/playlists/{sync_id}` | Get a single sync with latest log data |
| `POST` | `/PlaylistSync/{country_code}/playlists` | Create a sync |
| `PUT` | `/PlaylistSync/{country_code}/playlists/{sync_id}` | Update a sync |
| `DELETE` | `/PlaylistSync/{country_code}/playlists/{sync_id}` | Delete a sync |
| `POST` | `/PlaylistSync/{country_code}/playlists/{sync_id}/execute` | Trigger immediate sync |
| `GET` | `/PlaylistSync/{country_code}/playlists/{sync_id}/log` | Get sync logs (`limit`, `offset`) |

### Enums

```python
class ServiceType(IntEnum):   # Zero-based, matches .NET ServiceType
    Spotify = 0; Deezer = 1; YouTube = 2; SoundCloud = 3; Groove = 4

class MusicService(IntEnum):  # One-based, matches .NET MusicService
    Spotify = 1; Deezer = 2; YouTube = 3; SoundCloud = 4; Groove = 5
```

## Key Conventions

### Database models
- Auto-increment PKs use `Optional[int] = Field(default=None, primary_key=True, sa_column_kwargs={"name": "Id"})` — never bare `int`.
- **Exception**: `PlaylistSynchronizationLog` uses composite PK `(sync_id, time)` — no `id` column.
- Fields use **both** `alias=` and `sa_column_kwargs={"name": "..."}` together:
  - `alias=` controls **Pydantic JSON serialisation** only.
  - `sa_column_kwargs={"name": "..."}` controls the **actual DB column name** in SQL. Without it, SQLAlchemy defaults to the Python snake_case attribute name, which won't match PascalCase production MySQL columns.
  - Example: `service_type: int = Field(alias="ServiceType", sa_column_kwargs={"name": "ServiceType"})`
- `Application` uses legacy `str*`/`bln*`/`int*` prefixed column names mapped via `sa_column_kwargs`.
- **MySQL `BIT(1)` columns** require `sa_column=Column("colName", _Bit1Bool())` — **not** `sa_column_kwargs` with `bool`. SQLAlchemy's standard `Boolean` calls `bool()` on the raw value, making `b'\x00'` truthy. `_Bit1Bool` (in `playlist_sync/models/application.py`) overrides `result_processor` to handle raw bytes. Affected: `blnWorkout`, `blnIncludeOtherPlaylists`.
- API responses serialise **using alias names** (PascalCase for `PlaylistSynchronization`-based models).

### Response models
- **`ServiceAccountResponse`** uses `alias_generator=to_camel` for automatic camelCase serialisation. All routes returning it must include `response_model_by_alias=True`.
- **Naive datetimes from MySQL** are treated as UTC. Use `@field_serializer` to append `Z` when the .NET contract requires it.
- When adding a camelCase response model: use `alias_generator=to_camel` in `model_config` and `response_model_by_alias=True` on every route. Do **not** name fields camelCase directly — `from_attributes=True` mapping relies on snake_case attribute matching.
- **`PlaylistSyncResponse`** (all 3 GET playlist endpoints) is a plain `BaseModel`, not a SQLModel table. It merges `PlaylistSynchronization` + latest `PlaylistSynchronizationLog` + `ServiceAccount` + `InsertMedia` via `_build_sync_response()` + `_enrich_syncs()` helpers (3 additional batch queries). `synchronizedTrackCount` defaults to `0` even when DB is `NULL`.

### Testing
- Integration tests use `sqlite+aiosqlite:///:memory:` with `StaticPool` — no Docker/MySQL needed.
- Override DB session via `app.dependency_overrides[get_session] = override_fn` — **not** `unittest.mock.patch`. Always call `app.dependency_overrides.clear()` after the test.
- Close seed-data sessions before HTTP calls to avoid SQLite locking with `StaticPool`.
- `asyncio_mode = "auto"` is set in `pyproject.toml` — no `@pytest.mark.asyncio` needed.

### Configuration
`playlist_sync/config.py` is **not committed** — create from `.env.shadow`. Key variables:

| Variable | Default | Description |
|----------|---------|-------------|
| `DATABASE_URL` | `sqlite+aiosqlite:///./test.db` | Async DB URL (`mysql+aiomysql://` for prod) |
| `REDIS_URL` | `redis://localhost:6379/0` | Redis (health check) |
| `CELERY_BROKER_URL` / `CELERY_RESULT_BACKEND` | `redis://localhost:6379/0` | Celery |
| `SPOTIFY_CLIENT_ID` / `SPOTIFY_CLIENT_SECRET` | — | Spotify Web API credentials |
| `YOUTUBE_CLIENT_ID` / `YOUTUBE_CLIENT_SECRET` | — | YouTube OAuth2 credentials |
| `SYNC_SWEEP_INTERVAL_SECONDS` | `0` (disabled) | Celery Beat sweep cadence |
| `SENTRY_DSN` | `""` (disabled) | Leave empty to disable Sentry |

Sync DB contexts use `mysql+pymysql://` automatically (handled in `database.py`).

### Sentry
Opt-in via `SENTRY_DSN`. Active integrations: `FastApiIntegration` + `StarletteIntegration` in `playlist_sync/api.py`; `CeleryIntegration` in `worker/tasks.py`. Each `execute_single_sync_task` tags scope with `sync_id` and `triggered_manually`.

## Known Gaps vs .NET

| Gap | Severity | Details |
|-----|----------|---------|
| **Image download/upload** | Medium | .NET downloads/converts/caches cover images and uploads to target platforms. Python only stores the source image URL. |
| **YouTube InsertMedia** | Low | .NET injects extra videos at specific positions from `tblPlaylistSynchronizationInsertMedia`. Python YouTube synchronizer does not handle this. |
| **SyncResult.Time/SourceTrackCount** | Low | Python `PlaylistSynchronizationResult` is missing `Time` and `SourceTrackCount` fields that .NET writes to sync logs. |