"""
Metrics Repository - PostgreSQL 實作
=====================================
Phase 17 技術債修復: Router 層違規抽取

職責: Metrics 相關 DB 查詢 (AI Success Rate 統計)
設計: 實作 IMetricsRepository Protocol

版本: v1.0
建立: 2026-03-26 (台北時區)
建立者: Claude Code (Phase 17 技術債修復)
"""

from datetime import UTC, datetime, timedelta

import structlog
from sqlalchemy import text

from src.db.base import get_db_context
from src.repositories.interfaces import IMetricsRepository

logger = structlog.get_logger(__name__)


# =============================================================================
# MetricsDBRepository
# =============================================================================


class MetricsDBRepository(IMetricsRepository):
    """
    Metrics Repository - PostgreSQL 實作

    職責:
    - AI Success Rate 統計查詢
    - 趨勢數據查詢 (Sparkline)

    純 CRUD 操作，不含業務邏輯
    業務邏輯請放在 Service 層
    """

    async def get_ai_success_rate(
        self,
        hours: int = 24,
    ) -> tuple[float, int, int]:
        """
        計算 AI 提案成功執行率

        統帥鐵律: 若無數據，回傳真實的 0，嚴禁造假

        Args:
            hours: 統計時間範圍 (小時)

        Returns:
            (success_rate_percent, executed_count, total_count)
        """
        try:
            async with get_db_context() as session:
                # 2026-03-31 ogt: asyncpg 需要 datetime 物件，不能用字串
                cutoff = datetime.now(UTC) - timedelta(hours=hours)

                # Query: 統計 executed vs total (approved + executed + execution_failed)
                # 2026-05-06 ogt + Codex:
                # approval_records.status 目前實際寫入的是大寫 enum
                # (APPROVED / EXECUTION_SUCCESS / EXECUTION_FAILED)。舊查詢只看
                # lowercase executed，導致 AI Success 在報表層永遠趨近 0。
                query = text("""
                    SELECT
                        COUNT(CASE WHEN UPPER(status::text) = 'EXECUTION_SUCCESS' THEN 1 END) as executed_count,
                        COUNT(*) as total_count
                    FROM approval_records
                    WHERE created_at >= :cutoff
                    AND UPPER(status::text) IN ('APPROVED', 'EXECUTION_SUCCESS', 'EXECUTION_FAILED')
                """)

                result = await session.execute(query, {"cutoff": cutoff})
                row = result.fetchone()

                if row and row.total_count > 0:
                    executed = row.executed_count or 0
                    total = row.total_count
                    success_rate = (executed / total) * 100
                else:
                    executed = 0
                    total = 0
                    success_rate = 0.0

                logger.debug(
                    "ai_success_rate_queried",
                    hours=hours,
                    executed=executed,
                    total=total,
                    rate=success_rate,
                )

                return success_rate, executed, total

        except Exception as e:
            logger.exception(
                "ai_success_rate_query_error",
                hours=hours,
                error=str(e),
            )
            # 統帥鐵律: 發生錯誤時回傳真實的 0，非假數據
            return 0.0, 0, 0

    async def get_ai_success_trend(
        self,
        hours: int = 24,
        points: int = 10,
    ) -> list[float]:
        """
        取得 AI 成功率趨勢 (Sparkline 用)

        統帥鐵律: 若無數據，回傳真實的 [0.0] * points，嚴禁造假

        Args:
            hours: 統計時間範圍 (小時)
            points: 趨勢點數量

        Returns:
            list[float]: 每小時成功率列表 (由舊到新)
        """
        try:
            async with get_db_context() as session:
                # 2026-03-31 ogt: asyncpg 需要 datetime 物件，不能用字串
                cutoff = datetime.now(UTC) - timedelta(hours=hours)

                # Trend: 過去 N 個時間點的成功率 (每小時一點)
                # 2026-03-31 ogt: PostgreSQL 用 date_trunc 而非 strftime
                trend_query = text("""
                    SELECT
                        date_trunc('hour', created_at) as hour_bucket,
                        COUNT(CASE WHEN UPPER(status::text) = 'EXECUTION_SUCCESS' THEN 1 END) * 100.0 /
                        NULLIF(COUNT(*), 0) as hourly_rate
                    FROM approval_records
                    WHERE created_at >= :cutoff
                    AND UPPER(status::text) IN ('APPROVED', 'EXECUTION_SUCCESS', 'EXECUTION_FAILED')
                    GROUP BY hour_bucket
                    ORDER BY hour_bucket DESC
                    LIMIT :limit
                """)

                result = await session.execute(
                    trend_query,
                    {"cutoff": cutoff, "limit": points},
                )
                rows = result.fetchall()

                if rows:
                    # 由舊到新排列
                    trend_values = [float(r.hourly_rate or 0) for r in reversed(rows)]
                    # 如果點數不足，前面補 0
                    if len(trend_values) < points:
                        trend_values = [0.0] * (points - len(trend_values)) + trend_values
                else:
                    trend_values = [0.0] * points

                logger.debug(
                    "ai_success_trend_queried",
                    hours=hours,
                    points=points,
                    actual_points=len(trend_values),
                )

                return trend_values

        except Exception as e:
            logger.exception(
                "ai_success_trend_query_error",
                hours=hours,
                points=points,
                error=str(e),
            )
            # 統帥鐵律: 發生錯誤時回傳真實的 0，非假數據
            return [0.0] * points


# =============================================================================
# Singleton
# =============================================================================

_metrics_repository: MetricsDBRepository | None = None


def get_metrics_repository() -> MetricsDBRepository:
    """取得 MetricsDBRepository 實例 (Singleton)"""
    global _metrics_repository
    if _metrics_repository is None:
        _metrics_repository = MetricsDBRepository()
    return _metrics_repository
