"""
数据库配置与会话管理
"""

from typing import AsyncGenerator, Generator
from sqlalchemy.ext.asyncio import AsyncSession, AsyncEngine, create_async_engine, async_sessionmaker
from sqlalchemy.orm import declarative_base, sessionmaker, Session
from sqlalchemy.pool import NullPool
from sqlalchemy import create_engine, event
import logging

from app.core.config import settings

logger = logging.getLogger(__name__)

# 创建异步引擎
engine: AsyncEngine = create_async_engine(
    settings.DATABASE_URL,
    echo=settings.LOG_LEVEL == "DEBUG",
    poolclass=NullPool,  # 使用 NullPool 避免连接池问题
    future=True
)


# 为 SQLite 启用 WAL 模式和设置超时，解决并发访问时的数据库锁定问题
@event.listens_for(engine.sync_engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    """设置 SQLite PRAGMA 参数以提高并发性能"""
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")  # 启用 WAL 模式，允许读写并发
    cursor.execute("PRAGMA busy_timeout=30000")  # 30秒超时，避免立即报错
    cursor.execute("PRAGMA synchronous=NORMAL")  # 平衡性能和安全
    cursor.close()

# 创建异步会话工厂
AsyncSessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autocommit=False,
    autoflush=False
)

# 别名，用于后台任务等需要独立创建会话的场景
async_session_factory = AsyncSessionLocal

# 创建基类
Base = declarative_base()

# 创建同步引擎（用于监控等非异步服务）
# 根据数据库类型转换异步 URL 为同步 URL
def get_sync_database_url(async_url: str) -> str:
    """将异步数据库 URL 转换为同步 URL"""
    if "sqlite+aiosqlite://" in async_url:
        return async_url.replace("sqlite+aiosqlite://", "sqlite://")
    elif "postgresql+asyncpg://" in async_url:
        return async_url.replace("postgresql+asyncpg://", "postgresql://")
    elif "mysql+aiomysql://" in async_url:
        return async_url.replace("mysql+aiomysql://", "mysql+pymysql://")
    return async_url

sync_database_url = get_sync_database_url(settings.DATABASE_URL)
sync_engine = create_engine(
    sync_database_url,
    echo=settings.LOG_LEVEL == "DEBUG",
    poolclass=NullPool
)


# 为同步引擎也启用 SQLite WAL 模式
@event.listens_for(sync_engine, "connect")
def set_sync_sqlite_pragma(dbapi_connection, connection_record):
    """设置同步引擎的 SQLite PRAGMA 参数"""
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA busy_timeout=30000")
    cursor.execute("PRAGMA synchronous=NORMAL")
    cursor.close()


# 创建同步会话工厂
SyncSessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=sync_engine
)


def get_sync_db() -> Generator[Session, None, None]:
    """
    获取同步数据库会话
    用于监控等非异步服务
    """
    db = SyncSessionLocal()
    try:
        yield db
    except Exception as e:
        logger.error(f"Sync database session error: {e}")
        db.rollback()
        raise
    finally:
        db.close()


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """
    获取数据库会话
    用于 FastAPI 依赖注入
    """
    async with AsyncSessionLocal() as session:
        try:
            yield session
        except Exception as e:
            logger.error(f"Database session error: {e}")
            await session.rollback()
            raise
        finally:
            await session.close()


async def init_db() -> None:
    """初始化数据库（创建表）"""
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
        logger.info("Database tables created")


async def close_db() -> None:
    """关闭数据库连接"""
    await engine.dispose()
    logger.info("Database connections closed")