"""Create fresh database from schema + gzipped gov_raw dump"""
import subprocess, sys, os

DB = '/root/search.db'
NEW = '/root/search.db-fixed3'
DUMP = '/root/gov_raw.sql.gz'
SCHEMA = '/root/schema_full.sql'

# Clean up
for f in [NEW, NEW+'-wal', NEW+'-shm']:
    if os.path.exists(f): os.remove(f)

# Export schema (fast: .schema only reads metadata)
print("Exporting schema...")
r = subprocess.run(['sqlite3', DB, '.schema'], capture_output=True, text=True, timeout=30)
with open(SCHEMA, 'w') as f:
    f.write(r.stdout)
print(f"Schema: {len(r.stdout)} bytes")

# Create new DB
print("Creating new database...")
r = subprocess.run(['sqlite3', NEW], input=r.stdout, capture_output=True, text=True, timeout=30)
if r.returncode != 0:
    print(f"Schema create error: {r.stderr}")
    sys.exit(1)

# Set WAL mode
subprocess.run(['sqlite3', NEW, 'PRAGMA journal_mode=WAL;'], timeout=10)
print("WAL mode set")

# Import gov_raw data
print("Importing gov_raw data from dump...")
import subprocess
zcat = subprocess.Popen(['zcat', DUMP], stdout=subprocess.PIPE)
sqlite = subprocess.Popen(['sqlite3', NEW], stdin=zcat.stdout, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
zcat.stdout.close()
out, err = sqlite.communicate(timeout=600)
if sqlite.returncode != 0:
    print(f"Import error: {err.decode()}")
    sys.exit(1)
print("Data import complete!")

# Rebuild FTS
print("Rebuilding FTS...")
r = subprocess.run(['sqlite3', NEW, "INSERT INTO gov_search(gov_search) VALUES('rebuild');"], capture_output=True, text=True, timeout=120)
if r.returncode != 0:
    print(f"FTS rebuild error: {r.stderr}")
else:
    print("FTS rebuild OK!")

# Verify
print("\nVerifying...")
r = subprocess.run(['sqlite3', NEW, 'SELECT COUNT(*) FROM gov_raw;'], capture_output=True, text=True, timeout=30)
print(f"Gov_raw count: {r.stdout.strip()}")

# Test write
r = subprocess.run(['sqlite3', NEW, "INSERT INTO gov_raw(site_name,title,page_url,publish_date) VALUES('test_ok','t','t','2024-01-01');DELETE FROM gov_raw WHERE site_name='test_ok';"], capture_output=True, text=True, timeout=30)
if r.returncode == 0:
    print("Write/Delete: OK ✓")
else:
    print(f"Write FAILED: {r.stderr}")
    sys.exit(1)

# Check size
sz = os.path.getsize(NEW)
print(f"New DB size: {sz/1024/1024:.1f} MB")
print("\n✅ Success!")