"""Versioned schema migrations.

Each entry is (version, step) where step is a list of SQL statements or a
callable(conn). Steps run in order inside one transaction each and are
stamped into schema_version. Append new entries — never edit shipped ones:
an edited step silently diverges installs that already ran the old text.

A pre-framework database (tables exist, no schema_version) is treated as
version 0 — the baseline uses IF NOT EXISTS throughout, so replaying it
over live tables is a no-op and the DB just gets stamped.
"""

BASELINE = [
    """CREATE TABLE IF NOT EXISTS vods (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        filename TEXT UNIQUE NOT NULL,
        start_epoch INTEGER NOT NULL,
        duration_ms INTEGER NOT NULL DEFAULT 0
    )""",
    """CREATE TABLE IF NOT EXISTS gsi_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vod_id INTEGER,
        game TEXT NOT NULL,
        event_type TEXT NOT NULL,
        title TEXT NOT NULL,
        detail TEXT,
        epoch_ms INTEGER NOT NULL,
        vod_time_ms INTEGER,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(vod_id) REFERENCES vods(id) ON DELETE CASCADE
    )""",
    "CREATE INDEX IF NOT EXISTS idx_gsi_vod ON gsi_events(vod_id)",
    "CREATE INDEX IF NOT EXISTS idx_gsi_epoch ON gsi_events(epoch_ms)",
    """CREATE TABLE IF NOT EXISTS vod_chapters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vod_id INTEGER,
        game_tag TEXT NOT NULL,
        start_time_ms INTEGER,
        epoch_ms INTEGER NOT NULL,
        FOREIGN KEY(vod_id) REFERENCES vods(id) ON DELETE CASCADE
    )""",
    "CREATE INDEX IF NOT EXISTS idx_chap_vod ON vod_chapters(vod_id)",
]

MIGRATIONS = [
    (1, BASELINE),
    # cutkit-era auto-cut settings — the product never cuts automatically
    (2, ["DROP TABLE IF EXISTS gsi_cut_config"]),
    # transcription: per-vod job status + searchable segments (FTS5 proper,
    # not contentless — per-vod DELETE must work for re-transcribes)
    (3, [
        """CREATE TABLE IF NOT EXISTS transcripts (
            vod_id INTEGER PRIMARY KEY,
            status TEXT NOT NULL DEFAULT 'pending',
            model TEXT,
            error TEXT,
            segments INTEGER NOT NULL DEFAULT 0,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        """CREATE VIRTUAL TABLE IF NOT EXISTS transcript_fts USING fts5(
            text, vod_id UNINDEXED, t_ms UNINDEXED, dur_ms UNINDEXED
        )""",
    ]),
    # clip marks: a hotkey press books a deferred cut, executed once the
    # recording settles (cutting a still-growing OBS mp4 is unreliable)
    (4, [
        """CREATE TABLE IF NOT EXISTS pending_cuts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            event_id INTEGER NOT NULL,
            back_s REAL NOT NULL DEFAULT 30,
            post_s REAL NOT NULL DEFAULT 5,
            name TEXT NOT NULL DEFAULT '',
            status TEXT NOT NULL DEFAULT 'pending',  -- pending|done|error
            rel TEXT,
            error TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
    ]),
    # the ingest tray: a hotkey press captures a buffer and leaves it here
    # until it has been trimmed and named. Nothing is auto-published.
    (5, [
        """CREATE TABLE IF NOT EXISTS ingest_clips (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file TEXT NOT NULL,                      -- name inside INGEST_DIR
            game TEXT NOT NULL DEFAULT '',           -- auto-marked at capture
            source TEXT NOT NULL DEFAULT 'replay',   -- replay|vod
            event_id INTEGER,
            vod_id INTEGER,
            vod_time_ms INTEGER,
            epoch_ms INTEGER NOT NULL,
            duration_ms INTEGER NOT NULL DEFAULT 0,
            in_ms INTEGER NOT NULL DEFAULT 0,
            out_ms INTEGER,
            label TEXT NOT NULL DEFAULT '',
            status TEXT NOT NULL DEFAULT 'new',      -- new|kept|discarded
            rel TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        "CREATE INDEX IF NOT EXISTS ix_ingest_status ON ingest_clips (status, epoch_ms)",
    ]),
]


def current_version(conn) -> int:
    row = conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='schema_version'"
    ).fetchone()
    if not row:
        return 0
    v = conn.execute("SELECT version FROM schema_version").fetchone()
    return v[0] if v else 0


def migrate(conn) -> int:
    """Bring the database to the newest version. Returns the final version."""
    conn.execute("CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)")
    if conn.execute("SELECT COUNT(*) FROM schema_version").fetchone()[0] == 0:
        conn.execute("INSERT INTO schema_version (version) VALUES (0)")
    conn.commit()
    cur = conn.execute("SELECT version FROM schema_version").fetchone()[0]
    for ver, step in MIGRATIONS:
        if ver <= cur:
            continue
        try:
            if callable(step):
                step(conn)
            else:
                for stmt in step:
                    conn.execute(stmt)
            conn.execute("UPDATE schema_version SET version = ?", (ver,))
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        cur = ver
    return cur
