"""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)


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) -> None:
    with _conn() as c:
        c.execute(
            "INSERT INTO results (result_id, score, percentile, bank_version,"
            " identity, completed_at, payload, signature, answers, flagged)"
            " VALUES (?,?,?,?,?,?,?,?,?,?)",
            (result_id, score, percentile, bank_version, identity,
             completed_at, payload, signature, json.dumps(answers),
             int(flagged)),
        )


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}