#!/usr/bin/env python3
"""Clean up all 六枝特区-公示公告 records - use subprocess stdin for WAL safety"""
import subprocess
from bs4 import BeautifulSoup

DB_PATH = '/mnt/data/search.db'

def query(sql):
    """Execute query via sqlite3 CLI, return stdout"""
    result = subprocess.run(
        ['sqlite3', DB_PATH],
        input=sql, capture_output=True, text=True, timeout=30
    )
    if result.returncode != 0:
        print(f"Error: {result.stderr[:200]}")
    return result.stdout.strip()

def execute(sql):
    """Execute SQL via subprocess stdin (WAL-safe)"""
    result = subprocess.run(
        ['sqlite3', DB_PATH],
        input=sql, capture_output=True, text=True, timeout=30
    )
    if result.returncode != 0:
        if 'Error' in result.stderr:
            print(f"SQL Error: {result.stderr[:200]}")
            return False
    return True

# Get all records
print("=== Fetching records ===")
rows_output = query("SELECT rowid FROM gov_raw WHERE site_name='六枝特区-公示公告' ORDER BY rowid;")
rows = [r.strip() for r in rows_output.split('\n') if r.strip()]
total = len(rows)
print(f"Total records: {total}")

# Process in batches to avoid huge SQL
cleaned = 0
skipped = 0
errors = 0
batch = []

for i, rowid_str in enumerate(rows):
    try:
        rowid = int(rowid_str)
    except:
        errors += 1
        continue
    
    # Get content
    content = query(f"SELECT content FROM gov_raw WHERE rowid={rowid};")
    if not content:
        errors += 1
        continue
    
    # Parse and clean
    soup = BeautifulSoup(content, 'html.parser')
    junk_selectors = ['.yjfx-btn-span', '.windShare', '.sxp-ul', '.information', 'script', 'link']
    
    tags_removed = 0
    for sel in junk_selectors:
        found = soup.select(sel)
        tags_removed += len(found)
        for tag in found:
            tag.decompose()
    
    if tags_removed == 0:
        skipped += 1
        continue
    
    new_content = str(soup)
    # Escape single quotes for SQL
    escaped = new_content.replace("'", "''")
    batch.append(f"UPDATE gov_raw SET content='{escaped}' WHERE rowid={rowid};")
    
    if len(batch) >= 50:
        execute('\n'.join(batch))
        print(f"  Cleaned {cleaned+50}/{total}")
        cleaned += len(batch)
        batch = []

# Final batch
if batch:
    execute('\n'.join(batch))
    cleaned += len(batch)

print(f"\n=== Results ===")
print(f"Total: {total}")
print(f"Cleaned: {cleaned}")
print(f"Skipped (no junk): {skipped}")
print(f"Errors: {errors}")