"""
Synchronous database session — used exclusively by Celery worker tasks.

Celery tasks in this project are plain synchronous functions (see
app/workers/tasks.py). Reusing the async engine from app/db/session.py
inside a sync Celery task would require running an event loop inside
a thread that Celery itself manages, which is a well-known source of
"event loop already running" bugs. A dedicated sync engine with psycopg2
sidesteps that entirely, at the cost of maintaining two engines — a
trade worth making for reliability.

The engine is created lazily on first use, not at module import time.
This matters because app/workers/tasks.py is imported by the API
process too (analyze.py calls run_crop_analysis.delay()), and an
eagerly-created engine would force the API process to construct a
working sync DB connection just to import the module — unnecessary
coupling, and a real failure mode if the sync and async DSNs ever need
to differ (e.g. different connection pooling strategy per process role).
"""

from contextlib import contextmanager

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

from app.core.config import get_settings
from app.core.logging_config import get_logger

logger = get_logger(__name__)

_sync_engine = None
_SyncSessionFactory = None


def _get_sync_engine():
    global _sync_engine, _SyncSessionFactory
    if _sync_engine is None:
        settings = get_settings()
        # Convert the async DSN (postgresql+asyncpg://...) to a sync one (postgresql+psycopg2://...)
        sync_dsn = settings.DATABASE_URL.replace("+asyncpg", "+psycopg2")
        _sync_engine = create_engine(
            sync_dsn,
            pool_size=settings.DB_POOL_SIZE,
            max_overflow=settings.DB_MAX_OVERFLOW,
            echo=settings.DB_ECHO,
            pool_pre_ping=True,
        )
        _SyncSessionFactory = sessionmaker(bind=_sync_engine, expire_on_commit=False)
    return _SyncSessionFactory


@contextmanager
def sync_db_session():
    session_factory = _get_sync_engine()
    session: Session = session_factory()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()