"""
Migración: Añadir columnas last_read_pistas, last_read_letras, last_read_repertorios a user_groups usando el engine configurado del app
"""
import os
import sys

# Agregar ruta del app para poder importar
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__))))

from app.database import engine
from sqlalchemy import text

def run_migration():
    print(f"Conectando a base de datos usando el engine: {engine.url}")
    
    # SQLite/PostgreSQL columns
    columns_to_add = [
        ("last_read_pistas", "TIMESTAMP"),
        ("last_read_letras", "TIMESTAMP"),
        ("last_read_repertorios", "TIMESTAMP")
    ]
    
    with engine.begin() as connection:
        for col_name, col_type in columns_to_add:
            try:
                print(f"Intentando añadir columna '{col_name}' a 'user_groups'...")
                connection.execute(text(f"ALTER TABLE user_groups ADD COLUMN {col_name} {col_type}"))
                print(f"OK: Columna '{col_name}' añadida exitosamente.")
            except Exception as e:
                # Comprobar si es un error de columna duplicada
                err_str = str(e).lower()
                if "duplicate" in err_str or "already exists" in err_str:
                    print(f"Nota: La columna '{col_name}' ya existe.")
                else:
                    print(f"Error al añadir '{col_name}': {e}")

if __name__ == "__main__":
    run_migration()