"""Database engine and declarative base helpers."""

from __future__ import annotations

import os
from pathlib import Path

from sqlalchemy import create_engine, event
from sqlalchemy.engine import Engine
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker


class Base(DeclarativeBase):
    pass


def create_database_engine(database_url: str | None = None, **kwargs) -> Engine:
    """Create a configured SQLAlchemy engine without opening a global session."""
    url = database_url or os.environ.get("DATABASE_URL")
    if not url:
        data_dir = Path(os.environ.get("CROWDSIGHT_DATA_DIR", "backend/uploads"))
        data_dir.mkdir(parents=True, exist_ok=True)
        url = f"sqlite+pysqlite:///{(data_dir / 'crowdsight.db').resolve()}"

    # Normalize bare `postgres://` (dialect alias SQLAlchemy only resolves in
    # some versions) into the explicit psycopg3 dialect. Without this,
    # create_engine raises "NoSuchModuleError: Can't load plugin:
    # sqlalchemy.dialects:postgres" even though psycopg is installed.
    if url.startswith("postgres://"):
        url = url.replace("postgres://", "postgresql+psycopg://", 1)
    elif url.startswith("postgres+pq://"):  # legacy psycopg2 scheme
        url = url.replace("postgres+pq://", "postgresql+psycopg://", 1)

    connect_args = dict(kwargs.pop("connect_args", {}))
    if url.startswith("sqlite"):
        connect_args.setdefault("check_same_thread", False)

    engine = create_engine(
        url,
        future=True,
        pool_pre_ping=True,
        connect_args=connect_args,
        **kwargs,
    )
    if url.startswith("sqlite"):
        @event.listens_for(engine, "connect")
        def _enable_sqlite_foreign_keys(dbapi_connection, _connection_record):
            cursor = dbapi_connection.cursor()
            try:
                cursor.execute("PRAGMA foreign_keys=ON")
            finally:
                cursor.close()

    return engine


def create_session_factory(engine: Engine) -> sessionmaker[Session]:
    """Return a factory; callers own transaction boundaries and commits."""
    return sessionmaker(bind=engine, autoflush=True, expire_on_commit=False)