"""SQLite results store with run stamping and idempotent result keys.

Every run records: git sha, engine version, full config snapshot, and the
challenge-set content hash — results are only comparable within one hash.
Results are unique on (run_id, model, mode, challenge, sample); the runner
skips tuples that already exist, which is what makes crashed or extended
runs resumable (llmcensor's append pattern).
"""

from __future__ import annotations

import json
import sqlite3
import subprocess
from datetime import datetime, timezone
from pathlib import Path

from . import ENGINE_VERSION
from .config import REPO_ROOT

DB_PATH = REPO_ROOT / ".lab" / "results.sqlite3"

SCHEMA = """
CREATE TABLE IF NOT EXISTS runs (
    id INTEGER PRIMARY KEY,
    started_at TEXT NOT NULL,
    git_sha TEXT NOT NULL,
    engine_version TEXT NOT NULL,
    challenge_set_hash TEXT NOT NULL,
    config_json TEXT NOT NULL,
    note TEXT DEFAULT ''
);
CREATE TABLE IF NOT EXISTS results (
    run_id INTEGER NOT NULL REFERENCES runs(id),
    model TEXT NOT NULL,
    mode TEXT NOT NULL,
    challenge TEXT NOT NULL,
    sample INTEGER NOT NULL,
    status TEXT NOT NULL,              -- ok | error | degenerate
    error TEXT,
    score_total REAL,
    score_tests REAL,
    score_deliverables REAL,
    score_content REAL,
    tests_passed INTEGER,
    tests_failed INTEGER,
    wall_secs REAL,
    session_path TEXT,
    finished_at TEXT NOT NULL,
    UNIQUE (run_id, model, mode, challenge, sample)
);
"""


def connect(path: Path = DB_PATH) -> sqlite3.Connection:
    path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.executescript(SCHEMA)
    return conn


def git_sha() -> str:
    try:
        return subprocess.run(
            ["git", "rev-parse", "--short", "HEAD"],
            cwd=REPO_ROOT, capture_output=True, text=True, check=True,
        ).stdout.strip()
    except (subprocess.CalledProcessError, FileNotFoundError):
        return "unknown"


def create_run(conn: sqlite3.Connection, challenge_set_hash: str,
               config_snapshot: dict, note: str = "") -> int:
    cur = conn.execute(
        "INSERT INTO runs (started_at, git_sha, engine_version, challenge_set_hash, config_json, note)"
        " VALUES (?, ?, ?, ?, ?, ?)",
        (_now(), git_sha(), ENGINE_VERSION, challenge_set_hash,
         json.dumps(config_snapshot, sort_keys=True), note),
    )
    conn.commit()
    return cur.lastrowid


def has_result(conn: sqlite3.Connection, run_id: int, model: str, mode: str,
               challenge: str, sample: int) -> bool:
    row = conn.execute(
        "SELECT 1 FROM results WHERE run_id=? AND model=? AND mode=? AND challenge=? AND sample=?",
        (run_id, model, mode, challenge, sample),
    ).fetchone()
    return row is not None


def record_result(conn: sqlite3.Connection, run_id: int, model: str, mode: str,
                  challenge: str, sample: int, *, status: str, error: str | None = None,
                  grade=None, tests=None, wall_secs: float = 0.0,
                  session_path: str | None = None) -> None:
    conn.execute(
        "INSERT INTO results (run_id, model, mode, challenge, sample, status, error,"
        " score_total, score_tests, score_deliverables, score_content,"
        " tests_passed, tests_failed, wall_secs, session_path, finished_at)"
        " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        (run_id, model, mode, challenge, sample, status, error,
         grade.total if grade else None,
         grade.score_tests if grade else None,
         grade.score_deliverables if grade else None,
         grade.score_content if grade else None,
         tests.passed if tests else None,
         tests.failed if tests else None,
         wall_secs, session_path, _now()),
    )
    conn.commit()  # commit per result: a crash never loses prior work


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()