#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""qc_wholesite_delete.py —— 列出全库「整站 DELETE」脚本 + 判定能否安全去掉（只读）

判定：
  幂等插入（INSERT OR IGNORE / OR REPLACE 到 gov_raw）→ 可安全去掉 DELETE
  裸 INSERT INTO gov_raw                            → 去掉会撞 UNIQUE(page_url)，须先改成 OR REPLACE/IGNORE
  无 gov_raw 插入（走 crawler_lib / push_to_searchdb 等）→ 人工看
"""
import json
import os
import re
import sqlite3
from collections import Counter

D = "/root/gov_crawler/"
db = sqlite3.connect("file:/root/search.db?mode=ro", uri=True, timeout=30)
db.execute("PRAGMA busy_timeout=30000")

CFG = json.load(open(D + "daily_crawl_config.json", encoding="utf-8"))
crawls = CFG if isinstance(CFG, list) else CFG.get("tasks", CFG.get("scripts", []))
cfgmap = {}
for c in crawls:
    s = (c.get("script") or "").strip()
    if s:
        cfgmap.setdefault(s, []).append(c)
print("config 条目 %d / 脚本 %d" % (len(crawls), len(cfgmap)))

files = sorted(f for f in os.listdir(D)
               if f.startswith("crawl_") and f.endswith(".py")
               and "DELETE FROM gov_raw WHERE site_name" in open(D + f, encoding="utf-8", errors="ignore").read())

recs = []
for f in files:
    src = open(D + f, encoding="utf-8", errors="ignore").read()
    m = re.search(r'SITE_NAME\s*=\s*["\'](.+?)["\']', src)
    site = m.group(1) if m else ""
    if not site:
        m = re.search(r'["\']site_name["\']\s*[:=]\s*["\'](.+?)["\']', src)
        site = m.group(1) if m else "?"
    idem = bool(re.search(r"INSERT\s+OR\s+(REPLACE|IGNORE)\s+INTO\s+gov_raw", src, re.I))
    plain = bool(re.search(r"INSERT\s+INTO\s+gov_raw\s*\(", src, re.I))
    lib = bool(re.search(r"push_to_searchdb|crawler_lib", src))
    n_del = len(re.findall(r"DELETE\s+FROM\s+gov_raw\s+WHERE\s+site_name", src, re.I))
    n_del_s = len(re.findall(r"DELETE\s+FROM\s+gov_search", src, re.I))
    registered = f in cfgmap
    en = cfgmap[f][0].get("enabled", True) if registered else None
    args = cfgmap[f][0].get("args", "") if registered else ""
    rows = mx = 0
    if site and site != "?":
        rows = db.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (site,)).fetchone()[0]
        mx = db.execute("SELECT MAX(publish_date) FROM gov_raw WHERE site_name=?", (site,)).fetchone()[0] or ""
    if idem:
        verdict = "可安全去掉"
    elif plain:
        verdict = "须先改幂等插入"
    elif lib:
        verdict = "走 crawler_lib，人工看"
    else:
        verdict = "无 gov_raw 插入，人工看"
    recs.append(dict(script=f, 站点=site, 注册="是" if registered else "否",
                     启用=("" if en is None else ("是" if en else "否")), 参数=str(args)[:18],
                     行数=rows, 最新=str(mx)[:10], 幂等插入=idem, 裸INSERT=plain,
                     库=lib, 删gov_raw=n_del, 删gov_search=n_del_s, 判定=verdict))

recs.sort(key=lambda x: (x["判定"] != "可安全去掉", -x["行数"]))
print("\n=== 判定分布 ===")
for k, v in Counter(r["判定"] for r in recs).most_common():
    print("  %-22s %d" % (k, v))
print("  已注册日跑: %d / %d" % (sum(1 for r in recs if r["注册"] == "是"), len(recs)))

md = ["# 全库「整站 DELETE」脚本清单（只读体检 2026-09-25）", "",
      "共 **%d** 个脚本含 `DELETE FROM gov_raw WHERE site_name=?`（整站清空再重灌）。" % len(recs),
      "危险点：① 大事务 + 日跑并发 → `database is locked`；② 中途超时/异常 → **整站数据被清空**。",
      "判据：`page_url` 有 UNIQUE 索引 `idx_gov_raw_page_url` ⇒ 若脚本用 `INSERT OR IGNORE/REPLACE`，DELETE 本就多余。", "",
      "## 判定分布", ""]
for k, v in Counter(r["判定"] for r in recs).most_common():
    md.append("- %s：**%d** 个" % (k, v))
md += ["", "已注册日跑 %d / %d" % (sum(1 for r in recs if r["注册"] == "是"), len(recs)), "",
       "| 脚本 | 站点 | 注册 | 行数 | 最新 | 幂等 | 删gov_raw | 删gov_search | 判定 |", "|---|---|---|---|---|---|---|---|---|"]
for r in recs:
    md.append("| `%s` | %s | %s | %d | %s | %s | %d | %d | **%s** |" % (
        r["script"], r["站点"][:24], r["注册"], r["行数"], r["最新"],
        "✅" if r["幂等插入"] else ("裸" if r["裸INSERT"] else "—"),
        r["删gov_raw"], r["删gov_search"], r["判定"]))

day = "20260925"
open(D + "qc_out/qc_wholesite_delete_%s.md" % day, "w", encoding="utf-8").write("\n".join(md))
json.dump(recs, open(D + "qc_out/qc_wholesite_delete_%s.json" % day, "w", encoding="utf-8"),
          ensure_ascii=False, indent=1)
print("\n已写 qc_out/qc_wholesite_delete_%s.md/.json" % day)
print("\n=== 「可安全去掉」里行数最多的 12 个 ===")
for r in [x for x in recs if x["判定"] == "可安全去掉"][:12]:
    print("  %-34s %-22s %4d 行 最新%s 注册%s" % (r["script"][:34], r["站点"][:22], r["行数"], r["最新"], r["注册"]))
db.close()