#!/usr/bin/env python3
"""Clean up excessive blank lines in content field of gov_raw."""
import sqlite3, re, sys

DB_PATH = "/mnt/data/search.db"
BATCH_SIZE = 500

def clean_content(content):
    """Normalize excessive newlines to at most 2 consecutive."""
    if not content:
        return content
    # Replace 3+ newlines with 2
    cleaned = re.sub(r'\n{3,}', '\n\n', content)
    # Also trim leading/trailing whitespace
    cleaned = cleaned.strip()
    return cleaned

def main():
    db = sqlite3.connect(DB_PATH)
    db.execute("PRAGMA journal_mode=WAL")
    cur = db.cursor()
    
    # Get total count
    cur.execute("SELECT COUNT(*) FROM gov_raw WHERE content IS NOT NULL AND content != ''")
    total = cur.fetchone()[0]
    print(f"Total records with content: {total}", file=sys.stderr)
    
    # Process in batches
    offset = 0
    fixed = 0
    while offset < total:
        cur.execute(
            "SELECT id, content FROM gov_raw WHERE content IS NOT NULL AND content != '' ORDER BY id LIMIT ? OFFSET ?",
            (BATCH_SIZE, offset)
        )
        rows = cur.fetchall()
        if not rows:
            break
        
        for row_id, content in rows:
            cleaned = clean_content(content)
            if cleaned != content:
                cur.execute("UPDATE gov_raw SET content=? WHERE id=?", (cleaned, row_id))
                fixed += 1
        
        db.commit()
        offset += len(rows)
        print(f"  Progress: {offset}/{total}, fixed: {fixed}", file=sys.stderr)
    
    print(f"\nDone! Fixed {fixed} records", file=sys.stderr)

if __name__ == "__main__":
    main()