from collections.abc import AsyncIterator

from sqlalchemy import event, text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from app.core.config import get_settings
from app.models.base import Base

settings = get_settings()
engine = create_async_engine(settings.database_url, future=True)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


@event.listens_for(engine.sync_engine, "connect")
def _enable_sqlite_pragmas(dbapi_connection, _connection_record) -> None:  # type: ignore[no-untyped-def]
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL;")
    cursor.execute("PRAGMA foreign_keys=ON;")
    cursor.close()


async def init_database() -> None:
    async with engine.begin() as connection:
        await connection.execute(text("PRAGMA journal_mode=WAL;"))
        await connection.run_sync(Base.metadata.create_all)
        await _ensure_compatible_schema(connection)


async def get_db_session() -> AsyncIterator[AsyncSession]:
    async with SessionLocal() as session:
        yield session


async def _ensure_compatible_schema(connection) -> None:  # type: ignore[no-untyped-def]
    def sync_ensure(sync_connection) -> None:  # type: ignore[no-untyped-def]
        scene_columns = {
            row[1]
            for row in sync_connection.exec_driver_sql("PRAGMA table_info('scenes')").fetchall()
        }
        if "targets" not in scene_columns:
            sync_connection.exec_driver_sql(
                "ALTER TABLE scenes ADD COLUMN targets JSON NOT NULL DEFAULT '[]'"
            )

    await connection.run_sync(sync_ensure)