# What's Cookin' CEA

Automated email reporting system that tracks with favorable first reactions across markets and sends formatted HTML email reports with embedded track images, Spotify data, TikTok/Meta/Shazam chart positions, and other indicators.

## Project Structure

```
whats-cookin-cea/
├── pyproject.toml
├── src/
│   ├── __main__.py              # CLI entry point
│   ├── config/
│   │   ├── countries.py         # Country and label definitions
│   │   ├── tables.py           # Redshift table names and placeholders
│   │   ├── paths.py            # File system paths and query paths
│   │   ├── email.py            # SMTP and Spotify config
│   │   └── colors.py           # Row highlight colors
│   ├── db/
│   │   ├── session.py          # Database session singletons
│   │   └── queries.py          # SQL file loader with Jinja2 rendering + to_sql_string utility
│   ├── email/
│   │   ├── renderer.py         # Track data to HTML row rendering (cached Jinja2 environments)
│   │   ├── screenshot.py       # Headless Chrome screenshots via html2image + batch cover downloads
│   │   ├── composer.py         # MIME email composition with CID images
│   │   └── sender.py           # SMTP sending with connection reuse and recipient chunking
│   └── services/
│       ├── fill.py             # Data availability checks and date backfilling
│       ├── data.py             # SQL pipeline execution and CSV generation
│       ├── dashboard.py        # Tableau dashboard table updates and extract refresh
│       ├── distro.py           # Distribution list management
│       └── email_service.py    # End-to-end email orchestration
├── sql/                         # SQL query files (Jinja2 templates)
├── html/                        # Jinja2 email templates (.html.j2)
│   └── tmp/                     # Temporary screenshots and cover images
└── data/                        # Output CSVs
```

## Usage

```bash
uv run python -m src [dev|prod] [options]
```

| Option | Description |
|---|---|
| `dev` / `prod` | Mode. Dev always runs data + email; prod checks availability and only emails on Tue/Fri/Sun |
| `--date`, `-d` | Target report date (`YYYY-MM-DD`), defaults to latest available |
| `--email-only`, `-e` | Skip data generation, send from existing CSV (daily files are preserved) |
| `--data-only` | Generate data only, skip sending emails |
| `--affiliate`, `-a` | Send for a single affiliate only (country code, label code, or `CEA`) |
| `--to`, `-t` | Override recipients with comma-separated email address(es) |
| `--markets`, `-m` | Comma-separated country codes for an ad-hoc multi-market report (e.g., `DE,FR,IT`). Requires 2+ markets |
| `--log-file`, `-l` | Log filename stored in `LOGDIR` (default: `whats-cookin-cea.log`) |

### Examples

```bash
# Full production run
uv run python -m src prod

# Dev mode, specific date
uv run python -m src dev --date 2025-01-15

# Re-send emails for Germany only
uv run python -m src prod --email-only --affiliate DE

# Generate data without sending emails
uv run python -m src prod --data-only

# Send to specific recipients (overrides distribution list)
uv run python -m src prod --email-only --affiliate DE --to user@example.com

# Multi-market report for specific countries (ad-hoc)
uv run python -m src prod --markets DE,FR,IT --to user@example.com
```

### Scheduled Multi-Market Reports

Multi-market reports (e.g., Nordics) are configured in `src/config/email.py` and sent automatically as part of the production run — no separate cron job needed:

```python
# src/config/email.py
MULTI_MARKET_REPORTS = [
    {"markets": ["FI", "SE", "NO", "DK"], "distro_key": "NORDICS"},
]
```

Recipients are resolved from the distro DB table using the `distro_key` as the `country_code` value. To add a new scheduled multi-market report:
1. Add an entry to `MULTI_MARKET_REPORTS` with the market codes and a distro key
2. Add rows to the distro table with `country_code` matching the `distro_key`

## Pipeline

1. **Fill** - Checks for new data in Redshift, backfills any missing dates
2. **Generate** - Executes SQL pipeline (tracks, streams, charts, metadata), fills artwork via Spotify API, outputs dated CSV
3. **Dashboard** - Updates Tableau dashboard tables and refreshes extracts
4. **Email** - For each affiliate:
   - Downloads all unique cover art images in a single batch (deduplicated by URL)
   - Parses report data with vectorized row colors, icons, and trend indicators
   - Screenshots each track row and legend via headless Chrome
   - Composes MIME email with CID-attached images using Jinja2 templates
   - Sends all chunks over a single reused SMTP connection (recipients chunked at 49 per email)

### Report Types

| Type | Description |
|---|---|
| `cc` | Country-specific report (single-market per-track cards) |
| `ro` | Rep-owner / international tracks edition (multi-market aggregated cards, also used for CEA) |
| `lb` | Label edition (multi-market aggregated cards) |
| `mm` | Multi-market custom report (arbitrary country combination via `--markets` or `MULTI_MARKET_REPORTS` config) |
| `CEA` | Aggregated cross-market (tracks in 5+ markets, or 8+ CEA markets for external affiliates), grouped by rep_owner |

## Environment Variables

| Variable | Description |
|---|---|
| `smtpHost`, `smtpPort` | SMTP server |
| `smtpKey`, `smtpSecret` | SMTP credentials |
| `spotifyClientId`, `spotifyClientSecret` | Spotify API credentials |
| `headlessChromeShellPath` | Path to Chrome/Chromium binary (optional, auto-detects if unset) |
| `INFAMOUSDIR` | Base project directory override |
| `DATADIR` | Data output directory override |
| `LOGDIR` | Log file directory |

## Dependencies

| Package | Purpose |
|---|---|
| `djagitit` | Internal DB connections, Spotify helpers, Tableau refresh |
| `pandas` / `numpy` | Data manipulation |
| `html2image` | Headless Chrome HTML-to-PNG screenshots |
| `jinja2` | Email template rendering |
| `spotipy` | Spotify API (artwork lookup by ISRC) |
| `python-dotenv` | `.env` file loading |
| `loguru` | Logging |
| `Pillow` | Image cropping for screenshots |