"""VerIQ — SQLite storage (WAL mode)."""
import json
import os
import secrets
import sqlite3
import time
from pathlib import Path

DB_PATH = Path(os.environ.get("VERIQ_DB_PATH", "/var/lib/veriq/veriq.db"))

_SCHEMA = """
CREATE TABLE IF NOT EXISTS results (
    result_id   TEXT PRIMARY KEY,
    score       INTEGER NOT NULL,
    percentile  REAL NOT NULL,
    bank_version TEXT NOT NULL,
    identity    TEXT,
    completed_at TEXT NOT NULL,
    payload     TEXT NOT NULL,
    signature   TEXT NOT NULL,
    answers     TEXT NOT NULL,
    flagged     INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts REAL NOT NULL,
    kind TEXT NOT NULL,
    meta TEXT
);
CREATE INDEX IF NOT EXISTS idx_events_kind ON events(kind);
"""


def _conn() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    with _conn() as c:
        c.executescript(_SCHEMA)
        # migration: score_json snapshot column (added 2026-10-13, iter 4)
        try:
            c.execute("ALTER TABLE results ADD COLUMN score_json TEXT")
        except sqlite3.OperationalError:
            pass  # column already exists


def new_result_id() -> str:
    """8-char base62 URL-safe id with collision check."""
    alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
    with _conn() as c:
        while True:
            rid = "".join(secrets.choice(alphabet) for _ in range(8))
            row = c.execute("SELECT 1 FROM results WHERE result_id=?", (rid,)).fetchone()
            if row is None:
                return rid


def save_result(result_id: str, score: int, percentile: float,
                bank_version: str, identity: str | None, completed_at: str,
                payload: str, signature: str, answers: dict,
                flagged: bool = False, score_json: str | None = None) -> None:
    with _conn() as c:
        c.execute(
            "INSERT INTO results (result_id, score, percentile, bank_version,"
            " identity, completed_at, payload, signature, answers, flagged,"
            " score_json)"
            " VALUES (?,?,?,?,?,?,?,?,?,?,?)",
            (result_id, score, percentile, bank_version, identity,
             completed_at, payload, signature, json.dumps(answers),
             int(flagged), score_json),
        )


def get_result(result_id: str) -> dict | None:
    with _conn() as c:
        row = c.execute("SELECT * FROM results WHERE result_id=?", (result_id,)).fetchone()
        if row is None:
            return None
        return dict(row)


def log_event(kind: str, meta: dict | None = None) -> None:
    with _conn() as c:
        c.execute("INSERT INTO events (ts, kind, meta) VALUES (?,?,?)",
                  (time.time(), kind, json.dumps(meta or {})))


def event_stats() -> dict:
    with _conn() as c:
        rows = c.execute(
            "SELECT kind, COUNT(*) AS n FROM events GROUP BY kind").fetchall()
        return {r["kind"]: r["n"] for r in rows}


def stats_overview() -> dict:
    """Aggregate numbers for the /stats dashboard."""
    with _conn() as c:
        events = {r["kind"]: r["n"] for r in c.execute(
            "SELECT kind, COUNT(*) AS n FROM events GROUP BY kind").fetchall()}
        total, flagged = c.execute(
            "SELECT COUNT(*), COALESCE(SUM(flagged), 0) FROM results").fetchone()
        avg_iq = c.execute("SELECT AVG(score) FROM results").fetchone()[0]
        return {
            "events": events,
            "results_total": total,
            "results_flagged": flagged,
            "avg_iq": round(avg_iq, 1) if avg_iq is not None else None,
        }