"""Statistics manager — records AI request metrics in SQLite.

Schema
------
Table: requests
  id             INTEGER PK AUTOINCREMENT
  timestamp      TEXT    ISO-8601 UTC
  provider       TEXT    e.g. "deepseek"
  model          TEXT    e.g. "deepseek-chat"
  input_tokens   INTEGER
  output_tokens  INTEGER
  duration_ms    INTEGER
  success        INTEGER 0/1
  cancelled      INTEGER 0/1
  cached         INTEGER 0/1
"""

from __future__ import annotations

import csv
import logging
import sqlite3
import threading
from datetime import datetime, timedelta, timezone

UTC = timezone.utc
from pathlib import Path
from typing import Any

log = logging.getLogger(__name__)

_CREATE_TABLE = """
CREATE TABLE IF NOT EXISTS requests (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp     TEXT    NOT NULL,
    provider      TEXT    NOT NULL,
    model         TEXT    NOT NULL,
    input_tokens  INTEGER NOT NULL DEFAULT 0,
    output_tokens INTEGER NOT NULL DEFAULT 0,
    duration_ms   INTEGER NOT NULL DEFAULT 0,
    success       INTEGER NOT NULL DEFAULT 1,
    cancelled     INTEGER NOT NULL DEFAULT 0,
    cached        INTEGER NOT NULL DEFAULT 0
)
"""

_CREATE_INDEX = "CREATE INDEX IF NOT EXISTS idx_ts ON requests (timestamp)"

_MIGRATE_CACHED_COLUMN = "ALTER TABLE requests ADD COLUMN cached INTEGER NOT NULL DEFAULT 0"

_PERIODS: dict[str, timedelta] = {
    "day": timedelta(days=1),
    "week": timedelta(days=7),
    "month": timedelta(days=30),
}


class StatsManager:
    """Thread-safe SQLite-backed statistics manager."""

    def __init__(self, db_path: str | Path) -> None:
        self._db_path = Path(db_path).expanduser()
        self._db_path.parent.mkdir(parents=True, exist_ok=True)
        self._lock = threading.Lock()
        self._conn = sqlite3.connect(
            str(self._db_path),
            check_same_thread=False,
        )
        self._conn.row_factory = sqlite3.Row
        self._create_schema()
        log.info("StatsManager: DB at %s", self._db_path)

    def _create_schema(self) -> None:
        with self._conn:
            self._conn.execute(_CREATE_TABLE)
            self._conn.execute(_CREATE_INDEX)
        # Migration: add `cached` column to existing databases that predate it
        try:
            with self._conn:
                self._conn.execute(_MIGRATE_CACHED_COLUMN)
            log.info("StatsManager: migrated — added 'cached' column")
        except sqlite3.OperationalError:
            # Column already exists — expected for new databases
            pass

    # ------------------------------------------------------------------ #
    # Write
    # ------------------------------------------------------------------ #

    def add_record(
        self,
        *,
        provider: str,
        model: str,
        input_tokens: int = 0,
        output_tokens: int = 0,
        duration_ms: int = 0,
        success: bool = True,
        cancelled: bool = False,
        cached: bool = False,
        timestamp: datetime | None = None,
    ) -> None:
        """Insert one request record."""
        ts = (timestamp or datetime.now(UTC)).isoformat()
        with self._lock:
            with self._conn:
                self._conn.execute(
                    """INSERT INTO requests
                       (timestamp, provider, model,
                        input_tokens, output_tokens, duration_ms,
                        success, cancelled, cached)
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                    (
                        ts,
                        provider,
                        model,
                        int(input_tokens),
                        int(output_tokens),
                        int(duration_ms),
                        int(success),
                        int(cancelled),
                        int(cached),
                    ),
                )
        log.debug(
            "Stats: %s/%s in=%d out=%d dur=%dms ok=%s cached=%s",
            provider,
            model,
            input_tokens,
            output_tokens,
            duration_ms,
            success,
            cached,
        )

    def clear_old_records(self, before_date: datetime) -> int:
        """Delete records older than *before_date*. Returns deleted count."""
        ts = before_date.isoformat()
        with self._lock:
            with self._conn:
                cur = self._conn.execute("DELETE FROM requests WHERE timestamp < ?", (ts,))
        log.info("StatsManager: deleted %d records before %s", cur.rowcount, ts)
        return cur.rowcount

    # ------------------------------------------------------------------ #
    # Read — aggregate
    # ------------------------------------------------------------------ #

    def get_stats(
        self,
        period: str = "day",
        start_date: datetime | None = None,
        end_date: datetime | None = None,
    ) -> dict[str, Any]:
        """Return aggregated stats dict for the given period.

        Keys: total_requests, success_requests, cancelled_requests,
              total_input_tokens, total_output_tokens, avg_duration_ms,
              period_start, period_end.
        """
        start, end = self._resolve_period(period, start_date, end_date)
        where, params = self._date_filter(start, end)
        with self._lock:
            cur = self._conn.execute(
                f"""SELECT
                      COUNT(*)               AS total,
                      SUM(success)           AS successes,
                      SUM(cancelled)         AS cancellations,
                      COALESCE(SUM(input_tokens),  0) AS in_tokens,
                      COALESCE(SUM(output_tokens), 0) AS out_tokens,
                      COALESCE(AVG(duration_ms),   0) AS avg_dur
                    FROM requests {where}""",
                params,
            )
            row = cur.fetchone()
        return {
            "total_requests": int(row["total"] or 0),
            "success_requests": int(row["successes"] or 0),
            "cancelled_requests": int(row["cancellations"] or 0),
            "total_input_tokens": int(row["in_tokens"] or 0),
            "total_output_tokens": int(row["out_tokens"] or 0),
            "avg_duration_ms": float(row["avg_dur"] or 0),
            "period_start": start,
            "period_end": end,
        }

    def get_by_provider(
        self,
        period: str = "day",
        start_date: datetime | None = None,
        end_date: datetime | None = None,
    ) -> list[dict[str, Any]]:
        """Return per-provider breakdown.

        Each item: {provider, total, in_tokens, out_tokens}.
        """
        start, end = self._resolve_period(period, start_date, end_date)
        where, params = self._date_filter(start, end)
        with self._lock:
            cur = self._conn.execute(
                f"""SELECT provider,
                           COUNT(*)                      AS total,
                           COALESCE(SUM(input_tokens),  0) AS in_tokens,
                           COALESCE(SUM(output_tokens), 0) AS out_tokens
                    FROM requests {where}
                    GROUP BY provider
                    ORDER BY total DESC""",
                params,
            )
            rows = cur.fetchall()
        return [dict(r) for r in rows]

    def get_daily_totals(self, days: int = 30) -> list[dict[str, Any]]:
        """Return [{date, requests, in_tokens, out_tokens}] for the last *days* days."""
        since = (datetime.now(UTC) - timedelta(days=days)).isoformat()
        with self._lock:
            cur = self._conn.execute(
                """SELECT substr(timestamp, 1, 10) AS date,
                          COUNT(*)                      AS requests,
                          COALESCE(SUM(input_tokens),  0) AS in_tokens,
                          COALESCE(SUM(output_tokens), 0) AS out_tokens
                   FROM requests
                   WHERE timestamp >= ?
                   GROUP BY date
                   ORDER BY date""",
                (since,),
            )
            rows = cur.fetchall()
        return [dict(r) for r in rows]

    def get_daily_success_duration(self, days: int = 30) -> list[dict[str, Any]]:
        """Return [{date, success_rate, avg_duration_ms}] for the last *days* days.

        success_rate — fraction 0.0-100.0 of successful (non-cancelled) requests.
        avg_duration_ms — average duration in milliseconds (0 if no requests).
        """
        since = (datetime.now(UTC) - timedelta(days=days)).isoformat()
        with self._lock:
            cur = self._conn.execute(
                """SELECT substr(timestamp, 1, 10)             AS date,
                          COUNT(*)                             AS total,
                          COALESCE(SUM(success), 0)           AS successes,
                          COALESCE(AVG(duration_ms), 0)       AS avg_dur
                   FROM requests
                   WHERE timestamp >= ?
                   GROUP BY date
                   ORDER BY date""",
                (since,),
            )
            rows = cur.fetchall()
        result = []
        for r in rows:
            total = int(r["total"] or 0)
            successes = int(r["successes"] or 0)
            success_rate = (successes / total * 100.0) if total > 0 else 0.0
            result.append(
                {
                    "date": r["date"],
                    "success_rate": success_rate,
                    "avg_duration_ms": float(r["avg_dur"] or 0),
                }
            )
        return result

    # ------------------------------------------------------------------ #
    # Read — detail
    # ------------------------------------------------------------------ #

    def get_detailed_records(
        self,
        limit: int = 100,
        offset: int = 0,
        period: str = "all",
        start_date: datetime | None = None,
        end_date: datetime | None = None,
    ) -> list[dict[str, Any]]:
        """Return paginated list of individual request records."""
        start, end = self._resolve_period(period, start_date, end_date)
        where, params = self._date_filter(start, end)
        params = list(params) + [limit, offset]
        with self._lock:
            cur = self._conn.execute(
                f"""SELECT id, timestamp, provider, model,
                           input_tokens, output_tokens, duration_ms,
                           success, cancelled
                    FROM requests {where}
                    ORDER BY timestamp DESC
                    LIMIT ? OFFSET ?""",
                params,
            )
            rows = cur.fetchall()
        return [dict(r) for r in rows]

    def count_records(
        self,
        period: str = "all",
        start_date: datetime | None = None,
        end_date: datetime | None = None,
    ) -> int:
        """Return total count of records for the given period."""
        start, end = self._resolve_period(period, start_date, end_date)
        where, params = self._date_filter(start, end)
        with self._lock:
            cur = self._conn.execute(f"SELECT COUNT(*) FROM requests {where}", params)
            return int(cur.fetchone()[0] or 0)

    def get_performance_stats(
        self,
        period: str = "day",
        start_date: datetime | None = None,
        end_date: datetime | None = None,
    ) -> dict[str, Any]:
        """Return latency percentiles and error rate for the given period.

        Keys: p50_ms, p95_ms, p99_ms, min_ms, max_ms, error_rate, total.
        Latency is computed only over successful (non-cancelled) requests.
        """
        start, end = self._resolve_period(period, start_date, end_date)
        where, params = self._date_filter(start, end)
        with self._lock:
            cur = self._conn.execute(
                f"SELECT duration_ms, success, cancelled FROM requests {where}",
                params,
            )
            rows = cur.fetchall()

        total = len(rows)
        if total == 0:
            return {
                "p50_ms": 0,
                "p95_ms": 0,
                "p99_ms": 0,
                "min_ms": 0,
                "max_ms": 0,
                "error_rate": 0.0,
                "total": 0,
            }

        durations = sorted(r["duration_ms"] for r in rows if r["success"] and not r["cancelled"])
        errors = sum(1 for r in rows if not r["success"])

        def _percentile(sorted_vals: list[int], pct: float) -> int:
            if not sorted_vals:
                return 0
            import math

            idx = min(len(sorted_vals) - 1, max(0, math.ceil(len(sorted_vals) * pct) - 1))
            return sorted_vals[idx]

        return {
            "p50_ms": _percentile(durations, 0.50),
            "p95_ms": _percentile(durations, 0.95),
            "p99_ms": _percentile(durations, 0.99),
            "min_ms": durations[0] if durations else 0,
            "max_ms": durations[-1] if durations else 0,
            "error_rate": errors / total,
            "total": total,
        }

    # ------------------------------------------------------------------ #
    # Export
    # ------------------------------------------------------------------ #

    def export_csv(
        self,
        path: str | Path,
        period: str = "all",
        start_date: datetime | None = None,
        end_date: datetime | None = None,
    ) -> int:
        """Write records to a CSV file. Returns the number of rows written."""
        records = self.get_detailed_records(
            limit=999_999,
            period=period,
            start_date=start_date,
            end_date=end_date,
        )
        path = Path(path)
        with open(path, "w", newline="", encoding="utf-8") as f:
            if not records:
                return 0
            writer = csv.DictWriter(f, fieldnames=list(records[0].keys()))
            writer.writeheader()
            writer.writerows(records)
        log.info("Exported %d records to %s", len(records), path)
        return len(records)

    # ------------------------------------------------------------------ #
    # Lifecycle
    # ------------------------------------------------------------------ #

    def close(self) -> None:
        """Close the database connection."""
        with self._lock:
            try:
                self._conn.close()
            except (OSError, RuntimeError):
                log.debug("Error closing stats DB", exc_info=True)

    def __del__(self) -> None:
        try:
            self.close()
        except (OSError, AttributeError, RuntimeError):
            pass  # __del__ cannot log safely

    # ------------------------------------------------------------------ #
    # Helpers
    # ------------------------------------------------------------------ #

    @staticmethod
    def _resolve_period(
        period: str,
        start_date: datetime | None,
        end_date: datetime | None,
    ) -> tuple[datetime | None, datetime | None]:
        if start_date is not None or end_date is not None:
            return start_date, end_date
        if period == "all":
            return None, None
        delta = _PERIODS.get(period, timedelta(days=1))
        return datetime.now(UTC) - delta, datetime.now(UTC)

    @staticmethod
    def _date_filter(
        start: datetime | None,
        end: datetime | None,
    ) -> tuple[str, list]:
        clauses: list[str] = []
        params: list = []
        if start:
            clauses.append("timestamp >= ?")
            params.append(start.isoformat())
        if end:
            clauses.append("timestamp <= ?")
            params.append(end.isoformat())
        where = ("WHERE " + " AND ".join(clauses)) if clauses else ""
        return where, params
