app/db/database.py

import os

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

from app.config import settings
from app.logging_config import get_logger

logger = get_logger(__name__)

# ── Engine factory — SQLite or Postgres, driven entirely by DATABASE_URL ──────
# SQLite  : sqlite+aiosqlite:////data/smart_scheduling.db   (default / fallback)
# Postgres: postgresql+psycopg_async://user:pass@host/db    (set DATABASE_URL in .env / HF Secrets)
#
# To switch database: change DATABASE_URL in .env (or HF Secrets).
# No code change needed — the URL prefix selects the backend automatically.


def _build_engine(url: str):
    if url.startswith("sqlite"):
        return create_async_engine(
            url,
            echo=False,
            connect_args={"check_same_thread": False},
        )
    # Postgres (Neon / standard) — psycopg_async (psycopg3)
    return create_async_engine(
        url,
        echo=False,
        pool_size=2,
        max_overflow=3,
        pool_pre_ping=True,
        pool_recycle=300,     # recycle every 5 min — Neon closes idle connections
        connect_args={
            "sslmode":         "require",
            "connect_timeout": 30,
        },
    )


_is_sqlite = settings.database_url.startswith("sqlite")
engine = _build_engine(settings.database_url)
AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)


class Base(DeclarativeBase):
    pass


async def init_db():
    import app.models  # noqa: F401 — registers all models with Base.metadata

    if _is_sqlite:
        file_path = settings.database_url.split("///", 1)[-1]
        parent = os.path.dirname(file_path)
        if parent:
            os.makedirs(parent, exist_ok=True)
            logger.info("SQLite directory ready: %s", parent)
    else:
        logger.info("Using Postgres: %s", settings.database_url.split("@")[-1])

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


async def get_db() -> AsyncSession:
    async with AsyncSessionLocal() as session:
        yield session