"""
GovernanceRemediationDispatch Repository
=========================================
Wave 2 D: 治理事件修復派遣 Repository 層

職責: GovernanceRemediationDispatch 的 CRUD 與狀態機操作
設計: 純 async function，不依賴 Session class（對齊 approval_repository.py 風格）

狀態機合法轉換：
  pending    → dispatched | skipped | cancelled
  dispatched → executing  | failed  | cancelled
  executing  → succeeded  | failed  | cancelled
  failed     → pending（僅當 attempt_count < max_attempts，且必須 INSERT 新 row）
  succeeded / cancelled / skipped：terminal（禁止任何轉換）

失敗重試：INSERT 新 row（attempt_count+1），舊 row 永遠保留 failed（審計痕跡）

2026-05-03 ogt + Claude Sonnet 4.6（亞太）: Wave 2 D db-expert spec 實作
"""

from __future__ import annotations

from typing import Any

import structlog
from sqlalchemy import select
from sqlalchemy.exc import IntegrityError

from src.db.base import get_db_context
from src.db.models import GovernanceRemediationDispatch, generate_uuid, taipei_now

logger = structlog.get_logger(__name__)

# =============================================================================
# 狀態機常量
# =============================================================================

# 合法轉換表：from_status → set(to_status)
_VALID_TRANSITIONS: dict[str, set[str]] = {
    "pending":    {"dispatched", "skipped", "cancelled"},
    "dispatched": {"executing",  "failed",  "cancelled"},
    "executing":  {"succeeded",  "failed",  "cancelled"},
    # failed → pending 由 record_failure_and_retry 負責（INSERT 新 row）
    # succeeded / cancelled / skipped：terminal，無合法後繼
}

TERMINAL_STATUSES: frozenset[str] = frozenset({"succeeded", "cancelled", "skipped"})
ACTIVE_STATUSES: frozenset[str] = frozenset({"pending", "dispatched", "executing"})


# =============================================================================
# 自訂例外
# =============================================================================

class DispatchAlreadyActive(Exception):
    """同一 governance_event_id 已有活躍 dispatch（partial unique index 違反）"""


class InvalidStatusTransition(Exception):
    """狀態機轉換不合法"""


class DispatchNotFound(Exception):
    """找不到指定 dispatch_id 的記錄"""


# =============================================================================
# Repository 函數
# =============================================================================

async def create_dispatch(
    event_id: str,
    event_type: str,
    executor_type: str,
    *,
    playbook_id: str | None = None,
    incident_id: str | None = None,
    approval_id: str | None = None,
    decision_context: dict[str, Any] | None = None,
    max_attempts: int = 3,
    attempt_count: int = 0,
    created_by: str | None = "governance_dispatcher",
) -> GovernanceRemediationDispatch:
    """建立新的 pending dispatch row。

    同一 event_id 同一時間只能有一筆活躍 dispatch。
    若違反 partial unique index (ux_grd_one_active_per_event)，
    拋出 DispatchAlreadyActive。

    Args:
        event_id: 關聯的 ai_governance_events.id
        event_type: 治理事件類型（governance_event_type enum value）
        executor_type: 執行器類型（如 playbook_executor / manual）
        playbook_id: 可選，關聯 playbooks.playbook_id
        incident_id: 可選，關聯 incidents.incident_id
        approval_id: 可選，關聯 approval_records.id
        decision_context: 決策上下文 dict（服務層用 DecisionContextV1 驗證後傳入）
        max_attempts: 最大重試次數（預設 3）
        attempt_count: 本 row 的嘗試計數（重試 INSERT 時帶入上筆 +1）
        created_by: 建立者標識

    Returns:
        新建立的 GovernanceRemediationDispatch ORM 物件

    Raises:
        DispatchAlreadyActive: 同 event_id 已有 pending/dispatched/executing row
    """
    async with get_db_context() as db:
        row = GovernanceRemediationDispatch(
            id=generate_uuid(),
            governance_event_id=event_id,
            event_type=event_type,
            dispatch_status="pending",
            playbook_id=playbook_id,
            incident_id=incident_id,
            approval_id=approval_id,
            decision_context=decision_context or {},
            executor_type=executor_type,
            attempt_count=attempt_count,
            max_attempts=max_attempts,
            dispatched_at=taipei_now(),
            created_by=created_by,
        )
        db.add(row)
        try:
            await db.flush()
            await db.refresh(row)
        except IntegrityError as exc:
            await db.rollback()
            if "ux_grd_one_active_per_event" in str(exc.orig):
                raise DispatchAlreadyActive(
                    f"event_id={event_id} 已有活躍 dispatch（pending/dispatched/executing）"
                ) from exc
            raise

        logger.info(
            "dispatch_created",
            dispatch_id=row.id,
            event_id=event_id,
            event_type=event_type,
            executor_type=executor_type,
        )
        return row


async def get_active_for_event(
    event_id: str,
) -> GovernanceRemediationDispatch | None:
    """取得指定事件當前活躍的 dispatch（pending / dispatched / executing）。

    Args:
        event_id: ai_governance_events.id

    Returns:
        活躍 dispatch row，若無則 None
    """
    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.governance_event_id == event_id)
            .where(GovernanceRemediationDispatch.dispatch_status.in_(list(ACTIVE_STATUSES)))
            .order_by(GovernanceRemediationDispatch.dispatched_at.desc())
            .limit(1)
        )
        return result.scalar_one_or_none()


async def transition_status(
    dispatch_id: str,
    from_status: str,
    to_status: str,
    *,
    last_error: str | None = None,
) -> GovernanceRemediationDispatch:
    """執行狀態機轉換（驗證 from_status 合法後更新）。

    注意：failed → pending 的重試路徑應使用 record_failure_and_retry()，
    不應直接呼叫本函數（重試需要 INSERT 新 row）。

    Args:
        dispatch_id: governance_remediation_dispatch.id
        from_status: 預期的當前狀態（不符則拋 InvalidStatusTransition）
        to_status: 目標狀態
        last_error: 失敗時的錯誤訊息（僅 to_status=failed 時有意義）

    Returns:
        更新後的 GovernanceRemediationDispatch ORM 物件

    Raises:
        DispatchNotFound: 找不到 dispatch_id
        InvalidStatusTransition: 狀態轉換不合法或當前狀態與 from_status 不符
    """
    # 驗證轉換合法性
    allowed = _VALID_TRANSITIONS.get(from_status, set())
    if to_status not in allowed:
        raise InvalidStatusTransition(
            f"不允許的狀態轉換: {from_status!r} → {to_status!r}。"
            f"from_status={from_status!r} 的合法後繼: {allowed}"
        )

    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.id == dispatch_id)
        )
        row = result.scalar_one_or_none()

        if row is None:
            raise DispatchNotFound(f"dispatch_id={dispatch_id!r} 不存在")

        current = row.dispatch_status
        if current != from_status:
            raise InvalidStatusTransition(
                f"dispatch_id={dispatch_id!r} 當前狀態 {current!r} 與預期 {from_status!r} 不符"
            )

        row.dispatch_status = to_status

        if to_status == "executing":
            row.started_at = taipei_now()
        if to_status in TERMINAL_STATUSES or to_status == "failed":
            row.completed_at = taipei_now()
        if last_error is not None:
            row.last_error = last_error

        await db.flush()
        await db.refresh(row)

        logger.info(
            "dispatch_status_transitioned",
            dispatch_id=dispatch_id,
            from_status=from_status,
            to_status=to_status,
        )
        return row


async def record_failure_and_retry(
    dispatch_id: str,
    error: str,
) -> GovernanceRemediationDispatch | None:
    """記錄失敗並決定是否重試。

    策略：
    1. 將舊 row 標記為 failed（completed_at 填入，last_error 填入）
    2. 若 attempt_count + 1 < max_attempts，INSERT 新 pending row（attempt_count+1）
    3. 若已達上限，返回 None（不再重試）

    舊 row 永遠保留 failed（審計痕跡），不改 status。

    Args:
        dispatch_id: 當前失敗的 dispatch row id
        error: 錯誤訊息

    Returns:
        新建立的 pending retry row，若已達重試上限則 None

    Raises:
        DispatchNotFound: 找不到 dispatch_id
        InvalidStatusTransition: 舊 row 狀態不是 executing 或 dispatched
    """
    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.id == dispatch_id)
        )
        row = result.scalar_one_or_none()

        if row is None:
            raise DispatchNotFound(f"dispatch_id={dispatch_id!r} 不存在")

        if row.dispatch_status not in ("executing", "dispatched"):
            raise InvalidStatusTransition(
                f"record_failure_and_retry 只能對 executing/dispatched 狀態操作，"
                f"當前狀態: {row.dispatch_status!r}"
            )

        # Step 1: 標記舊 row 為 failed（審計痕跡）
        row.dispatch_status = "failed"
        row.last_error = error
        row.completed_at = taipei_now()
        await db.flush()

        next_attempt = row.attempt_count + 1
        if next_attempt >= row.max_attempts:
            # 已達上限，不再重試
            logger.warning(
                "dispatch_failure_max_attempts_reached",
                dispatch_id=dispatch_id,
                attempt_count=row.attempt_count,
                max_attempts=row.max_attempts,
            )
            return None

        # Step 2: INSERT 新 pending row（保留 FK 關聯）
        new_row = GovernanceRemediationDispatch(
            id=generate_uuid(),
            governance_event_id=row.governance_event_id,
            event_type=row.event_type,
            dispatch_status="pending",
            playbook_id=row.playbook_id,
            incident_id=row.incident_id,
            approval_id=row.approval_id,
            decision_context=row.decision_context,
            executor_type=row.executor_type,
            attempt_count=next_attempt,
            max_attempts=row.max_attempts,
            dispatched_at=taipei_now(),
            created_by=row.created_by,
        )
        db.add(new_row)

        try:
            await db.flush()
            await db.refresh(new_row)
        except IntegrityError as exc:
            await db.rollback()
            if "ux_grd_one_active_per_event" in str(exc.orig):
                raise DispatchAlreadyActive(
                    f"retry INSERT 失敗：event_id={row.governance_event_id} 已有活躍 dispatch"
                ) from exc
            raise

        logger.info(
            "dispatch_retry_inserted",
            old_dispatch_id=dispatch_id,
            new_dispatch_id=new_row.id,
            attempt_count=next_attempt,
        )
        return new_row


async def list_pending(
    limit: int = 50,
    offset: int = 0,
) -> list[GovernanceRemediationDispatch]:
    """列出所有 pending dispatch（按 dispatched_at DESC）。

    用於 /governance Queue tab 顯示待處理隊列。

    Args:
        limit: 每頁筆數（預設 50）
        offset: 分頁偏移

    Returns:
        按 dispatched_at 倒序排列的 pending dispatch 列表
    """
    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.dispatch_status == "pending")
            .order_by(GovernanceRemediationDispatch.dispatched_at.desc())
            .limit(limit)
            .offset(offset)
        )
        return list(result.scalars().all())


async def list_pending_by_executor(
    executor_type: str,
    *,
    limit: int = 50,
) -> list[GovernanceRemediationDispatch]:
    """列出指定 executor 的 pending dispatch（按 dispatched_at ASC）。

    用於 Hermes / 其他 worker 消費自己的 work item。由 repository 層集中查詢，
    避免 job 直接散落表名與狀態條件。

    Args:
        executor_type: dispatch.executor_type，例如 hermes_kb_growth_healthcheck
        limit: 本輪最多取幾筆，避免 backlog 一次拖垮 worker

    Returns:
        最舊優先的 pending dispatch 列表。
    """
    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.dispatch_status == "pending")
            .where(GovernanceRemediationDispatch.executor_type == executor_type)
            .order_by(GovernanceRemediationDispatch.dispatched_at.asc())
            .limit(limit)
        )
        return list(result.scalars().all())


async def update_decision_context(
    dispatch_id: str,
    decision_context: dict[str, Any],
) -> GovernanceRemediationDispatch:
    """更新 dispatch 的 decision_context，保留同一 row 的 audit trail。

    這只更新 dispatch work item 的讀模型上下文，不修改 immutable
    ai_governance_events，也不代表治理事件已被解決。

    Args:
        dispatch_id: governance_remediation_dispatch.id
        decision_context: 新的 JSONB context

    Returns:
        更新後的 GovernanceRemediationDispatch ORM 物件

    Raises:
        DispatchNotFound: 找不到 dispatch_id
    """
    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.id == dispatch_id)
        )
        row = result.scalar_one_or_none()
        if row is None:
            raise DispatchNotFound(f"dispatch_id={dispatch_id!r} 不存在")

        row.decision_context = decision_context
        await db.flush()
        await db.refresh(row)

        logger.info(
            "dispatch_decision_context_updated",
            dispatch_id=dispatch_id,
            event_id=row.governance_event_id,
            executor_type=row.executor_type,
        )
        return row


async def list_by_event(
    event_id: str,
) -> list[GovernanceRemediationDispatch]:
    """取得指定事件的所有 dispatch 記錄（含歷史失敗）。

    用於 /governance Events tab 展開行顯示完整歷史。
    按 dispatched_at DESC 排序（最新的在前）。

    Args:
        event_id: ai_governance_events.id

    Returns:
        該事件的所有 dispatch rows，含歷史失敗（audit trail）
    """
    async with get_db_context() as db:
        result = await db.execute(
            select(GovernanceRemediationDispatch)
            .where(GovernanceRemediationDispatch.governance_event_id == event_id)
            .order_by(GovernanceRemediationDispatch.dispatched_at.desc())
        )
        return list(result.scalars().all())


# =============================================================================
# Singleton（對齊 approval_repository.py 模式）
# =============================================================================
# 本模組以 module-level 函數提供介面，不使用 class 封裝。
# 若需要 DI 注入，直接 import 函數即可。
