#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""site_coverage.py — 站点覆盖健康度（自校准）

为什么需要它
------------
旧的「当日是否成功」判据 = 脚本状态 + new_count。问题：
  ① 77% 的运行 new_count=0，其中 98% 被记为「成功」→ 几乎不产生信号
  ② new_count 把「源站无新数据 / 已入库去重 / 脚本哑火」混成一个数
  ③ 用统一阈值（如「超过 30 天没数据」）会误报：有的站本来就月更

本脚本的思路
------------
不问「今天抓到东西了吗」，而问「我们比这个站点自己的正常节奏落后了吗」：
  · 用该站点**自身历史发布节奏**算容忍阈值（自校准，无需人工配置）
  · 阈值 = max(7 天, 3 × 中位发布间隔)
  · 再叠加运行侧证据（近 14 天跑了没、有无失败/静默）

输出
----
  ① 控制台分级报告（停滞 / 观察 / 正常 / 已废弃）
  ② 快照表 site_coverage（按 run_date 累积，供「连续 K 天超阈值才报警」）

用法
----
  python3 site_coverage.py [--days=400] [--no-snapshot] [--top=25]
"""
import argparse
import collections
import datetime as dt
import os
import re
import sqlite3
import statistics
import sys

DB = os.getenv("SEARCH_DB", "/root/search.db")


def norm_date(s):
    """把 2026-9-9 / 2026-09-09 统一成 date 对象；失败返回 None。"""
    s = (s or "").strip()
    if not s:
        return None
    m = re.match(r"^(\d{4})[-/年](\d{1,2})[-/月](\d{1,2})", s)
    if not m:
        return None
    try:
        return dt.date(int(m.group(1)), int(m.group(2)), int(m.group(3)))
    except ValueError:
        return None


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--days", type=int, default=400, help="分析窗口（天）")
    ap.add_argument("--no-snapshot", action="store_true")
    ap.add_argument("--top", type=int, default=25)
    a = ap.parse_args()

    today = dt.date.today()
    since = today - dt.timedelta(days=a.days)
    print("=" * 100)
    print("站点覆盖健康度（自校准）  分析窗口 %s ~ %s" % (since, today))
    print("=" * 100)

    db = sqlite3.connect(DB, timeout=300)
    db.execute("PRAGMA busy_timeout=300000")
    c = db.cursor()

    # ---- ① 各脚本的发布日期分布（归一化后算节奏）----
    rows = c.execute("""SELECT script_name, publish_date, COUNT(*) FROM gov_raw
                        WHERE publish_date IS NOT NULL AND publish_date != ''
                          AND script_name IS NOT NULL AND script_name != ''
                        GROUP BY 1, 2""").fetchall()
    dates = collections.defaultdict(dict)     # script -> {date: n}
    for sc, pd_, n in rows:
        d = norm_date(pd_)
        if d is None:
            continue
        dates[sc][d] = dates[sc].get(d, 0) + n
    print("参与分析脚本 %d 个（有非空 script_name 且有可解析日期）" % len(dates))

    # ---- ② 运行侧证据（近 14 天）----
    d14 = (today - dt.timedelta(days=14)).isoformat()
    run = {}
    for sc, tot, fails, newsum in c.execute("""SELECT script_name, COUNT(*),
                   SUM(CASE WHEN status != '成功' THEN 1 ELSE 0 END),
                   COALESCE(SUM(new_count),0)
                   FROM run_logs WHERE run_date >= ? GROUP BY 1""", (d14,)):
        run[sc] = (tot, fails or 0, newsum or 0)
    flags = collections.Counter()
    try:
        for sc, fl, n in c.execute("""SELECT script_name, flag, COUNT(*) FROM run_logs
                                      WHERE run_date >= ? AND flag != '' GROUP BY 1,2""", (d14,)):
            flags[(sc, fl)] = n
    except sqlite3.OperationalError:
        pass          # 列还没建（首次部署前）

    # ---- ②b 最近 14 天入库的记录里有没有日期 ----
    # 关键：有脚本「一直在写但库内最新日期很旧」，真因是**写进来的记录 publish_date 为空**
    # （实证 crawl_yichun_jjxw.py）。这是「日期缺失」问题，不能冒充「站点停滞」。
    recent = {}
    for sc, tot, und in c.execute("""SELECT script_name, COUNT(*),
                   SUM(CASE WHEN publish_date IS NULL OR publish_date='' THEN 1 ELSE 0 END)
                   FROM gov_raw WHERE inserted_at >= ? AND script_name != '' GROUP BY 1""", (d14,)):
        recent[sc] = (tot, und or 0)

    # ---- ③ 逐个脚本判定 ----
    out = []
    for sc, dm in dates.items():
        ds = sorted(dm.keys())
        db_max = ds[-1]
        stale = (today - db_max).days
        # 只看窗口内的日期算节奏
        win = [d for d in ds if d >= since]
        if len(win) >= 3:
            gaps = [(win[i + 1] - win[i]).days for i in range(len(win) - 1)]
            gaps = [g for g in gaps if g > 0]
            typical = int(statistics.median(gaps)) if gaps else 30
        else:
            typical = 30
        threshold = max(7, 3 * typical)
        tot, fails, newsum = run.get(sc, (0, 0, 0))
        silent = flags.get((sc, "SILENT"), 0)
        noitems = flags.get((sc, "NOITEMS"), 0)
        tot14, und14 = recent.get(sc, (0, 0))
        und_ratio = (und14 / tot14) if tot14 else 0.0

        if tot14 > 0 and und_ratio >= 0.5:
            # 最近入库的记录过半没日期 → 是「日期缺失」，不是站点停滞
            verdict = "缺日期"
        elif tot == 0 and stale > 60:
            verdict = "已废弃"
        elif stale > 2 * threshold and newsum > 0:
            # 一直在写、库内最新日期却不前进 → 灌老数据/日期异常，需人工看
            verdict = "新增未推进"
        elif stale > 2 * threshold:
            verdict = "停滞"
        elif stale > threshold:
            verdict = "观察"
        else:
            verdict = "正常"
        out.append({
            "script": sc, "db_max": db_max.isoformat(), "stale": stale,
            "typical": typical, "threshold": threshold, "verdict": verdict,
            "runs14": tot, "fails14": fails, "new14": newsum,
            "silent14": silent, "noitems14": noitems,
            "records": sum(dm.values()),
            "ins14": tot14, "undated14": und14,
        })

    order = {"停滞": 0, "新增未推进": 1, "缺日期": 2, "观察": 3, "正常": 4, "已废弃": 5}
    out.sort(key=lambda x: (order[x["verdict"]], -x["stale"]))
    cnt = collections.Counter(x["verdict"] for x in out)

    print()
    print("分级结果: " + " | ".join("%s %d" % (k, cnt[k]) for k in
          ("停滞", "新增未推进", "缺日期", "观察", "正常", "已废弃")))

    for lvl in ("停滞", "新增未推进", "缺日期", "观察"):
        items = [x for x in out if x["verdict"] == lvl]
        if not items:
            continue
        print()
        print("=" * 100)
        print("【%s】%d 个（阈值按各站自身节奏自校准）" % (lvl, len(items)))
        print("=" * 100)
        print("  %-34s %-11s %5s %5s %5s %5s %6s %6s %6s" %
              ("脚本", "库内最新", "滞后", "节奏", "阈值", "近14入库", "无日期", "新增", "静默"))
        for x in items[:a.top]:
            print("  %-34s %-11s %5d %5d %5d %5d %6d %6d %6d" %
                  (x["script"][:34], x["db_max"], x["stale"], x["typical"],
                   x["threshold"], x["ins14"], x["undated14"], x["new14"], x["silent14"]))
        if len(items) > a.top:
            print("  ... 还有 %d 个" % (len(items) - a.top))

    # ---- ④ 快照落表（供「连续 K 天超阈值才报警」）----
    if not a.no_snapshot:
        c.execute("""CREATE TABLE IF NOT EXISTS site_coverage (
            run_date TEXT, script_name TEXT, db_max TEXT, stale_days INTEGER,
            typical_gap INTEGER, threshold INTEGER, verdict TEXT,
            runs_14d INTEGER, fails_14d INTEGER, new_14d INTEGER,
            silent_14d INTEGER, noitems_14d INTEGER, records INTEGER,
            ins_14d INTEGER, undated_14d INTEGER,
            PRIMARY KEY (run_date, script_name))""")
        # 兼容首次部署时已建的旧表结构
        have = {r[1] for r in c.execute("PRAGMA table_info(site_coverage)")}
        for col in ("ins_14d", "undated_14d"):
            if col not in have:
                c.execute("ALTER TABLE site_coverage ADD COLUMN %s INTEGER" % col)
        c.executemany("""INSERT OR REPLACE INTO site_coverage VALUES
            (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            [(today.isoformat(), x["script"], x["db_max"], x["stale"], x["typical"],
              x["threshold"], x["verdict"], x["runs14"], x["fails14"], x["new14"],
              x["silent14"], x["noitems14"], x["records"], x["ins14"], x["undated14"])
             for x in out])
        db.commit()
        print()
        print("快照已写入 site_coverage: %s 共 %d 行" % (today.isoformat(), len(out)))
    db.close()


if __name__ == "__main__":
    main()