"""Chatbot Q&A system — DB-driven answers, Persian normalization, fuzzy matching."""
from __future__ import annotations

import json
import re
import time
from typing import Optional

from bot.db.core import _exec, _fetchone, _fetchall


# ───────── Q&A Management ─────────
async def add_qa(chat_id: int, question: str, answer: str, created_by: int = 0) -> bool:
    """Add a Q&A pair. Returns True if updated existing."""
    existing = await _fetchone(
        "SELECT 1 FROM chatbot_qa WHERE chat_id=? AND question=?",
        (chat_id, question.strip()),
    )
    if existing:
        await _exec(
            "UPDATE chatbot_qa SET answer=?, updated_at=? WHERE chat_id=? AND question=?",
            (answer.strip(), int(time.time()), chat_id, question.strip()),
        )
        return True
    await _exec(
        "INSERT INTO chatbot_qa (chat_id, question, answer, created_by) VALUES (?, ?, ?, ?)",
        (chat_id, question.strip(), answer.strip(), created_by),
    )
    return False


async def remove_qa(chat_id: int, question: str) -> int:
    cur = await _exec(
        "DELETE FROM chatbot_qa WHERE chat_id=? AND question=?",
        (chat_id, question.strip()),
    )
    return cur.rowcount if cur else 0


async def remove_all_qa(chat_id: int) -> int:
    cur = await _exec(
        "DELETE FROM chatbot_qa WHERE chat_id=?", (chat_id,)
    )
    return cur.rowcount if cur else 0


async def list_qa(chat_id: int) -> list[dict]:
    rows = await _fetchall(
        "SELECT id, question, answer, created_by, created_at FROM chatbot_qa WHERE chat_id=? ORDER BY id",
        (chat_id,),
    )
    return [dict(r) for r in rows]


async def find_qa(chat_id: int, text: str) -> Optional[str]:
    """Find matching answer using exact match, then contains, then fuzzy."""
    if not text or not text.strip():
        return None

    text_lower = text.strip().lower()

    # 1. Exact match
    row = await _fetchone(
        "SELECT answer FROM chatbot_qa WHERE chat_id=? AND LOWER(question)=?",
        (chat_id, text_lower),
    )
    if row:
        return row["answer"]

    # 2. Normalized Persian match
    normalized = _normalize_persian(text_lower)
    row = await _fetchone(
        "SELECT answer FROM chatbot_qa WHERE chat_id=? AND LOWER(question)=?",
        (chat_id, normalized),
    )
    if row:
        return row["answer"]

    # 3. Contains match
    row = await _fetchone(
        "SELECT answer FROM chatbot_qa WHERE chat_id=? AND LOWER(question) LIKE ?",
        (chat_id, f"%{normalized}%"),
    )
    if row:
        return row["answer"]

    # 4. Fuzzy match (Levenshtein-like)
    rows = await _fetchall(
        "SELECT question, answer FROM chatbot_qa WHERE chat_id=?",
        (chat_id,),
    )
    if not rows:
        return None

    best_score = 0.0
    best_answer = None
    for r in rows:
        q_normalized = _normalize_persian(r["question"].lower())
        score = _similarity(normalized, q_normalized)
        if score > best_score:
            best_score = score
            best_answer = r["answer"]

    if best_score >= 0.6:
        return best_answer
    return None


async def get_qa_count(chat_id: int) -> int:
    row = await _fetchone(
        "SELECT COUNT(*) AS c FROM chatbot_qa WHERE chat_id=?", (chat_id,)
    )
    return row["c"] if row else 0


def _normalize_persian(text: str) -> str:
    """Normalize Persian characters."""
    text = text.replace("ي", "ی").replace("ك", "ک").replace("ة", "ه")
    text = text.replace("أ", "ا").replace("ؤ", "و")
    return text.strip()


def _similarity(a: str, b: str) -> float:
    """Simple character-level similarity score (0-1)."""
    if not a or not b:
        return 0.0
    if a == b:
        return 1.0

    # Simple bigram similarity
    def _bigrams(s):
        return set(s[i:i+2] for i in range(len(s)-1))

    a_bigrams = _bigrams(a)
    b_bigrams = _bigrams(b)
    if not a_bigrams or not b_bigrams:
        return 0.0
    intersection = a_bigrams & b_bigrams
    union = a_bigrams | b_bigrams
    return len(intersection) / len(union) if union else 0.0


# ───────── Broadcast / Mass Messaging ─────────
async def broadcast_create(
    chat_id: int, target_type: str, send_type: str,
    caption: str = "", file_id: str = None,
    from_chat_id: int = None, message_id: int = None,
) -> int:
    """Create a broadcast job. Returns job ID."""
    target_for = "groups" if target_type == "gp" else "members"
    send_data = json.dumps({
        "for": target_type,
        "send": send_type,
        "chat": chat_id,
        "caption": caption,
        "file_id": file_id,
        "from_chat": from_chat_id,
        "msgid": message_id,
    })
    cur = await _exec(
        "INSERT INTO broadcast_queue (target_chat, target_type, send_type, caption, file_id, from_chat_id, source_msg_id, status, sent_count, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', 0, ?)",
        (chat_id, target_for, send_type, caption, file_id, from_chat_id, message_id, int(time.time())),
    )
    return cur.lastrowid if cur else 0


async def broadcast_get_pending() -> Optional[dict]:
    row = await _fetchone(
        "SELECT * FROM broadcast_queue WHERE status='pending' ORDER BY id LIMIT 1"
    )
    return dict(row) if row else None


async def broadcast_update_progress(job_id: int, sent: int, status: str = "running"):
    await _exec(
        "UPDATE broadcast_queue SET sent_count=?, status=? WHERE id=?",
        (sent, status, job_id),
    )


async def broadcast_complete(job_id: int):
    await _exec(
        "UPDATE broadcast_queue SET status='done', completed_at=? WHERE id=?",
        (int(time.time()), job_id),
    )


async def broadcast_delete(job_id: int):
    await _exec("DELETE FROM broadcast_queue WHERE id=?", (job_id,))


async def broadcast_list(chat_id: int) -> list[dict]:
    rows = await _fetchall(
        "SELECT id, target_type, send_type, status, sent_count, created_at FROM broadcast_queue WHERE target_chat=? ORDER BY id DESC",
        (chat_id,),
    )
    return [dict(r) for r in rows]