"""SQLite: the OBS vod library, GSI events, and chapter marks — everything
the VOD timeline is drawn from."""
import os
import sqlite3
import subprocess
import time
from datetime import datetime

from clipshelf import config
from clipshelf.config import DB_PATH
def connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.row_factory = sqlite3.Row
    return conn


def init():
    """Create/upgrade the schema. Called explicitly at app startup — never
    on import (bundlers and tests need import to be side-effect free)."""
    from clipshelf.migrations import migrate
    conn = connect()
    try:
        migrate(conn)
    finally:
        conn.close()


def _probe_duration_ms(path: str) -> float:
    try:
        res = subprocess.run(
            ["ffprobe", "-v", "error", "-show_entries", "format=duration",
             "-of", "default=noprint_wrappers=1:nokey=1", path],
            capture_output=True, text=True, check=True, timeout=10)
        return float(res.stdout.strip()) * 1000
    except Exception:
        return 0


def _start_epoch(path: str, filename: str) -> int:
    """OBS default filename "%CCYY-%MM-%DD %hh-%mm-%ss" parses in LOCAL time;
    fall back to the file's creation time when the name doesn't match."""
    try:
        dt = datetime.strptime(filename.rsplit(".", 1)[0], "%Y-%m-%d %H-%M-%S")
        return int(dt.timestamp())
    except ValueError:
        return int(os.path.getctime(path))


def sync_vod_library():
    """Register new OBS recordings and link orphan GSI events to them.

    Events carrying an OBS timecode match a vod whose start aligns with
    (epoch - timecode); events without one (no obs-websocket connection)
    match any vod whose recorded window contains their epoch, which also
    backfills vod_time_ms.
    """
    if not config.VOD_DIR or not os.path.isdir(config.VOD_DIR):
        return
    conn = connect()
    try:
        for fn in os.listdir(config.VOD_DIR):
            if not fn.lower().endswith(".mp4") or fn.endswith("_proxy.mp4"):
                continue
            if fn.startswith("Replay"):
                continue  # OBS replay-buffer saves — ingested by clipmark, not VODs
            path = os.path.join(config.VOD_DIR, fn)
            if not os.path.isfile(path):
                continue
            row = conn.execute("SELECT id, duration_ms FROM vods WHERE filename = ?", (fn,)).fetchone()
            settled = time.time() - os.path.getmtime(path) > 300
            if row and row["duration_ms"] > 0 and settled:
                continue  # final duration recorded
            # keep re-probing until the recording settles — a probe of a
            # still-growing hybrid mp4 reports a partial duration
            dur = _probe_duration_ms(path)
            if not row:
                conn.execute("INSERT INTO vods (filename, start_epoch, duration_ms) VALUES (?, ?, ?)",
                             (fn, _start_epoch(path, fn), dur))
            elif dur > 0:
                conn.execute("UPDATE vods SET duration_ms = ? WHERE id = ?", (dur, row["id"]))

        for ev in conn.execute("SELECT id, game, event_type, epoch_ms, vod_time_ms"
                               " FROM gsi_events WHERE vod_id IS NULL").fetchall():
            if ev["vod_time_ms"] is not None:
                theoretical_start = int(ev["epoch_ms"] / 1000) - int(ev["vod_time_ms"] / 1000)
                m = conn.execute("SELECT id FROM vods WHERE ABS(start_epoch - ?) < 120 LIMIT 1",
                                 (theoretical_start,)).fetchone()
                if m:
                    conn.execute("UPDATE gsi_events SET vod_id = ? WHERE id = ?", (m["id"], ev["id"]))
            else:
                epoch_s = ev["epoch_ms"] / 1000
                m = conn.execute(
                    "SELECT id, start_epoch FROM vods WHERE duration_ms > 0"
                    " AND start_epoch <= ? AND start_epoch + duration_ms / 1000 >= ? LIMIT 1",
                    (epoch_s, epoch_s)).fetchone()
                if m:
                    vt = int(ev["epoch_ms"] - m["start_epoch"] * 1000)
                    dup = conn.execute(
                        """SELECT 1 FROM gsi_events WHERE vod_id = ? AND game = ?
                           AND event_type = ? AND vod_time_ms = ? AND id != ?""",
                        (m["id"], ev["game"], ev["event_type"], vt, ev["id"])).fetchone()
                    if dup:  # duplicate ingest — adopting it would double kill counts
                        conn.execute("DELETE FROM gsi_events WHERE id = ?", (ev["id"],))
                    else:
                        conn.execute("UPDATE gsi_events SET vod_id = ?, vod_time_ms = ? WHERE id = ?",
                                     (m["id"], vt, ev["id"]))

        # chapter marks (game detector) link exactly like events do
        for ch in conn.execute("SELECT id, game_tag, epoch_ms, start_time_ms"
                               " FROM vod_chapters WHERE vod_id IS NULL").fetchall():
            if ch["start_time_ms"] is not None:
                theoretical_start = int(ch["epoch_ms"] / 1000) - int(ch["start_time_ms"] / 1000)
                m = conn.execute("SELECT id FROM vods WHERE ABS(start_epoch - ?) < 120 LIMIT 1",
                                 (theoretical_start,)).fetchone()
                if m:
                    conn.execute("UPDATE vod_chapters SET vod_id = ? WHERE id = ?", (m["id"], ch["id"]))
            else:
                epoch_s = ch["epoch_ms"] / 1000
                m = conn.execute(
                    "SELECT id, start_epoch FROM vods WHERE duration_ms > 0"
                    " AND start_epoch <= ? AND start_epoch + duration_ms / 1000 >= ? LIMIT 1",
                    (epoch_s, epoch_s)).fetchone()
                if m:
                    st = int(ch["epoch_ms"] - m["start_epoch"] * 1000)
                    conn.execute("UPDATE vod_chapters SET vod_id = ?, start_time_ms = ? WHERE id = ?",
                                 (m["id"], st, ch["id"]))
        conn.commit()
    except Exception as e:
        from clipshelf import problems
        problems.record("recording library", e,
                        "recordings may not appear until this is resolved")
    finally:
        conn.close()
