import sqlite3
import json
import os
from pathlib import Path

db_path = Path("data/agent.db")
if not db_path.exists():
    db_path = Path("agent.db")

print(f"Connecting to {db_path}...")
try:
    conn = sqlite3.connect(db_path, timeout=10)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute("SELECT id, source_url FROM posts WHERE source_url LIKE '{%'")
    rows = c.fetchall()
    
    print(f"Found {len(rows)} bad posts with JSON strings in source_url.")
    updates = []
    
    for r in rows:
        try:
            data = json.loads(r['source_url'])
            if 'url' in data:
                clean_url = data['url']
            elif 'urls' in data and len(data['urls']) > 0:
                clean_url = data['urls'][0]
            else:
                continue
            updates.append((clean_url, r['id']))
        except:
            pass
            
    if updates:
        c.executemany("UPDATE posts SET source_url = ? WHERE id = ?", updates)
        conn.commit()
    print(f"Migrated {len(updates)} posts successfully.")
    conn.close()
    
except Exception as e:
    print(f"Error: {e}")