#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026-08-11 荆州正文尾部模板噪声存量清理（无限重试版）:
与调度器爬虫并发时，写锁竞争会导致部分 UPDATE 失败。本版对失败条目循环重试
直到全部清理完成，不会永久跳过。
"""
import re
import sqlite3
import time

DB = "/mnt/data/search.db"
SITE = "荆州市生态环境局"


def clean(c):
    if not c:
        return c
    c = re.sub(r'<a href="javascript:[^"]*">\s*关闭\s*</a>', '', c)
    c = re.sub(r'<div class="zw-box-print">.*?</div>', '', c, flags=re.DOTALL)
    c = re.sub(
        r'<div class="zw-leader-jl">\s*<h3><span>相关(?:链接|文档|图片|音频|视频)</span></h3>.*?</div>',
        '', c, flags=re.DOTALL)

    def att_clean(m):
        paras = []
        for am in re.finditer(r'<a[^>]*href=[\'"]([^\'"]+)[\'"][^>]*>(.*?)</a>', m.group(0), re.DOTALL):
            href, text = am.group(1).strip(), re.sub(r'<[^>]+>', '', am.group(2)).strip()
            if href and text:
                paras.append('<p><a href="{}">{}</a></p>'.format(href, text))
        return '\n'.join(paras)

    c = re.sub(
        r'<div class="zw-leader-jl">\s*<h3><span>相关附件</span></h3>.*?</div>',
        att_clean, c, flags=re.DOTALL)
    return c


conn = sqlite3.connect(DB, timeout=5)
conn.execute("PRAGMA busy_timeout=3000")

# 初始：待清理 = 含噪声的记录
rows = conn.execute(
    "SELECT id, content FROM gov_raw WHERE site_name=? AND "
    "(content LIKE '%相关链接%' OR content LIKE '%zw-box-print%' OR content LIKE '%zw-leader-jl%')",
    (SITE,)
).fetchall()
total = len(rows)
print(f"待清理: {total} 条")

updated = 0
pending = [(rid, c) for rid, c in rows]
round_no = 0
while pending:
    round_no += 1
    next_pending = []
    for rid, content in pending:
        nc = clean(content)
        if nc == content:
            continue
        try:
            conn.execute("UPDATE gov_raw SET content=? WHERE id=?", (nc, rid))
            conn.commit()
            updated += 1
        except sqlite3.OperationalError as e:
            if 'locked' in str(e):
                next_pending.append((rid, content))
            else:
                print(f"  ERR {rid}: {e}")
    pending = next_pending
    if pending:
        print(f"第{round_no}轮完成: 更新{updated} 剩余{len(pending)}，60s 后重试")
        time.sleep(60)
    else:
        break

print(f"完成: 清理 {updated}/{total}")

# 验证栩辰
r = conn.execute(
    "SELECT id, length(content), substr(content, -100) FROM gov_raw WHERE id IN (42136,42315,42352)"
).fetchall()
for rid, clen, tail in r:
    print(f"--- {rid} ({clen}字) 尾部: {tail.strip()[-60:]}")
conn.close()