from __future__ import annotations

from contextlib import asynccontextmanager
from typing import AsyncIterator

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

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

_settings = get_settings()

engine = create_async_engine(_settings.database_url, echo=False)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)


async def _migrate(conn) -> None:
    """Lightweight migrations: add missing columns to an existing DB."""
    from sqlalchemy import text

    wanted = {
        "users": [
            ("total_spent", "INTEGER NOT NULL DEFAULT 0"),
            ("weekly_spent", "INTEGER NOT NULL DEFAULT 0"),
        ],
        "stock": [("totp_secret", "VARCHAR(255) NOT NULL DEFAULT ''")],
        "purchases": [
            ("totp_secret", "VARCHAR(255) NOT NULL DEFAULT ''"),
            ("item_id", "INTEGER"),
            ("reseller_id", "INTEGER"),
            ("reviewed", "BOOLEAN NOT NULL DEFAULT 0"),
        ],
        "topups": [
            ("method", "VARCHAR(16) NOT NULL DEFAULT 'manual'"),
            ("invoice_id", "VARCHAR(64) NOT NULL DEFAULT ''"),
        ],
    }
    for table, columns in wanted.items():
        result = await conn.exec_driver_sql(f"PRAGMA table_info({table})")
        existing = {row[1] for row in result.fetchall()}
        for name, ddl in columns:
            if name not in existing:
                await conn.exec_driver_sql(
                    f"ALTER TABLE {table} ADD COLUMN {name} {ddl}"
                )


async def init_db() -> None:
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
        await _migrate(conn)
    # load runtime (admin-editable) config into the in-memory cache
    from app import runtime

    async with SessionLocal() as session:
        await runtime.load(session)


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