#!/usr/bin/env python3
import sqlite3
from pathlib import Path
from typing import Set, Dict, List

# Este script se guarda como demo/scripts/migrate_audiodescriptions.py
# BASE_DIR apunta a la carpeta demo/
BASE_DIR = Path(__file__).resolve().parent.parent

DB_REL_PATHS = [
    Path("temp") / "audiodescriptions.db",
    Path("data") / "audiodescriptions.db",
]


def get_existing_columns(conn, table_name: str) -> Set[str]:
    cur = conn.execute(f"PRAGMA table_info({table_name})")
    cols = {row[1] for row in cur.fetchall()}  # row[1] = nombre de columna
    return cols


def ensure_columns(conn, table_name: str, columns_sql: Dict[str, str]) -> None:
    """Asegura que existen las columnas indicadas (si no, hace ALTER TABLE).

    columns_sql: nombre_columna -> sentencia ALTER TABLE ADD COLUMN ...
    """
    existing = get_existing_columns(conn, table_name)
    for col_name, alter_sql in columns_sql.items():
        if col_name not in existing:
            print(f"  - Añadiendo columna {col_name}...")
            conn.execute(alter_sql)
        else:
            print(f"  - Columna {col_name} ya existe, se omite.")


def copy_free_ad_into_new_columns(conn, table_name: str, target_columns: List[str]) -> None:
    """Actualiza cada columna destino con el valor actual de free_ad."""
    set_clause = ", ".join(f"{col} = free_ad" for col in target_columns)
    sql = f"UPDATE {table_name} SET {set_clause}"
    print(f"  - Ejecutando: {sql}")
    conn.execute(sql)


def migrate_db(db_path: Path) -> None:
    full_path = BASE_DIR / db_path
    if not full_path.exists():
        print(f"[AVISO] DB no encontrada, se omite: {full_path}")
        return

    print(f"\n=== Migrando BD: {full_path} ===")
    conn = sqlite3.connect(full_path)
    try:
        conn.isolation_level = None  # manejo manual de transacciones
        conn.execute("BEGIN")

        table_name = "audiodescriptions"

        # 1) Asegurar columnas nuevas con los nombres finales
        columns_sql: Dict[str, str] = {
            "ok_une_ad":    f"ALTER TABLE {table_name} ADD COLUMN ok_une_ad TEXT",
            "test_une_ad":  f"ALTER TABLE {table_name} ADD COLUMN test_une_ad TEXT",
            "ok_free_ad":   f"ALTER TABLE {table_name} ADD COLUMN ok_free_ad TEXT",
            "test_free_ad": f"ALTER TABLE {table_name} ADD COLUMN test_free_ad TEXT",
        }
        ensure_columns(conn, table_name, columns_sql)

        # 2) Copiar free_ad a las cuatro columnas nuevas
        target_cols: List[str] = ["ok_une_ad", "test_une_ad", "ok_free_ad", "test_free_ad"]
        copy_free_ad_into_new_columns(conn, table_name, target_cols)

        conn.execute("COMMIT")
        print(f"✔ Migración completada en: {full_path}")
    except Exception as e:
        print(f"❌ Error en {full_path}: {e}")
        try:
            conn.execute("ROLLBACK")
        except Exception:
            pass
    finally:
        conn.close()


def main() -> None:
    print("Script de migración de audiodescriptions.db")
    print("Añade columnas ok_une_ad, test_une_ad, ok_free_ad, test_free_ad")
    print("y copia free_ad en todas ellas.\n")

    for rel in DB_REL_PATHS:
        migrate_db(rel)


if __name__ == "__main__":
    main()