import logging

from typing import AsyncGenerator
from sqlalchemy.engine import URL
from utils.config import Config 
from sqlalchemy.orm import declarative_base
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

logger = logging.getLogger(__name__)

logger.info("Inicializando configuração do DataLake (MySQL)...")
try:
    config = Config().config('dataLake')
    logger.debug(f"Config DataLake recebida: host={config.get('host')}, database={config.get('database')}")

    DB_USER = config.get('user')
    DB_PASSWORD = config.get('password')
    DB_HOST = config.get('host')
    DB_PORT = config.get('port')
    DB_NAME = config.get('database')

    missing = [k for k, v in {
        'DATALAKE_USER': DB_USER,
        'DATALAKE_PASSWORD': DB_PASSWORD,
        'DATALAKE_HOST': DB_HOST,
        'DATALAKE_PORT': DB_PORT,
        'DATALAKE_DATABASE': DB_NAME,
    }.items() if not v]

    if missing:
        error_msg = f"Variáveis de ambiente ausentes para MySQL: {', '.join(missing)}"
        logger.error(error_msg)
        raise RuntimeError(error_msg)
    
    logger.info("Configuração do DataLake validada com sucesso")
except Exception as e:
    logger.error(f"Erro ao configurar DataLake: {e}", exc_info=True)
    raise

ASYNC_DATABASE_URL = URL.create(
    drivername="mysql+aiomysql",
    username=DB_USER,
    password=DB_PASSWORD,
    host=DB_HOST,
    port=int(DB_PORT),
    database=DB_NAME,
)

async_engine = create_async_engine(
    ASYNC_DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,
    pool_recycle=1800,
    connect_args={"charset": "utf8mb4", "autocommit": False},
)

Base = declarative_base()

AsyncSessionLocal = async_sessionmaker(
    bind=async_engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False,
    autocommit=False,
)

async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionLocal() as session:
        yield session

async def get_mysql_db() -> AsyncGenerator[AsyncSession, None]:
    """Alias para get_db() - retorna sessão MySQL (DataLake/Omie)"""
    async with AsyncSessionLocal() as session:
        yield session

logger.info("Inicializando configuração do SQL Server...")
try:
    sqlserver_config = Config().config('sqlServer')
    logger.debug(f"Config SQL Server recebida: host={sqlserver_config.get('host')}, database={sqlserver_config.get('database')}")

    SQLSERVER_USER = sqlserver_config.get('user')
    SQLSERVER_PASSWORD = sqlserver_config.get('password')
    SQLSERVER_HOST = sqlserver_config.get('host')
    SQLSERVER_PORT = sqlserver_config.get('port', '1433')
    SQLSERVER_DATABASE = sqlserver_config.get('database')

    sqlserver_missing = [k for k, v in {
        'SQLSERVER_USER': SQLSERVER_USER,
        'SQLSERVER_PASSWORD': SQLSERVER_PASSWORD,
        'SQLSERVER_HOST': SQLSERVER_HOST,
        'SQLSERVER_PORT': SQLSERVER_PORT,
        'SQLSERVER_DATABASE': SQLSERVER_DATABASE,
    }.items() if not v]

    if sqlserver_missing:
        error_msg = f"Variáveis de ambiente ausentes para SQL Server: {', '.join(sqlserver_missing)}"
        logger.error(error_msg)
        raise RuntimeError(error_msg)
    
    logger.info("Configuração do SQL Server validada com sucesso")
except Exception as e:
    logger.error(f"Erro ao configurar SQL Server: {e}", exc_info=True)
    raise

ASYNC_SQLSERVER_URL = URL.create(
    drivername="mssql+aioodbc",
    username=SQLSERVER_USER,
    password=SQLSERVER_PASSWORD,
    host=SQLSERVER_HOST,
    port=int(SQLSERVER_PORT),
    database=SQLSERVER_DATABASE,
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "TrustServerCertificate": "yes",
    }
)

sqlserver_async_engine = create_async_engine(
    ASYNC_SQLSERVER_URL,
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,
    pool_recycle=1800,
    echo=False,
)

SqlServerBase = declarative_base()

SqlServerAsyncSessionLocal = async_sessionmaker(
    bind=sqlserver_async_engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False,
    autocommit=False,
)

async def get_sqlserver_db() -> AsyncGenerator[AsyncSession, None]:
    logger.debug("Criando sessão do SQL Server...")
    try:
        async with SqlServerAsyncSessionLocal() as session:
            logger.debug("Sessão do SQL Server criada com sucesso")
            try:
                yield session
            finally:
                logger.debug("Fechando sessão do SQL Server...")
            logger.debug("Sessão do SQL Server fechada com sucesso")
    except Exception as e:
        from fastapi import HTTPException
        if isinstance(e, HTTPException):
            logger.debug(f"HTTPException propagada (comportamento esperado): {e.status_code} - {e.detail}")
            raise
        logger.error(f"Erro ao criar/fechar sessão do SQL Server: {e}", exc_info=True)
        raise