from collections.abc import Generator

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

from app.config import get_settings


class Base(DeclarativeBase):
    pass


def _create_engine():
    settings = get_settings()
    url = settings.database_url
    connect_args = {}
    engine_kwargs: dict = {"pool_pre_ping": True}
    if url.startswith("sqlite"):
        connect_args["check_same_thread"] = False
    else:
        engine_kwargs.update(
            pool_size=settings.db_pool_size,
            max_overflow=settings.db_max_overflow,
            pool_timeout=settings.db_pool_timeout,
        )
        if settings.db_pool_recycle > 0:
            engine_kwargs["pool_recycle"] = settings.db_pool_recycle
    return create_engine(url, connect_args=connect_args, **engine_kwargs)


engine = _create_engine()
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


def release_db_connection(db: Session) -> None:
    """Return a request session to the pool before slow async I/O (SMTP, uploads)."""
    db.close()


def get_db() -> Generator[Session, None, None]:
    db = SessionLocal()
    try:
        yield db
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def sync_users_email_verified_column(engine) -> None:
    """Add users.email_verified for existing databases (existing rows default to verified)."""
    from sqlalchemy import inspect, text

    url = str(engine.url).lower()
    try:
        insp = inspect(engine)
        if "users" not in insp.get_table_names():
            return
        cols = {c["name"] for c in insp.get_columns("users")}
    except Exception:
        return

    if "email_verified" in cols:
        return

    with engine.begin() as conn:
        if "postgresql" in url:
            conn.execute(
                text(
                    "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN "
                    "NOT NULL DEFAULT true"
                )
            )
        else:
            try:
                conn.execute(
                    text("ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT 1")
                )
            except Exception:
                pass


def sync_user_profile_columns(engine) -> None:
    """Add user profile fields used by the user profile screen."""
    from sqlalchemy import inspect, text

    try:
        insp = inspect(engine)
        if "users" not in insp.get_table_names():
            return
        cols = {c["name"] for c in insp.get_columns("users")}
    except Exception:
        return

    additions = {
        "phone_number": "VARCHAR(32)",
        "address": "VARCHAR(512)",
        "postal_code": "VARCHAR(32)",
        "profile_image": "VARCHAR(512)",
        "otp": "VARCHAR(32)",
        "google_drive_link": "VARCHAR(512)",
    }
    missing = [(name, ddl) for name, ddl in additions.items() if name not in cols]
    if not missing:
        return

    is_pg = engine.dialect.name == "postgresql"
    with engine.begin() as conn:
        for name, ddl in missing:
            if is_pg:
                conn.execute(text(f"ALTER TABLE users ADD COLUMN IF NOT EXISTS {name} {ddl}"))
            else:
                try:
                    conn.execute(text(f"ALTER TABLE users ADD COLUMN {name} {ddl}"))
                except Exception:
                    pass


def sync_users_list_indexes(engine) -> None:
    """Indexes for admin list-users ordering/pagination."""
    from sqlalchemy import text

    is_pg = engine.dialect.name == "postgresql"
    with engine.begin() as conn:
        if is_pg:
            conn.execute(
                text(
                    "CREATE INDEX IF NOT EXISTS ix_users_created_at_id_desc "
                    "ON users (created_at DESC, id DESC)"
                )
            )
        else:
            try:
                conn.execute(
                    text(
                        "CREATE INDEX IF NOT EXISTS ix_users_created_at_id "
                        "ON users (created_at, id)"
                    )
                )
            except Exception:
                pass


def init_db() -> None:
    from app import models  # noqa: F401
    from app import models_admin  # noqa: F401
    from app.programme import models_programme  # noqa: F401

    Base.metadata.create_all(bind=engine)


def sync_user_track_traces_table(engine) -> None:
    from app.models import UserTrackTrace

    UserTrackTrace.__table__.create(bind=engine, checkfirst=True)