"""01_extensions_inventory.py —— 第 18 章配套代码 #1

用途
    列出当前 PG 实例上：
        ① 已经 CREATE EXTENSION 启用的扩展（pg_extension）
        ② 安装在文件系统、可以 CREATE EXTENSION 启用的扩展（pg_available_extensions）
        ③ 这些扩展中各自的版本与简介
    生产排查时第一步：先确认有哪些扩展可用，再决定方案。

运行
    python 01_extensions_inventory.py
"""

from __future__ import annotations

import sys

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def banner(t: str) -> None:
    print()
    print("=" * 80)
    print(f" {t}")
    print("=" * 80)


def fmt_table(headers, rows, widths=None):
    if not rows:
        print("  (空)")
        return
    if widths is None:
        widths = [max(len(str(h)), max((len(str(r[i])) for r in rows), default=0))
                  for i, h in enumerate(headers)]
    print("  " + "  ".join(f"{h:<{widths[i]}}" for i, h in enumerate(headers)))
    print("  " + "  ".join("-" * w for w in widths))
    for r in rows:
        print("  " + "  ".join(f"{str(r[i]):<{widths[i]}}" for i in range(len(headers))))


# 一些常用扩展的「人话简介」
HUMAN_DESC = {
    "pg_stat_statements":  "聚合 SQL 执行统计（必装）",
    "pgcrypto":            "对称/非对称加密、UUID、HMAC",
    "pageinspect":         "查看页/元组的二进制结构",
    "pgstattuple":         "精确测量表/索引膨胀",
    "pg_trgm":             "三元组相似度，LIKE %x% 提速",
    "btree_gin":           "让 GIN 也支持普通类型",
    "btree_gist":          "让 GiST 也支持普通类型",
    "hstore":              "key-value 类型（JSONB 之前的方案）",
    "tablefunc":           "crosstab 交叉表 / normal_rand",
    "unaccent":            "去除重音字符",
    "intarray":            "整数数组高级操作符",
    "dblink":              "执行远程 PG 上的 SQL",
    "postgres_fdw":        "把远程 PG 表当本地表查",
    "file_fdw":            "把 CSV 当外部表查",
    "postgis":             "地理信息系统（重磅）",
    "vector":              "向量类型 + HNSW/IVFFlat 索引（pgvector）",
    "timescaledb":         "时序数据库扩展（hypertable + 压缩）",
    "pg_cron":             "在 PG 内跑 cron 定时任务",
    "pg_partman":          "自动管理时序分区",
    "citus":               "PG 的水平分布式扩展",
    "plpython3u":          "在 PG 中运行 Python 函数",
    "plv8":                "在 PG 中运行 JavaScript 函数",
    "age":                 "图数据库（OpenCypher 语法）",
    "auto_explain":        "慢查询自动 EXPLAIN",
    "wal2json":            "把 WAL 解码为 JSON（CDC）",
    "pg_buffercache":      "查看 shared_buffers 缓存了哪些页",
    "pgrouting":           "PostGIS 之上的路网寻路",
}


def main() -> None:
    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(f"连接失败：{exc}")

    with conn, conn.cursor() as cur:
        banner("① 已启用扩展（pg_extension）")
        cur.execute("""
            SELECT extname,
                   extversion,
                   nspname AS schema
            FROM pg_extension e
            JOIN pg_namespace n ON n.oid = e.extnamespace
            ORDER BY extname
        """)
        rows = [(name, ver, sch, HUMAN_DESC.get(name, ""))
                for name, ver, sch in cur.fetchall()]
        fmt_table(["extname", "version", "schema", "中文说明"], rows)

        banner("② 已安装可用 / 但未启用的扩展（pg_available_extensions）")
        cur.execute("""
            SELECT name, default_version, installed_version, comment
            FROM pg_available_extensions
            WHERE installed_version IS NULL
            ORDER BY name
        """)
        rows = cur.fetchall()
        if rows:
            fmt_table(
                ["name", "default_ver", "installed_ver", "official comment"],
                [(n, dv, iv or "-", (c or "")[:60]) for n, dv, iv, c in rows],
            )
            print(f"\n  共 {len(rows)} 个扩展可用，启用方法：CREATE EXTENSION 名字;")
        else:
            print("  (无未启用扩展)")

        banner("③ 全教程关注的「明星扩展」是否就绪？")
        STARS = ["pg_stat_statements", "pg_trgm", "pgcrypto", "btree_gin",
                 "postgis", "vector", "timescaledb", "pg_cron",
                 "pg_partman", "citus", "auto_explain", "wal2json"]
        cur.execute("SELECT name FROM pg_available_extensions")
        avail = {r[0] for r in cur.fetchall()}
        cur.execute("SELECT extname FROM pg_extension")
        installed = {r[0] for r in cur.fetchall()}

        rows = []
        for ext in STARS:
            if ext in installed:
                status, hint = "✅ 已启用", "可直接使用"
            elif ext in avail:
                status, hint = "🟡 可启用", f"CREATE EXTENSION {ext};"
            else:
                status, hint = "❌ 未安装", "需在 OS 层装包：见对应章节"
            rows.append((ext, status, HUMAN_DESC.get(ext, ""), hint))
        fmt_table(["extension", "状态", "用途", "下一步"], rows)


if __name__ == "__main__":
    main()