"""发布文章 + 标签管理

运行：python code/posts.py
"""
from __future__ import annotations

from db import fetch_one, fetch_all, execute, close_pool


def publish(tenant_id: int, author_id: int, title: str, body: str,
            tags: list[str]) -> int:
    """发布一篇文章，返回 post_id。
    触发器会自动根据 title/body 生成 search_vector。
    """
    row = fetch_one(
        "SELECT api_publish(%s, %s, %s, %s, %s) AS id",
        (tenant_id, author_id, title, body, tags),
    )
    return row["id"]


def list_recent(tenant_id: int, limit: int = 10) -> list[dict]:
    return fetch_all(
        """SELECT id, title, tags, created_at, view_count
           FROM   posts
           WHERE  tenant_id = %s AND status = 'published'
           ORDER  BY created_at DESC
           LIMIT  %s""",
        (tenant_id, limit),
    )


def filter_by_tag(tenant_id: int, tag: str) -> list[dict]:
    """JSONB GIN 索引：包含某标签的文章。"""
    # tags @> '["postgres"]' 会走 idx_posts_tags(jsonb_path_ops) GIN 索引
    return fetch_all(
        """SELECT id, title, tags
           FROM   posts
           WHERE  tenant_id = %s
             AND  tags @> %s::jsonb
           ORDER  BY created_at DESC""",
        (tenant_id, f'["{tag}"]'),
    )


def increment_view(post_id: int, created_at) -> int:
    """分区表的更新需要带分区键（created_at）才能裁剪。"""
    return execute(
        """UPDATE posts
           SET    view_count = view_count + 1
           WHERE  id = %s AND created_at = %s""",
        (post_id, created_at),
    )


def _demo() -> None:
    print("=== 发文 / 标签查询演示 ===")

    pid = publish(
        tenant_id=1,
        author_id=1,
        title="测试发文：PG 的全文检索真香",
        body="本文演示发文时触发器自动维护 search_vector 的能力，"
             "以及 JSONB 标签 GIN 索引的查询加速。",
        tags=["postgres", "fulltext", "demo"],
    )
    print(f"[OK] 新文章 id={pid}")

    print("\n-- 最新 5 篇 --")
    for r in list_recent(1, 5):
        print(f" #{r['id']:>3}  {r['title'][:40]}  tags={r['tags']}")

    print("\n-- tag='postgres' 的文章（JSONB GIN） --")
    rows = filter_by_tag(1, "postgres")
    print(f" 共 {len(rows)} 篇，前 3 篇：")
    for r in rows[:3]:
        print(f"   #{r['id']:>3}  {r['title'][:40]}")


if __name__ == "__main__":
    try:
        _demo()
    finally:
        close_pool()