"""AI response cache — SQLite-backed with TTL expiry.

Schema
------
Table: cache
  id          INTEGER PK AUTOINCREMENT
  query_hash  TEXT    SHA-256 of normalised query
  provider    TEXT    e.g. "deepseek"
  model       TEXT    e.g. "deepseek-chat"
  response    TEXT    full AI response text
  created_at  TEXT    ISO-8601 UTC timestamp

Unique constraint: (query_hash, provider, model)
"""

from __future__ import annotations

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 cache (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    query_hash  TEXT    NOT NULL,
    provider    TEXT    NOT NULL,
    model       TEXT    NOT NULL,
    response    TEXT    NOT NULL,
    created_at  TEXT    NOT NULL
)
"""

_CREATE_INDEX = """
CREATE UNIQUE INDEX IF NOT EXISTS idx_cache_key ON cache (query_hash, provider, model)
"""


class AICache:
    """Thread-safe SQLite-backed AI response cache with TTL expiry."""

    def __init__(self, db_path: str | Path, ttl_seconds: int = 86400) -> None:
        self._db_path = Path(db_path).expanduser()
        self._db_path.parent.mkdir(parents=True, exist_ok=True)
        self._ttl = ttl_seconds
        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("AICache: DB at %s (TTL=%ds)", self._db_path, ttl_seconds)

    def _create_schema(self) -> None:
        with self._conn:
            self._conn.execute(_CREATE_TABLE)
            self._conn.execute(_CREATE_INDEX)

    # ------------------------------------------------------------------ #
    # Read
    # ------------------------------------------------------------------ #

    def get(self, query_hash: str, provider: str, model: str) -> str | None:
        """Return cached response or None if not found / expired."""
        cutoff = (datetime.now(UTC) - timedelta(seconds=self._ttl)).isoformat()
        with self._lock:
            cur = self._conn.execute(
                """SELECT response FROM cache
                   WHERE query_hash = ? AND provider = ? AND model = ?
                     AND created_at >= ?""",
                (query_hash, provider, model, cutoff),
            )
            row = cur.fetchone()
        if row:
            log.debug("Cache hit: hash=%s provider=%s model=%s", query_hash[:8], provider, model)
            return str(row["response"])
        return None

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

    def set(self, query_hash: str, provider: str, model: str, response: str) -> None:
        """Insert or replace a cache entry."""
        ts = datetime.now(UTC).isoformat()
        with self._lock:
            with self._conn:
                self._conn.execute(
                    """INSERT INTO cache (query_hash, provider, model, response, created_at)
                       VALUES (?, ?, ?, ?, ?)
                       ON CONFLICT(query_hash, provider, model)
                       DO UPDATE SET response = excluded.response,
                                     created_at = excluded.created_at""",
                    (query_hash, provider, model, response, ts),
                )
        log.debug(
            "Cache set: hash=%s provider=%s model=%s len=%d",
            query_hash[:8],
            provider,
            model,
            len(response),
        )

    # ------------------------------------------------------------------ #
    # Invalidation
    # ------------------------------------------------------------------ #

    def invalidate(
        self,
        query_hash: str,
        provider: str | None = None,
        model: str | None = None,
    ) -> int:
        """Delete cache entries by query_hash, optionally filtered.

        Returns number of deleted rows.
        """
        clauses = ["query_hash = ?"]
        params: list[Any] = [query_hash]
        if provider is not None:
            clauses.append("provider = ?")
            params.append(provider)
        if model is not None:
            clauses.append("model = ?")
            params.append(model)
        where = " AND ".join(clauses)
        with self._lock:
            with self._conn:
                cur = self._conn.execute(f"DELETE FROM cache WHERE {where}", params)
        log.debug("Cache invalidate: hash=%s deleted=%d", query_hash[:8], cur.rowcount)
        return cur.rowcount

    def clear(self) -> int:
        """Delete all cache entries. Returns count."""
        with self._lock:
            with self._conn:
                cur = self._conn.execute("DELETE FROM cache")
        log.info("AICache: cleared %d entries", cur.rowcount)
        return cur.rowcount

    def clear_expired(self) -> int:
        """Delete entries older than TTL. Returns count."""
        cutoff = (datetime.now(UTC) - timedelta(seconds=self._ttl)).isoformat()
        with self._lock:
            with self._conn:
                cur = self._conn.execute("DELETE FROM cache WHERE created_at < ?", (cutoff,))
        log.debug("AICache: purged %d expired entries", cur.rowcount)
        return cur.rowcount

    # ------------------------------------------------------------------ #
    # Stats
    # ------------------------------------------------------------------ #

    def get_stats(self) -> dict[str, Any]:
        """Return {total_entries, expired_entries, size_bytes}."""
        cutoff = (datetime.now(UTC) - timedelta(seconds=self._ttl)).isoformat()
        with self._lock:
            total_row = self._conn.execute("SELECT COUNT(*) FROM cache").fetchone()
            expired_row = self._conn.execute(
                "SELECT COUNT(*) FROM cache WHERE created_at < ?", (cutoff,)
            ).fetchone()
        total = int(total_row[0] or 0)
        expired = int(expired_row[0] or 0)
        size = self._db_path.stat().st_size if self._db_path.exists() else 0
        return {
            "total_entries": total,
            "expired_entries": expired,
            "active_entries": total - expired,
            "size_bytes": size,
        }

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

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

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