#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""最终清理 v4: 删除孤立 md 表格分隔行噪音 + 剩余 md 附件链接
目标:
  1. 删除 content 中孤立出现的 | --- | 分隔行（无可转表格内容的残留噪音）
  2. 转换剩余 md 附件链接 [name](url) → <a>
只处理已确认的残留记录，逐行游标。
"""
import sqlite3, re, sys

MD_LINK_RE = re.compile(r'\[([^\]]{1,200})\]\((https?://[^)\s]+)\)')
# 分隔行（含空表头行）——孤立出现视为噪音
SEP_RE = re.compile(r'^\s*\|?\s*:?-{2,}:?\s*(\|\s*:?-{2,}:?\s*)+\|?\s*$')
# 全空管道行 |  |  |
EMPTY_PIPE_RE = re.compile(r'^\s*\|(\s*\|)+\s*$')


def clean_noise(content):
    """删除孤立分隔行噪音"""
    lines = content.split('\n')
    out = []
    for i, ln in enumerate(lines):
        s = ln.strip()
        if SEP_RE.match(ln) or EMPTY_PIPE_RE.match(ln):
            # 检查是否是"孤立"分隔行: 上下行都不是表格行（无 | 数据）
            prev_tbl = i > 0 and ('|' in lines[i-1])
            nxt_tbl = i < len(lines)-1 and ('|' in lines[i+1])
            if not prev_tbl and not nxt_tbl:
                continue  # 孤立分隔行 → 删除
            # 若是表格块的一部分，保留（交给人工/其它处理）
            out.append(ln)
            continue
        # 转换残留 md 链接
        out.append(MD_LINK_RE.sub(lambda m: f'<a href="{m.group(2)}">{m.group(1)}</a>', ln))
    return '\n'.join(out)


def main():
    dry = '--dry' in sys.argv
    conn = sqlite3.connect("/root/search.db", timeout=60)
    conn.execute("PRAGMA busy_timeout=60000")
    conn.execute("PRAGMA journal_mode=WAL")
    c = conn.cursor()

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

    updates = []
    errors = 0
    c.execute("SELECT rowid, site_name, content FROM gov_raw")
    while True:
        rows = c.fetchmany(10000)
        if not rows:
            break
        for rowid, site, content in rows:
            if content is None:
                continue
            if not ('|' in content or '](http' in content):
                continue
            try:
                new = clean_noise(content)
                if new != content:
                    updates.append((new, rowid))
            except Exception as e:
                errors += 1
                print(f"  ❌ rowid {rowid}: {e}", flush=True)
        print(f"  已扫描, 收集 {len(updates)} 条...", flush=True)
    print(f"收集完成: {len(updates)} 条待更新 / 错误 {errors}", flush=True)

    if not dry:
        conn.execute("BEGIN")
        for i in range(0, len(updates), 5000):
            batch = updates[i:i + 5000]
            c.executemany("UPDATE gov_raw SET content=? WHERE rowid=?", batch)
            conn.commit()
            print(f"  已写入 {i + len(batch)} ...", flush=True)
    conn.close()
    print(f"\n=== 完成 === (dry={dry}) 更新: {len(updates)}")


if __name__ == '__main__':
    main()