import re

from sqlalchemy import text
from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session

_TOKEN_RE = re.compile(r"[A-Za-z0-9]+")

_CREATE_FTS = """
CREATE VIRTUAL TABLE IF NOT EXISTS faqs_fts USING fts5(
    question, answer, category,
    tenant_id UNINDEXED,
    faq_id UNINDEXED
)
"""


def init_fts(engine: Engine) -> None:
    with engine.begin() as conn:
        conn.execute(text(_CREATE_FTS))


def rebuild_fts(db: Session) -> None:
    """Repopulate the FTS index from the faqs table. Called after seeding."""
    db.execute(text("DELETE FROM faqs_fts"))
    db.execute(
        text(
            """
            INSERT INTO faqs_fts (question, answer, category, tenant_id, faq_id)
            SELECT question, answer, category, tenant_id, id FROM faqs WHERE status = 'published'
            """
        )
    )
    db.commit()


def _to_match_query(raw: str) -> str | None:
    tokens = _TOKEN_RE.findall(raw)
    if not tokens:
        return None
    return " OR ".join(f"{t}*" for t in tokens)


def search_faqs(db: Session, tenant_id: str, query: str, limit: int = 3) -> list[dict]:
    match = _to_match_query(query)
    if not match:
        return []
    rows = db.execute(
        text(
            """
            SELECT faq_id, question, answer, category
            FROM faqs_fts
            WHERE faqs_fts MATCH :match AND tenant_id = :tenant_id
            ORDER BY bm25(faqs_fts)
            LIMIT :limit
            """
        ),
        {"match": match, "tenant_id": tenant_id, "limit": limit},
    ).mappings().all()
    return [dict(r) for r in rows]