"""02_recursive_cte.py —— 员工组织架构递归展开

演示：
  1) 从 CEO 递归向下展开，生成 (name, lvl, path)
  2) 从任意员工向上找所有上级
  3) 用 CYCLE（PG 14+）防死循环（若版本不支持则跳过）
"""
from __future__ import annotations

from _common import connect, print_table, section


def expand_down(cur) -> None:
    section("1. 从 CEO 向下展开组织架构")
    cur.execute(
        """
        WITH RECURSIVE org AS (
          SELECT id, name, manager_id, 1 AS lvl,
                 name::TEXT AS path
          FROM ch5_employees
          WHERE manager_id IS NULL
          UNION ALL
          SELECT e.id, e.name, e.manager_id, o.lvl + 1,
                 o.path || ' > ' || e.name
          FROM ch5_employees e
          JOIN org o ON e.manager_id = o.id
        )
        SELECT lpad('', (lvl-1)*2, ' ') || name AS tree, lvl, path
        FROM org
        ORDER BY path
        """
    )
    print_table(cur.fetchall(), ["tree", "lvl", "path"])


def find_up(cur, start_name: str = "Alice") -> None:
    section(f"2. 从 {start_name} 向上查找所有上级")
    cur.execute(
        """
        WITH RECURSIVE mgr AS (
          SELECT id, name, manager_id, 1 AS lvl
          FROM ch5_employees WHERE name = %s
          UNION ALL
          SELECT e.id, e.name, e.manager_id, m.lvl + 1
          FROM ch5_employees e
          JOIN mgr m ON m.manager_id = e.id
        )
        SELECT lvl, name FROM mgr ORDER BY lvl
        """,
        (start_name,),
    )
    print_table(cur.fetchall(), ["距离", "姓名"])


def demo_cycle(cur) -> None:
    """PG 14+ 支持 CYCLE 子句，用于防止环导致死循环。"""
    section("3. CYCLE 子句防死循环（PG 14+）")
    cur.execute("SHOW server_version_num")
    (ver,) = cur.fetchone()
    if int(ver) < 140000:
        print("  当前 PG 版本 < 14，跳过 CYCLE 子句演示。")
        return

    cur.execute(
        """
        WITH RECURSIVE org AS (
          SELECT id, name, manager_id, 1 AS lvl
          FROM ch5_employees WHERE manager_id IS NULL
          UNION ALL
          SELECT e.id, e.name, e.manager_id, o.lvl + 1
          FROM ch5_employees e JOIN org o ON e.manager_id = o.id
        )
        CYCLE id SET is_cycle USING path
        SELECT name, lvl, is_cycle, path FROM org ORDER BY path LIMIT 5
        """
    )
    print_table(cur.fetchall(), ["name", "lvl", "is_cycle", "path"])


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            expand_down(cur)
            find_up(cur, "Alice")
            demo_cycle(cur)


if __name__ == "__main__":
    main()