"""
SQLAlchemy database setup and session management.
Provides async database connectivity for the tempering machine control system.
"""

from contextlib import asynccontextmanager
from typing import AsyncGenerator

from sqlalchemy import create_engine, MetaData, event
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy.pool import StaticPool

from .config import settings


# Database metadata and base model
metadata = MetaData(
    naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_%(constraint_name)s",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s"
    }
)

Base = declarative_base(metadata=metadata)


class DatabaseManager:
    """Database connection and session manager."""
    
    def __init__(self):
        self.engine = None
        self.async_engine = None
        self.session_factory = None
        self.async_session_factory = None
    
    def init_sync_db(self) -> None:
        """Initialize synchronous database connection."""
        database_url = settings.database.url
        
        # Handle SQLite-specific configuration
        if database_url.startswith("sqlite"):
            connect_args = {
                "check_same_thread": False,
                "timeout": 20,
                "isolation_level": None,  # Autocommit mode
            }
            
            self.engine = create_engine(
                database_url,
                echo=settings.database.echo,
                connect_args=connect_args,
                poolclass=StaticPool,
                pool_pre_ping=settings.database.pool_pre_ping,
                pool_recycle=settings.database.pool_recycle,
            )
        else:
            # PostgreSQL or other databases
            self.engine = create_engine(
                database_url,
                echo=settings.database.echo,
                pool_size=settings.database.pool_size,
                max_overflow=settings.database.max_overflow,
                pool_pre_ping=settings.database.pool_pre_ping,
                pool_recycle=settings.database.pool_recycle,
            )
        
        self.session_factory = sessionmaker(
            autocommit=False,
            autoflush=False,
            bind=self.engine,
        )
        
        # Enable WAL mode for SQLite for better concurrent access
        if database_url.startswith("sqlite"):
            @event.listens_for(self.engine, "connect")
            def set_sqlite_pragma(dbapi_connection, connection_record):
                cursor = dbapi_connection.cursor()
                # Enable WAL mode for better concurrency
                cursor.execute("PRAGMA journal_mode=WAL")
                # Set synchronous mode for better performance
                cursor.execute("PRAGMA synchronous=NORMAL")
                # Enable foreign key constraints
                cursor.execute("PRAGMA foreign_keys=ON")
                # Set busy timeout
                cursor.execute("PRAGMA busy_timeout=30000")
                cursor.close()
    
    def init_async_db(self) -> None:
        """Initialize asynchronous database connection."""
        database_url = settings.database.url
        
        # Convert sync SQLite URL to async
        if database_url.startswith("sqlite"):
            async_database_url = database_url.replace("sqlite://", "sqlite+aiosqlite://")
            connect_args = {
                "check_same_thread": False,
                "timeout": 20,
            }
            
            self.async_engine = create_async_engine(
                async_database_url,
                echo=settings.database.echo,
                connect_args=connect_args,
                poolclass=StaticPool,
                pool_pre_ping=settings.database.pool_pre_ping,
                pool_recycle=settings.database.pool_recycle,
            )
        else:
            # Convert PostgreSQL URL to async
            if database_url.startswith("postgresql://"):
                async_database_url = database_url.replace("postgresql://", "postgresql+asyncpg://")
            else:
                async_database_url = database_url
            
            self.async_engine = create_async_engine(
                async_database_url,
                echo=settings.database.echo,
                pool_size=settings.database.pool_size,
                max_overflow=settings.database.max_overflow,
                pool_pre_ping=settings.database.pool_pre_ping,
                pool_recycle=settings.database.pool_recycle,
            )
        
        self.async_session_factory = async_sessionmaker(
            self.async_engine,
            class_=AsyncSession,
            autocommit=False,
            autoflush=False,
        )
    
    def create_tables(self) -> None:
        """Create all database tables."""
        if not self.engine:
            raise RuntimeError("Database engine not initialized")
        Base.metadata.create_all(bind=self.engine)
    
    async def create_tables_async(self) -> None:
        """Create all database tables asynchronously."""
        if not self.async_engine:
            raise RuntimeError("Async database engine not initialized")
        async with self.async_engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
    
    def get_session(self):
        """Get a synchronous database session."""
        if not self.session_factory:
            raise RuntimeError("Database session factory not initialized")
        return self.session_factory()
    
    @asynccontextmanager
    async def get_async_session(self) -> AsyncGenerator[AsyncSession, None]:
        """Get an asynchronous database session."""
        if not self.async_session_factory:
            raise RuntimeError("Async database session factory not initialized")
        
        async with self.async_session_factory() as session:
            try:
                yield session
            except Exception:
                await session.rollback()
                raise
            finally:
                await session.close()
    
    async def close_async(self) -> None:
        """Close async database connections."""
        if self.async_engine:
            await self.async_engine.dispose()
    
    def close_sync(self) -> None:
        """Close synchronous database connections."""
        if self.engine:
            self.engine.dispose()


# Global database manager instance
db_manager = DatabaseManager()


def init_database() -> None:
    """Initialize database connections."""
    db_manager.init_sync_db()
    db_manager.init_async_db()


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """Dependency for FastAPI to get database session."""
    async with db_manager.get_async_session() as session:
        yield session


def get_sync_db():
    """Get synchronous database session for non-async contexts."""
    return db_manager.get_session()


async def create_tables():
    """Create all database tables."""
    await db_manager.create_tables_async()


async def close_database():
    """Close database connections."""
    await db_manager.close_async()
    db_manager.close_sync()