"""Knowledge Base Manager with SQLite backend and fuzzy matching.

Provides fast Q&A lookup for exam questions, auto-learning from AI responses,
and import/export capabilities.

Search strategy (two-stage):
  1. FTS5 full-text pre-filter narrows candidates to ≤100 rows (fast).
  2. SequenceMatcher selects the best match from candidates (accurate).
  Falls back to a LIMIT-500 linear scan when FTS5 is unavailable.
"""

from __future__ import annotations

import csv
import logging
import os
import re
import sqlite3
from difflib import SequenceMatcher

log = logging.getLogger(__name__)

# Regex to strip FTS5 operator characters before building MATCH queries
_FTS_STRIP = re.compile(r"[^\w\s\u3000-\u9fff\uF900-\uFAFF]")


class KnowledgeManager:
    """Knowledge Base Manager with SQLite backend."""

    DEFAULT_SIMILARITY_THRESHOLD = 0.85

    def __init__(self, db_path: str | None = None, similarity_threshold: float | None = None):
        if db_path is None:
            db_path = os.path.expanduser("~/.muravey-china/knowledge.db")
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        self.db_path = db_path
        self.SIMILARITY_THRESHOLD = (
            similarity_threshold
            if similarity_threshold is not None
            else self.DEFAULT_SIMILARITY_THRESHOLD
        )
        self._fts_enabled = False  # set in _init_db
        self._init_db()
        log.info(
            "Knowledge base initialized: %s (threshold=%.2f, fts5=%s)",
            db_path,
            self.SIMILARITY_THRESHOLD,
            self._fts_enabled,
        )

    def _init_db(self) -> None:
        with sqlite3.connect(self.db_path) as conn:
            conn.execute(
                """
                CREATE TABLE IF NOT EXISTS knowledge (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    question TEXT NOT NULL,
                    answer TEXT NOT NULL,
                    category TEXT DEFAULT 'general',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    source TEXT DEFAULT 'manual',
                    usage_count INTEGER DEFAULT 0
                )
            """,
            )
            conn.execute("CREATE INDEX IF NOT EXISTS idx_question ON knowledge(question)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_category ON knowledge(category)")

            # Attempt to create FTS5 virtual table for fast candidate pre-filtering.
            # FTS5 is available in SQLite ≥ 3.9.0 (released 2015); falls back gracefully.
            try:
                conn.execute(
                    "CREATE VIRTUAL TABLE IF NOT EXISTS knowledge_fts "
                    "USING fts5(question, tokenize='unicode61')"
                )
                # Populate FTS5 index from existing rows on first run / schema upgrade
                fts_count = conn.execute("SELECT COUNT(*) FROM knowledge_fts").fetchone()[0]
                if fts_count == 0:
                    conn.execute(
                        "INSERT INTO knowledge_fts(rowid, question) "
                        "SELECT id, question FROM knowledge"
                    )
                self._fts_enabled = True
            except sqlite3.OperationalError:
                self._fts_enabled = False
                log.debug("FTS5 unavailable — using linear fuzzy scan")

    def find_answer(self, question: str) -> dict | None:
        """Find answer with fuzzy matching. Returns dict or None."""
        q_normalized = question.lower().strip()

        result = None
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(
                "SELECT * FROM knowledge WHERE LOWER(question) = ?",
                (q_normalized,),
            )
            row = cursor.fetchone()
            if row:
                result = dict(row)

        if result:
            self._increment_usage(result["id"])
            log.info("KB exact match: %s", result["question"][:50])
            return result

        return self._fuzzy_search(question)

    def _fts_candidates(self, conn: sqlite3.Connection, question_lower: str) -> list[dict] | None:
        """Return FTS5 candidate rows or None if FTS5 is unavailable/empty-result.

        Strips FTS5 operator characters from the query and uses OR matching
        on up to 6 words for broad recall.
        """
        if not self._fts_enabled:
            return None
        safe = _FTS_STRIP.sub(" ", question_lower).strip()
        words = [w for w in safe.split() if len(w) > 1][:6]
        if not words:
            return None
        fts_query = " OR ".join(f'"{w}"' for w in words)
        try:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(
                "SELECT k.* FROM knowledge k "
                "JOIN knowledge_fts fts ON k.id = fts.rowid "
                "WHERE knowledge_fts MATCH ? LIMIT 100",
                (fts_query,),
            )
            rows = [dict(r) for r in cursor.fetchall()]
            return rows if rows else None
        except sqlite3.OperationalError:
            self._fts_enabled = False
            log.debug("FTS5 search failed — disabling and falling back")
            return None

    def _fuzzy_search(self, question: str) -> dict | None:
        best_match = None
        best_ratio = 0.0
        question_lower = question.lower()

        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row

            # Stage 1: FTS5 pre-filter (fast) — narrows candidates to ≤100 rows
            candidates = self._fts_candidates(conn, question_lower)

            # Stage 2: if FTS5 returned nothing or is unavailable, use linear scan
            if candidates is None:
                cursor = conn.execute(
                    "SELECT * FROM knowledge ORDER BY updated_at DESC LIMIT 500",
                )
                candidates = [dict(r) for r in cursor]

            # Stage 3: SequenceMatcher on candidates for accurate ranking
            for row in candidates:
                ratio = SequenceMatcher(None, question_lower, row["question"].lower()).ratio()
                if ratio > best_ratio and ratio >= self.SIMILARITY_THRESHOLD:
                    best_ratio = ratio
                    best_match = dict(row)
                    best_match["_similarity"] = ratio
                    if ratio > 0.95:
                        break

        if best_match:
            self._increment_usage(best_match["id"])
            log.info(
                "KB fuzzy match (%.2f): %s",
                best_match.get("_similarity", 0),
                best_match["question"][:50],
            )

        return best_match

    def add_qa(
        self,
        question: str,
        answer: str,
        category: str = "general",
        source: str = "manual",
    ) -> int:
        """Add new Q&A entry. Returns row ID."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.execute(
                "INSERT INTO knowledge (question, answer, category, source) VALUES (?, ?, ?, ?)",
                (question, answer, category, source),
            )
            row_id = cursor.lastrowid or 0
            if self._fts_enabled:
                try:
                    conn.execute(
                        "INSERT INTO knowledge_fts(rowid, question) VALUES (?, ?)",
                        (row_id, question),
                    )
                except sqlite3.OperationalError:
                    pass
            log.info("KB added: %s (category=%s)", question[:50], category)
            return row_id

    def update_qa(
        self, entry_id: int, question: str, answer: str, category: str = "general"
    ) -> bool:
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.execute(
                "UPDATE knowledge SET question = ?, answer = ?, category = ?,"
                " updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                (question, answer, category, entry_id),
            )
            updated = cursor.rowcount > 0
            if updated and self._fts_enabled:
                try:
                    conn.execute(
                        "UPDATE knowledge_fts SET question = ? WHERE rowid = ?",
                        (question, entry_id),
                    )
                except sqlite3.OperationalError:
                    pass
            return updated

    def delete_qa(self, entry_id: int) -> bool:
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.execute("DELETE FROM knowledge WHERE id = ?", (entry_id,))
            deleted = cursor.rowcount > 0
            if deleted and self._fts_enabled:
                try:
                    conn.execute("DELETE FROM knowledge_fts WHERE rowid = ?", (entry_id,))
                except sqlite3.OperationalError:
                    pass
            return deleted

    def import_csv(self, csv_path: str) -> int:
        """Import Q&A from CSV. Returns number of imported rows."""
        count = 0
        with open(csv_path, encoding="utf-8") as f:
            reader = csv.DictReader(f)
            for row in reader:
                question = row.get("question", "").strip()
                answer = row.get("answer", "").strip()
                if not question or not answer:
                    continue
                self.add_qa(
                    question=question,
                    answer=answer,
                    category=row.get("category", "general"),
                    source="import",
                )
                count += 1
        log.info("KB imported %d entries from %s", count, csv_path)
        return count

    def export_csv(self, csv_path: str) -> None:
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(
                "SELECT id, question, answer, category, source, usage_count"
                " FROM knowledge ORDER BY category, question",
            )
            with open(csv_path, "w", encoding="utf-8", newline="") as f:
                writer = csv.DictWriter(
                    f,
                    fieldnames=["id", "question", "answer", "category", "source", "usage_count"],
                )
                writer.writeheader()
                for row in cursor:
                    writer.writerow(dict(row))

    def get_stats(self) -> dict:
        with sqlite3.connect(self.db_path) as conn:
            total = conn.execute("SELECT COUNT(*) FROM knowledge").fetchone()[0]
            categories = conn.execute(
                "SELECT category, COUNT(*) as count FROM knowledge GROUP BY category",
            ).fetchall()
            top_used = conn.execute(
                "SELECT question, usage_count FROM knowledge ORDER BY usage_count DESC LIMIT 5",
            ).fetchall()
            return {
                "total_entries": total,
                "categories": dict(categories),
                "top_used": [(q, c) for q, c in top_used],
            }

    def list_all(self, limit: int = 100, offset: int = 0) -> list[dict]:
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(
                "SELECT * FROM knowledge ORDER BY updated_at DESC LIMIT ? OFFSET ?",
                (limit, offset),
            )
            return [dict(row) for row in cursor.fetchall()]

    def _increment_usage(self, entry_id: int) -> None:
        with sqlite3.connect(self.db_path, timeout=5.0) as conn:
            conn.execute(
                "UPDATE knowledge SET usage_count = usage_count + 1 WHERE id = ?",
                (entry_id,),
            )
