"""hello_pg.py —— 第 1 章配套代码

用途
    第一次连接本地 PostgreSQL，打印版本号、当前数据库、当前用户，
    以及若干关键服务端参数（shared_buffers / max_connections 等）。
    用于验证你本地的 PG 环境是否就绪，并初步认识 psycopg v3 的连接 API。

运行方法
    1. 确保本地已经有可用的 PostgreSQL（默认 host=127.0.0.1 port=5432）。
    2. 安装依赖：
           pip install "psycopg[binary]>=3.1"
    3. 创建演示库（首次运行时执行一次即可）：
           psql -h 127.0.0.1 -U postgres -c "CREATE DATABASE learn_pg;"
    4. 直接运行：
           python hello_pg.py

预期输出
    ============================================================
     Hello PostgreSQL!
    ============================================================
     server_version          : 17.0 (...)
     current_database        : learn_pg
     current_user            : postgres
     ...
"""

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"


KEY_PARAMS = [
    "server_version",
    "shared_buffers",
    "max_connections",
    "work_mem",
    "effective_cache_size",
    "wal_level",
    "max_wal_size",
    "default_transaction_isolation",
    "client_encoding",
    "server_encoding",
    "TimeZone",
]


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


def main() -> None:
    banner("Hello PostgreSQL!")

    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(
            "连接 PostgreSQL 失败：\n"
            f"  {exc}\n"
            "请检查：\n"
            "  · PG 服务是否已启动（默认端口 5432）；\n"
            "  · 数据库 learn_pg 是否已创建（CREATE DATABASE learn_pg;）；\n"
            "  · pg_hba.conf 是否允许 postgres 用户本地连接。"
        )

    with conn:
        with conn.cursor() as cur:
            cur.execute("SELECT version()")
            print(f" {'version':<24}: {cur.fetchone()[0]}")

            cur.execute("SELECT current_database(), current_user, now()")
            db, usr, now = cur.fetchone()
            print(f" {'current_database':<24}: {db}")
            print(f" {'current_user':<24}: {usr}")
            print(f" {'now()':<24}: {now}")

            print()
            banner("关键服务端参数（SHOW xxx）")
            for param in KEY_PARAMS:
                cur.execute(f"SHOW {param}")
                value = cur.fetchone()[0]
                print(f" {param:<32}: {value}")

            print()
            banner("活跃连接（pg_stat_activity）")
            cur.execute(
                """
                SELECT pid, usename, application_name, state, query
                FROM pg_stat_activity
                WHERE datname = current_database()
                ORDER BY pid
                """
            )
            for row in cur.fetchall():
                pid, usr, app, state, query = row
                query_short = (query or "").replace("\n", " ").strip()[:50]
                print(
                    f"  pid={pid:>6}  user={usr or '-':<10}  app={app or '-':<18}"
                    f"  state={state or '-':<10}  query={query_short!r}"
                )

            print()
            banner("已安装扩展（pg_extension）")
            cur.execute(
                "SELECT extname, extversion FROM pg_extension ORDER BY extname"
            )
            for name, ver in cur.fetchall():
                print(f"  {name:<24} v{ver}")

    print()
    print("✅ Done. 已成功连接到本地 PostgreSQL。")


if __name__ == "__main__":
    main()