"""物化视图刷新：mv_hot_posts

教学要点：
    1. REFRESH MATERIALIZED VIEW        → 会锁表，读也被阻塞
    2. REFRESH MATERIALIZED VIEW CONCURRENTLY
         → 不阻塞读，但要求视图上有 UNIQUE 索引（init.sql 已建）
    3. 生产上用 pg_cron 定时触发：
         SELECT cron.schedule('refresh_hot', '0 * * * *',
                              'REFRESH MATERIALIZED VIEW CONCURRENTLY blog.mv_hot_posts');

运行：python code/refresh_hot.py
"""
from __future__ import annotations

import time

from db import fetch_all, execute, close_pool


def refresh(concurrent: bool = True) -> float:
    """刷新物化视图，返回耗时秒。"""
    sql = ("REFRESH MATERIALIZED VIEW CONCURRENTLY blog.mv_hot_posts"
           if concurrent
           else "REFRESH MATERIALIZED VIEW blog.mv_hot_posts")
    t0 = time.time()
    execute(sql)
    return time.time() - t0


def top_n(tenant_id: int, n: int = 10) -> list[dict]:
    return fetch_all(
        """SELECT id, title, view_count, recent_likes, hot_score
           FROM   mv_hot_posts
           WHERE  tenant_id = %s
           ORDER  BY hot_score DESC
           LIMIT  %s""",
        (tenant_id, n),
    )


if __name__ == "__main__":
    try:
        cost = refresh(concurrent=True)
        print(f"[OK] 物化视图刷新完成，耗时 {cost:.3f}s")

        print("\n=== tenant=1 的热门 Top 10 ===")
        for r in top_n(1, 10):
            print(f" score={r['hot_score']:7.2f}  "
                  f"views={r['view_count']:>5}  "
                  f"likes={r['recent_likes']:>3}  "
                  f"#{r['id']:>3}  {r['title'][:40]}")
    finally:
        close_pool()