#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""refresh_linkbox.py —— 修复存量：重抓 + UPDATE 回写（不 DELETE）

守三铁律：① 先建备份表 ② 绝不 DELETE + 重跑 ③ UPDATE ... WHERE page_url=?
"""
import importlib.util
import json
import os
import re
import sqlite3
import sys
import time

D = "/root/gov_crawler/"
SITES = [
    ("宾阳县-建设项目环境影响评价审批", "crawl_binyang_hjsp.py"),
    ("大足区古龙镇-其他公文", "crawl_dazu_glz_qtgw.py"),
    ("丹寨县人民政府-环境执法监管", "crawl_danzhai_hjzf.py"),
]
APPLY = "--apply" in sys.argv

db = sqlite3.connect("/root/search.db", timeout=60)
db.execute("PRAGMA busy_timeout=60000")

names = tuple(s for s, _ in SITES)
print("=== ① 备份表 ===")
n_guarded = db.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='bak_linkbox_20260925'").fetchone()[0]
if not n_guarded:
    db.execute("CREATE TABLE bak_linkbox_20260925 AS SELECT id, site_name, page_url, title, content, attachments "
               "FROM gov_raw WHERE site_name IN (%s)" % ",".join("?" * len(names)), names)
    db.commit()
print("  bak_linkbox_20260925 行数:", db.execute("SELECT COUNT(*) FROM bak_linkbox_20260925").fetchone()[0])

total_upd = total_att_gain = total_att_before = 0
for site, script in SITES:
    spec = importlib.util.spec_from_file_location("m_" + script, D + script)
    m = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(m)
    rows = list(db.execute("SELECT id, page_url, title, content, attachments FROM gov_raw WHERE site_name=?", (site,)))
    print("\n=== %s（%d 行）===" % (site, len(rows)))
    upd = gain = before = 0
    for rid, url, title, content, atts_old in rows:
        n_old = len(json.loads(atts_old)) if atts_old else 0
        before += n_old
        h = m.fetch(url)
        if not h:
            continue
        r = m.parse_detail(h, url)
        chtml = r[2]
        extra = r[3] if len(r) > 3 else []
        content_new, has_table, atts = m.html_to_text(chtml, url)
        known = set(u for u, _ in atts)
        for _u, _nm in (extra or []):
            if _u in known:
                continue
            known.add(_u)
            atts.append((_u, _nm))
            content_new += '\n\n<p><a href="%s" target="_blank">%s</a></p>' % (_u, _nm)
        n_new = len(atts)
        if n_new > n_old:
            gain += 1
        if content_new != (content or ""):
            upd += 1
            if APPLY:
                db.execute("UPDATE gov_raw SET content=?, attachments=?, has_table=? WHERE page_url=?",
                           (content_new,
                            json.dumps([{"title": t, "url": u} for u, t in atts], ensure_ascii=False) if atts else "",
                            has_table, url))
        time.sleep(0.15)
    if APPLY:
        db.commit()
    print("  需更新 %d 行 | 附件数变多的行 %d | 附件总数 %d → 重新统计见下" % (upd, gain, before))
    total_upd += upd; total_att_gain += gain; total_att_before += before

if APPLY:
    print("\n=== ② 回读 ===")
    for site, _ in SITES:
        n = db.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (site,)).fetchone()[0]
        na = db.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=? AND attachments IS NOT NULL AND attachments!=''", (site,)).fetchone()[0]
        print("  %-34s %d 行 | 有附件 %d 行" % (site, n, na))
    row = db.execute("SELECT title, content, attachments FROM gov_raw WHERE page_url=?",
                     ("http://www.binyang.gov.cn/gk/xxgkml/shgysyjslygk/hjbhly/jsxmhjyxpjsp/t6684215.html",)).fetchone()
    if row:
        print("\n  目标文章: %s" % row[0][:50])
        print("  含 .docx:", "P020260720396340403399" in (row[1] or ""))
        print("  attachments:", (row[2] or "(空)")[:150])
        print("  尾部:", repr((row[1] or "")[-220:]))
else:
    print("\n(dry-run：需更新 %d 行、附件变多 %d 行；加 --apply 才写)" % (total_upd, total_att_gain))
db.close()