"""Persistent, zero-extra-request API observability.

This module only records the API calls the application already makes. It never
calls Gemini, never probes keys, and never performs health-check requests.
SQLite is used so request history and provider health survive app restarts.
"""
from __future__ import annotations

import json
import re
import sqlite3
import threading
import uuid
from datetime import datetime, timedelta, timezone
from pathlib import Path

import app_config

DB_FILE = app_config.CONFIG_DIR / "api_monitor.sqlite3"
RETENTION_DAYS = max(1, int(__import__("os").environ.get("API_MONITOR_RETENTION_DAYS", "30")))
_INIT_LOCK = threading.Lock()
_INITIALIZED = False


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


def _iso(dt: datetime | None = None) -> str:
    return (dt or _now()).isoformat(timespec="seconds")


def _conn():
    app_config.CONFIG_DIR.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(str(DB_FILE), timeout=20)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA busy_timeout=20000")
    return con


def init_db() -> None:
    global _INITIALIZED
    if _INITIALIZED:
        return
    with _INIT_LOCK:
        if _INITIALIZED:
            return
        with _conn() as con:
            con.executescript("""
            CREATE TABLE IF NOT EXISTS api_requests (
                id TEXT PRIMARY KEY,
                username TEXT NOT NULL,
                endpoint TEXT NOT NULL,
                kind TEXT NOT NULL,
                model TEXT,
                status TEXT NOT NULL DEFAULT 'running',
                final_provider_id TEXT,
                final_provider_label TEXT,
                attempts INTEGER NOT NULL DEFAULT 0,
                successful_attempts INTEGER NOT NULL DEFAULT 0,
                failed_attempts INTEGER NOT NULL DEFAULT 0,
                duration_ms INTEGER,
                error_code TEXT,
                error_message TEXT,
                history_id TEXT,
                created_at TEXT NOT NULL,
                finished_at TEXT
            );
            CREATE INDEX IF NOT EXISTS idx_requests_created ON api_requests(created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_requests_user ON api_requests(username, created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_requests_status ON api_requests(status, created_at DESC);

            CREATE TABLE IF NOT EXISTS api_attempts (
                id TEXT PRIMARY KEY,
                request_id TEXT NOT NULL,
                username TEXT NOT NULL,
                provider_id TEXT,
                provider_label TEXT,
                provider_scope TEXT,
                assigned_to TEXT,
                key_preview TEXT,
                model TEXT,
                is_fallback INTEGER NOT NULL DEFAULT 0,
                status TEXT NOT NULL DEFAULT 'running',
                error_code TEXT,
                http_status INTEGER,
                error_message TEXT,
                duration_ms INTEGER,
                created_at TEXT NOT NULL,
                finished_at TEXT,
                FOREIGN KEY(request_id) REFERENCES api_requests(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_attempts_request ON api_attempts(request_id, created_at);
            CREATE INDEX IF NOT EXISTS idx_attempts_provider ON api_attempts(provider_id, created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_attempts_user ON api_attempts(username, created_at DESC);

            CREATE TABLE IF NOT EXISTS provider_health (
                provider_id TEXT PRIMARY KEY,
                state TEXT NOT NULL,
                message TEXT,
                checked_at TEXT,
                cooldown_until REAL
            );
            """)
        _INITIALIZED = True


def _clean_error(message: str | None, limit: int = 500) -> str:
    text = re.sub(r"\s+", " ", str(message or "")).strip()
    return text[:limit]


def _http_status(message: str | None):
    m = re.search(r"(?:status|code|HTTP|http)\s*[:=]?\s*(4\d\d|5\d\d)\b", str(message or ""))
    if m:
        return int(m.group(1))
    m = re.search(r"\b(429|403|401|404|408|409|422|500|502|503|504)\b", str(message or ""))
    return int(m.group(1)) if m else None


def begin_request(*, username: str, endpoint: str, kind: str, model: str = "", history_id: str = "") -> str:
    init_db()
    rid = uuid.uuid4().hex
    with _conn() as con:
        con.execute(
            "INSERT INTO api_requests(id,username,endpoint,kind,model,status,history_id,created_at) VALUES(?,?,?,?,?,?,?,?)",
            (rid, username, endpoint, kind, model, "running", history_id or None, _iso()),
        )
    return rid


def finish_request(request_id: str, *, status: str, provider_id: str = "", provider_label: str = "", error_code: str = "", error_message: str = "") -> None:
    init_db()
    now = _now()
    with _conn() as con:
        row = con.execute("SELECT created_at FROM api_requests WHERE id=?", (request_id,)).fetchone()
        if not row:
            return
        try:
            started = datetime.fromisoformat(row["created_at"])
            duration = max(0, int((now - started).total_seconds() * 1000))
        except Exception:
            duration = None
        counts = con.execute(
            "SELECT COUNT(*) attempts, SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) ok, SUM(CASE WHEN status NOT IN ('success','skipped') THEN 1 ELSE 0 END) failed FROM api_attempts WHERE request_id=?",
            (request_id,),
        ).fetchone()
        con.execute(
            "UPDATE api_requests SET status=?, final_provider_id=?, final_provider_label=?, attempts=?, successful_attempts=?, failed_attempts=?, duration_ms=?, error_code=?, error_message=?, finished_at=? WHERE id=?",
            (status, provider_id or None, provider_label or None, counts["attempts"] or 0, counts["ok"] or 0, counts["failed"] or 0, duration, error_code or None, _clean_error(error_message), _iso(now), request_id),
        )


def begin_attempt(request_id: str, *, username: str, provider: dict, model: str, is_fallback: bool) -> str:
    init_db()
    aid = uuid.uuid4().hex
    key = provider.get("api_key") or ""
    preview = ("••••" + key[-4:]) if key else ""
    scope = provider.get("scope") or ("assigned" if provider.get("assigned_to") else "user")
    with _conn() as con:
        con.execute(
            "INSERT INTO api_attempts(id,request_id,username,provider_id,provider_label,provider_scope,assigned_to,key_preview,model,is_fallback,status,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",
            (aid, request_id, username, provider.get("id"), provider.get("label") or provider.get("type") or "Gemini key", scope, provider.get("assigned_to") or None, preview, model, 1 if is_fallback else 0, "running", _iso()),
        )
        con.execute("UPDATE api_requests SET attempts=attempts+1 WHERE id=?", (request_id,))
    return aid


def finish_attempt(attempt_id: str, *, status: str, error_code: str = "", error_message: str = "", http_status: int | None = None) -> None:
    init_db()
    now = _now()
    with _conn() as con:
        row = con.execute("SELECT created_at FROM api_attempts WHERE id=?", (attempt_id,)).fetchone()
        if not row:
            return
        try:
            started = datetime.fromisoformat(row["created_at"])
            duration = max(0, int((now - started).total_seconds() * 1000))
        except Exception:
            duration = None
        con.execute(
            "UPDATE api_attempts SET status=?, error_code=?, http_status=?, error_message=?, duration_ms=?, finished_at=? WHERE id=?",
            (status, error_code or None, http_status if http_status is not None else _http_status(error_message), _clean_error(error_message), duration, _iso(now), attempt_id),
        )


def record_skipped_attempt(request_id: str, *, username: str, provider: dict, model: str, is_fallback: bool, reason: str) -> None:
    aid = begin_attempt(request_id, username=username, provider=provider, model=model, is_fallback=is_fallback)
    finish_attempt(aid, status="skipped", error_code="capacity", error_message=reason)


def set_provider_health(provider_id: str, state: str, message: str = "", cooldown_until: float | None = None) -> None:
    init_db()
    with _conn() as con:
        con.execute(
            "INSERT INTO provider_health(provider_id,state,message,checked_at,cooldown_until) VALUES(?,?,?,?,?) ON CONFLICT(provider_id) DO UPDATE SET state=excluded.state,message=excluded.message,checked_at=excluded.checked_at,cooldown_until=excluded.cooldown_until",
            (provider_id, state, _clean_error(message, 300), _iso(), cooldown_until),
        )


def get_provider_health(provider_id: str):
    init_db()
    with _conn() as con:
        row = con.execute("SELECT provider_id,state,message,checked_at,cooldown_until FROM provider_health WHERE provider_id=?", (provider_id,)).fetchone()
    return dict(row) if row else None


def clear_provider_health(provider_id: str) -> None:
    init_db()
    with _conn() as con:
        con.execute("DELETE FROM provider_health WHERE provider_id=?", (provider_id,))


def clear_all_provider_health() -> None:
    init_db()
    with _conn() as con:
        con.execute("DELETE FROM provider_health")


def _since(hours: int) -> str:
    return _iso(_now() - timedelta(hours=max(1, min(720, hours))))


def summary(*, hours: int = 24) -> dict:
    init_db()
    since = _since(hours)
    with _conn() as con:
        r = con.execute("""
            SELECT COUNT(*) total,
                   SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) success,
                   SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END) failed,
                   SUM(CASE WHEN status IN ('blocked_credit','blocked_no_provider') THEN 1 ELSE 0 END) blocked,
                   AVG(CASE WHEN duration_ms IS NOT NULL THEN duration_ms END) avg_ms
            FROM api_requests WHERE created_at >= ?
        """, (since,)).fetchone()
        a = con.execute("""
            SELECT COUNT(*) attempts,
                   SUM(CASE WHEN status='rate_limit' THEN 1 ELSE 0 END) rate_limits,
                   SUM(CASE WHEN status='dead' THEN 1 ELSE 0 END) dead,
                   SUM(CASE WHEN status='timeout' THEN 1 ELSE 0 END) timeouts,
                   SUM(CASE WHEN status='error' THEN 1 ELSE 0 END) errors,
                   SUM(CASE WHEN status='invalid_response' THEN 1 ELSE 0 END) invalid_response,
                   SUM(CASE WHEN is_fallback=1 THEN 1 ELSE 0 END) fallback_attempts
            FROM api_attempts WHERE created_at >= ?
        """, (since,)).fetchone()
        health = con.execute("SELECT state, COUNT(*) n FROM provider_health GROUP BY state").fetchall()
    total = r["total"] or 0
    success = r["success"] or 0
    return {
        "hours": hours, "total_requests": total, "success": success, "failed": r["failed"] or 0,
        "blocked": r["blocked"] or 0, "success_rate": round(success * 100 / total, 1) if total else 0,
        "avg_ms": round(r["avg_ms"] or 0), "attempts": a["attempts"] or 0,
        "rate_limits": a["rate_limits"] or 0, "dead": a["dead"] or 0, "timeouts": a["timeouts"] or 0,
        "errors": a["errors"] or 0, "invalid_response": a["invalid_response"] or 0,
        "fallback_attempts": a["fallback_attempts"] or 0,
        "provider_health": {x["state"]: x["n"] for x in health},
    }


def list_requests(*, hours: int = 24, limit: int = 100, username: str = "", status: str = "") -> list[dict]:
    init_db()
    since = _since(hours)
    limit = max(1, min(500, int(limit)))
    clauses = ["r.created_at >= ?"]
    params = [since]
    if username:
        clauses.append("r.username = ?"); params.append(username)
    if status:
        clauses.append("r.status = ?"); params.append(status)
    where = " AND ".join(clauses)
    with _conn() as con:
        rows = con.execute(f"""
            SELECT r.*, COALESCE(SUM(CASE WHEN a.status='rate_limit' THEN 1 ELSE 0 END),0) rate_limits,
                   COALESCE(SUM(CASE WHEN a.status='dead' THEN 1 ELSE 0 END),0) dead_attempts
            FROM api_requests r LEFT JOIN api_attempts a ON a.request_id=r.id
            WHERE {where}
            GROUP BY r.id ORDER BY r.created_at DESC LIMIT ?
        """, (*params, limit)).fetchall()
    return [dict(x) for x in rows]


def request_detail(request_id: str) -> dict | None:
    init_db()
    with _conn() as con:
        r = con.execute("SELECT * FROM api_requests WHERE id=?", (request_id,)).fetchone()
        if not r:
            return None
        a = con.execute("SELECT * FROM api_attempts WHERE request_id=? ORDER BY created_at ASC", (request_id,)).fetchall()
    return {"request": dict(r), "attempts": [dict(x) for x in a]}


def user_health(*, hours: int = 24) -> list[dict]:
    init_db()
    since = _since(hours)
    with _conn() as con:
        rows = con.execute("""
            SELECT username, COUNT(*) total,
                   SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) success,
                   SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END) failed,
                   SUM(CASE WHEN status='blocked_credit' THEN 1 ELSE 0 END) blocked,
                   AVG(CASE WHEN duration_ms IS NOT NULL THEN duration_ms END) avg_ms,
                   MAX(created_at) last_request
            FROM api_requests WHERE created_at >= ?
            GROUP BY username ORDER BY total DESC
        """, (since,)).fetchall()
        attempt_rows = con.execute("""
            SELECT username,
                   SUM(CASE WHEN status='rate_limit' THEN 1 ELSE 0 END) rate_limits,
                   SUM(CASE WHEN status='dead' THEN 1 ELSE 0 END) dead,
                   SUM(CASE WHEN status='error' THEN 1 ELSE 0 END) errors
            FROM api_attempts WHERE created_at >= ? GROUP BY username
        """, (since,)).fetchall()
    extra = {x["username"]: dict(x) for x in attempt_rows}
    out=[]
    for r in rows:
        d=dict(r); d.update(extra.get(r["username"], {}));
        total=d.get("total") or 0; success=d.get("success") or 0
        d["success_rate"] = round(success*100/total,1) if total else 0
        d["avg_ms"] = round(d.get("avg_ms") or 0)
        out.append(d)
    return out


def provider_health_stats(*, hours: int = 24) -> list[dict]:
    init_db()
    since = _since(hours)
    with _conn() as con:
        rows = con.execute("""
            SELECT a.provider_id, MAX(a.provider_label) provider_label, MAX(a.key_preview) key_preview,
                   COUNT(*) attempts,
                   SUM(CASE WHEN a.status='success' THEN 1 ELSE 0 END) success,
                   SUM(CASE WHEN a.status='rate_limit' THEN 1 ELSE 0 END) rate_limits,
                   SUM(CASE WHEN a.status='dead' THEN 1 ELSE 0 END) dead,
                   SUM(CASE WHEN a.status='error' THEN 1 ELSE 0 END) errors,
                   SUM(CASE WHEN a.is_fallback=1 THEN 1 ELSE 0 END) fallback_attempts,
                   AVG(CASE WHEN a.duration_ms IS NOT NULL THEN a.duration_ms END) avg_ms,
                   MAX(a.created_at) last_used
            FROM api_attempts a WHERE a.created_at >= ?
            GROUP BY a.provider_id ORDER BY attempts DESC
        """, (since,)).fetchall()
    return [dict(x) | {"avg_ms": round(x["avg_ms"] or 0)} for x in rows]


def cleanup() -> None:
    init_db()
    cutoff = _iso(_now() - timedelta(days=RETENTION_DAYS))
    with _conn() as con:
        con.execute("DELETE FROM api_attempts WHERE created_at < ?", (cutoff,))
        con.execute("DELETE FROM api_requests WHERE created_at < ?", (cutoff,))


def clear_all_data() -> dict:
    """Remove all stored monitoring history and provider-health records.

    This only clears observability data. It does not remove API keys, users,
    generation history, or the application's in-memory rotation state.
    """
    init_db()
    deleted = {"requests": 0, "attempts": 0, "provider_health": 0}
    with _conn() as con:
        deleted["attempts"] = con.execute("SELECT COUNT(*) FROM api_attempts").fetchone()[0]
        deleted["requests"] = con.execute("SELECT COUNT(*) FROM api_requests").fetchone()[0]
        deleted["provider_health"] = con.execute("SELECT COUNT(*) FROM provider_health").fetchone()[0]
        con.execute("DELETE FROM api_attempts")
        con.execute("DELETE FROM api_requests")
        con.execute("DELETE FROM provider_health")
    # Reclaim the SQLite file/WAL space where possible. If a concurrent
    # monitoring write temporarily prevents VACUUM, the data is still cleared.
    try:
        with _conn() as con:
            con.execute("PRAGMA wal_checkpoint(TRUNCATE)")
            con.execute("VACUUM")
    except sqlite3.Error:
        pass
    return deleted


init_db()
try:
    cleanup()
except Exception:
    pass