"""Database engine and session utilities for persistence operations."""

from collections.abc import Generator

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session, declarative_base, sessionmaker

from app.core.config import get_settings


Base = declarative_base()


settings = get_settings()
engine = create_engine(settings.database_url, pool_pre_ping=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False, expire_on_commit=False)


def get_session() -> Generator[Session, None, None]:
    """Provides a transactional database session for FastAPI request handling."""

    session = SessionLocal()
    try:
        yield session
    finally:
        session.close()


def init_db() -> None:
    """Initializes all ORM tables and search-related database extensions/indexes."""

    from app import models  # noqa: F401

    Base.metadata.create_all(bind=engine)
    with engine.begin() as connection:
        connection.execute(text("CREATE EXTENSION IF NOT EXISTS pg_trgm"))
        connection.execute(
            text(
                """
                CREATE INDEX IF NOT EXISTS idx_documents_text_search
                ON documents
                USING GIN (
                    to_tsvector(
                        'simple',
                        coalesce(original_filename, '') || ' ' ||
                        coalesce(logical_path, '') || ' ' ||
                        coalesce(extracted_text, '')
                    )
                )
                """
            )
        )
        connection.execute(text("CREATE INDEX IF NOT EXISTS idx_documents_sha256 ON documents (sha256)"))