#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Media Date Cache:
- Caches and applies mtimes for TV episodes (Sonarr) and Movies (Radarr)
- First preference: FIRST import date from *history*
- Fallbacks: TV -> episode airDateUtc ; Movies -> digitalRelease or physicalRelease
- Works from DB cache; with --live-on-miss it queries Sonarr/Radarr when missing, inserts, and applies immediately
- Multi-root aware via .env (TV_ROOTS, MOVIE_ROOTS) and/or --root
- Gotify progress notifications supported

Only dependency: requests
"""

import os, re, sys, time, json, sqlite3, argparse, stat, traceback
from datetime import datetime, timezone
from typing import Dict, Any, List, Optional, Tuple

try:
    import requests
except ImportError:
    print("ERROR: 'requests' is required. Activate your venv and run: pip install requests", file=sys.stderr)
    sys.exit(1)

# -----------------------------
# Utilities / ENV
# -----------------------------

ENV_DEFAULT_PATH = os.path.join(os.path.dirname(__file__), ".env")
ISO_FMT = "%Y-%m-%dT%H:%M:%S%z"

def now_iso() -> str:
    return datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")

def log(msg: str):
    print(f"[{now_iso()}] {msg}", flush=True)

def load_env(dotenv_path: str = ENV_DEFAULT_PATH) -> Dict[str, str]:
    """Simple .env loader with inline comment stripping."""
    env = dict(os.environ)
    if os.path.exists(dotenv_path):
        with open(dotenv_path, "r", encoding="utf-8", errors="ignore") as f:
            for raw in f:
                line = raw.strip()
                if not line or line.startswith("#"):
                    continue
                if "=" not in line:
                    continue
                k, v = line.split("=", 1)
                k = k.strip()
                v = v.strip()
                # Remove inline comments (# ...) unless inside quotes
                quoted = (v.startswith("'") and v.endswith("'")) or (v.startswith('"') and v.endswith('"'))
                if not quoted and "#" in v:
                    v = v.split("#", 1)[0].strip()
                if quoted:
                    v = v[1:-1]
                env[k] = v
    return env

def env_get(env: Dict[str, str], key: str, default: Optional[str] = None) -> Optional[str]:
    return env.get(key, default)

def env_get_int(env: Dict[str, str], key: str, default: int) -> int:
    try:
        val = env.get(key, None)
        if val is None:
            return default
        return int(str(val).split("#", 1)[0].strip())
    except Exception:
        return default

# -----------------------------
# Gotify
# -----------------------------

def gotify_send(env: Dict[str, str], title: str, message: str, priority: Optional[int] = None):
    url = env_get(env, "GOTIFY_URL", "")
    token = env_get(env, "GOTIFY_TOKEN", "")
    if not url or not token:
        return
    prio = priority if priority is not None else env_get_int(env, "GOTIFY_PRIORITY", 5)
    try:
        r = requests.post(
            url.rstrip("/") + "/message",
            data={"title": title, "message": message, "priority": prio},
            headers={"X-Gotify-Key": token},
            timeout=15,
        )
        r.raise_for_status()
    except Exception as e:
        # Never crash on Gotify issues
        log(f"WARN: gotify failed: {e}")

# -----------------------------
# DB
# -----------------------------

SCHEMA_SQL = """
PRAGMA journal_mode=WAL;

CREATE TABLE IF NOT EXISTS files (
  path TEXT PRIMARY KEY,
  ts   INTEGER NOT NULL,
  source TEXT NOT NULL,       -- history|airdate|digital|physical|manual
  updated_at INTEGER NOT NULL
);

-- Optional per-episode cache (Sonarr)
CREATE TABLE IF NOT EXISTS episodes (
  series_id INTEGER NOT NULL,
  season    INTEGER NOT NULL,
  episode   INTEGER NOT NULL,
  import_ts INTEGER,
  airdate_ts INTEGER,
  PRIMARY KEY(series_id, season, episode)
);

-- Optional per-movie cache (Radarr)
CREATE TABLE IF NOT EXISTS movies (
  movie_id INTEGER PRIMARY KEY,
  import_ts INTEGER,
  digital_ts INTEGER,
  physical_ts INTEGER
);
"""

def db_open(db_path: str) -> sqlite3.Connection:
    os.makedirs(os.path.dirname(db_path), exist_ok=True)
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys=ON")
    conn.executescript(SCHEMA_SQL)
    return conn

def db_upsert_file(conn: sqlite3.Connection, path: str, ts: int, source: str):
    conn.execute(
        "INSERT INTO files(path, ts, source, updated_at) VALUES(?,?,?,?) "
        "ON CONFLICT(path) DO UPDATE SET ts=excluded.ts, source=excluded.source, updated_at=excluded.updated_at",
        (path, ts, source, int(time.time())),
    )

def db_get_file_ts(conn: sqlite3.Connection, path: str) -> Optional[Tuple[int, str]]:
    cur = conn.execute("SELECT ts, source FROM files WHERE path=?", (path,))
    row = cur.fetchone()
    return (row[0], row[1]) if row else None

def db_upsert_episode(conn: sqlite3.Connection, series_id: int, season: int, episode: int, import_ts: Optional[int], airdate_ts: Optional[int]):
    conn.execute(
        "INSERT INTO episodes(series_id, season, episode, import_ts, airdate_ts) VALUES(?,?,?,?,?) "
        "ON CONFLICT(series_id, season, episode) DO UPDATE SET import_ts=COALESCE(excluded.import_ts, episodes.import_ts), airdate_ts=COALESCE(excluded.airdate_ts, episodes.airdate_ts)",
        (series_id, season, episode, import_ts, airdate_ts),
    )

def db_get_episode(conn: sqlite3.Connection, series_id: int, season: int, episode: int) -> Optional[Tuple[Optional[int], Optional[int]]]:
    cur = conn.execute(
        "SELECT import_ts, airdate_ts FROM episodes WHERE series_id=? AND season=? AND episode=?",
        (series_id, season, episode),
    )
    return cur.fetchone()

def db_upsert_movie(conn: sqlite3.Connection, movie_id: int, import_ts: Optional[int], digital_ts: Optional[int], physical_ts: Optional[int]):
    conn.execute(
        "INSERT INTO movies(movie_id, import_ts, digital_ts, physical_ts) VALUES(?,?,?,?) "
        "ON CONFLICT(movie_id) DO UPDATE SET import_ts=COALESCE(excluded.import_ts, movies.import_ts), digital_ts=COALESCE(excluded.digital_ts, movies.digital_ts), physical_ts=COALESCE(excluded.physical_ts, movies.physical_ts)",
        (movie_id, import_ts, digital_ts, physical_ts),
    )

def db_get_movie(conn: sqlite3.Connection, movie_id: int) -> Optional[Tuple[Optional[int], Optional[int], Optional[int]]]:
    cur = conn.execute("SELECT import_ts, digital_ts, physical_ts FROM movies WHERE movie_id=?", (movie_id,))
    return cur.fetchone()

# -----------------------------
# Sonarr / Radarr clients
# -----------------------------

class BaseArr:
    def __init__(self, base: str, api_key: str):
        self.base = base.rstrip("/")
        self.session = requests.Session()
        self.session.headers.update({"X-Api-Key": api_key})

    def _get(self, path: str, params: Dict[str, Any] = None):
        url = self.base + path
        r = self.session.get(url, params=params or {}, timeout=60)
        r.raise_for_status()
        try:
            return r.json()
        except Exception:
            return None

class Sonarr(BaseArr):
    # Endpoints: v3
    def all_series(self) -> List[Dict[str, Any]]:
        data = self._get("/api/v3/series")
        return data if isinstance(data, list) else []

    def get_series(self, series_id: int) -> Optional[Dict[str, Any]]:
        data = self._get(f"/api/v3/series/{series_id}")
        return data if isinstance(data, dict) else None

    def episodes_for_series(self, series_id: int) -> List[Dict[str, Any]]:
        # Returns list
        data = self._get("/api/v3/episode", params={"seriesId": series_id})
        return data if isinstance(data, list) else []

    def history_page(self, page: int, page_size: int = 250, series_id: Optional[int] = None) -> Tuple[List[Dict[str, Any]], int]:
        params = {
            "page": page,
            "pageSize": page_size,
            "sortDirection": "ascending",
            "includeEpisode": "true",
            "includeSeries": "true",
        }
        if series_id:
            params["seriesId"] = series_id
        data = self._get("/api/v3/history", params=params)
        # Sonarr returns dict with records/totalRecords
        if isinstance(data, dict):
            recs = data.get("records") or data.get("Records") or []
            total = int(data.get("totalRecords") or data.get("TotalRecords") or 0)
            return recs, total
        # Fallback: maybe a raw list (rare)
        if isinstance(data, list):
            return data, len(data)
        return [], 0

class Radarr(BaseArr):
    def all_movies(self) -> List[Dict[str, Any]]:
        data = self._get("/api/v3/movie")
        return data if isinstance(data, list) else []

    def history_page(self, page: int, page_size: int = 250, movie_id: Optional[int] = None) -> Tuple[List[Dict[str, Any]], int]:
        params = {
            "page": page,
            "pageSize": page_size,
            "sortDirection": "ascending",
            "includeMovie": "true",
        }
        if movie_id:
            params["movieId"] = movie_id
        data = self._get("/api/v3/history", params=params)
        if isinstance(data, dict):
            recs = data.get("records") or []
            total = int(data.get("totalRecords") or 0)
            return recs, total
        if isinstance(data, list):
            return data, len(data)
        return [], 0

# -----------------------------
# Parsing helpers
# -----------------------------

IMDB_RE = re.compile(r"\bimdb-(tt\d+)\b", re.IGNORECASE)
SXXEYY_RE = re.compile(r"[Ss](\d{1,2})[ ._-]*[Ee](\d{2})(?:[ ._-]*[Ee-](\d{2}))?")
SEASON_DIR_RE = re.compile(r"[Ss]eason[ _-]*(\d+)")
YEAR_PAREN_RE = re.compile(r"\((\d{4})\)")

def parse_imdb(folder: str) -> Optional[str]:
    m = IMDB_RE.search(folder)
    return m.group(1).lower() if m else None

def parse_series_season_episode(path: str) -> Optional[Tuple[int, List[int]]]:
    # Try filename first
    fn = os.path.basename(path)
    m = SXXEYY_RE.search(fn)
    if m:
        season = int(m.group(1))
        e1 = int(m.group(2))
        e2 = m.group(3)
        eps = [e1] if not e2 else list(range(e1, int(e2) + 1))
        return season, eps
    # Try parent directory (rare)
    parts = path.split(os.sep)
    for part in reversed(parts):
        m2 = SXXEYY_RE.search(part)
        if m2:
            season = int(m2.group(1))
            e1 = int(m2.group(2))
            e2 = m2.group(3)
            eps = [e1] if not e2 else list(range(e1, int(e2) + 1))
            return season, eps
    return None

def extract_series_folder(path: str) -> str:
    # e.g. /.../TV/tv/Friends (1994) [imdb-tt0108778]/Season 05/...
    # series folder is the directory directly under tv/tv6
    parts = path.split(os.sep)
    # Find 'tv' or 'tv6' and take the next component
    for i, p in enumerate(parts):
        if p in ("tv", "tv6"):
            if i + 1 < len(parts):
                return parts[i + 1]
    # Fallback: take grandparent of the file
    return os.path.basename(os.path.dirname(os.path.dirname(path)))

def extract_title_and_year(series_folder: str) -> Tuple[str, Optional[int]]:
    # "Friends (1994) [imdb-tt0108778]" -> ("Friends", 1994)
    year = None
    m = YEAR_PAREN_RE.search(series_folder)
    if m:
        try:
            year = int(m.group(1))
        except Exception:
            year = None
    # title is everything before "(YYYY"
    if "(" in series_folder:
        title = series_folder.split("(")[0].strip()
    else:
        title = series_folder
    return title, year

def to_epoch(ts_str: str) -> Optional[int]:
    if not ts_str:
        return None
    try:
        # Normalize to UTC if no timezone provided
        if ts_str.endswith("Z"):
            dt = datetime.fromisoformat(ts_str.replace("Z", "+00:00"))
        else:
            # Sonarr/Radarr dates are iso8601 with Z; but just in case:
            dt = datetime.fromisoformat(ts_str if "+" in ts_str or ts_str.endswith("Z") else ts_str + "+00:00")
        return int(dt.timestamp())
    except Exception:
        # Try a few loose formats
        try:
            dt = datetime.strptime(ts_str, "%Y-%m-%dT%H:%M:%S.%fZ")
            return int(dt.replace(tzinfo=timezone.utc).timestamp())
        except Exception:
            return None

def earliest_valid(*vals: Optional[int]) -> Optional[int]:
    xs = [v for v in vals if isinstance(v, int) and v > 0]
    return min(xs) if xs else None

# -----------------------------
# Root resolution
# -----------------------------

def split_paths(s: str) -> List[str]:
    # split on ':' or ',' and keep existing directories only
    raw = [p.strip() for p in re.split(r"[:,]", s) if p.strip()]
    return [p for p in raw if os.path.isdir(p)]

def resolve_roots(kind: str, root_arg: Optional[str], env: Dict[str, str]) -> List[str]:
    roots: List[str] = []
    if kind == "tv":
        if root_arg and os.path.isdir(root_arg):
            roots.append(root_arg)
        tv_env = env_get(env, "TV_ROOTS", "")
        if tv_env:
            roots.extend(split_paths(tv_env))
        # If root points to /Media/TV, include known subfolders if present
        more = []
        for r in list(roots):
            if os.path.isdir(os.path.join(r, "tv")):
                more.append(os.path.join(r, "tv"))
            if os.path.isdir(os.path.join(r, "tv6")):
                more.append(os.path.join(r, "tv6"))
        roots.extend(more)
    elif kind == "movie":
        if root_arg and os.path.isdir(root_arg):
            roots.append(root_arg)
        mv_env = env_get(env, "MOVIE_ROOTS", "")
        if mv_env:
            roots.extend(split_paths(mv_env))
        more = []
        for r in list(roots):
            if os.path.isdir(os.path.join(r, "movies")):
                more.append(os.path.join(r, "movies"))
            if os.path.isdir(os.path.join(r, "movies6")):
                more.append(os.path.join(r, "movies6"))
        roots.extend(more)
    # Dedup while preserving order:
    seen = set()
    dedup = []
    for p in roots:
        if p not in seen:
            seen.add(p)
            dedup.append(p)
    return dedup or ([root_arg] if root_arg else [])

def iter_media_files(roots: List[str], kind: str):
    exts = {".mkv", ".mp4", ".avi", ".m4v", ".mov"}
    for base in roots:
        if not base or not os.path.isdir(base):
            continue
        for dirpath, _, filenames in os.walk(base):
            for fn in filenames:
                if os.path.splitext(fn)[1].lower() in exts:
                    yield os.path.join(dirpath, fn)

# -----------------------------
# Build caches from Sonarr/Radarr
# -----------------------------

def build_sonarr_maps(env: Dict[str, str]) -> Tuple[Sonarr, Dict[int, Dict[str, Any]], Dict[str, int], Dict[Tuple[int, int, int], int], Dict[int, Dict[Tuple[int, int], Dict[str, Any]]]]:
    """
    Returns:
      client,
      series_by_id,
      series_by_imdb (imdbId -> seriesId),
      earliest_import_by_episode_id,
      episodes_map_by_series (seriesId -> {(season,episode): episode_obj})
    """
    url = env_get(env, "SONARR_URL", "")
    key = env_get(env, "SONARR_API_KEY", "")
    if not url or not key:
        raise RuntimeError("SONARR_URL/SONARR_API_KEY not set in environment or .env")
    client = Sonarr(url, key)

    series_list = client.all_series()
    series_by_id: Dict[int, Dict[str, Any]] = {}
    series_by_imdb: Dict[str, int] = {}
    for s in series_list:
        sid = int(s.get("id"))
        series_by_id[sid] = s
        imdb = (s.get("imdbId") or "").lower().strip()
        if imdb:
            series_by_imdb[imdb] = sid

    # Episodes per series
    episodes_map_by_series: Dict[int, Dict[Tuple[int, int], Dict[str, Any]]] = {}
    for sid in series_by_id.keys():
        eps = client.episodes_for_series(sid)
        m: Dict[Tuple[int, int], Dict[str, Any]] = {}
        for e in eps:
            key = (int(e.get("seasonNumber", -1)), int(e.get("episodeNumber", -1)))
            m[key] = e
        episodes_map_by_series[sid] = m

    # Build earliest import per episode via paged history
    earliest_import_by_episode_id: Dict[Tuple[int], int] = {}
    page = 1
    page_size = 250
    total = None
    while True:
        recs, tot = client.history_page(page, page_size)
        if total is None:
            total = tot
        if not recs:
            break
        for r in recs:
            et = str(r.get("eventType") or "").lower()
            if "import" not in et:
                continue
            ep = r.get("episode") or {}
            eid = ep.get("id")
            date = to_epoch(r.get("date"))
            if eid and date:
                cur = earliest_import_by_episode_id.get(eid)
                earliest_import_by_episode_id[eid] = min(cur, date) if cur else date
        if len(recs) < page_size:
            break
        page += 1

    return client, series_by_id, series_by_imdb, earliest_import_by_episode_id, episodes_map_by_series

def build_radarr_maps(env: Dict[str, str]) -> Tuple[Radarr, Dict[int, Dict[str, Any]], Dict[str, int], Dict[int, int], Dict[int, Tuple[Optional[int], Optional[int]]]]:
    """
    Returns:
      client,
      movies_by_id,
      movies_by_imdb (imdbId -> movieId),
      earliest_import_by_movie_id,
      releases_by_movie_id (digital_ts, physical_ts)
    """
    url = env_get(env, "RADARR_URL", "")
    key = env_get(env, "RADARR_API_KEY", "")
    if not url or not key:
        raise RuntimeError("RADARR_URL/RADARR_API_KEY not set in environment or .env")
    client = Radarr(url, key)

    movies = client.all_movies()
    movies_by_id: Dict[int, Dict[str, Any]] = {}
    movies_by_imdb: Dict[str, int] = {}
    releases_by_movie_id: Dict[int, Tuple[Optional[int], Optional[int]]] = {}

    for m in movies:
        mid = int(m.get("id"))
        movies_by_id[mid] = m
        imdb = (m.get("imdbId") or "").lower().strip()
        if imdb:
            movies_by_imdb[imdb] = mid
        # known release fields
        digital = to_epoch(m.get("digitalRelease") or m.get("digitalReleaseDate") or m.get("inCinemas"))
        physical = to_epoch(m.get("physicalRelease") or m.get("physicalReleaseDate"))
        releases_by_movie_id[mid] = (digital, physical)

    # History earliest import by movie
    earliest_by_movie: Dict[int, int] = {}
    page = 1
    page_size = 250
    total = None
    while True:
        recs, tot = client.history_page(page, page_size)
        if total is None:
            total = tot
        if not recs:
            break
        for r in recs:
            et = str(r.get("eventType") or "").lower()
            if "import" not in et:
                continue
            movie = r.get("movie") or {}
            mid = movie.get("id")
            date = to_epoch(r.get("date"))
            if mid and date:
                cur = earliest_by_movie.get(mid)
                earliest_by_movie[mid] = min(cur, date) if cur else date
        if len(recs) < page_size:
            break
        page += 1

    return client, movies_by_id, movies_by_imdb, earliest_by_movie, releases_by_movie_id

# -----------------------------
# Core: get preferred ts per file
# -----------------------------

def tv_preferred_ts_for_file(
    path: str,
    conn: sqlite3.Connection,
    sonarr_pack: Tuple[Sonarr, Dict[int, Dict[str, Any]], Dict[str, int], Dict[int, int], Dict[int, Dict[Tuple[int, int], Dict[str, Any]]]],
    live_insert_db: bool = True,
) -> Optional[Tuple[int, str]]:
    client, series_by_id, series_by_imdb, earliest_import_by_episode_id, episodes_map_by_series = sonarr_pack

    series_folder = extract_series_folder(path)
    imdb = parse_imdb(series_folder)
    title, year = extract_title_and_year(series_folder)

    # Get seriesId
    series_id = None
    if imdb and imdb in series_by_imdb:
        series_id = series_by_imdb[imdb]
    else:
        # fallback: match by (title, year)
        cand = []
        tnorm = title.strip().lower()
        for sid, s in series_by_id.items():
            stitle = str(s.get("title") or "").strip().lower()
            syear = s.get("year")
            if stitle == tnorm and (year is None or syear == year):
                cand.append(sid)
        if cand:
            series_id = cand[0]
    if not series_id:
        return None

    # Parse SxxEyy
    se = parse_series_season_episode(path)
    if not se:
        return None
    season, eps = se

    # Ensure we have episode map
    emap = episodes_map_by_series.get(series_id, {})
    # Find best ts among target episodes
    import_ts_list: List[int] = []
    air_ts_list: List[int] = []
    for e in eps:
        epi = emap.get((season, e))
        if not epi:
            continue
        eid = epi.get("id")
        air_ts = to_epoch(epi.get("airDateUtc") or epi.get("airDate"))
        if air_ts:
            air_ts_list.append(air_ts)
        if eid in earliest_import_by_episode_id:
            import_ts_list.append(earliest_import_by_episode_id[eid])

        # write into episodes cache table if enabled
        if live_insert_db:
            db_upsert_episode(conn, series_id, season, e,
                              earliest_import_by_episode_id.get(eid),
                              air_ts)

    # Preference: earliest import; else earliest airdate
    imp = min(import_ts_list) if import_ts_list else None
    air = min(air_ts_list) if air_ts_list else None
    if imp:
        return imp, "history"
    if air:
        return air, "airdate"
    return None

def movie_preferred_ts_for_file(
    path: str,
    conn: sqlite3.Connection,
    radarr_pack: Tuple[Radarr, Dict[int, Dict[str, Any]], Dict[str, int], Dict[int, int], Dict[int, Tuple[Optional[int], Optional[int]]]],
    live_insert_db: bool = True,
) -> Optional[Tuple[int, str]]:
    client, movies_by_id, movies_by_imdb, earliest_by_movie, releases_by_movie_id = radarr_pack

    # Movie folder right under movies/movies6
    parts = path.split(os.sep)
    movie_folder = ""
    for i, p in enumerate(parts):
        if p in ("movies", "movies6"):
            if i + 1 < len(parts):
                movie_folder = parts[i + 1]
            break
    if not movie_folder:
        movie_folder = os.path.basename(os.path.dirname(path))

    imdb = parse_imdb(movie_folder)
    title, year = extract_title_and_year(movie_folder)

    # Map to movie_id
    movie_id = None
    if imdb and imdb in movies_by_imdb:
        movie_id = movies_by_imdb[imdb]
    else:
        # title/year fallback
        tnorm = title.strip().lower()
        cand = []
        for mid, m in movies_by_id.items():
            mtitle = str(m.get("title") or "").strip().lower()
            myear = m.get("year")
            if mtitle == tnorm and (year is None or myear == year):
                cand.append(mid)
        if cand:
            movie_id = cand[0]
    if not movie_id:
        return None

    imp = earliest_by_movie.get(movie_id)
    digital, physical = releases_by_movie_id.get(movie_id, (None, None))

    # Preference: earliest import; fallbacks: digital -> physical
    if live_insert_db:
        db_upsert_movie(conn, movie_id, imp, digital, physical)

    if imp:
        return imp, "history"
    if digital:
        return digital, "digital"
    if physical:
        return physical, "physical"
    return None

# -----------------------------
# Apply mtimes
# -----------------------------

def apply_mtime(path: str, ts: int):
    try:
        os.utime(path, (ts, ts), follow_symlinks=False)
    except Exception:
        # Try with follow_symlinks default if FS complains
        os.utime(path, (ts, ts))

# -----------------------------
# Commands
# -----------------------------

def cmd_build_cache(args, env):
    kind = args.kind
    db = db_open(args.db)

    if kind == "tv":
        log("Refreshing Sonarr caches (series, episodes, history earliest-import)…")
        sonarr_pack = build_sonarr_maps(env)
        # Optionally write episodes into DB (import & airdate)
        conn = db
        _, series_by_id, _, earliest_imp_by_eid, episodes_map_by_series = sonarr_pack
        wrote = 0
        for sid, epmap in episodes_map_by_series.items():
            for (season, epnum), epi in epmap.items():
                eid = epi.get("id")
                air = to_epoch(epi.get("airDateUtc") or epi.get("airDate"))
                imp = earliest_imp_by_eid.get(eid)
                db_upsert_episode(conn, sid, season, epnum, imp, air)
                wrote += 1
                if wrote % 1000 == 0:
                    conn.commit()
        conn.commit()
        log(f"Episode cache rows upserted: {wrote}")

    elif kind == "movie":
        log("Refreshing Radarr caches (movies, history earliest-import)…")
        radarr_pack = build_radarr_maps(env)
        conn = db
        _, movies_by_id, _, earliest_by_movie, releases = radarr_pack
        wrote = 0
        for mid in movies_by_id.keys():
            imp = earliest_by_movie.get(mid)
            dig, phy = releases.get(mid, (None, None))
            db_upsert_movie(conn, mid, imp, dig, phy)
            wrote += 1
            if wrote % 1000 == 0:
                conn.commit()
        conn.commit()
        log(f"Movie cache rows upserted: {wrote}")
    else:
        raise SystemExit("--kind must be tv or movie")

    # Reports (optional)
    if args.report_json or args.report_csv:
        export_report(db, kind, args.report_json, args.report_csv)

    gotify_send(env, "Cache build: done", f"kind={kind}")
    db.close()

def export_report(conn: sqlite3.Connection, kind: str, json_path: Optional[str], csv_path: Optional[str]):
    if kind == "tv":
        rows = conn.execute("SELECT series_id, season, episode, import_ts, airdate_ts FROM episodes").fetchall()
        records = [
            {"series_id": r[0], "season": r[1], "episode": r[2], "import_ts": r[3], "airdate_ts": r[4]}
            for r in rows
        ]
    else:
        rows = conn.execute("SELECT movie_id, import_ts, digital_ts, physical_ts FROM movies").fetchall()
        records = [
            {"movie_id": r[0], "import_ts": r[1], "digital_ts": r[2], "physical_ts": r[3]}
            for r in rows
        ]

    if json_path:
        os.makedirs(os.path.dirname(json_path), exist_ok=True)
        with open(json_path, "w", encoding="utf-8") as f:
            json.dump(records, f, indent=2)

    if csv_path:
        os.makedirs(os.path.dirname(csv_path), exist_ok=True)
        import csv
        with open(csv_path, "w", newline="", encoding="utf-8") as f:
            w = csv.DictWriter(f, fieldnames=list(records[0].keys()) if records else [])
            if records:
                w.writeheader()
                w.writerows(records)

def cmd_apply(args, env):
    kind = args.kind
    db = db_open(args.db)
    gotify_send(env, "Apply mtimes: start", f"kind={kind}")

    roots = resolve_roots(kind, args.root, env)
    if not roots:
        raise SystemExit("No valid roots found. Set --root or TV_ROOTS/MOVIE_ROOTS in .env")

    # If live-on-miss, build in-memory maps once to avoid hammering endpoints
    sonarr_pack = radarr_pack = None
    if args.live_on_miss:
        if kind == "tv":
            log("Cache miss detected; refreshing Sonarr data live once…")
            sonarr_pack = build_sonarr_maps(env)
        else:
            log("Cache miss detected; refreshing Radarr data live once…")
            radarr_pack = build_radarr_maps(env)

    # Scan
    files = list(iter_media_files(roots, kind))
    log(f"Found {len(files)} media file(s) under {', '.join(roots)}")

    # Ticker
    notify_every = env_get_int(env, "GOTIFY_TICK_EVERY", 500)  # files
    last_notify = 0
    done = 0
    updated = 0
    skipped = 0
    failures = 0

    for idx, path in enumerate(files, 1):
        relog = False
        try:
            # Already cached by path?
            cached = db_get_file_ts(db, path)
            if cached and not args.reapply_all:
                skipped += 1
                continue

            # Compute preferred ts (from cache tables if present; or live-on-miss)
            ts_src = None

            if kind == "tv":
                ts_src = None
                # If we already built episode cache and path is not in files yet: try live maps for this file
                if sonarr_pack:
                    got = tv_preferred_ts_for_file(path, db, sonarr_pack, live_insert_db=True)
                    if got:
                        ts, src = got
                        db_upsert_file(db, path, ts, src)
                        ts_src = (ts, src)
                if not ts_src:
                    # Ultimate fallback: none
                    pass

            else:  # movie
                ts_src = None
                if radarr_pack:
                    got = movie_preferred_ts_for_file(path, db, radarr_pack, live_insert_db=True)
                    if got:
                        ts, src = got
                        db_upsert_file(db, path, ts, src)
                        ts_src = (ts, src)

            if not ts_src:
                # If we couldn't determine a date at all
                log(f"WARN: no import/release date for {path}")
                failures += 1
            else:
                ts, src = ts_src
                apply_mtime(path, ts)
                updated += 1

        except Exception as e:
            failures += 1
            log(f"WARN: failed on {path}: {e}")
            # Uncomment to debug deeply
            # traceback.print_exc()

        done += 1

        if notify_every > 0 and done - last_notify >= notify_every:
            gotify_send(env, f"Apply {kind}: progress", f"{done}/{len(files)} files (updated {updated}, skipped {skipped}, failures {failures})")
            last_notify = done

    db.commit()
    gotify_send(env, f"Apply mtimes: done ({kind})", f"total={done} updated={updated} skipped={skipped} failures={failures}")
    log(f"Apply complete: total={done} updated={updated} skipped={skipped} failures={failures}")
    db.close()

# -----------------------------
# CLI
# -----------------------------

def main():
    parser = argparse.ArgumentParser(description="Media date cache and mtime applier (Sonarr/Radarr).")
    sub = parser.add_subparsers(dest="cmd", required=True)

    # build-cache
    p_build = sub.add_parser("build-cache", help="Build caches from Sonarr/Radarr into SQLite")
    p_build.add_argument("--kind", choices=["tv", "movie"], required=True)
    p_build.add_argument("--db", required=True)
    p_build.add_argument("--report-json")
    p_build.add_argument("--report-csv")

    # apply
    p_apply = sub.add_parser("apply", help="Apply file mtimes using DB cache; with --live-on-miss it will query Sonarr/Radarr for misses")
    p_apply.add_argument("--kind", choices=["tv", "movie"], required=True)
    p_apply.add_argument("--db", required=True)
    p_apply.add_argument("--root", help="Root path; if omitted, uses TV_ROOTS/MOVIE_ROOTS from .env (supports multi-roots)")
    p_apply.add_argument("--live-on-miss", action="store_true", help="Query Sonarr/Radarr at runtime for files missing in DB and cache them")
    p_apply.add_argument("--reapply-all", action="store_true", help="Ignore existing per-file cache and reapply")

    args = parser.parse_args()
    env = load_env()

    if args.cmd == "build-cache":
        gotify_send(env, "Cache build: start", f"kind={args.kind}")
        cmd_build_cache(args, env)
    elif args.cmd == "apply":
        cmd_apply(args, env)

if __name__ == "__main__":
    main()