"""Backup and verify search.db using SQLite backup API"""
import sqlite3, sys, os

SRC = '/root/search.db'
DST = '/root/search.db.recovered'

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

print("Opening source DB (read-only)...")
src = sqlite3.connect(SRC)
src.execute("PRAGMA query_only = 1")

print("Creating destination DB...")
dst = sqlite3.connect(DST)
dst.execute("PRAGMA journal_mode = WAL")

print("Starting backup...")
try:
    src.backup(dst, pages=1000)
    print("Backup complete!")
except Exception as e:
    print(f"Backup error: {e}")
    src.close()
    dst.close()
    sys.exit(1)

src.close()
dst.close()

# Verify the new DB
print("Verifying recovered DB...")
db = sqlite3.connect(DST)
try:
    cnt = db.execute("SELECT COUNT(*) FROM gov_raw").fetchone()[0]
    print(f"Record count: {cnt}")
    
    # Test write
    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"Verify error: {e}")
    db.close()
    sys.exit(1)

db.close()
print("\n✅ Recovery successful! DST:", DST)