#!/usr/bin/env python3
"""Export airports and routes tables from the full DB into a slim SQLite for the API."""
import sqlite3
import os
import sys

src = os.environ.get("SOURCE_DB", "/Users/scott/Developer/route-risk/RouteRisk/data/flight_delays.db")
dst = os.environ.get("DEST_DB", "backend/data/flight_delays.db")

if not os.path.exists(src):
    print(f"source DB not found: {src}", file=sys.stderr)
    sys.exit(1)

os.makedirs(os.path.dirname(dst), exist_ok=True)

src_conn = sqlite3.connect(src)
dst_conn = sqlite3.connect(dst)

for table in ("airports", "routes"):
    cols = src_conn.execute(f"PRAGMA table_info({table})").fetchall()
    if not cols:
        print(f"table {table} not found in source", file=sys.stderr)
        src_conn.close()
        dst_conn.close()
        sys.exit(1)
    col_defs = ", ".join(f"{c[1]} {c[2]}" for c in cols)
    dst_conn.execute(f"DROP TABLE IF EXISTS {table}")
    dst_conn.execute(f"CREATE TABLE {table} ({col_defs})")
    rows = src_conn.execute(f"SELECT * FROM {table}").fetchall()
    placeholders = ",".join(["?"] * len(cols))
    dst_conn.executemany(f"INSERT INTO {table} VALUES ({placeholders})", rows)
    print(f"  {table}: {len(rows):,} rows")

dst_conn.commit()
src_conn.close()
dst_conn.close()

mb = os.path.getsize(dst) / 1024 / 1024
print(f"slim DB: {mb:.1f} MB -> {dst}")
