"""
Database session configuration for SQLAlchemy 2.0+
"""

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session, Session
from src.core.config import settings
from typing import Generator
from contextlib import contextmanager
import traceback
import logging

logger = logging.getLogger(__name__)

# Creates connection to PostgreSQL with SQLAlchemy 2.0 configurations
engine = create_engine(
    settings.getDbConnectionUri(),
    pool_pre_ping=True,
    pool_size=settings.POOL_SIZE,
    max_overflow=settings.MAX_POOL_OVERFLOW,
    echo=settings.SQL_ECHO,  # Enable SQL logging based on settings
    future=True,  # Use SQLAlchemy 2.0 style
)

# Create a local session maker to interact with the db via ORM
SessionLocal = sessionmaker(
    bind=engine,
    autocommit=False,
    autoflush=False,
    expire_on_commit=False  # Keep objects usable after commit
)

def get_session() -> Generator[Session, None, None]:
    """
    Dependency function that yields a database session.
    
    Yields:
        Database session
    """
    db = SessionLocal()
    try:
        yield db
    except Exception as e:
        logger.error(f"Database session error: {e}")
        db.rollback()
        raise
    finally:
        db.close()


@contextmanager
def SessionCelery() -> Generator[Session, None, None]:
    """
    Context manager for Celery tasks that need database access.
    
    Yields:
        Scoped database session for Celery tasks
    """
    db = scoped_session(SessionLocal)
    try:
        yield db
        db.commit()
    except Exception as e:
        logger.error(f"Celery database session error: {e}")
        logger.error(f"Traceback: {traceback.format_exc()}")
        db.rollback()
        raise
    finally:
        db.remove()  # Use remove() for scoped sessions


# Legacy alias for backward compatibility
get_db = get_session


# Import SQLAlchemy Models for registration with metadata
# These imports ensure models are registered with SQLAlchemy's metadata
# Tables will be created and managed through Alembic migrations
def register_models():
    """
    Register SQLAlchemy models with metadata.
    This ensures all models are loaded and available for Alembic migrations.
    Tables should be created/managed through Alembic, not create_all().
    """
    import src.apps.auth.models
    import src.apps.base.models
    import src.apps.customers.models
    import src.apps.files.models
    import src.apps.invoices.models
    import src.apps.merchants.models
    import src.apps.notes.models
    import src.apps.payment_methods.models
    import src.apps.payment_requests.models
    import src.apps.products.models
    import src.apps.product_categories.models
    import src.apps.receipts.models
    import src.apps.role_permissions.models
    # import src.apps.settings.models
    import src.apps.site_masters.models
    import src.apps.site_settings.models
    import src.apps.transactions.models