"""Database configuration and connection management."""
from typing import Generator

from sqlalchemy import MetaData, create_engine
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker

from app.config import settings


class Base(DeclarativeBase):
    """Base class for SQLAlchemy models."""
    metadata = MetaData()


# Database URL construction
DATABASE_URL = f"oracle+oracledb://{settings.oracle_user}:{settings.oracle_password}@{settings.oracle_dsn}"

# Synchronous engine for Oracle
engine = create_engine(
    DATABASE_URL,
    echo=settings.debug,
    pool_pre_ping=True,
    pool_recycle=3600,
)

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


def get_db() -> Generator[Session, None]:
    """Dependency to get database session."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


class DatabaseService:
    """Database service for health checks and utilities."""

    @staticmethod
    def health_check() -> bool:
        """Check database connectivity."""
        try:
            with SessionLocal() as db:
                db.execute("SELECT 1 FROM DUAL")  # type:ignore[call-overload]
                return True
        except Exception:
            return False