#!/usr/bin/env python3
"""全库游标扫描：md 表格残留 + md 附件链接残留（转换器修复后二次验证）"""
import sqlite3
import re
import sys

DB = "/root/search.db"

# md 表格特征：含 | --- 分隔行
MD_TABLE_RE = re.compile(r"^\s*\|[\s:|—-]+\|\s*$", re.M)
# 也可能 | --- 不带首尾竖线
MD_TABLE_RE2 = re.compile(r"^\s*\|?\s*:?-{3,}:?\s*(\|\s*:?-{3,}:?\s*)+\|?\s*$", re.M)
# md 附件链接
MD_LINK_RE = re.compile(r"\[[^\]]+\]\((https?://[^)\s]+\.(?:pdf|doc|docx|xls|xlsx|zip|rar)[^)\s]*)\)", re.I)

conn = sqlite3.connect(DB)
conn.execute("PRAGMA busy_timeout=60000")
c = conn.cursor()

c.execute("SELECT COUNT(*) FROM gov_raw")
total = c.fetchone()[0]
print("总行数:", total)

# 游标扫描
md_tables = {}
md_links = {}
batch = 50000
offset = 0
checked = 0
while True:
    c.execute("SELECT rowid, site_name, content FROM gov_raw LIMIT ? OFFSET ?", (batch, offset))
    rows = c.fetchall()
    if not rows:
        break
    for rid, site, content in rows:
        if not content:
            continue
        if MD_TABLE_RE2.search(content):
            md_tables.setdefault(site, 0)
            md_tables[site] += 1
        lm = MD_LINK_RE.findall(content)
        if lm:
            md_links.setdefault(site, 0)
            md_links[site] += len(lm)
    checked += len(rows)
    offset += batch
    if offset % 200000 < batch:
        print("checked:", checked)

print("=" * 50)
print("md 表格残留:", sum(md_tables.values()), "条 /", len(md_tables), "站")
for s, n in sorted(md_tables.items(), key=lambda x: -x[1])[:15]:
    print("  %s: %d" % (s, n))
print("md 附件链接残留:", sum(md_links.values()), "个 /", len(md_links), "站")
for s, n in sorted(md_links.items(), key=lambda x: -x[1])[:15]:
    print("  %s: %d" % (s, n))
conn.close()