"""Database engine and session plumbing.

Synchronous SQLAlchemy on purpose: the background scheduler, the Telegram
polling bot and the request handlers all touch the same tables, and one boring
threadsafe sessionmaker is far easier to reason about than three event loops.
FastAPI runs plain `def` endpoints in a threadpool, so nothing blocks.
"""

from __future__ import annotations

from collections.abc import Iterator
from contextlib import contextmanager

from sqlalchemy import create_engine, inspect, text
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker

from app.config import env_settings


class Base(DeclarativeBase):
    pass


def _engine_kwargs(url: str) -> dict:
    if url.startswith("sqlite"):
        # One file, many threads (scheduler + bot + requests).
        return {"connect_args": {"check_same_thread": False}}
    return {"pool_pre_ping": True, "pool_size": 5, "max_overflow": 10}


engine = create_engine(env_settings().database_url, **_engine_kwargs(env_settings().database_url))
SessionLocal = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False)


# Columns a refactor later dropped from the model but, with no migration tool
# in this project, left behind on any database that was already running —
# still `NOT NULL` with no server-side default, so every insert that no
# longer sets them fails. `create_all` only ever adds; this is the other half.
_DEAD_COLUMNS: dict[str, tuple[str, ...]] = {
    "tasks": ("completed_here",),
}


def _drop_dead_columns() -> None:
    inspector = inspect(engine)
    existing_tables = set(inspector.get_table_names())
    with engine.begin() as conn:
        for table, columns in _DEAD_COLUMNS.items():
            if table not in existing_tables:
                continue
            existing_columns = {c["name"] for c in inspector.get_columns(table)}
            for column in columns:
                if column in existing_columns:
                    conn.execute(text(f'ALTER TABLE {table} DROP COLUMN {column}'))


def init_db() -> None:
    """Create any missing tables. The schema is additive, so this is enough —
    plus the one-time cleanup above for columns a refactor removed from the
    model."""
    from app import models  # noqa: F401  (register the mappers)

    Base.metadata.create_all(bind=engine)
    _drop_dead_columns()


@contextmanager
def session_scope() -> Iterator[Session]:
    """A transactional session for background jobs and scripts."""
    session = SessionLocal()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()


def get_session() -> Iterator[Session]:
    """FastAPI dependency: one session per request, committed by the endpoint."""
    session = SessionLocal()
    try:
        yield session
    finally:
        session.close()