"""
01_pgdata_explorer.py
---------------------
通过系统视图查看 PostgreSQL 数据目录结构、数据库 / 表的 OID、relfilenode、文件路径与大小。

依赖：
    pip install "psycopg[binary]>=3.1"

运行：
    python 01_pgdata_explorer.py
"""

import psycopg

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


def section(title: str) -> None:
    print("\n" + "=" * 70)
    print(f"  {title}")
    print("=" * 70)


def main() -> None:
    with psycopg.connect(CONN_STR) as conn, conn.cursor() as cur:
        # ---------- 1. 数据目录与版本信息 ----------
        section("1. 数据目录全局信息")
        cur.execute(
            """
            SELECT
              setting AS data_directory
            FROM pg_settings WHERE name = 'data_directory';
            """
        )
        print(f"PGDATA  = {cur.fetchone()[0]}")

        cur.execute("SHOW server_version;")
        print(f"version = {cur.fetchone()[0]}")

        cur.execute("SHOW block_size;")
        print(f"page    = {cur.fetchone()[0]} bytes")

        # ---------- 2. 当前数据库的 OID ----------
        section("2. 当前数据库 OID 与磁盘目录")
        cur.execute(
            """
            SELECT oid, datname,
                   pg_size_pretty(pg_database_size(oid)) AS size
            FROM pg_database
            WHERE datname = current_database();
            """
        )
        oid, name, size = cur.fetchone()
        print(f"db_name = {name}")
        print(f"db_oid  = {oid}   →   base/{oid}/  目录")
        print(f"db_size = {size}")

        # ---------- 3. 表空间（pg_tblspc/<oid> 软链接） ----------
        section("3. 表空间（Tablespace）列表")
        cur.execute(
            """
            SELECT spcname,
                   pg_tablespace_location(oid) AS location,
                   pg_size_pretty(pg_tablespace_size(oid)) AS size
            FROM pg_tablespace
            ORDER BY spcname;
            """
        )
        rows = cur.fetchall()
        print(f"{'name':<15} {'location':<40} size")
        print("-" * 70)
        for r in rows:
            loc = r[1] or "(默认 PGDATA/base)"
            print(f"{r[0]:<15} {loc:<40} {r[2]}")

        # ---------- 4. 用户表的 OID / relfilenode / 文件路径 ----------
        section("4. 用户表的 OID 与物理文件路径")
        cur.execute(
            """
            SELECT
              c.oid                              AS table_oid,
              c.relname,
              c.relfilenode,
              pg_relation_filepath(c.oid)        AS file_path,
              pg_size_pretty(pg_relation_size(c.oid))   AS heap_size,
              pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE n.nspname = 'public'
              AND c.relkind IN ('r', 'p')
            ORDER BY c.relname;
            """
        )
        rows = cur.fetchall()
        print(
            f"{'oid':>7} {'relname':<20} {'relfilenode':>12}  {'file_path':<25} "
            f"{'heap':>8}  {'total':>8}"
        )
        print("-" * 95)
        for r in rows:
            print(
                f"{r[0]:>7} {r[1]:<20} {r[2]:>12}  {r[3]:<25} {r[4]:>8}  {r[5]:>8}"
            )
        print("\n→ 注意 file_path 形如 base/<dbid>/<relfilenode>")
        print("→ heap = 主数据；total = 主数据 + 索引 + TOAST + 附属文件")

        # ---------- 5. relfilenode 会被哪些操作改变 ----------
        section("5. 实验：什么操作会让 relfilenode 变化？")
        cur.execute(
            "SELECT relfilenode FROM pg_class WHERE relname = 'ch9_demo_orders';"
        )
        before = cur.fetchone()[0]
        print(f"VACUUM FULL 之前 relfilenode = {before}")

        # VACUUM FULL 不能在事务里跑，开自动提交
        with psycopg.connect(CONN_STR, autocommit=True) as c2, c2.cursor() as cur2:
            cur2.execute("VACUUM FULL ch9_demo_orders;")
            cur2.execute(
                "SELECT relfilenode FROM pg_class WHERE relname = 'ch9_demo_orders';"
            )
            after = cur2.fetchone()[0]
        print(f"VACUUM FULL 之后 relfilenode = {after}")
        print(f"oid 不变，但物理文件被重写：{before} → {after}\n")

        # ---------- 6. 列出该表的所有附属文件 ----------
        section("6. 一张表附属的全部物理文件 (heap / fsm / vm / toast)")
        cur.execute(
            """
            SELECT
              c.relname AS object,
              CASE c.relkind
                WHEN 'r' THEN 'heap'
                WHEN 'i' THEN 'index'
                WHEN 't' THEN 'toast-heap'
                WHEN 'S' THEN 'sequence'
                ELSE c.relkind::text
              END AS kind,
              pg_relation_filepath(c.oid) AS path,
              pg_size_pretty(pg_relation_size(c.oid)) AS size
            FROM pg_class c
            WHERE c.oid IN (
                SELECT oid FROM pg_class WHERE relname = 'ch9_demo_orders'
                UNION ALL
                SELECT reltoastrelid FROM pg_class WHERE relname = 'ch9_demo_orders'
                UNION ALL
                SELECT indexrelid
                  FROM pg_index
                  WHERE indrelid = 'ch9_demo_orders'::regclass
            );
            """
        )
        for r in cur.fetchall():
            print(f"  {r[1]:<10} {r[0]:<35} {r[2] or '(无)':<25} {r[3]}")

        print(
            "\n提示：FSM / VM 文件 (_fsm / _vm) 不在 pg_class 里，"
            "它们是隐藏附属文件，文件名 = relfilenode + '_fsm' / '_vm'。"
        )


if __name__ == "__main__":
    main()