#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""A 类「销毁者」修复：给 `INSERT OR REPLACE INTO gov_raw` 补上 script_name。

为什么优先修这批
----------------
`INSERT OR REPLACE` 在 page_url 冲突时会**删除旧行再插入**，若 SQL 不含 script_name，
每次重跑都把归属清空（实证：济南市生态环境局 949 条天天被清空，写它的
crawl_jinan_epb_new.py / crawl_jinan_epb_yppz.py 就在 A 类名单里）。

修法（外科手术级）
------------------
只改 SQL 文本本身：列清单末尾加 `, script_name`，VALUES 末尾加一个字面量
`'crawl_xxx.py'`。**不动参数元组**，避免错位。

安全网
------
① 每个文件改前备份到 Archive/；
② 改后用 sqlite3 在临时库上 `EXPLAIN` 改后的 SQL —— 列数/值数不匹配会立刻报错；
③ ast 语法检查；
④ 支持 --limit 先试改几个。

用法: python3 fix_destroyers_scriptname.py [--limit=N] [--dry-run]
"""
import io
import os
import re
import shutil
import sqlite3
import sys
import ast

D = "/root/gov_crawler"
ARCH = os.path.join(D, "Archive")
DRY = "--dry-run" in sys.argv
LIMIT = None
for a in sys.argv[1:]:
    if a.startswith("--limit="):
        LIMIT = int(a.split("=")[1])

STMT = re.compile(
    r"INSERT\s+OR\s+REPLACE\s+INTO\s+gov_raw"
    r'(?:"\s*"|\s)*'
    r"\(([^)]*)\)"
    r'(?:"\s*"|\s)*'
    r"VALUES\s*\(([^)]*)\)",
    re.I | re.S)

# 临时库：用真实 gov_raw 的列定义建表，供 EXPLAIN 校验
TMP = "/tmp/_sqlcheck.db"
if os.path.exists(TMP):
    os.remove(TMP)
_t = sqlite3.connect(TMP)
_cols = sqlite3.connect("/root/search.db").execute("PRAGMA table_info(gov_raw)").fetchall()
_t.execute("CREATE TABLE gov_raw (%s)" % ", ".join(
    '"%s" %s' % (c[1], c[2] or "TEXT") for c in _cols))
_t.commit()


def check_sql(sql):
    """列/值数量不匹配会被 sqlite 直接报出来。

    注意：EXPLAIN 遇 `?` 占位符必须提供等量绑定值，否则会误报
    「Incorrect number of bindings supplied」——2026-09-21 因此误判了上百个文件。
    """
    s = re.sub(r"\{[^}]*\}", "?", sql)          # f-string / .format 占位
    n = s.count("?")
    try:
        _t.execute("EXPLAIN " + s, [None] * n)
        return None
    except Exception as e:
        return str(e)


files = [f for f in sorted(os.listdir(D)) if f.endswith(".py")]
changed = []
skipped = []
failed = []

for fn in files:
    p = os.path.join(D, fn)
    try:
        src = io.open(p, encoding="utf-8", errors="ignore").read()
    except Exception:
        continue
    if not re.search(r"INSERT\s+OR\s+REPLACE\s+INTO\s+gov_raw", src, re.I):
        continue

    # 逐个语句处理（从后往前替换，避免位置偏移）
    ms = list(STMT.finditer(src))
    if not ms:
        continue
    todo = [m for m in ms if "script_name" not in m.group(1)]
    if not todo:
        skipped.append(fn)
        continue
    if LIMIT and len(changed) >= LIMIT:
        break

    new_src = src
    ok = True
    for m in reversed(todo):
        span = m.group(0)
        cols, vals = m.group(1).strip(), m.group(2).strip()
        # 判断该 span 所处 Python 字面量的引号类型，避免注入后破引号
        q = '"'
        j = m.start() - 1
        while j >= 0:
            if src[j] in "\"'":
                q = src[j]
                break
            if src[j] == "\n":
                break
            j -= 1
        lit = ("'%s'" % fn) if q == '"' else ("\\'%s\\'" % fn)
        rep = ("INSERT OR REPLACE INTO gov_raw (%s, script_name) VALUES (%s, %s)"
               % (cols, vals, lit))
        chk = check_sql("INSERT OR REPLACE INTO gov_raw (%s, script_name) VALUES (%s, 'x.py')"
                        % (cols, vals))
        if chk:
            failed.append((fn, "SQL 校验失败: %s" % chk[:70]))
            ok = False
            break
        new_src = new_src[:m.start()] + rep + new_src[m.end():]

    if not ok:
        continue
    try:
        ast.parse(new_src)
    except SyntaxError as e:
        failed.append((fn, "语法失败: %s" % str(e)[:70]))
        continue

    if not DRY:
        os.makedirs(ARCH, exist_ok=True)
        bak = os.path.join(ARCH, fn + ".bak_20260921_replfix")
        if not os.path.exists(bak):
            shutil.copy2(p, bak)
        io.open(p, "w", encoding="utf-8").write(new_src)
    changed.append(fn)

print("=" * 92)
print("已修 %d 个文件（含 script_name 的 INSERT OR REPLACE）%s" % (len(changed), "  [DRY-RUN]" if DRY else ""))
print("=" * 92)
for f in changed:
    print("   " + f)
if skipped:
    print()
    print("跳过（已含 script_name）: %d 个" % len(skipped))
if failed:
    print()
    print("❌ 失败 %d 个:" % len(failed))
    for f, why in failed:
        print("   %-40s %s" % (f, why))