# 030 — Data store and metadata cache

## 1. File and access

- `addon_data/plugin.video.milionar/library.db`, Python `sqlite3`
  (bundled with Kodi's Python), `PRAGMA journal_mode=WAL`,
  `busy_timeout=5000`, `foreign_keys=ON`. One connection per process call;
  every write is one transaction.
- `store.py` is the only module that runs SQL; everything else calls its
  functions. `schema_version` table with forward migrations.

## 2. Schema

```sql
titles(          -- one row per movie or series the addon knows
  media_type TEXT CHECK(media_type IN ('movie','tv')),
  tmdb_id INTEGER, PRIMARY KEY(media_type, tmdb_id),
  in_list INTEGER DEFAULT 0,          -- My movies / My series
  list_added_at INTEGER,               -- epoch s
  hidden_from_continue_at INTEGER,     -- "Remove from Continue" (080)
  last_activity_at INTEGER)            -- max of plays/marks, sorts Continue

movie_state(tmdb_id INTEGER PRIMARY KEY,
  playcount INTEGER DEFAULT 0, last_played_at INTEGER,
  resume_pos REAL DEFAULT 0, resume_total REAL DEFAULT 0,
  last_ident TEXT, last_provider TEXT, last_file_name TEXT,
  mirrored_at INTEGER)

episode_state(show_id INTEGER, season INTEGER, episode INTEGER,
  playcount INTEGER DEFAULT 0, last_played_at INTEGER,
  resume_pos REAL DEFAULT 0, resume_total REAL DEFAULT 0,
  last_ident TEXT, last_provider TEXT, last_file_name TEXT,
  mirrored_at INTEGER,
  PRIMARY KEY(show_id, season, episode))

play_log(id INTEGER PRIMARY KEY, media_type TEXT, tmdb_id INTEGER,
  season INTEGER, episode INTEGER, started_at INTEGER, ended_at INTEGER,
  position REAL, total REAL, outcome TEXT)  -- 'resume','watched','short'; Recently played row and debugging

meta(key TEXT PRIMARY KEY, json TEXT, fetched_at INTEGER, ttl INTEGER)  -- §3
```

Rules: `resume_pos > 0` ⇔ in progress; `playcount > 0` ⇔ watched; marking
watched clears the resume point; marking unwatched sets playcount 0 and
clears resume. Series-level watched state is derived from `episode_state`
over the aired regular episodes (050 §1), never stored.

## 3. TMDb metadata cache (`meta.py`)

- Keys: `movie:<id>`, `tv:<id>`, `season:<id>:<n>`, `collection:<id>`,
  language `cs-CZ` with `en-US` fallback for empty overview/title (the same
  fallback the addon uses today).
- TTL: movie 30 days; `tv:<id>` and its seasons 12 h when TMDb `status` is
  "Returning Series" or "In Production", else 30 days; collection 30 days.
- Reads never block on the network in rows and listings: a stale entry is
  served and a refresh is queued (`NotifyAll(…, library_refresh, {keys})` →
  service). Listings of a show never seen before fetch inline once (the
  user explicitly opened it) under a busy dialog.
- **One cache for all TMDb data (decision 2026-10-01).** Series, season, movie
  and collection data of *every* screen (old listings `display_series_seasons`,
  `display_season_episodes`, the My list dialog, collection overviews, autoplay,
  tracker) come from `wcs.library2.tmdb` and the canonical keys above, so
  nothing is downloaded twice. Listings the user opened call it with
  `strict=True` (a failed download raises, the usual TMDb error dialog shows);
  playback and background code get `None`. A cached `tv:` / `season:` /
  `movie:` entry that lacks `seasons` / `episodes` / `genres` counts as missing
  and is refetched (partial list snapshots are never seeded into `meta`).
- Everything else (logos, original-title aliases, TMDb search matches, ČSFD
  charts and pairing) goes through `wcs/metadata/TMDbCache.py`, which is now a
  thin facade over `meta` with the generic key space `http:<sha256 of
  endpoint|params>`: same call signatures (`get_or_fetch`, `get_from_cache`,
  `set_to_cache`), plus `delete` and `delete_where` for the ČSFD cache reset.
  The reader picks the lifetime (`expire_seconds`); rows are kept 30 days and
  expired ones are purged on the first write of a process. `tmdb_cache.json` is
  no longer read or written (nothing to migrate: it is only a cache).

## 4. Lists

- My movies / My series are `titles.in_list = 1`. On first run the existing
  `watchlist_movies.json` (3) and `watchlist_series.json` (12) are imported
  into `titles` (decision 11 keeps the lists).
- From then on the old addon functions `load_tmdb_movies/series`,
  `store_tmdb_*`, `remove_tmdb_*_by_index` become thin wrappers over the
  store, so the addon's existing menus and the Moje filmy / Moje seriály
  dialogs show the same list in every skin. The JSON files are kept as a
  read-only backup (renamed `*.json.migrated`).

## 5. Clean start (decision 11)

Watch state, resume points and history start empty: `global_watch_history.json`
is not imported (it stays for the addon's old "Naposledy přehrané" listing in
other skins), Kodi library playcounts are not imported.

## 6. Result (L0, 2026-10-01)

- Built: `wcs/library2/store.py` (SQLite, WAL, one connection per call,
  `PRAGMA user_version` migrations, `tx()` = one IMMEDIATE transaction),
  `meta.py` (TTL cache, stale = age >= ttl, so ttl 0 is always stale),
  `urls.py` (canonical URLs, `parse` / `canonical`), `fixture_provider.py`
  (gated by hidden setting `dev_fixture_provider` AND the harness `ENABLED`
  flag; `seed_meta()` loads the fixture TMDb data).
- **Schema additions to §2** (implementation detail, no decision touched):
  `titles.list_pos` (order of the old JSON list is the display order of
  My movies / My series), `titles.legacy_json` (the old list entry, returned
  unchanged by the wrappers so every existing menu and dialog keeps its
  data) and a `flags` table (`watchlists_imported`).
- Old list functions in `wcs/user_data.py` (`load/store_tmdb_movies/series`,
  `remove_*_by_index` via them) read and write the store; on first use the two
  JSON files are imported and renamed `*.json.migrated`. If the store itself
  fails they fall back to the JSON files and log an error.
- Real data: the user's My series list has **13** titles today (the plan
  said 12 at planning time), My movies 3. After import the addon's
  "Moje filmy" listing returns 3 titles and "Moje seriály" 13 (live check
  through `Files.GetDirectory`); backup copy of both JSON files in the
  session scratchpad, originals kept as `*.json.migrated`.
- Tests: `python3 -m unittest discover -s scripts/library_harness -p 'test_*.py'`
  -> 20 tests green (schema + migration, import 3 + 12, list order / append /
  remove, list changes never touch watch state, mark rules for movies and
  episodes, activity + hidden-from-continue, transaction rollback, **three
  processes writing 180 rows concurrently**, meta TTL and status TTLs, urls
  round trip, fixture seeding, legacy wrappers).
- Fixtures: `scripts/library_harness/fixtures/tmdb_fixture.json` (generated by
  `make_fixtures.py`, frozen today 2026-06-15: show 900001 with seasons 0 / 1
  (10) / 2 (8, last two unaired), ended show 900002, films 900101-900103 of
  collection 900900, 900103 unreleased) and the test videos
  `scripts/library_harness/media/fixture_long.mp4` (240 s) and
  `fixture_short.mp4` (40 s) from `make_media.py` (git-ignored, excluded
  from the deploy ZIP).