from collections.abc import AsyncGenerator

from sqlalchemy import delete, text, update
from sqlalchemy.ext.asyncio import AsyncEngine, create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import SQLModel
from sqlmodel.ext.asyncio.session import AsyncSession

from app.config import settings

engine: AsyncEngine = create_async_engine(
    settings.database_url,
    echo=False,
)

async_session_factory = sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)


async def _add_column_if_missing(conn, table_name: str, column_name: str, column_sql: str) -> None:
    rows = await conn.exec_driver_sql(f"PRAGMA table_info('{table_name}')")
    existing_columns = {str(row[1]) for row in rows.fetchall()}
    if column_name in existing_columns:
        return
    await conn.exec_driver_sql(f"ALTER TABLE {table_name} ADD COLUMN {column_sql}")


async def _run_sqlite_migrations(conn) -> None:
    if not str(settings.database_url).startswith("sqlite"):
        return
    await _add_column_if_missing(conn, "poll", "winner_source", "winner_source TEXT DEFAULT ''")
    await _add_column_if_missing(conn, "poll", "config_json", "config_json TEXT DEFAULT '{}'")
    await _add_column_if_missing(conn, "poll", "runtime_json", "runtime_json TEXT DEFAULT '{}'")
    await _add_column_if_missing(conn, "lineeventrecord", "raw_json", "raw_json TEXT DEFAULT ''")
    await _add_column_if_missing(conn, "lineeventrecord", "room_id", "room_id TEXT DEFAULT ''")
    await _add_column_if_missing(conn, "lineeventrecord", "participant_id", "participant_id TEXT DEFAULT ''")
    await _add_column_if_missing(
        conn, "lineeventrecord", "line_user_id_hash", "line_user_id_hash TEXT DEFAULT ''"
    )
    await _add_column_if_missing(conn, "lineeventrecord", "reply_status", "reply_status TEXT DEFAULT ''")
    await _add_column_if_missing(conn, "lineeventrecord", "reply_error", "reply_error TEXT DEFAULT ''")
    await conn.execute(text("CREATE INDEX IF NOT EXISTS ix_lineeventrecord_room_id ON lineeventrecord (room_id)"))
    await conn.execute(
        text("CREATE INDEX IF NOT EXISTS ix_lineeventrecord_participant_id ON lineeventrecord (participant_id)")
    )
    await conn.execute(
        text(
            "CREATE INDEX IF NOT EXISTS ix_lineeventrecord_line_user_id_hash "
            "ON lineeventrecord (line_user_id_hash)"
        )
    )


async def init_db() -> None:
    from app.models import entities  # noqa: F401 — register tables

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


async def reset_runtime_state() -> None:
    from app.models.entities import AudienceSession, JoinToken, LineEventRecord, Participant, Poll, PollStatus, Vote

    async with async_session_factory() as session:
        await session.exec(delete(AudienceSession))
        await session.exec(delete(JoinToken))
        await session.exec(delete(Vote))
        await session.exec(delete(LineEventRecord))
        await session.exec(delete(Participant))
        await session.exec(
            update(Poll).values(
                status=PollStatus.draft,
                winner_option=None,
                winner_source="",
                deadline_at=None,
                runtime_json="{}",
            )
        )
        await session.commit()


async def get_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_factory() as session:
        yield session