from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase

from config import settings

# ---------------------------------------------------------------------------
# Engine & session factory
# ---------------------------------------------------------------------------

engine = create_async_engine(
    settings.DATABASE_URL,
    echo=False,
    pool_pre_ping=True,
    pool_size=10,
    max_overflow=20,
)

AsyncSessionLocal: async_sessionmaker[AsyncSession] = async_sessionmaker(
    bind=engine,
    expire_on_commit=False,
    class_=AsyncSession,
)


# ---------------------------------------------------------------------------
# Declarative base
# ---------------------------------------------------------------------------

class Base(DeclarativeBase):
    pass


# ---------------------------------------------------------------------------
# FastAPI dependency
# ---------------------------------------------------------------------------

async def get_db() -> AsyncSession:  # type: ignore[return]
    """Yield an async database session and ensure it is closed afterward."""
    async with AsyncSessionLocal() as session:
        try:
            yield session
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()


# ---------------------------------------------------------------------------
# Table creation helper (used in lifespan)
# ---------------------------------------------------------------------------

async def init_db() -> None:
    """Create all tables defined in the ORM models and apply automatic column migrations."""
    from sqlalchemy import text
    import models  # noqa: F401  (side-effect import)

    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

        # Automatic schema migration for users table columns if missing
        await conn.execute(text("ALTER TABLE users ADD COLUMN IF NOT EXISTS custom_role_name VARCHAR(64);"))
        await conn.execute(
            text(
                "ALTER TABLE users ADD COLUMN IF NOT EXISTS permissions TEXT DEFAULT 'domains,cloudflare,hosting,dns,accounts,users,settings';"
            )
        )