import os
import logging
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import declarative_base
from sqlalchemy.exc import SQLAlchemyError
from dotenv import load_dotenv

# Setup logger
logger = logging.getLogger("app.database")
logger.setLevel(logging.INFO)

# Load .env variables
load_dotenv()

# Ensure your DATABASE_URL is in correct asyncpg format
DATABASE_URL = os.getenv("DATABASE_URL")

if not DATABASE_URL:
    logger.warning("DATABASE_URL not found in environment. Using default SQLite for development.")
    DATABASE_URL = "sqlite+aiosqlite:///./dubsway_dev.db"

# Create the async engine with better configuration
engine = create_async_engine(
    DATABASE_URL, 
    echo=False,  # Set to True for debugging
    future=True,
    pool_pre_ping=True,  # Verify connections before use
    pool_recycle=3600,   # Recycle connections every hour
    pool_size=10,        # Connection pool size
    max_overflow=20      # Max overflow connections
)

# Session factory using async_sessionmaker (recommended for SQLAlchemy 2.0+)
AsyncSessionLocal = async_sessionmaker(
    bind=engine, 
    class_=AsyncSession, 
    expire_on_commit=False,
    autocommit=False,
    autoflush=False
)

# Base class for models
Base = declarative_base()

# Dependency for routes to get the async session
async def get_db():
    """Dependency to get database session for FastAPI routes"""
    async with AsyncSessionLocal() as session:
        try:
            yield session
        except SQLAlchemyError as e:
            logger.error(f"Database error: {e}")
            await session.rollback()
            raise
        finally:
            await session.close()

async def init_db():
    """Initialize database tables"""
    try:
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
        logger.info("✅ Database tables created successfully")
    except Exception as e:
        logger.error(f"❌ Failed to initialize database: {e}")
        raise

async def close_db():
    """Close database connections"""
    try:
        await engine.dispose()
        logger.info("✅ Database connections closed")
    except Exception as e:
        logger.error(f"❌ Error closing database: {e}")
        raise