"""
Migración SQLite → PostgreSQL — CrowData.

Uso:
    1. Configurar DATABASE_URL en .env con la conexión PostgreSQL:
       DATABASE_URL=postgresql+asyncpg://user:password@localhost:5432/crowdata

    2. Instalar dependencias:
       pip install asyncpg

    3. Ejecutar:
       python -m app.migrations.sqlite_to_pg

    4. Verificar que la tabla login_history existe:
       python -c "from app.database import init_db; import asyncio; asyncio.run(init_db())"
"""
import asyncio
import logging
import sqlite3
from datetime import datetime
from pathlib import Path

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

DB_PATH = Path(__file__).parent.parent / "crowdata.db"


def get_sqlite_data():
    """Lee todos los datos de SQLite."""
    if not DB_PATH.exists():
        logger.error(f"SQLite DB not found: {DB_PATH}")
        return {}

    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    data = {}

    # Listar todas las tablas
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [row[0] for row in cursor.fetchall()]
    logger.info(f"Tablas encontradas en SQLite: {tables}")

    for table in tables:
        cursor.execute(f"SELECT * FROM {table}")
        rows = [dict(row) for row in cursor.fetchall()]
        data[table] = rows
        logger.info(f"  {table}: {len(rows)} registros")

    conn.close()
    return data


async def migrate_to_postgres(data: dict):
    """Inserta los datos en PostgreSQL."""
    from sqlalchemy import text
    from app.database import AsyncSessionLocal

    async with AsyncSessionLocal() as db:
        for table_name, rows in data.items():
            if not rows:
                continue

            logger.info(f"Migrando tabla {table_name} ({len(rows)} registros)...")

            for row in rows:
                # Limpiar columnas que no existen en el modelo
                columns = list(row.keys())
                values = list(row.values())

                # Construir INSERT dinámico
                cols_str = ", ".join(columns)
                placeholders = ", ".join([f":{col}" for col in columns])
                query = text(f"INSERT INTO {table_name} ({cols_str}) VALUES ({placeholders})")

                try:
                    await db.execute(query, row)
                except Exception as e:
                    logger.warning(f"  Error insertando en {table_name}: {e}")
                    # Continuar con el siguiente registro

            await db.commit()
            logger.info(f"  {table_name} migrado correctamente")


async def main():
    logger.info("=== Migración SQLite → PostgreSQL ===")
    logger.info(f"SQLite DB: {DB_PATH}")

    # Leer datos de SQLite
    data = get_sqlite_data()
    if not data:
        logger.error("No se encontraron datos en SQLite")
        return

    # Migrar a PostgreSQL
    await migrate_to_postgres(data)

    logger.info("=== Migración completada ===")
    logger.info("Verificar con: python -c \"from app.database import init_db; import asyncio; asyncio.run(init_db())\"")


if __name__ == "__main__":
    asyncio.run(main())