"""
02_page_inspect.py
------------------
用 pageinspect 扩展像内窥镜一样查看 8KB 页的内部结构：
  · PageHeader：pd_lsn / pd_lower / pd_upper / pd_special
  · ItemId 数组：lp_off / lp_len / lp_flags
  · HeapTuple 头：t_xmin / t_xmax / t_ctid / t_infomask / t_hoff

要求：先在数据库里执行
    CREATE EXTENSION IF NOT EXISTS pageinspect;
"""

import psycopg

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


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


def render_page(lower: int, upper: int) -> str:
    """画一个简单的 8KB 页 ASCII 比例尺图。"""
    width = 60
    used_head = round(lower / 8192 * width)
    used_tail = round((8192 - upper) / 8192 * width)
    free = width - used_head - used_tail
    return (
        "[" + "#" * used_head + "." * free + "*" * used_tail + "]\n"
        f" |←头(24)+ItemId 共{lower}B|       Free Space {upper-lower}B       "
        f"|←Tuple 共{8192-upper}B|"
    )


def main() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        # 重建一张测试表，可控制元组数
        cur.execute("DROP TABLE IF EXISTS ch9_pi_demo;")
        cur.execute("CREATE TABLE ch9_pi_demo (id INT, name TEXT);")
        cur.execute(
            "INSERT INTO ch9_pi_demo "
            "SELECT g, repeat('x', 50) FROM generate_series(1, 8) g;"
        )

        # ---------- 1. PageHeader ----------
        banner("1. PageHeader (page_header)")
        cur.execute(
            """
            SELECT lsn, checksum, flags, lower, upper, special,
                   pagesize, version, prune_xid
            FROM page_header(get_raw_page('ch9_pi_demo', 0));
            """
        )
        cols = [d.name for d in cur.description]
        row = cur.fetchone()
        for c, v in zip(cols, row):
            print(f"  {c:<12} = {v}")

        lower, upper = row[3], row[4]
        print()
        print("  页布局示意图（8192 字节）：")
        print(render_page(lower, upper))

        # ---------- 2. ItemId 行指针 ----------
        banner("2. ItemId 行指针 (heap_page_items 部分列)")
        cur.execute(
            """
            SELECT lp, lp_off, lp_flags, lp_len
            FROM heap_page_items(get_raw_page('ch9_pi_demo', 0))
            ORDER BY lp;
            """
        )
        print(f"  {'lp':>3} {'lp_off':>6} {'lp_flags':>8} {'lp_len':>6}")
        for r in cur.fetchall():
            print(f"  {r[0]:>3} {r[1]:>6} {r[2]:>8} {r[3]:>6}")
        print(
            "\n  lp_flags 含义：1=NORMAL(有效) 2=REDIRECT 3=DEAD(VACUUM待回收)"
        )

        # ---------- 3. 元组头 ----------
        banner("3. HeapTuple 头部字段")
        cur.execute(
            """
            SELECT lp, t_xmin, t_xmax, t_field3 AS t_cid_or_xvac,
                   t_ctid, t_infomask, t_infomask2, t_hoff
            FROM heap_page_items(get_raw_page('ch9_pi_demo', 0))
            ORDER BY lp;
            """
        )
        print(
            f"  {'lp':>2} {'t_xmin':>8} {'t_xmax':>8} {'t_cid':>6} "
            f"{'t_ctid':>8} {'mask':>6} {'mask2':>6} {'t_hoff':>6}"
        )
        for r in cur.fetchall():
            print(
                f"  {r[0]:>2} {r[1]:>8} {r[2]:>8} {r[3]:>6} "
                f"{str(r[4]):>8} {r[5]:>6} {r[6]:>6} {r[7]:>6}"
            )

        # ---------- 4. 制造 UPDATE 看 ctid 链 ----------
        banner("4. 制造一次 UPDATE 看 t_ctid 形成版本链")
        cur.execute("UPDATE ch9_pi_demo SET name = 'updated' WHERE id = 1;")
        cur.execute(
            """
            SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask
            FROM heap_page_items(get_raw_page('ch9_pi_demo', 0))
            ORDER BY lp;
            """
        )
        print(f"  {'lp':>2} {'t_xmin':>8} {'t_xmax':>8} {'t_ctid':>10}  infomask")
        for r in cur.fetchall():
            print(f"  {r[0]:>2} {r[1]:>8} {r[2]:>8} {str(r[3]):>10}  {bin(r[4])}")
        print("\n  → id=1 的老版本 t_xmax 不再为 0，t_ctid 指向新版本（HOT）。")

        # ---------- 5. 表的总页数 ----------
        banner("5. 表的总页数")
        cur.execute(
            "SELECT relpages, pg_relation_size('ch9_pi_demo')/8192 AS real_pages "
            "FROM pg_class WHERE relname = 'ch9_pi_demo';"
        )
        relpages, real_pages = cur.fetchone()
        print(f"  pg_class.relpages = {relpages}")
        print(f"  实际页数          = {real_pages}")
        print("  (relpages 由 ANALYZE 更新，可能略有滞后)")


if __name__ == "__main__":
    main()