"""
Reusable bot observability module (DVI-824).

Records runs/steps/logs/artifacts in bot_diagnostics.db (SQLite).
Bot-agnostic — any future bot plugs in with DiagnosticRun().
No bot-specific logic lives here.
"""

import json
import logging
import shutil
import sqlite3
import threading
import time
import uuid
from pathlib import Path

DB_PATH = Path(__file__).resolve().parent / "bot_diagnostics.db"
ARTIFACTS_DIR = Path(__file__).resolve().parent / "diagnostics"

_SCHEMA = """
CREATE TABLE IF NOT EXISTS runs (
    id           TEXT PRIMARY KEY,
    bot          TEXT NOT NULL,
    trigger      TEXT NOT NULL,
    target_date  TEXT,
    started_at   REAL NOT NULL,
    finished_at  REAL,
    status       TEXT NOT NULL DEFAULT 'running',
    summary_json TEXT,
    error        TEXT
);
CREATE INDEX IF NOT EXISTS idx_runs_bot_started ON runs (bot, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_runs_bot_status  ON runs (bot, status);

CREATE TABLE IF NOT EXISTS steps (
    id          TEXT PRIMARY KEY,
    run_id      TEXT NOT NULL,
    seq         INTEGER NOT NULL,
    name        TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'running',
    started_at  REAL NOT NULL,
    finished_at REAL,
    detail_json TEXT,
    FOREIGN KEY (run_id) REFERENCES runs (id)
);
CREATE INDEX IF NOT EXISTS idx_steps_run ON steps (run_id, seq);

CREATE TABLE IF NOT EXISTS logs (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id  TEXT NOT NULL,
    step_id TEXT,
    ts      REAL NOT NULL,
    level   TEXT NOT NULL,
    message TEXT NOT NULL,
    FOREIGN KEY (run_id) REFERENCES runs (id)
);
CREATE INDEX IF NOT EXISTS idx_logs_run_level ON logs (run_id, level);
CREATE INDEX IF NOT EXISTS idx_logs_run_step  ON logs (run_id, step_id);

CREATE TABLE IF NOT EXISTS artifacts (
    id           TEXT PRIMARY KEY,
    run_id       TEXT NOT NULL,
    step_id      TEXT,
    name         TEXT NOT NULL,
    kind         TEXT NOT NULL,
    path         TEXT NOT NULL,
    size_bytes   INTEGER,
    content_type TEXT,
    created_at   REAL NOT NULL,
    FOREIGN KEY (run_id) REFERENCES runs (id)
);
CREATE INDEX IF NOT EXISTS idx_artifacts_run ON artifacts (run_id);

CREATE TABLE IF NOT EXISTS settings (
    bot            TEXT PRIMARY KEY,
    max_runs       INTEGER NOT NULL DEFAULT 100,
    max_log_lines  INTEGER NOT NULL DEFAULT 2000,
    retention_days INTEGER
);
"""

_CONTENT_TYPES: dict[str, str] = {
    "xlsx": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "pdf":  "application/pdf",
    "json": "application/json",
    "csv":  "text/csv",
}

_SETTING_DEFAULTS: dict = {"max_runs": 100, "max_log_lines": 2000, "retention_days": None}

# ── DB helpers ────────────────────────────────────────────────────────────────

def _open_db() -> sqlite3.Connection:
    """Open bot_diagnostics.db, create schema on first use. Caller must close."""
    conn = sqlite3.connect(str(DB_PATH), check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.executescript(_SCHEMA)
    return conn


# ── Thread-local: tracks which run/step is active on each thread ──────────────

_tl = threading.local()  # .run (DiagnosticRun | None), .step_id (str | None)


# ── Logging handler: funnels logging.* calls into the active run's DB rows ───

class _DiagHandler(logging.Handler):
    """Captures log records emitted inside a DiagnosticRun step into the DB."""

    def emit(self, record: logging.LogRecord) -> None:
        run: "DiagnosticRun | None" = getattr(_tl, "run", None)
        if run is None:
            return
        step_id: str | None = getattr(_tl, "step_id", None)
        try:
            run._db_log(step_id, record.levelname, self.format(record))
        except Exception:
            pass


_diag_handler = _DiagHandler()
_diag_handler.setLevel(logging.DEBUG)
# Attach once at import time; thread-local guard means it's a no-op on non-diag threads.
logging.getLogger().addHandler(_diag_handler)


# ── Step context manager ──────────────────────────────────────────────────────

class _StepCtx:
    """Context manager for one named step within a DiagnosticRun."""

    def __init__(self, run: "DiagnosticRun", name: str) -> None:
        self._run = run
        self._name = name
        self.id: str = str(uuid.uuid4())
        self._warned: bool = False

    def __enter__(self) -> "_StepCtx":
        run = self._run
        with run._lock:
            run._step_seq += 1
            seq = run._step_seq
            run._conn.execute(
                "INSERT INTO steps (id, run_id, seq, name, status, started_at)"
                " VALUES (?, ?, ?, ?, 'running', ?)",
                (self.id, run.id, seq, self._name, time.time()),
            )
            run._conn.commit()
        _tl.step_id = self.id
        return self

    def __exit__(self, exc_type, exc_val, exc_tb) -> bool:
        if exc_type:
            status = "error"
        elif self._warned:
            status = "warn"
        else:
            status = "ok"
        with self._run._lock:
            if exc_val:
                # Store error detail; overwrites any set_detail() call intentionally.
                self._run._conn.execute(
                    "UPDATE steps SET status=?, finished_at=?, detail_json=? WHERE id=?",
                    (status, time.time(), json.dumps({"error": str(exc_val)}), self.id),
                )
            else:
                # Preserve detail_json written by set_detail().
                self._run._conn.execute(
                    "UPDATE steps SET status=?, finished_at=? WHERE id=?",
                    (status, time.time(), self.id),
                )
            self._run._conn.commit()
        _tl.step_id = None
        return False  # never suppress exceptions

    def set_detail(self, d: dict) -> None:
        with self._run._lock:
            self._run._conn.execute(
                "UPDATE steps SET detail_json=? WHERE id=?",
                (json.dumps(d), self.id),
            )
            self._run._conn.commit()

    def warn(self) -> None:
        self._warned = True
        with self._run._lock:
            self._run._conn.execute(
                "UPDATE steps SET status='warn' WHERE id=?", (self.id,)
            )
            self._run._conn.commit()


# ── DiagnosticRun ─────────────────────────────────────────────────────────────

class DiagnosticRun:
    """
    Context manager recording one bot pipeline execution.

    Usage::

        with DiagnosticRun("winston", "scheduler", "2026-06-30") as run:
            with run.step("fetch_data"):
                log.info("Fetching…")   # captured automatically
            run.set_summary({"rows": 42})
    """

    def __init__(
        self,
        bot: str,
        trigger: str,
        target_date: str | None = None,
        run_id: str | None = None,
    ) -> None:
        self.bot = bot
        self.trigger = trigger
        self.target_date = target_date
        self.id: str = run_id or str(uuid.uuid4())
        self._lock = threading.Lock()
        self._step_seq = 0
        self._status = "running"
        self._conn: sqlite3.Connection | None = None
        self._log_count = 0
        self._max_log_lines = _SETTING_DEFAULTS["max_log_lines"]
        self._max_runs = _SETTING_DEFAULTS["max_runs"]

    def __enter__(self) -> "DiagnosticRun":
        self._conn = _open_db()
        row = self._conn.execute(
            "SELECT max_runs, max_log_lines FROM settings WHERE bot=?", (self.bot,)
        ).fetchone()
        if row:
            self._max_runs = row["max_runs"]
            self._max_log_lines = row["max_log_lines"]
        self._conn.execute(
            "INSERT INTO runs (id, bot, trigger, target_date, started_at, status)"
            " VALUES (?, ?, ?, ?, ?, 'running')",
            (self.id, self.bot, self.trigger, self.target_date, time.time()),
        )
        self._conn.commit()
        _tl.run = self
        _tl.step_id = None
        return self

    def __exit__(self, exc_type, exc_val, exc_tb) -> bool:
        if exc_type and self._status == "running":
            self._status = "error"
        elif not exc_type and self._status == "running":
            self._status = "done"
        with self._lock:
            err_msg = str(exc_val) if exc_val and self._status == "error" else None
            if self._conn:
                self._conn.execute(
                    "UPDATE runs SET status=?, finished_at=?, error=? WHERE id=?",
                    (self._status, time.time(), err_msg, self.id),
                )
                self._conn.commit()
        _tl.run = None
        _tl.step_id = None
        conn, self._conn = self._conn, None
        if conn:
            conn.close()
        _prune(self.bot, self._max_runs)
        return False

    def step(self, name: str) -> _StepCtx:
        return _StepCtx(self, name)

    def set_summary(self, d: dict) -> None:
        with self._lock:
            if self._conn:
                self._conn.execute(
                    "UPDATE runs SET summary_json=? WHERE id=?",
                    (json.dumps(d), self.id),
                )
                self._conn.commit()

    def warn(self) -> None:
        with self._lock:
            if self._status == "running":
                self._status = "warn"

    def cancel(self) -> None:
        """Mark the run as cancelled (user-requested stop). Terminal status
        that __exit__ will not override."""
        with self._lock:
            self._status = "cancelled"

    def fail(self, err=None) -> None:
        with self._lock:
            self._status = "error"
            if err and self._conn:
                self._conn.execute(
                    "UPDATE runs SET error=? WHERE id=?",
                    (str(err), self.id),
                )
                self._conn.commit()

    def _db_log(self, step_id: str | None, level: str, message: str) -> None:
        """Write one log record; lock-safe, trims if over cap."""
        with self._lock:
            if self._conn is None:
                return
            self._conn.execute(
                "INSERT INTO logs (run_id, step_id, ts, level, message)"
                " VALUES (?, ?, ?, ?, ?)",
                (self.id, step_id, time.time(), level, message),
            )
            self._log_count += 1
            if self._log_count > self._max_log_lines:
                # Drop the oldest line to stay within cap.
                self._conn.execute(
                    "DELETE FROM logs WHERE id = ("
                    "  SELECT id FROM logs WHERE run_id=? ORDER BY id ASC LIMIT 1"
                    ")",
                    (self.id,),
                )
                self._log_count = self._max_log_lines
            self._conn.commit()

    def log(self, level: str, message: str) -> None:
        """Directly insert a log line, independent of the Python logging module."""
        self._db_log(getattr(_tl, "step_id", None), level.upper(), message)

    def add_artifact(
        self,
        name: str,
        data: bytes,
        kind: str,
        step: _StepCtx | None = None,
    ) -> str:
        """Persist bytes to disk and record an artifacts row. Returns artifact id."""
        art_id = str(uuid.uuid4())
        step_id = step.id if step else None
        art_dir = ARTIFACTS_DIR / self.bot / self.id
        art_dir.mkdir(parents=True, exist_ok=True)
        ext = kind.lower()
        fpath = art_dir / f"{art_id}.{ext}"
        fpath.write_bytes(data)
        ct = _CONTENT_TYPES.get(ext, "application/octet-stream")
        with self._lock:
            if self._conn:
                self._conn.execute(
                    "INSERT INTO artifacts"
                    " (id, run_id, step_id, name, kind, path, size_bytes, content_type, created_at)"
                    " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    (art_id, self.id, step_id, name, kind,
                     str(fpath), len(data), ct, time.time()),
                )
                self._conn.commit()
        return art_id


# ── Settings helpers ──────────────────────────────────────────────────────────

def get_settings(bot: str) -> dict:
    conn = _open_db()
    try:
        row = conn.execute(
            "SELECT max_runs, max_log_lines, retention_days FROM settings WHERE bot=?",
            (bot,),
        ).fetchone()
        return dict(row) if row else dict(_SETTING_DEFAULTS)
    finally:
        conn.close()


def set_settings(bot: str, **kwargs) -> dict:
    cur = get_settings(bot)
    for k in ("max_runs", "max_log_lines", "retention_days"):
        if k in kwargs:
            cur[k] = kwargs[k]  # None is a valid value for retention_days
    conn = _open_db()
    try:
        conn.execute(
            "INSERT INTO settings (bot, max_runs, max_log_lines, retention_days)"
            " VALUES (?, ?, ?, ?)"
            " ON CONFLICT(bot) DO UPDATE SET"
            "   max_runs=excluded.max_runs,"
            "   max_log_lines=excluded.max_log_lines,"
            "   retention_days=excluded.retention_days",
            (bot, cur["max_runs"], cur["max_log_lines"], cur.get("retention_days")),
        )
        conn.commit()
    finally:
        conn.close()
    return cur


# ── Query helpers (used by REST routes in app.py) ────────────────────────────

def list_runs(
    bot: str,
    limit: int = 50,
    status: str | None = None,
    q: str | None = None,
) -> list[dict]:
    conn = _open_db()
    try:
        sql = (
            "SELECT id, bot, trigger, target_date, started_at, finished_at,"
            " status, summary_json, error"
            " FROM runs WHERE bot=?"
        )
        params: list = [bot]
        if status:
            sql += " AND status=?"
            params.append(status)
        if q:
            sql += " AND (summary_json LIKE ? OR error LIKE ? OR trigger LIKE ?)"
            params.extend([f"%{q}%", f"%{q}%", f"%{q}%"])
        sql += " ORDER BY started_at DESC LIMIT ?"
        params.append(min(limit, 500))
        return [dict(r) for r in conn.execute(sql, params).fetchall()]
    finally:
        conn.close()


def get_run(run_id: str) -> dict | None:
    conn = _open_db()
    try:
        row = conn.execute("SELECT * FROM runs WHERE id=?", (run_id,)).fetchone()
        if not row:
            return None
        result = dict(row)
        result["steps"] = [
            dict(s) for s in conn.execute(
                "SELECT * FROM steps WHERE run_id=? ORDER BY seq", (run_id,)
            ).fetchall()
        ]
        result["artifacts"] = [
            dict(a) for a in conn.execute(
                "SELECT id, run_id, step_id, name, kind, size_bytes, content_type, created_at"
                " FROM artifacts WHERE run_id=? ORDER BY created_at",
                (run_id,),
            ).fetchall()
        ]
        # Include most-recent 500 logs inline; /logs endpoint supports filtered/full access.
        logs = conn.execute(
            "SELECT id, run_id, step_id, ts, level, message"
            " FROM logs WHERE run_id=? ORDER BY id DESC LIMIT 500",
            (run_id,),
        ).fetchall()
        result["logs"] = [dict(l) for l in reversed(logs)]
        return result
    finally:
        conn.close()


def get_run_logs(
    run_id: str,
    level: str | None = None,
    step_id: str | None = None,
    q: str | None = None,
    limit: int = 2000,
) -> list[dict]:
    conn = _open_db()
    try:
        sql = (
            "SELECT id, run_id, step_id, ts, level, message"
            " FROM logs WHERE run_id=?"
        )
        params: list = [run_id]
        if level:
            sql += " AND level=?"
            params.append(level.upper())
        if step_id:
            sql += " AND step_id=?"
            params.append(step_id)
        if q:
            sql += " AND message LIKE ?"
            params.append(f"%{q}%")
        sql += " ORDER BY id ASC LIMIT ?"
        params.append(min(limit, 5000))
        return [dict(r) for r in conn.execute(sql, params).fetchall()]
    finally:
        conn.close()


def force_cancel(run_id: str) -> bool:
    """Directly mark a still-running run 'cancelled' in the DB (terminal).

    Unlike cooperative cancellation (which needs the worker thread to observe a
    flag), this updates the row unconditionally so the UI reflects the stop even
    when the worker is gone — e.g. a run orphaned by a process restart, or one
    wedged inside a blocking step. Returns True if a running row was updated.
    """
    conn = _open_db()
    try:
        now = time.time()
        cur = conn.execute(
            "UPDATE runs SET status='cancelled', finished_at=?,"
            " error=COALESCE(error,'Stopped by user') WHERE id=? AND status='running'",
            (now, run_id),
        )
        conn.execute(
            "UPDATE steps SET status='error', finished_at=?"
            " WHERE run_id=? AND status='running'",
            (now, run_id),
        )
        conn.commit()
        return cur.rowcount > 0
    finally:
        conn.close()


def sweep_running(bot: str, note: str = "Interrupted by restart") -> int:
    """Mark every still-'running' run for a bot as 'cancelled'.

    A run only exists while its worker thread is alive; no thread survives a
    process restart, so any row left 'running' at startup is an orphan/zombie
    that would otherwise spin forever in the UI. Returns the count swept.
    """
    conn = _open_db()
    try:
        now = time.time()
        rows = conn.execute(
            "SELECT id FROM runs WHERE bot=? AND status='running'", (bot,)
        ).fetchall()
        for r in rows:
            conn.execute(
                "UPDATE runs SET status='cancelled', finished_at=?,"
                " error=COALESCE(error,?) WHERE id=?",
                (now, note, r["id"]),
            )
            conn.execute(
                "UPDATE steps SET status='error', finished_at=?"
                " WHERE run_id=? AND status='running'",
                (now, r["id"]),
            )
        conn.commit()
        return len(rows)
    finally:
        conn.close()


def get_artifact(artifact_id: str) -> dict | None:
    conn = _open_db()
    try:
        row = conn.execute(
            "SELECT * FROM artifacts WHERE id=?", (artifact_id,)
        ).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


# ── Pruning ───────────────────────────────────────────────────────────────────

def _delete_run(conn: sqlite3.Connection, run_id: str, bot: str) -> None:
    conn.execute("DELETE FROM logs      WHERE run_id=?", (run_id,))
    conn.execute("DELETE FROM steps     WHERE run_id=?", (run_id,))
    conn.execute("DELETE FROM artifacts WHERE run_id=?", (run_id,))
    conn.execute("DELETE FROM runs      WHERE id=?",     (run_id,))
    art_dir = ARTIFACTS_DIR / bot / run_id
    if art_dir.exists():
        shutil.rmtree(str(art_dir), ignore_errors=True)


def _prune(bot: str, max_runs: int | None = None) -> None:
    """Remove oldest runs beyond max_runs cap and expired runs beyond retention_days."""
    try:
        conn = _open_db()
        try:
            if max_runs is None:
                row = conn.execute(
                    "SELECT max_runs FROM settings WHERE bot=?", (bot,)
                ).fetchone()
                max_runs = row["max_runs"] if row else _SETTING_DEFAULTS["max_runs"]

            # Runs beyond count cap (oldest first)
            excess = conn.execute(
                "SELECT id FROM runs WHERE bot=?"
                " ORDER BY started_at DESC LIMIT -1 OFFSET ?",
                (bot, max_runs),
            ).fetchall()
            for r in excess:
                _delete_run(conn, r["id"], bot)

            # Runs beyond age cap
            ret_row = conn.execute(
                "SELECT retention_days FROM settings WHERE bot=?", (bot,)
            ).fetchone()
            if ret_row and ret_row["retention_days"]:
                cutoff = time.time() - ret_row["retention_days"] * 86400
                old = conn.execute(
                    "SELECT id FROM runs WHERE bot=? AND started_at < ?",
                    (bot, cutoff),
                ).fetchall()
                for r in old:
                    _delete_run(conn, r["id"], bot)

            conn.commit()
        finally:
            conn.close()
    except Exception:
        pass


def prune_all() -> None:
    """Prune every bot — called by the periodic sweep thread."""
    try:
        conn = _open_db()
        try:
            bots = [r[0] for r in conn.execute("SELECT DISTINCT bot FROM runs").fetchall()]
        finally:
            conn.close()
        for bot in bots:
            _prune(bot)
    except Exception:
        pass


def start_periodic_prune(interval_seconds: int = 3600) -> threading.Thread:
    """Start a daemon thread that calls prune_all() on the given interval."""
    def _loop() -> None:
        while True:
            time.sleep(interval_seconds)
            prune_all()

    t = threading.Thread(target=_loop, daemon=True, name="diag-prune")
    t.start()
    return t