"""SQLite persistence (aiosqlite, WAL)."""

from __future__ import annotations

import json
import time
from pathlib import Path
from typing import Any, Iterable

import aiosqlite

SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'admin',
    created_at REAL NOT NULL,
    last_login_at REAL
);
CREATE TABLE IF NOT EXISTS api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    prefix TEXT NOT NULL,
    key_hash TEXT UNIQUE NOT NULL,
    scopes TEXT NOT NULL DEFAULT 'inference',
    created_at REAL NOT NULL,
    last_used_at REAL,
    request_count INTEGER NOT NULL DEFAULT 0,
    revoked_at REAL
);
CREATE TABLE IF NOT EXISTS models (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    family TEXT,
    provider TEXT,
    architecture TEXT,
    model_type TEXT,
    parameter_count INTEGER,
    active_parameter_count INTEGER,
    quantization TEXT,
    quant_bits REAL,
    runtime TEXT NOT NULL,
    format TEXT NOT NULL,
    path TEXT NOT NULL,
    weights_file TEXT,
    mmproj_file TEXT,
    disk_size_bytes INTEGER NOT NULL DEFAULT 0,
    weights_bytes INTEGER NOT NULL DEFAULT 0,
    estimated_ram_gb REAL,
    kv_bytes_per_token INTEGER,
    recommended_context INTEGER,
    max_context INTEGER,
    task TEXT NOT NULL DEFAULT 'text-generation',
    vision INTEGER NOT NULL DEFAULT 0,
    embedding INTEGER NOT NULL DEFAULT 0,
    reranker INTEGER NOT NULL DEFAULT 0,
    thinking INTEGER NOT NULL DEFAULT 0,
    tools INTEGER NOT NULL DEFAULT 0,
    size_class TEXT,
    installed INTEGER NOT NULL DEFAULT 1,
    enabled INTEGER NOT NULL DEFAULT 1,
    favorite INTEGER NOT NULL DEFAULT 0,
    pinned INTEGER NOT NULL DEFAULT 0,
    verified INTEGER NOT NULL DEFAULT 0,
    compatible INTEGER NOT NULL DEFAULT 1,
    compatibility_status TEXT,
    compatibility_reason TEXT,
    tags TEXT NOT NULL DEFAULT '[]',
    repository TEXT,
    manifest TEXT,
    notes TEXT,
    overrides TEXT NOT NULL DEFAULT '{}',
    created_at REAL NOT NULL,
    updated_at REAL NOT NULL,
    last_loaded_at REAL,
    last_used_at REAL,
    load_count INTEGER NOT NULL DEFAULT 0,
    request_count INTEGER NOT NULL DEFAULT 0,
    tokens_generated INTEGER NOT NULL DEFAULT 0,
    min_load_ms REAL,
    avg_load_ms REAL,
    max_load_ms REAL,
    last_load_ms REAL,
    avg_tps REAL,
    first_token_latency_ms REAL
);
CREATE TABLE IF NOT EXISTS model_aliases (
    alias TEXT PRIMARY KEY,
    model_id TEXT NOT NULL,
    created_at REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS model_benchmarks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    model_id TEXT NOT NULL,
    created_at REAL NOT NULL,
    load_ms REAL,
    prompt_tokens INTEGER,
    prompt_tps REAL,
    generation_tokens INTEGER,
    generation_tps REAL,
    ttft_ms REAL,
    peak_memory_gb REAL,
    avg_memory_gb REAL,
    cpu_percent REAL,
    gpu_percent REAL,
    thermal_state TEXT,
    context INTEGER,
    runtime TEXT,
    params TEXT,
    notes TEXT
);
CREATE TABLE IF NOT EXISTS model_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at REAL NOT NULL,
    model_id TEXT,
    event TEXT NOT NULL,
    detail TEXT
);
CREATE TABLE IF NOT EXISTS inference_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at REAL NOT NULL,
    model_id TEXT,
    requested_model TEXT,
    endpoint TEXT NOT NULL,
    api_key_id INTEGER,
    stream INTEGER NOT NULL DEFAULT 0,
    prompt_tokens INTEGER,
    completion_tokens INTEGER,
    ttft_ms REAL,
    total_ms REAL,
    tps REAL,
    load_wait_ms REAL,
    status INTEGER,
    error_code TEXT,
    prompt TEXT,
    completion TEXT
);
CREATE INDEX IF NOT EXISTS idx_inference_created ON inference_requests(created_at);
CREATE TABLE IF NOT EXISTS system_metrics (
    ts REAL PRIMARY KEY,
    mem_used_gb REAL,
    mem_available_gb REAL,
    mem_pressure INTEGER,
    swap_used_gb REAL,
    cpu_percent REAL,
    gpu_percent REAL,
    disk_free_gb REAL,
    thermal TEXT,
    worker_rss_gb REAL,
    loaded_model TEXT
);
CREATE TABLE IF NOT EXISTS jobs (
    id TEXT PRIMARY KEY,
    kind TEXT NOT NULL,
    status TEXT NOT NULL,
    title TEXT,
    payload TEXT,
    progress REAL NOT NULL DEFAULT 0,
    detail TEXT,
    result TEXT,
    error TEXT,
    created_at REAL NOT NULL,
    started_at REAL,
    finished_at REAL
);
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at REAL NOT NULL,
    actor TEXT,
    action TEXT NOT NULL,
    target TEXT,
    detail TEXT,
    ip TEXT
);
CREATE TABLE IF NOT EXISTS harvest_candidates (
    repo_id TEXT PRIMARY KEY,
    runtime TEXT NOT NULL,
    family TEXT,
    base_model TEXT,
    name TEXT,
    task TEXT,
    quantization TEXT,
    parameter_count INTEGER,
    download_bytes INTEGER,
    estimated_ram_gb REAL,
    size_class TEXT,
    compatibility_status TEXT,
    compatibility_reason TEXT,
    downloads INTEGER,
    likes INTEGER,
    last_modified TEXT,
    files TEXT,
    duplicate_of TEXT,
    installed INTEGER NOT NULL DEFAULT 0,
    selected INTEGER NOT NULL DEFAULT 0,
    dismissed INTEGER NOT NULL DEFAULT 0,
    score REAL,
    scanned_at REAL NOT NULL,
    raw TEXT
);
"""


class Database:
    def __init__(self, path: Path):
        self.path = path
        self._conn: aiosqlite.Connection | None = None

    async def connect(self) -> None:
        self.path.parent.mkdir(parents=True, exist_ok=True)
        self._conn = await aiosqlite.connect(self.path)
        self._conn.row_factory = aiosqlite.Row
        await self._conn.execute("PRAGMA journal_mode=WAL")
        await self._conn.execute("PRAGMA synchronous=NORMAL")
        await self._conn.execute("PRAGMA foreign_keys=ON")
        await self._conn.executescript(SCHEMA)
        await self._conn.commit()

    async def close(self) -> None:
        if self._conn:
            await self._conn.commit()
            await self._conn.close()
            self._conn = None

    @property
    def conn(self) -> aiosqlite.Connection:
        assert self._conn is not None, "database not connected"
        return self._conn

    # -- helpers ----------------------------------------------------------
    async def execute(self, sql: str, params: Iterable[Any] = ()) -> int:
        cur = await self.conn.execute(sql, tuple(params))
        await self.conn.commit()
        return cur.lastrowid or 0

    async def executemany(self, sql: str, rows: Iterable[Iterable[Any]]) -> None:
        await self.conn.executemany(sql, [tuple(r) for r in rows])
        await self.conn.commit()

    async def fetchone(self, sql: str, params: Iterable[Any] = ()) -> dict | None:
        cur = await self.conn.execute(sql, tuple(params))
        row = await cur.fetchone()
        return dict(row) if row else None

    async def fetchall(self, sql: str, params: Iterable[Any] = ()) -> list[dict]:
        cur = await self.conn.execute(sql, tuple(params))
        rows = await cur.fetchall()
        return [dict(r) for r in rows]

    async def scalar(self, sql: str, params: Iterable[Any] = ()) -> Any:
        cur = await self.conn.execute(sql, tuple(params))
        row = await cur.fetchone()
        return row[0] if row else None

    # -- settings -----------------------------------------------------------
    async def get_setting(self, key: str, default: Any = None) -> Any:
        row = await self.fetchone("SELECT value FROM settings WHERE key=?", (key,))
        if not row:
            return default
        try:
            return json.loads(row["value"])
        except json.JSONDecodeError:
            return row["value"]

    async def set_setting(self, key: str, value: Any) -> None:
        await self.execute(
            "INSERT INTO settings(key, value, updated_at) VALUES(?,?,?) "
            "ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at",
            (key, json.dumps(value), time.time()),
        )

    async def all_settings(self) -> dict[str, Any]:
        rows = await self.fetchall("SELECT key, value FROM settings")
        out = {}
        for r in rows:
            try:
                out[r["key"]] = json.loads(r["value"])
            except json.JSONDecodeError:
                out[r["key"]] = r["value"]
        return out

    # -- audit / events -----------------------------------------------------
    async def audit(self, action: str, *, actor: str | None = None, target: str | None = None,
                    detail: Any = None, ip: str | None = None) -> None:
        await self.execute(
            "INSERT INTO audit_logs(created_at, actor, action, target, detail, ip) VALUES(?,?,?,?,?,?)",
            (time.time(), actor, action, target, json.dumps(detail) if detail is not None else None, ip),
        )

    async def model_event(self, model_id: str | None, event: str, detail: Any = None) -> None:
        await self.execute(
            "INSERT INTO model_events(created_at, model_id, event, detail) VALUES(?,?,?,?)",
            (time.time(), model_id, event, json.dumps(detail) if detail is not None else None),
        )