"""04_pg_cron_demo.py —— 第 18 章配套代码 #4

用途
    演示 pg_cron：在 PG 内部调度定时任务，无需 OS 级 crontab。
    例子：
      · 每天凌晨 3 点清理 30 天前的日志表
      · 每 5 分钟把 raw_metrics 的数据聚合到 hourly_metrics
      · 每周日全量 VACUUM ANALYZE

前置条件
    pg_cron 必须装在专门的 cron schema 里，且 shared_preload_libraries 包含它：
        # postgresql.conf
        shared_preload_libraries = 'pg_cron'
        cron.database_name = 'learn_pg'      # 任务表所在库
    安装：
        Ubuntu/Debian:  apt install postgresql-XX-cron
        编译安装请参考  https://github.com/citusdata/pg_cron
    然后：
        psql -U postgres -c 'CREATE EXTENSION pg_cron;'
        重启 PG。

行为
    · 如果 pg_cron 已装：脚本注册 3 个示例任务，列出当前任务表
    · 如果未装：打印安装步骤后退出
"""

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"


JOBS = [
    {
        "name": "demo_clean_logs",
        "schedule": "0 3 * * *",
        "command": "DELETE FROM ch17_perf_orders WHERE created_at < now() - interval '180 days'",
        "desc": "每天凌晨 3 点清理 180 天前的旧订单",
    },
    {
        "name": "demo_refresh_mv",
        "schedule": "*/5 * * * *",
        "command": "ANALYZE ch17_perf_orders",
        "desc": "每 5 分钟刷新 ch17_perf_orders 统计信息",
    },
    {
        "name": "demo_weekly_vacuum",
        "schedule": "0 4 * * 0",
        "command": "VACUUM (ANALYZE, VERBOSE) ch17_perf_orders",
        "desc": "每周日凌晨 4 点 VACUUM ANALYZE",
    },
]


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


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:
        cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'pg_cron'")
        if cur.fetchone() is None:
            print(
                "❌ pg_cron 未启用。安装步骤：\n"
                "  1. apt install postgresql-XX-cron  （或源码编译）\n"
                "  2. 编辑 postgresql.conf：\n"
                "       shared_preload_libraries = 'pg_cron'\n"
                "       cron.database_name = 'learn_pg'\n"
                "  3. 重启 PG\n"
                "  4. psql -U postgres -d learn_pg -c 'CREATE EXTENSION pg_cron;'\n"
                "  5. 重新运行本脚本"
            )
            return

        banner("① 当前已注册任务（cron.job）")
        cur.execute("""
            SELECT jobid, jobname, schedule, command, active, username, database
            FROM cron.job
            ORDER BY jobid
        """)
        for r in cur.fetchall():
            print(f"  #{r[0]} {r[1]:<25} schedule={r[2]:<12} active={r[4]}  "
                  f"db={r[6]}  user={r[5]}\n      cmd={r[3]}")

        banner("② 注册三个示例任务")
        for job in JOBS:
            cur.execute(
                """
                SELECT cron.schedule_in_database(
                    %s,        -- jobname
                    %s,        -- schedule (5 字段 cron)
                    %s,        -- command
                    'learn_pg' -- database
                )
                """,
                (job["name"], job["schedule"], job["command"]),
            )
            jobid = cur.fetchone()[0]
            print(f"  ✅ #{jobid:<3} {job['name']:<25} schedule={job['schedule']:<12} "
                  f"  -- {job['desc']}")

        banner("③ 任务执行历史（cron.job_run_details，最近 10 条）")
        cur.execute("""
            SELECT jobid, status, start_time, end_time, return_message
            FROM cron.job_run_details
            ORDER BY start_time DESC
            LIMIT 10
        """)
        rows = cur.fetchall()
        if not rows:
            print("  (尚无执行记录，等待下一次调度时间到达)")
        else:
            for r in rows:
                print(f"  job={r[0]} status={r[1]} {r[2]} → {r[3]}  msg={r[4]}")

        banner("④ 卸载示例任务（防止污染）")
        for job in JOBS:
            cur.execute("SELECT cron.unschedule(%s)", (job["name"],))
            print(f"  🗑️  unschedule({job['name']!r})")

        print("\n💡 pg_cron 关键 API：")
        print("   · cron.schedule(name, sched, sql)       注册 / 替换任务（5 字段 cron）")
        print("   · cron.schedule_in_database(...)        指定目标库（推荐）")
        print("   · cron.unschedule(name)                  注销任务")
        print("   · cron.job / cron.job_run_details        任务定义 / 执行历史视图")


if __name__ == "__main__":
    main()