"""Setup de base de datos SQLite con SQLAlchemy."""
from sqlalchemy import create_engine, text, inspect
from sqlalchemy.orm import sessionmaker, DeclarativeBase
from config import DATABASE_URL


engine = create_engine(DATABASE_URL, echo=False)
SessionLocal = sessionmaker(bind=engine)


class Base(DeclarativeBase):
    pass


def get_db():
    """Dependency para FastAPI: obtiene sesion DB."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def _migrate_agents_token():
    """Agrega columna 'token' a tabla agents si no existe (migracion L4.1)."""
    insp = inspect(engine)
    if 'agents' in insp.get_table_names():
        columns = [col['name'] for col in insp.get_columns('agents')]
        if 'token' not in columns:
            with engine.begin() as conn:
                conn.execute(text("ALTER TABLE agents ADD COLUMN token VARCHAR"))
            print("[DB] Migracion: columna 'token' agregada a tabla 'agents'")


def _migrate_session_folio():
    """Agrega columna 'folio_number' a tabla sessions si no existe."""
    insp = inspect(engine)
    if 'sessions' in insp.get_table_names():
        columns = [col['name'] for col in insp.get_columns('sessions')]
        if 'folio_number' not in columns:
            with engine.begin() as conn:
                conn.execute(text("ALTER TABLE sessions ADD COLUMN folio_number VARCHAR"))
            print("[DB] Migracion: columna 'folio_number' agregada a tabla 'sessions'")


def _migrate_agent_venue_id():
    """Agrega venue_id a agents si no existe, y rellena con el VENUE_ID de este servidor."""
    from config import VENUE_ID
    insp = inspect(engine)
    if 'agents' in insp.get_table_names():
        columns = [col['name'] for col in insp.get_columns('agents')]
        if 'venue_id' not in columns:
            with engine.begin() as conn:
                conn.execute(text("ALTER TABLE agents ADD COLUMN venue_id VARCHAR"))
                conn.execute(text(f"UPDATE agents SET venue_id = '{VENUE_ID}' WHERE venue_id IS NULL"))
            print(f"[DB] Migracion: venue_id agregado a agents (valor: {VENUE_ID})")


def _migrate_combo_registry_venue_id():
    """Agrega venue_id a combo_registry si no existe, y rellena con el VENUE_ID de este servidor."""
    from config import VENUE_ID
    insp = inspect(engine)
    if 'combo_registry' in insp.get_table_names():
        columns = [col['name'] for col in insp.get_columns('combo_registry')]
        if 'venue_id' not in columns:
            with engine.begin() as conn:
                conn.execute(text("ALTER TABLE combo_registry ADD COLUMN venue_id VARCHAR"))
                conn.execute(text(f"UPDATE combo_registry SET venue_id = '{VENUE_ID}' WHERE venue_id IS NULL"))
            print(f"[DB] Migracion: venue_id agregado a combo_registry (valor: {VENUE_ID})")


def _migrate_combo_config_json():
    """Agrega config_json a combo_registry si no existe."""
    insp = inspect(engine)
    if 'combo_registry' in insp.get_table_names():
        columns = [col['name'] for col in insp.get_columns('combo_registry')]
        if 'config_json' not in columns:
            with engine.begin() as conn:
                conn.execute(text("ALTER TABLE combo_registry ADD COLUMN config_json TEXT"))
            print("[DB] Migracion: columna 'config_json' agregada a combo_registry")


def init_db():
    """Crea todas las tablas y aplica migraciones."""
    from models import Package, Session, AuditLog, SyncQueue, Agent, FolioCounter, OdooTicket, ComboRegistry, TerminalPackage  # noqa
    Base.metadata.create_all(bind=engine)
    _migrate_agents_token()
    _migrate_session_folio()
    _migrate_agent_venue_id()
    _migrate_combo_registry_venue_id()
    _migrate_combo_config_json()
