# Crystal 2 — PHP dashboard + Python worker (no Django)

Upload an EPG export or a prepared schedule, press one button, get an estimated
audience for every telecast in the file.

## What this is
- `public/`  — the dashboard your analysts use (PHP, served by Apache)
- `worker/`  — the Python estimator service (systemd). MySQL is the only bridge between them.
- `engine/`  — the estimator itself: EPG adapter, evidence retrieval, projection, intervals
- `reference_cache/` — nightly parquet snapshot of the tvviewers reference tables
- `fixtures/` — the small synthetic set the offline tests run against. Not the cache.
- `schema.sql` — the application tables. Your tvviewers reference schema is read-only throughout.

## Install (once, ~10 minutes)
1. Put this folder where Apache can serve `public/`.
2. `mysql crystal < schema.sql`
3. `cp config.sample.php config.php` and set DB credentials.
   Same for `worker/config.sample.json` → `worker/config.json`.
   Both must be readable by `www-data` and by nobody else:
   `chmod 640 config.php worker/config.json && chgrp www-data config.php worker/config.json`
4. `mkdir -p uploads exports && chown www-data:www-data uploads exports`
5. `pip3 install -r worker/requirements.txt`
6. **Build the reference cache** — do this before the first run:
   `python3 worker/build_cache.py`
   Then set `"reference_mode": "fixtures"` and
   `"fixtures_dir": "<abs path>/reference_cache"` in `worker/config.json`.
7. `sudo cp worker/crystal-worker.service /etc/systemd/system/`
   `sudo systemctl daemon-reload && sudo systemctl enable --now crystal-worker`
8. Open the site → New estimate → drop your file → watch the ring.

## The reference cache — read this before going live
`ReferenceData.from_mysql()` pulls ~6M rows across 14 frames from the remote
reference server. Called per run, that is ~220 seconds before a single row is
estimated. `worker/build_cache.py` snapshots it to parquet, which loads in
under a second, and the same snapshot carries the EPG adapter's channel bridge,
event vocabulary and country aliases.

Refresh it nightly:

    0 4 * * *  cd /var/www/html/crystal/crystal2 && python3 worker/build_cache.py

The build stages to a temporary directory and swaps atomically, so a failed
pull leaves the previous good snapshot in place and the worker keeps serving.

`reference_mode` decides which path is used:

| value | behaviour |
|---|---|
| `fixtures` | read `fixtures_dir` — what production wants |
| `mysql`    | query the server directly — what build_cache.py does |

## Timings (5,000-row EPG, 7 markets)

| stage | from MySQL | from cache |
|---|---|---|
| read + adapt EPG | ~85s remote lookups + ~72s resolving | ~2s |
| load reference | ~220s | ~1s |
| build evidence index | 14s every run | 0s (3 MB cache) |
| estimate rows | ~170s (~2,500s once teams are present) | **~0.5s** |
| write workbooks + JSON | ~2s | ~2s |
| **total** | **~8 min** | **seconds** |

Estimation is ~0.00013s per row. Three things got it there: the de-based value
of an evidence row is computed once rather than per target, fuzzy team matching
compares distinct team strings rather than every row in the market (India has
228,000; profiling showed 458,062 `SequenceMatcher` calls for a 10-row
schedule), and event names are resolved per distinct title rather than per row.

## Measured, not assumed

`worker/fit_model.py` estimates from global_sports itself the quantities the
engine used to take on faith, and writes them to `reference_cache/calibration.json`:

| Component | Was | Now |
|---|---|---|
| hour-of-day curve | invented constants | measured, within-stratum ratios |
| weekday curve | invented constants | measured (the invented swing was ~6x too strong) |
| exponent on TV universe | assumed 1.0 | fitted 0.19 (se 0.005) |
| exponent on channel share | assumed 1.0 | fitted 0.50 (se 0.005) |
| exponent on sport affinity | assumed 1.0 | fitted 0.56 (se 0.010) |
| per-tier interval sigma | assumed 0.18-0.50 | measured 1.33-1.50 by backtest |

The engine falls back to the old constants when that file is absent, and
reports which it used through `ReferenceData.calibration_source`. Re-run it
whenever the snapshot is rebuilt.

## Security notes
- `uploads/` and `exports/` must not be directly web-served (the included
  `.htaccess` blocks them); downloads go through `download.php` only.
- `config.php` lives outside `public/`. Keep it out of backups you share.
- The worker only ever SELECTs from `tvviewers`. Everything it writes goes to
  the `crystal` schema.