"""Export data from corrupted search.db and reimport to fresh DB"""
import sqlite3, sys, os, re

SRC = '/root/search.db'
DST = '/root/search.db.fixed'
CONFIG_PATH = '/root/gov_crawler/daily_crawl_config.json'

# Remove old target
for f in [DST, DST+'-wal', DST+'-shm']:
    if os.path.exists(f):
        os.remove(f)

print("Reading from corrupted DB...")
src = sqlite3.connect(SRC)
src.row_factory = sqlite3.Row
src.execute("PRAGMA query_only = 1")

# Get schema
schema_sql = []
c = src.cursor()
c.execute("SELECT sql FROM sqlite_master WHERE type IN ('table','index','view','trigger') ORDER BY type='table' DESC, rootpage")
rows = c.fetchall()

print(f"Creating new DB ({len(rows)} schema objects)...")
dst = sqlite3.connect(DST)
dst.execute("PRAGMA journal_mode = WAL")
dc = dst.cursor()

for row in rows:
    sql = row['sql']
    if sql:
        try:
            dc.execute(sql)
        except Exception as e:
            print(f"  Schema error [{sql[:50]}...]: {e}")

dst.commit()

# Get list of real tables (not FTS, not sqlite_*)
c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE '%_fts%' AND name NOT LIKE '%_content' AND name NOT LIKE '%_segdir' AND name NOT LIKE '%_segments' AND name NOT LIKE '%_docsize' AND name NOT LIKE '%_stat%'")
real_tables = [r[0] for r in c.fetchall()]
print(f"Real tables: {real_tables}")

# Export data table by table
for table_name in real_tables:
    c.execute(f"SELECT * FROM [{table_name}]")
    rows = c.fetchall()
    if not rows:
        print(f"  {table_name}: 0 rows, skipping")
        continue
    
    cols = [d[0] for d in c.description]
    placeholders = ','.join(['?'] * len(cols))
    col_list = ','.join([f'[{col}]' for col in cols])
    
    print(f"  {table_name}: {len(rows)} rows...")
    for i, row in enumerate(rows):
        if i % 10000 == 0 and i > 0:
            print(f"    ...{i} rows inserted")
            dst.commit()
        values = [row[col] for col in cols]
        try:
            dc.execute(f"INSERT INTO [{table_name}] ({col_list}) VALUES ({placeholders})", values)
        except Exception as e:
            print(f"    Row {i} error: {e}")
    
    dst.commit()
    print(f"  {table_name}: done")

src.close()

# Rebuild FTS
print("\nRebuilding FTS...")
try:
    dc.execute("INSERT INTO gov_search(gov_search) VALUES('rebuild')")
    dst.commit()
    print("FTS rebuild complete!")
except Exception as e:
    print(f"FTS rebuild error: {e}")

dst.close()

# Verify
print("\nVerifying fixed DB...")
db = sqlite3.connect(DST)
cnt = db.execute("SELECT COUNT(*) FROM gov_raw").fetchone()[0]
print(f"Record count: {cnt}")

try:
    db.execute("INSERT INTO gov_raw(site_name,title,page_url,publish_date) VALUES('test_write_ok','test','test','2024-01-01')")
    db.commit()
    print("Write test: OK")
    db.execute("DELETE FROM gov_raw WHERE site_name='test_write_ok'")
    db.commit()
    print("Delete test: OK")
except Exception as e:
    print(f"Write test failed: {e}")

db.close()
print(f"\n✅ Fixed DB at: {DST}")