"""Async SQLAlchemy engine/session and sync engine for Celery workers."""
import os
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from app.core.config import settings
from app.models import Base

DB_URL = settings.DATABASE_URL
if DB_URL.startswith("postgres://"):
    DB_URL = DB_URL.replace("postgres://", "postgresql://", 1)

sync_engine = create_engine(DB_URL, pool_pre_ping=True, pool_size=5, max_overflow=10)
SyncSession = sessionmaker(bind=sync_engine, expire_on_commit=False)

async_engine = create_async_engine(DB_URL, pool_pre_ping=True, pool_size=5, max_overflow=10)
AsyncSessionLocal = async_sessionmaker(async_engine, class_=AsyncSession, expire_on_commit=False)


async def init_db() -> None:
    """Create tables (dev convenience; production uses Alembic migrations)."""
    async with async_engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


async def close_engines() -> None:
    await async_engine.dispose()
    sync_engine.dispose()


def check_db_health() -> bool:
    try:
        with sync_engine.connect() as c:
            c.execute(text("SELECT 1"))
        return True
    except Exception:
        return False