Merge branch 'docs/mediacenter-videos-app': Videos group app

Poster grid / flat list browsing, TMDB metadata enrichment, thumbnail
generation and caching, season-specific overviews, manual match
correction, and the create-group wizard's app-selection step.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01LAmyXtc6dAADsH23ydXQpY

1 files changed, 188 insertions, 0 deletions

diff --git a/packages/meshbay-node/src/meshbay_node/media_cache.py b/packages/meshbay-node/src/meshbay_node/media_cache.py
new file mode 100644
index 0000000..6daae3f
--- /dev/null
+++ b/packages/meshbay-node/src/meshbay_node/media_cache.py
@@ -0,0 +1,188 @@
+"""
+MeshBay Node — TMDB metadata and thumbnail cache for the Videos group app.
+
+Node-wide (not per-group, `data_dir/media_cache.db`), same rationale as
+`tmdb_enabled`/`tmdb_api_token` living in `group_settings` under the
+`group_id=""` sentinel (docs/mediacenter.md §5.5): TMDB is one operator's
+budget and credential, and a thumbnail is the same bytes regardless of which
+group happens to share the file.
+
+Disposable and rebuildable, like the rest of the file index (§1, §2) — never
+a second identity for a file. Every row here is keyed off a value the node
+can already derive (a file's own blake3 id, or a TMDB id), so losing this
+database costs re-probing/re-fetching, not data.
+"""
+
+import json
+import logging
+import time
+from pathlib import Path
+
+import aiosqlite
+
+log = logging.getLogger(__name__)
+
+_SCHEMA = """
+CREATE TABLE IF NOT EXISTS file_tmdb (
+    file_id     TEXT PRIMARY KEY,
+    tmdb_id     TEXT NOT NULL,
+    media_type  TEXT NOT NULL
+);
+CREATE TABLE IF NOT EXISTS tmdb_meta (
+    tmdb_id     TEXT NOT NULL,
+    media_type  TEXT NOT NULL,
+    json        TEXT NOT NULL,
+    fetched_at  REAL NOT NULL,
+    PRIMARY KEY (tmdb_id, media_type)
+);
+CREATE TABLE IF NOT EXISTS thumbs (
+    thumb_hash  TEXT PRIMARY KEY,
+    file_id     TEXT NOT NULL,
+    jpeg        BLOB NOT NULL
+);
+CREATE INDEX IF NOT EXISTS idx_thumbs_file ON thumbs(file_id);
+CREATE TABLE IF NOT EXISTS season_meta (
+    tmdb_id     TEXT NOT NULL,
+    season      INTEGER NOT NULL,
+    json        TEXT NOT NULL,
+    fetched_at  REAL NOT NULL,
+    PRIMARY KEY (tmdb_id, season)
+);
+"""
+
+# TMDB overviews/ratings do drift; a file's own resolved tmdb_id does not
+# need re-checking on this schedule, only the metadata blob (§5.4, V3).
+TMDB_META_TTL_SECS = 30 * 86400
+
+
+class MediaCache:
+    """Async SQLite cache for TMDB lookups and generated thumbnails."""
+
+    def __init__(self, db_path: Path):
+        self._db_path = db_path
+        self._db: aiosqlite.Connection | None = None
+
+    async def open(self) -> None:
+        self._db_path.parent.mkdir(parents=True, exist_ok=True)
+        self._db = await aiosqlite.connect(str(self._db_path))
+        await self._db.executescript(_SCHEMA)
+        await self._db.commit()
+
+    async def close(self) -> None:
+        if self._db:
+            await self._db.close()
+            self._db = None
+
+    # ── file -> tmdb id mapping ──────────────────────────────────────────────
+
+    async def get_file_tmdb(self, file_id: str) -> tuple[str, str] | None:
+        """Returns (tmdb_id, media_type), or None if this file was never resolved."""
+        async with self._db.execute(
+            "SELECT tmdb_id, media_type FROM file_tmdb WHERE file_id = ?",
+            (file_id,),
+        ) as cur:
+            row = await cur.fetchone()
+        return (row[0], row[1]) if row else None
+
+    async def set_file_tmdb(self, file_id: str, tmdb_id: str, media_type: str) -> None:
+        await self._db.execute(
+            "INSERT OR REPLACE INTO file_tmdb (file_id, tmdb_id, media_type) "
+            "VALUES (?, ?, ?)",
+            (file_id, tmdb_id, media_type),
+        )
+        await self._db.commit()
+
+    # ── tmdb id -> metadata json ─────────────────────────────────────────────
+
+    async def get_tmdb_meta(self, tmdb_id: str, media_type: str) -> dict | None:
+        """Returns None on a miss or on an entry older than TMDB_META_TTL_SECS."""
+        async with self._db.execute(
+            "SELECT json, fetched_at FROM tmdb_meta WHERE tmdb_id = ? AND media_type = ?",
+            (tmdb_id, media_type),
+        ) as cur:
+            row = await cur.fetchone()
+        if not row:
+            return None
+        raw_json, fetched_at = row
+        if time.time() - fetched_at > TMDB_META_TTL_SECS:
+            return None
+        return json.loads(raw_json)
+
+    async def set_tmdb_meta(self, tmdb_id: str, media_type: str, meta: dict) -> None:
+        await self._db.execute(
+            "INSERT OR REPLACE INTO tmdb_meta (tmdb_id, media_type, json, fetched_at) "
+            "VALUES (?, ?, ?, ?)",
+            (tmdb_id, media_type, json.dumps(meta), time.time()),
+        )
+        await self._db.commit()
+
+    # ── tmdb id + season number -> season-level metadata json ────────────────
+    #
+    # A show's own overview (tmdb_meta above) is one static field an operator
+    # found does not necessarily describe every season alike (mediacenter.md
+    # §5.4) — this is TMDB's per-season `overview`/`air_date`/`poster_path`,
+    # fetched and cached independently, on the same staleness schedule.
+
+    async def get_season_meta(self, tmdb_id: str, season: int) -> dict | None:
+        async with self._db.execute(
+            "SELECT json, fetched_at FROM season_meta WHERE tmdb_id = ? AND season = ?",
+            (tmdb_id, season),
+        ) as cur:
+            row = await cur.fetchone()
+        if not row:
+            return None
+        raw_json, fetched_at = row
+        if time.time() - fetched_at > TMDB_META_TTL_SECS:
+            return None
+        return json.loads(raw_json)
+
+    async def set_season_meta(self, tmdb_id: str, season: int, meta: dict) -> None:
+        await self._db.execute(
+            "INSERT OR REPLACE INTO season_meta (tmdb_id, season, json, fetched_at) "
+            "VALUES (?, ?, ?, ?)",
+            (tmdb_id, season, json.dumps(meta), time.time()),
+        )
+        await self._db.commit()
+
+    # ── thumbnails ────────────────────────────────────────────────────────────
+
+    async def get_thumb(self, thumb_hash: str) -> bytes | None:
+        async with self._db.execute(
+            "SELECT jpeg FROM thumbs WHERE thumb_hash = ?", (thumb_hash,),
+        ) as cur:
+            row = await cur.fetchone()
+        return bytes(row[0]) if row else None
+
+    async def get_thumb_hash_by_file_id(self, file_id: str) -> str | None:
+        """
+        A TMDB poster/backdrop is stored under a synthetic file_id
+        (`tmdb:{poster_path}`, stable across requests for the same image) —
+        this is how `_fetch_and_cache_poster` recognizes "already fetched"
+        without knowing the content hash up front (that's only known once
+        the bytes are downloaded).
+        """
+        async with self._db.execute(
+            "SELECT thumb_hash FROM thumbs WHERE file_id = ?", (file_id,),
+        ) as cur:
+            row = await cur.fetchone()
+        return row[0] if row else None
+
+    async def put_thumb(self, thumb_hash: str, file_id: str, jpeg: bytes) -> None:
+        await self._db.execute(
+            "INSERT OR REPLACE INTO thumbs (thumb_hash, file_id, jpeg) VALUES (?, ?, ?)",
+            (thumb_hash, file_id, jpeg),
+        )
+        await self._db.commit()
+
+    # ── pruning ───────────────────────────────────────────────────────────────
+
+    async def prune_file(self, file_id: str) -> None:
+        """
+        Called when a file leaves the index (deletion, unshared root). Removes
+        its thumbnail and its file->tmdb mapping. `tmdb_meta` rows are left
+        alone — they're keyed by tmdb_id, not file_id, and other files (other
+        episodes of the same show) may still reference the same entry.
+        """
+        await self._db.execute("DELETE FROM thumbs WHERE file_id = ?", (file_id,))
+        await self._db.execute("DELETE FROM file_tmdb WHERE file_id = ?", (file_id,))
+        await self._db.commit()