"""
Audit Log Repository - Phase 17 P0 Router 層違規修復
====================================================

封裝 AuditLog 的資料庫操作，消除 Router 層直接執行 SQL。

符合 leWOOOgo 積木化規範:
- Router -> Service -> Repository -> DB
"""

from datetime import timedelta
from typing import Any

import structlog
from sqlalchemy import func, select

from src.db.base import get_db_context
from src.db.models import AuditLog, AutoRepairExecution

logger = structlog.get_logger(__name__)


class AuditLogRepository:
    """
    AuditLog Repository

    封裝 AuditLog 表的 CRUD 操作
    """

    async def list_logs(
        self,
        page: int = 1,
        page_size: int = 20,
        success_only: bool | None = None,
        namespace: str | None = None,
    ) -> tuple[list[AuditLog], int]:
        """
        分頁查詢 AuditLog

        Args:
            page: 頁碼
            page_size: 每頁數量
            success_only: 只顯示成功/失敗
            namespace: 篩選 namespace

        Returns:
            (logs, total_count)
        """
        async with get_db_context() as db:
            # 建立基本查詢
            query = select(AuditLog).order_by(AuditLog.created_at.desc())
            count_query = select(func.count(AuditLog.id))

            # 篩選條件
            if success_only is not None:
                query = query.where(AuditLog.success == success_only)
                count_query = count_query.where(AuditLog.success == success_only)

            if namespace:
                query = query.where(AuditLog.namespace == namespace)
                count_query = count_query.where(AuditLog.namespace == namespace)

            # 總數
            total_result = await db.execute(count_query)
            total = total_result.scalar() or 0

            # 分頁
            offset = (page - 1) * page_size
            query = query.offset(offset).limit(page_size)

            result = await db.execute(query)
            logs = list(result.scalars().all())

            return logs, total

    async def get_by_id(self, log_id: str) -> AuditLog | None:
        """
        根據 ID 取得 AuditLog

        Args:
            log_id: 日誌 ID

        Returns:
            AuditLog | None
        """
        async with get_db_context() as db:
            result = await db.execute(
                select(AuditLog).where(AuditLog.id == log_id)
            )
            return result.scalar_one_or_none()

    async def get_stats(self, since_hours: int = 24) -> dict[str, Any]:
        """
        取得統計資訊

        Args:
            since_hours: 統計最近幾小時 (預設 24)

        Returns:
            統計資訊字典
        """
        from src.utils.timezone import now_taipei

        async with get_db_context() as db:
            # 總數
            total_result = await db.execute(select(func.count(AuditLog.id)))
            total = total_result.scalar() or 0

            # 成功數
            success_result = await db.execute(
                select(func.count(AuditLog.id)).where(AuditLog.success.is_(True))
            )
            success_count = success_result.scalar() or 0

            # 平均執行時間
            avg_result = await db.execute(
                select(func.avg(AuditLog.execution_duration_ms)).where(
                    AuditLog.execution_duration_ms.isnot(None)
                )
            )
            avg_duration = avg_result.scalar()

            # 按操作類型統計
            op_result = await db.execute(
                select(
                    AuditLog.operation_type,
                    func.count(AuditLog.id),
                ).group_by(AuditLog.operation_type)
            )
            by_operation = {str(row[0]): row[1] for row in op_result.all()}

            # 按 namespace 統計
            ns_result = await db.execute(
                select(
                    AuditLog.namespace,
                    func.count(AuditLog.id),
                ).group_by(AuditLog.namespace)
            )
            by_namespace = {str(row[0]): row[1] for row in ns_result.all()}

            # 最近 24 小時
            since = now_taipei() - timedelta(hours=since_hours)
            last24_result = await db.execute(
                select(func.count(AuditLog.id)).where(AuditLog.created_at >= since)
            )
            last24_count = last24_result.scalar() or 0

            return {
                "total": total,
                "success_count": success_count,
                "failure_count": total - success_count,
                "success_rate": round((success_count / total * 100) if total > 0 else 0, 2),
                "avg_duration_ms": round(avg_duration, 2) if avg_duration else None,
                "by_operation_type": by_operation,
                "by_namespace": by_namespace,
                "last_24h_count": last24_count,
            }


# =============================================================================
# AutoRepairExecutionRepository
# 2026-04-08 Claude Code: 統帥指令「所有操作都必須被記錄，寫入資料庫」
# =============================================================================


class AutoRepairExecutionRepository:
    """自動修復執行記錄 Repository"""

    async def create(
        self,
        incident_id: str,
        playbook_id: str,
        playbook_name: str,
        success: bool,
        executed_steps: list[str],
        error_message: str | None = None,
        triggered_by: str = "auto_repair",
        similarity_score: float | None = None,
        risk_level: str | None = None,
        execution_time_ms: int | None = None,
    ) -> AutoRepairExecution:
        """寫入一筆自動修復執行記錄"""
        async with get_db_context() as db:
            record = AutoRepairExecution(
                incident_id=incident_id,
                playbook_id=playbook_id,
                playbook_name=playbook_name,
                success=success,
                executed_steps=executed_steps,
                error_message=error_message,
                triggered_by=triggered_by,
                similarity_score=similarity_score,
                risk_level=risk_level,
                execution_time_ms=execution_time_ms,
            )
            db.add(record)
            await db.flush()
            await db.refresh(record)
            return record

    async def list_by_incident(self, incident_id: str) -> list[AutoRepairExecution]:
        """查詢某 incident 的所有修復記錄"""
        async with get_db_context() as db:
            result = await db.execute(
                select(AutoRepairExecution)
                .where(AutoRepairExecution.incident_id == incident_id)
                .order_by(AutoRepairExecution.created_at.desc())
            )
            return list(result.scalars().all())

    async def get_stats(self, since_hours: int = 24) -> dict[str, Any]:
        """統計最近 N 小時的自動修復執行情況"""
        from datetime import timedelta
        from src.utils.timezone import now_taipei

        since = now_taipei() - timedelta(hours=since_hours)
        async with get_db_context() as db:
            total_r = await db.execute(
                select(func.count(AutoRepairExecution.id))
                .where(AutoRepairExecution.created_at >= since)
            )
            total = total_r.scalar() or 0

            success_r = await db.execute(
                select(func.count(AutoRepairExecution.id))
                .where(AutoRepairExecution.created_at >= since)
                .where(AutoRepairExecution.success.is_(True))
            )
            success_count = success_r.scalar() or 0

            return {
                "total": total,
                "success_count": success_count,
                "failure_count": total - success_count,
                "success_rate": round(success_count / total * 100, 1) if total > 0 else 0,
                "since_hours": since_hours,
            }


_auto_repair_execution_repo: AutoRepairExecutionRepository | None = None


def get_auto_repair_execution_repository() -> AutoRepairExecutionRepository:
    """取得 AutoRepairExecutionRepository 實例 (Singleton)"""
    global _auto_repair_execution_repo
    if _auto_repair_execution_repo is None:
        _auto_repair_execution_repo = AutoRepairExecutionRepository()
    return _auto_repair_execution_repo


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

_audit_log_repository: AuditLogRepository | None = None


def get_audit_log_repository() -> AuditLogRepository:
    """取得 AuditLogRepository 實例 (Singleton)"""
    global _audit_log_repository
    if _audit_log_repository is None:
        _audit_log_repository = AuditLogRepository()
    return _audit_log_repository
