# Schema diff — `engine/reference.py::from_mysql()` vs. real `tvviewers`

Captured 2026-08-16 against `tvviewers` @ 62.138.14.194 (read-only).
Method: every SELECT in `from_mysql()` was executed against the live DB
wrapped in `SELECT * FROM (<query>) _t LIMIT 1`. Result: **6 of 9 pass,
3 fail outright.**

## A. Hard failures — query does not execute

| Frame | Broken reference | Reality | Fix |
|---|---|---|---|
| `global_sports` | `real_channel` | Column does not exist on `global_sports`. It exists on `gl_ratings`. | Drop it from the SELECT, or join `gl_ratings` to obtain it. |
| `team_weights` | `sports_team_weight_value` | Real column is `ama_team_weight` `decimal(5,2)` | Rename. |
| `host_factor` | `host_country_factor_value` | Real column is `host_country_factor` `decimal(8,4)` | Rename. Also add `WHERE active_flag = 1`. |

Error text, verbatim:
```
ERROR 1054 (42S22): Unknown column 'real_channel' in 'field list'
ERROR 1054 (42S22): Unknown column 'sports_team_weight_value' in 'field list'
ERROR 1054 (42S22): Unknown column 'host_country_factor_value' in 'field list'
```

## B. Table missing entirely

`event_interest_factor` (listed in `table_inventory.md`) **does not exist** in
`tvviewers`. `ERROR 1146: Table 'tvviewers.event_interest_factor' doesn't exist`.
`event_country_interest_factor` (20,263 rows) does exist and is already mapped.
Event-level interest, if still needed, has no source table — ask Joseph whether
it was folded into the country-level table.

## C. Queries that pass but rest on a false uniqueness assumption

The accessors take `.iloc[0]`, i.e. "first match wins". Where the key is not
unique, that silently returns an arbitrary row.

| Accessor | Assumed key | Real cardinality | Impact |
|---|---|---|---|
| `team_w(team)` | `team_name` unique | 28,102 rows / **8,062 distinct teams** | Severe. Real key is `(event_name, event_season_key, team_name)`. "France" has 491 rows and **90 distinct weights spanning 0.53–1.80**. Current signature cannot disambiguate. |
| `share(country, channel)` | `(country, channel)` unique | 53,945 rows / 51,071 distinct pairs → **2,874 dupes**; 4,305 NULL `ti_total_day_share` | Moderate — arbitrary pick among duplicates. |
| `tvu(country)` | `territory` unique | 274 rows / 272 distinct — **Cabo Verde** (527.3 vs 380.0) and **Eswatini** (1256.2 vs 804.0) each appear twice | Minor but wrong for those two. |
| `host_f(event, country)` | `(event_name, host_country_name)` unique | 32 rows / 32 distinct pairs, all `active_flag=1` | Clean. No action beyond the rename. |

`team_w()` is the one that needs a decision, not a patch — see "Blocking
question" below.

## D. Data-shape findings that affect accuracy, not correctness

Measured over the 5,903,804 `global_sports` rows with `ama_000 > 0`:

| Column | % NULL | Consequence |
|---|---|---|
| `match_level` | **80.3%** | `match_level_weights` frame applies to ~1 row in 5. |
| `sports_teams` | **85.5%** | `team_w()` has no input for ~6 rows in 7. |
| `telecast_type` | **32.4%** | A third of evidence falls to the hardcoded 0.15 "unknown" multiplier. |
| `sub_genre` | 31.3% | |
| `event_name` | 22.2% | |
| `start_time` | 0.3% | |
| `country` | 0.0% | |

**Broadcast-day time overflow.** `start_time` is MySQL `TIME`, which permits
values past 24:00. 6.47% of rows are ≥ 24:00:00, max observed **30:xx**
(i.e. 6am the following day). `hour_w(h)` indexes 0–23, so these fall through
to the 0.5 default. There is a `lower_start_time` tinyint column and a STORED
generated `daypart` column intended for this, but `lower_start_time` is NULL
on 948,262 rows.

**Hour curve is hardcoded but a real table exists.** `from_mysql()` seeds
`hour_weights` from `world.py`'s invented `HOUR_W` constants. Meanwhile
`tvviewers.viewership_by_hour` holds a real curve with a sports-specific
column: `hour_num, hour_general_percentage, hour_sports_percentage, notes`.
The engine is using invented numbers while real ones sit unused.

## E. Confirmed correct (no change needed)

`gl_ratings` (`gsiq_country`/`gsiq_channel` join keys), `tvuniverse`
(`territory`/`total_individuals_2025`), `event_country_tvuniverse_factor`,
`event_country_interest_factor`, `match_level_weights` (mapped to
`match_level_weight_by_event`), `global_sports_ama_caps`. The source-citation
approach in the existing comments held up — 6 of 9 were exactly right.

## F. Evidence pool available for Task 4

`global_sports.prog_date` spans **2017-03-26 → 2026-08-09**, 5,903,804 rows
with usable `ama_000`. Ample for the 90-day / 30-day holdout design.

## Blocking question for Task 3

`team_w(team)` cannot be fixed by renaming a column. The real table is keyed by
event and season; the accessor is keyed by team alone. Per handoff rule
"If a table's real structure genuinely requires a logic change, STOP and
describe the issue rather than improvising a fix" — flagged, not changed.
Options: (a) change the signature to `team_w(event, season, team)` and thread
event/season through `core.py`; (b) collapse to one weight per team by an
agreed rule (mean? latest season? max?); (c) leave team weighting disabled for
v1 given 85.5% of evidence rows have no `sports_teams` value anyway.

---

# Fixes applied (2026-08-16, Task 3 partial)

## Section A — all three hard failures fixed in `engine/reference.py`
- `global_sports`: dropped the non-existent `real_channel` from the SELECT.
- `team_weights`: `sports_team_weight_value` -> `ama_team_weight`; also now
  selects `event_name` / `event_season` so a future accessor can disambiguate,
  and filters `ama_team_weight IS NOT NULL`.
- `host_factor`: `host_country_factor_value` -> `host_country_factor`, plus
  `WHERE active_flag = 1`.

Verified: `ReferenceData.from_mysql()` now loads all 12 frames from the live
database in ~117s.

| frame | rows |
|---|---|
| global_sports | 5,903,804 |
| gl_ratings | 53,945 |
| tvuniverse | 274 |
| team_weights | 28,078 |
| host_factor | 32 |
| event_country_tvuniverse_factor | 159,390 |
| event_country_interest_factor | 20,227 |
| match_level_weights | 2,419 |
| global_sports_ama_caps | 36,371 |
| telecast_multipliers | 8 |
| weekday_weights | 7 |
| hour_weights | 24 |

## Two further bugs found while wiring, both fixed
1. **DSN credential encoding.** `from_mysql()` interpolated the password into
   the connection URL unescaped. Any password containing `@` (as the real one
   does) made SQLAlchemy read part of the password as the hostname:
   `Can't connect to MySQL server on '24@62.138.14.194'`. Credentials are now
   `quote_plus`-encoded, and an explicit port is supported.
2. **Undeclared dependencies.** `worker/requirements.txt` listed only pymysql,
   pandas and openpyxl. `reference.py` imports `sqlalchemy`, and
   `save_fixtures`/`from_fixtures` need `pyarrow`. Neither was installed and
   neither was declared; both added.

## Split reference / application database
`from_mysql()` previously reused one set of credentials for both the
read-only `tvviewers` reference DB and the read-write `crystal` application
schema. Those are different servers here. `reference_engine()` now prefers
`ref_db_host` / `ref_db_user` / `ref_db_pass` / `ref_db_port`, falling back to
`db_*` so single-server setups still work.

## Still open (unchanged)
- `team_w(team)` first-match-wins vs the real (event, season, team) key.
- `hour_weights` still seeded from invented constants in `world.py` while
  `tvviewers.viewership_by_hour` holds a real `hour_sports_percentage` curve.
- `event_interest_factor` does not exist in the database.