"""模糊检索：pg_trgm

场景：用户输入有拼写错误、或只记得一半关键词。
    similarity(a, b)  → [0,1] 的相似度
    a % b             → 相似度 >= pg_trgm.similarity_threshold (默认 0.3)
    a <-> b           → 距离 (1 - similarity)，越小越相似，可 ORDER BY

运行：python code/search_trgm.py "psotgres"   # 故意拼错
"""
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]:
    """用 title <-> %s 走 GIN(title gin_trgm_ops) 相似度排序。"""
    sql = """
        SELECT id, title,
               similarity(title, %(q)s) AS sim
        FROM   posts
        WHERE  tenant_id = %(t)s
          AND  status = 'published'
          AND  title %% %(q)s                -- % 是 pg_trgm 的相似操作符，psycopg 里要写 %%
        ORDER  BY title <-> %(q)s
        LIMIT  %(l)s
    """
    return fetch_all(sql, {"q": q, "t": tenant_id, "l": limit})


def explain(tenant_id: int, q: str) -> list[dict]:
    sql = """
        EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
        SELECT id, title
        FROM   posts
        WHERE  tenant_id = %s AND title %% %s
        ORDER  BY title <-> %s
        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("  （无结果，可调小 pg_trgm.similarity_threshold）")
    for r in rows:
        print(f" sim={r['sim']:.3f}  #{r['id']:>3}  {r['title']}")
    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 "psotgres"  # 故意拼错
    try:
        _demo(keyword)
    finally:
        close_pool()