"""
資料庫連線管理
"""

from sqlalchemy import create_engine, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from backend.config import settings
import logging

logger = logging.getLogger(__name__)

# SQLAlchemy 設定
engine = create_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,  # 在 DEBUG 模式下顯示 SQL 語句
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,  # 連線前檢查
    pool_recycle=3600,   # 1小時後回收連線
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_database_session() -> Session:
    """取得資料庫 session"""
    db = SessionLocal()
    try:
        return db
    except Exception as e:
        db.close()
        raise e

def close_database_session(db: Session):
    """關閉資料庫 session"""
    try:
        db.close()
    except Exception as e:
        logger.error(f"關閉資料庫 session 時發生錯誤: {str(e)}")

def test_database_connection() -> bool:
    """測試資料庫連線"""
    try:
        with engine.connect() as connection:
            result = connection.execute(text("SELECT 1"))
            return result.fetchone()[0] == 1
    except Exception as e:
        logger.error(f"資料庫連線測試失敗: {str(e)}")
        return False

# 依賴注入函數，用於 FastAPI
def get_db():
    """FastAPI 依賴注入函數"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()