"""全文检索：tsvector + ts_rank + GIN

核心：
    to_tsquery / plainto_tsquery / websearch_to_tsquery 把用户输入转成查询
    search_vector @@ query  是否命中
    ts_rank(search_vector, query)  相关度排序

运行：python code/search_fulltext.py "postgres 索引"
"""
from __future__ import annotations

import sys

from db import fetch_all, close_pool


def search(tenant_id: int, q: str, limit: int = 10) -> list[dict]:
    """走 GIN(search_vector) 全文检索，按 ts_rank 降序。

    websearch_to_tsquery 会自动处理用户输入中的空格、引号、减号，
    比 plainto_tsquery 更贴近 Google 风格搜索。
    """
    sql = """
        SELECT
            id, title,
            ts_rank(search_vector,
                    websearch_to_tsquery('simple', %(q)s)) AS rank,
            ts_headline('simple', body,
                        websearch_to_tsquery('simple', %(q)s),
                        'MaxWords=30, MinWords=10, StartSel=<<, StopSel=>>')
                AS snippet
        FROM    posts
        WHERE   tenant_id = %(t)s
          AND   status = 'published'
          AND   search_vector @@ websearch_to_tsquery('simple', %(q)s)
        ORDER   BY rank DESC
        LIMIT   %(l)s
    """
    return fetch_all(sql, {"q": q, "t": tenant_id, "l": limit})


def explain(tenant_id: int, q: str) -> list[dict]:
    """打印执行计划，用于教学演示 GIN 索引是否生效。"""
    sql = """
        EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
        SELECT id, title
        FROM   posts
        WHERE  tenant_id = %s
          AND  search_vector @@ websearch_to_tsquery('simple', %s)
        ORDER  BY ts_rank(search_vector,
                          websearch_to_tsquery('simple', %s)) DESC
        LIMIT  10
    """
    return fetch_all(sql, (tenant_id, q, q))


def _demo(q: str) -> None:
    print(f"=== 全文检索：{q!r} ===")
    rows = search(1, q, limit=5)
    if not rows:
        print("  （无结果）")
    for r in rows:
        print(f" rank={r['rank']:.4f}  #{r['id']:>3}  {r['title'][:40]}")
        print(f"   摘要：{r['snippet']}")
    print()

    print("-- EXPLAIN --")
    for r in explain(1, q):
        print(" ", r["QUERY PLAN"])


if __name__ == "__main__":
    keyword = sys.argv[1] if len(sys.argv) > 1 else "postgres MVCC"
    try:
        _demo(keyword)
    finally:
        close_pool()