turf_saas/migrations/migrate_sqlite_to_postgres.py

#!/usr/bin/env python3
"""
SQLite → PostgreSQL Data Migration Script
Migrates existing turf_saas.db data to PostgreSQL.

Usage:
    python migrations/migrate_sqlite_to_postgres.py \
        --sqlite /path/to/turf_saas.db \
        --pg-url postgresql://turf:password@localhost:5432/turf_saas

Run AFTER alembic upgrade head.
"""

import argparse
import sqlite3
import sys
import os
import logging
from datetime import datetime

logger = logging.getLogger("migrate")
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")


# Tables to migrate (in order to respect FK constraints)
TABLES = [
    "predictions",
    "results",
    "performance",
    "scraping_logs",
    "pmu_reunions",
    "pmu_meteo",
    "pmu_courses",
    "pmu_partants",
    "ml_predictions_cache",
    "users",
    "subscriptions",
    "refresh_tokens",
]


def get_sqlite_conn(sqlite_path: str):
    conn = sqlite3.connect(sqlite_path)
    conn.row_factory = sqlite3.Row
    return conn


def get_pg_conn(pg_url: str):
    try:
        import psycopg2
        import psycopg2.extras

        conn = psycopg2.connect(pg_url)
        return conn
    except ImportError:
        logger.error("psycopg2 not installed. Run: pip install psycopg2-binary")
        sys.exit(1)


def migrate_table(sqlite_conn, pg_conn, table: str, batch_size: int = 500) -> int:
    """Migrate a single table from SQLite to PostgreSQL. Returns row count."""
    import psycopg2.extras

    sqlite_cur = sqlite_conn.cursor()
    pg_cur = pg_conn.cursor()

    # Get rows from SQLite
    try:
        sqlite_cur.execute(f"SELECT * FROM {table}")
    except Exception as e:
        logger.warning(f"  Skipping {table}: {e}")
        return 0

    rows = sqlite_cur.fetchall()
    if not rows:
        logger.info(f"  {table}: empty — skipping")
        return 0

    # Get column names
    columns = [desc[0] for desc in sqlite_cur.description]
    # Exclude 'id' to let PostgreSQL generate SERIAL
    non_id_columns = [c for c in columns if c != "id"]

    if not non_id_columns:
        logger.warning(f"  {table}: no columns to insert")
        return 0

    placeholders = ", ".join(["%s"] * len(non_id_columns))
    col_list = ", ".join(non_id_columns)
    insert_sql = f"INSERT INTO {table} ({col_list}) VALUES ({placeholders}) ON CONFLICT DO NOTHING"

    inserted = 0
    batch = []

    for row in rows:
        row_dict = dict(row)
        values = tuple(row_dict.get(c) for c in non_id_columns)
        batch.append(values)

        if len(batch) >= batch_size:
            try:
                pg_cur.executemany(insert_sql, batch)
                pg_conn.commit()
                inserted += len(batch)
            except Exception as e:
                pg_conn.rollback()
                logger.error(f"  {table} batch error: {e}")
            batch = []

    # Final batch
    if batch:
        try:
            pg_cur.executemany(insert_sql, batch)
            pg_conn.commit()
            inserted += len(batch)
        except Exception as e:
            pg_conn.rollback()
            logger.error(f"  {table} final batch error: {e}")

    # Sync PostgreSQL sequence to max id
    try:
        pg_cur.execute(f"SELECT MAX(id) FROM {table}")
        max_id = pg_cur.fetchone()[0]
        if max_id:
            seq_name = f"{table}_id_seq"
            pg_cur.execute(f"SELECT setval('{seq_name}', {max_id})")
            pg_conn.commit()
    except Exception:
        pass  # Table may not have a sequence

    return inserted


def run_migration(sqlite_path: str, pg_url: str):
    logger.info(f"=== SQLite → PostgreSQL Migration ===")
    logger.info(f"SQLite: {sqlite_path}")
    logger.info(f"PostgreSQL: {pg_url.split('@')[-1]}")  # Hide credentials in log
    logger.info(f"Started: {datetime.now().isoformat()}")

    if not os.path.exists(sqlite_path):
        logger.error(f"SQLite file not found: {sqlite_path}")
        sys.exit(1)

    sqlite_conn = get_sqlite_conn(sqlite_path)
    pg_conn = get_pg_conn(pg_url)

    total = 0
    for table in TABLES:
        logger.info(f"  Migrating: {table}...")
        count = migrate_table(sqlite_conn, pg_conn, table)
        logger.info(f"  → {table}: {count} rows migrated")
        total += count

    sqlite_conn.close()
    pg_conn.close()

    logger.info(f"=== Migration complete: {total} total rows ===")
    logger.info(f"Finished: {datetime.now().isoformat()}")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Migrate SQLite → PostgreSQL")
    parser.add_argument(
        "--sqlite",
        default=os.environ.get("DB_PATH", "/home/h3r7/turf_saas/turf_saas.db"),
        help="Path to SQLite database file",
    )
    parser.add_argument(
        "--pg-url",
        default=os.environ.get("DATABASE_URL", ""),
        help="PostgreSQL connection URL",
    )
    parser.add_argument("--batch-size", type=int, default=500)
    args = parser.parse_args()

    if not args.pg_url:
        logger.error("--pg-url or DATABASE_URL env var required")
        sys.exit(1)

    run_migration(args.sqlite, args.pg_url)