path: root/packages/meshbay-node/src/meshbay_node/media_cache.py

"""
MeshBay Node — TMDB/MusicBrainz metadata and thumbnail cache, shared by the
Videos and Music group apps.

Node-wide (not per-group, `data_dir/media_cache.db`), same rationale as
`tmdb_enabled`/`tmdb_api_token` (and `musicbrainz_enabled`,
docs/musicbay.md §6) living in `group_settings` under the `group_id=""`
sentinel (docs/mediacenter.md §5.5): the credential/budget is one
operator's, and a thumbnail or cover image is the same bytes
regardless of which group happens to share the file. The `file_mbid`/
`mbid_meta` tables below are the Music app's equivalent of `file_tmdb`/
`tmdb_meta`, sharing the same `thumbs` table for cover art (a MusicBrainz
release's cover is cached under a synthetic `musicbrainz:{mbid}` file_id,
the same trick `_fetch_and_cache_poster` uses for a TMDB poster_path).

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)
);
-- Videos app: the ffprobe fields enrich.py derives alongside the
-- thumbnail — duration/width/height only, deliberately *not*
-- display_title/season/episode. Those come from guessit against the
-- filename, which is exactly what a rename needs re-derived
-- (_reenrich_renamed_video_entries exists for precisely that); caching
-- them here would silently defeat that mechanism by handing back the old
-- name's parse under the new name. ffprobe's own output has no such
-- concern — the same bytes probe the same regardless of what the file is
-- called — so only the content-only fields are safe to skip recomputing.
-- thumb_hash is not duplicated here either, for the same reason it isn't
-- in photo_meta — get_thumb_hash_by_file_id(file_id) already answers that,
-- and it too is content-only (a frame grab doesn't depend on the name).
CREATE TABLE IF NOT EXISTS video_meta (
    file_id       TEXT PRIMARY KEY,
    duration      INTEGER,
    width         INTEGER,
    height        INTEGER
);
CREATE TABLE IF NOT EXISTS file_mbid (
    file_id     TEXT PRIMARY KEY,
    mbid        TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS mbid_meta (
    mbid        TEXT PRIMARY KEY,
    json        TEXT NOT NULL,
    fetched_at  REAL NOT NULL
);
-- Photos app (docs/photos.md): the technical/EXIF fields enrich_photo.py
-- reads alongside the thumbnail. Durable for the same reason `thumbs` is —
-- without this, only the thumbnail bytes survived a restart, and every
-- image was still fully re-decoded through Pillow just to re-derive
-- width/height/taken_at/camera, which get_thumb_hash_by_file_id's own
-- cache hit had already proven unnecessary. thumb_hash is not duplicated
-- here — get_thumb_hash_by_file_id(file_id) already answers that, and a
-- second copy would just be one more place for the two to drift.
CREATE TABLE IF NOT EXISTS photo_meta (
    file_id     TEXT PRIMARY KEY,
    width       INTEGER,
    height      INTEGER,
    taken_at    INTEGER,
    camera      TEXT
);
"""

# 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

# Same default as TMDB (docs/musicbay.md §6) — MusicBrainz release data is
# not expected to drift faster; revisit if that proves wrong in practice.
MUSICBRAINZ_META_TTL_SECS = 30 * 86400


class MediaCache:
    """Async SQLite cache for TMDB/MusicBrainz lookups and generated thumbnails/cover art."""

    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()

    # ── file -> musicbrainz release id mapping (Music app) ───────────────────

    async def get_file_mbid(self, file_id: str) -> str | None:
        async with self._db.execute(
            "SELECT mbid FROM file_mbid WHERE file_id = ?", (file_id,),
        ) as cur:
            row = await cur.fetchone()
        return row[0] if row else None

    async def set_file_mbid(self, file_id: str, mbid: str) -> None:
        await self._db.execute(
            "INSERT OR REPLACE INTO file_mbid (file_id, mbid) VALUES (?, ?)",
            (file_id, mbid),
        )
        await self._db.commit()

    # ── musicbrainz release id -> metadata json (Music app) ───────────────────

    async def get_mbid_meta(self, mbid: str) -> dict | None:
        """Returns None on a miss or on an entry older than MUSICBRAINZ_META_TTL_SECS."""
        async with self._db.execute(
            "SELECT json, fetched_at FROM mbid_meta WHERE mbid = ?", (mbid,),
        ) as cur:
            row = await cur.fetchone()
        if not row:
            return None
        raw_json, fetched_at = row
        if time.time() - fetched_at > MUSICBRAINZ_META_TTL_SECS:
            return None
        return json.loads(raw_json)

    async def set_mbid_meta(self, mbid: str, meta: dict) -> None:
        await self._db.execute(
            "INSERT OR REPLACE INTO mbid_meta (mbid, json, fetched_at) VALUES (?, ?, ?)",
            (mbid, 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()

    # ── photo technical/EXIF fields (Photos app) ─────────────────────────────

    async def get_photo_meta(self, file_id: str) -> dict | None:
        async with self._db.execute(
            "SELECT width, height, taken_at, camera FROM photo_meta WHERE file_id = ?",
            (file_id,),
        ) as cur:
            row = await cur.fetchone()
        if not row:
            return None
        return {"width": row[0], "height": row[1], "taken_at": row[2], "camera": row[3]}

    async def put_photo_meta(self, file_id: str, width: int | None, height: int | None,
                             taken_at: int | None, camera: str | None) -> None:
        await self._db.execute(
            "INSERT OR REPLACE INTO photo_meta (file_id, width, height, taken_at, camera) "
            "VALUES (?, ?, ?, ?, ?)",
            (file_id, width, height, taken_at, camera),
        )
        await self._db.commit()

    # ── video technical fields (Videos app) ──────────────────────────────────
    #
    # duration/width/height only — see the schema comment on why
    # display_title/season/episode are deliberately not cached here.

    async def get_video_meta(self, file_id: str) -> dict | None:
        async with self._db.execute(
            "SELECT duration, width, height FROM video_meta WHERE file_id = ?",
            (file_id,),
        ) as cur:
            row = await cur.fetchone()
        if not row:
            return None
        return {"duration": row[0], "width": row[1], "height": row[2]}

    async def put_video_meta(self, file_id: str, duration: int | None,
                             width: int | None, height: int | None) -> None:
        await self._db.execute(
            "INSERT OR REPLACE INTO video_meta (file_id, duration, width, height) "
            "VALUES (?, ?, ?, ?)",
            (file_id, duration, width, height),
        )
        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, its per-app technical fields, and its file->tmdb/
        file->mbid mappings. `tmdb_meta`/`mbid_meta` rows are left alone —
        they're keyed by tmdb_id/mbid, not file_id, and other files (other
        episodes of the same show, other tracks of the same release) may
        still reference the same entry.
        """
        await self._db.execute("DELETE FROM thumbs WHERE file_id = ?", (file_id,))
        await self._db.execute("DELETE FROM photo_meta WHERE file_id = ?", (file_id,))
        await self._db.execute("DELETE FROM video_meta WHERE file_id = ?", (file_id,))
        await self._db.execute("DELETE FROM file_tmdb WHERE file_id = ?", (file_id,))
        await self._db.execute("DELETE FROM file_mbid WHERE file_id = ?", (file_id,))
        await self._db.commit()