#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""A 方案：找出「静默丢数据」bug 的全体受害者。

判据三重交叉（任一单独都不够）：
  静态 S —— 脚本里有手动 INSERT INTO gov_search，且没有 2026-09-22 的 pre-commit 修复标记
            （= 未修的危险模式）
  动态 D —— run_logs 近 14 天：运行次数 ≥5，new_count 全为 0（或 ≥90%），中位耗时 < 15s
  数据 X —— 库内该脚本 MAX(publish_date) 有明显停滞（与今日差 > 阈值）

输出按「证据强度」排序。
"""
import io
import os
import re
import sqlite3
import statistics

D = "/root/gov_crawler"
FIX_MARK = "2026-09-22: 先提交 gov_raw"
pat_gs = re.compile(r"INSERT\s+(?:OR\s+REPLACE\s+)?INTO\s+gov_search", re.I)

# ── 静态：未修的危险模式 ───────────────────────────────────────────────
danger = {}
for fn in sorted(os.listdir(D)):
    if not fn.endswith(".py"):
        continue
    try:
        src = io.open(os.path.join(D, fn), encoding="utf-8", errors="ignore").read()
    except Exception:
        continue
    if not pat_gs.search(src):
        continue
    if FIX_MARK in src:
        continue
    if fn in ("crawl_xiantao.py",):     # 已用另一种方式修好
        continue
    danger[fn] = True
print("静态「未修危险模式」脚本: %d 个" % len(danger))

db = sqlite3.connect("/root/search.db", timeout=180)
db.execute("PRAGMA busy_timeout=180000")
c = db.cursor()

# ── 动态：run_logs 特征 ────────────────────────────────────────────────
logs = {}
for fn in danger:
    rows = list(c.execute("""SELECT new_count, elapsed_seconds FROM run_logs
                             WHERE script_name=? AND run_date >= date('now','-14 days')""", (fn,)))
    if len(rows) < 5:
        continue
    news = [r[0] or 0 for r in rows]
    els = [r[1] or 0 for r in rows]
    zero = sum(1 for x in news if x == 0)
    logs[fn] = {
        "runs": len(rows),
        "zero_ratio": zero / len(rows),
        "med_elapsed": statistics.median(els),
        "max_new": max(news),
    }

# ── 数据：库内最新日期 ────────────────────────────────────────────────
print()
victims = []
today = c.execute("SELECT date('now','localtime')").fetchone()[0]
for fn, d in logs.items():
    row = c.execute("""SELECT COUNT(*), MAX(publish_date) FROM gov_raw WHERE script_name=?""", (fn,)).fetchone()
    n, mx = row[0], row[1]
    if not n:
        continue
    stale_days = None
    if mx and re.match(r"^\d{4}-\d{1,2}-\d{1,2}$", mx.strip()):
        y, m, dd = (int(x) for x in mx.split("-"))
        import datetime
        try:
            stale_days = (datetime.date.fromisoformat(today) - datetime.date(y, m, dd)).days
        except Exception:
            pass
    strong = (d["zero_ratio"] >= 0.9 and d["med_elapsed"] < 15)
    victims.append({
        "fn": fn, "n": n, "mx": mx, "stale": stale_days,
        "runs": d["runs"], "zero": d["zero_ratio"], "el": d["med_elapsed"],
        "max_new": d["max_new"], "strong": strong,
    })

victims.sort(key=lambda v: (-(v["strong"]), -(v["stale"] or 0), v["el"]))

print("=" * 100)
print("🎯 高危受害者（静态未修 + 近14天 ≥90% 运行 new=0 + 中位耗时<15s）：")
print("=" * 100)
hits = [v for v in victims if v["strong"]]
print("  共 %d 个\n" % len(hits))
print("  %-40s %6s %-11s %8s %7s %8s %8s" % ("脚本", "库内条", "库内最新", "停滞天", "运行", "零新增", "中位秒"))
for v in hits:
    print("  %-40s %6d %-11s %8s %6d %7.0f%% %8.1f" % (
        v["fn"], v["n"], str(v["mx"])[:10], str(v["stale"]), v["runs"], v["zero"] * 100, v["el"]))

print()
print("─" * 100)
print("次一级（有该模式但动态特征不完全吻合，需人工看）：%d 个" % (len(victims) - len(hits)))
for v in victims:
    if not v["strong"]:
        print("  %-40s %6d %-11s %8s %6d %7.0f%% %8.1f" % (
            v["fn"], v["n"], str(v["mx"])[:10], str(v["stale"]), v["runs"], v["zero"] * 100, v["el"]))
db.close()