#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""qc_timeout_list.py —— 用 run_logs 证据出「日跑失败/超时」确定清单

判据（按技能既定口径：失败率 + 首末失败日 + 连续区间，不用单一阈值）：
  持续型 = 失败率 ≥30% 且 最近一次仍失败     → 仍在坏，优先
  反复型 = 失败率 ≥30% 但最近一次成功
  偶发型 = 失败率 <30%
  分类：超时 / 站点不可达 / 其他
"""
import datetime
import json
import re
import sqlite3
from collections import defaultdict

D = "/root/gov_crawler/"
OUT = D + "qc_out/"
TODAY = datetime.date.today()
db = sqlite3.connect("file:/root/search.db?mode=ro", uri=True, timeout=30)
db.execute("PRAGMA busy_timeout=30000")

rows = list(db.execute(
    "SELECT script_name, config_name, group_name, run_date, status, new_count, "
    "elapsed_seconds, error_detail FROM run_logs"))
print("run_logs %d 行" % len(rows))

agg = defaultdict(lambda: dict(runs=0, ok=0, to=0, na=0, other=0, maxel=0.0,
                               first_to="", last_to="", last_date="", last_status="",
                               cfg="", grp=""))
for sc, cfg, grp, rd, st, nc, el, err in rows:
    a = agg[sc]
    a["runs"] += 1
    a["cfg"] = cfg or a["cfg"]
    a["grp"] = grp or a["grp"]
    a["maxel"] = max(a["maxel"], float(el or 0))
    blob = "%s %s" % (st or "", (err or "")[:400])
    is_to = bool(re.search(r"timeout|超时|timed out", blob, re.I))
    is_na = bool(re.search(r"不可达|unreachable|resolve|refused|连接|SSL", blob, re.I))
    if st == "成功":
        a["ok"] += 1
    elif is_to:
        a["to"] += 1
    elif is_na:
        a["na"] += 1
    else:
        a["other"] += 1
    if is_to:
        if not a["first_to"] or rd < a["first_to"]:
            a["first_to"] = rd
        if rd > a["last_to"]:
            a["last_to"] = rd
    if rd >= a["last_date"]:
        a["last_date"] = rd
        a["last_status"] = st or ""

recs = []
for sc, a in agg.items():
    fails = a["to"] + a["na"] + a["other"]
    if not fails:
        continue
    rate = fails / float(a["runs"])
    kind = "超时" if a["to"] and a["to"] >= a["na"] else ("站点不可达" if a["na"] else "其他")
    cls = "持续型" if (rate >= 0.3 and a["last_status"] != "成功") else ("反复型" if rate >= 0.3 else "偶发型")
    recs.append(dict(script=sc, 分类=cls, 类型=kind, 运行=a["runs"], 成功=a["ok"], 失败=fails,
                     失败率="%.0f%%" % (100 * rate), 超时=a["to"], 不可达=a["na"], 其他=a["other"],
                     首超时=a["first_to"], 末超时=a["last_to"], 最长耗时=a["maxel"],
                     末次日期=a["last_date"], 末次状态=a["last_status"], 站点=a["cfg"], 组=a["grp"]))

recs.sort(key=lambda x: (-(x["分类"] == "持续型"), -x["超时"], -x["失败"]))
print("\n=== 汇总 ===")
from collections import Counter
print("  有失败记录的脚本: %d" % len(recs))
print("  分类:", Counter(r["分类"] for r in recs).most_common())
print("  类型:", Counter(r["类型"] for r in recs).most_common())
print("  其中「持续型」（失败率>=30%% 且末次仍失败）: %d <- 优先处理" % sum(1 for r in recs if r["分类"] == "持续型"))
print("  超时最高耗时分布:", Counter("≥1800s" if r["最长耗时"] >= 1800 else
                                 "600~1800s" if r["最长耗时"] >= 600 else "<600s" for r in recs).most_common())

md = ["# 爬虫 QC · 日跑失败/超时确定清单（证据口径）", "",
      "- 数据源：`run_logs` 表 %d 行 | 覆盖 %s ~ %s（约 2 个月）" % (
          len(rows), min(r[3] for r in rows), max(r[3] for r in rows)),
      "- 判据：失败率 + 首末失败日 + 末次状态（不用单一阈值）；类型分 超时 / 站点不可达 / 其他",
      "- ⚠️ 本清单取代 D3 静态判据（实测静态判据精确率仅 8%，且漏掉 217 个）", "",
      "## 汇总", "",
      "- 有失败记录的脚本：**%d** 个（其中「持续型」%d 个 ← 优先）" % (
          len(recs), sum(1 for r in recs if r["分类"] == "持续型")),
      "- 类型分布：" + " / ".join("%s %d" % (k, v) for k, v in Counter(r["类型"] for r in recs).most_common()),
      "- 分类分布：" + " / ".join("%s %d" % (k, v) for k, v in Counter(r["分类"] for r in recs).most_common()), "",
      "## 持续型（失败率≥30% 且最近一次仍失败）", ""]
for r in [x for x in recs if x["分类"] == "持续型"][:80]:
    md.append("- `%s` ｜ %s ｜ 运行%d 失败%d(%s) 超时%d ｜ 最长%.0fs ｜ 末次 %s %s ｜ %s" % (
        r["script"], r["类型"], r["运行"], r["失败"], r["失败率"], r["超时"],
        r["最长耗时"], r["末次日期"], r["末次状态"], (r["站点"] or "")[:24]))
md += ["", "## 反复型（失败率≥30% 但最近一次成功）", ""]
for r in [x for x in recs if x["分类"] == "反复型"][:60]:
    md.append("- `%s` ｜ %s ｜ 运行%d 失败%d(%s) 超时%d ｜ 最长%.0fs ｜ 末次 %s %s" % (
        r["script"], r["类型"], r["运行"], r["失败"], r["失败率"], r["超时"],
        r["最长耗时"], r["末次日期"], r["末次状态"]))
md += ["", "（偶发型 %d 个见 json）" % sum(1 for r in recs if r["分类"] == "偶发型")]

day = TODAY.strftime("%Y%m%d")
open(OUT + "qc_timeout_%s.md" % day, "w", encoding="utf-8").write("\n".join(md))
json.dump(recs, open(OUT + "qc_timeout_%s.json" % day, "w", encoding="utf-8"),
          ensure_ascii=False, indent=1)
print("\n  产出: qc_out/qc_timeout_%s.md / .json" % day)

print("\n=== 持续型 top20（优先处理）===")
for r in [x for x in recs if x["分类"] == "持续型"][:20]:
    print("  %-36s %-6s 运行%3d 失败%3d(%4s) 超时%3d 最长%6.0fs 末次 %s %s" % (
        r["script"][:36], r["类型"], r["运行"], r["失败"], r["失败率"], r["超时"],
        r["最长耗时"], r["末次日期"], r["末次状态"]))
db.close()