"""
Governance Query Service — /governance 頁面 DB 查詢邏輯
======================================================
封裝 3 個 governance endpoint 的資料庫查詢。
Router 層禁直接存取 DB（leWOOOgo 積木化鐵律）。

函式清單:
  query_governance_events(...)  → GovernanceEventsResponse
  query_governance_queue(...)   → GovernanceQueueResponse
  query_governance_summary(...) → GovernanceSummaryResponse

Graceful fallback 規則:
  queue endpoint — governance_remediation_dispatch 表可能尚未建立（Track D 進行中）。
  捕捉 sqlalchemy.exc.ProgrammingError（表不存在）後回傳 table_pending=True 的空列表，
  確保 API 在表建立前不拋 500。

2026-05-02 ogt + Claude Sonnet 4.6 Asia/Taipei
"""

from __future__ import annotations

from datetime import datetime, timedelta, timezone

import structlog
from sqlalchemy import func, select, text
from sqlalchemy.exc import ProgrammingError

from src.db.base import get_db_context
from src.db.models import AiGovernanceEvent
from src.models.governance import (
    DailyCount,
    DispatchItem,
    GovernanceEvent,
    GovernanceEventsResponse,
    GovernanceQueueResponse,
    GovernanceSummaryResponse,
    map_severity,
)
from src.utils.timezone import now_taipei

logger = structlog.get_logger(__name__)

# =============================================================================
# 常數
# =============================================================================

_TAIPEI = timezone(timedelta(hours=8))


# =============================================================================
# helpers
# =============================================================================

def _extract_impact(details: dict) -> str:
    """
    從 details 抽摘要字串，≤80 字。

    優先讀 details["impact"]（dict），取 status + 主要 metric 欄位。
    fallback 到 details 頂層常見欄位。
    """
    impact_block = details.get("impact")
    if isinstance(impact_block, dict):
        parts: list[str] = []
        if "status" in impact_block:
            parts.append(str(impact_block["status"]))
        # 主要 metric 欄位優先順序
        for key in ("metric", "value", "rate", "ratio", "score", "count"):
            if key in impact_block:
                parts.append(f"{key}={impact_block[key]}")
                break
        summary = " ".join(parts)
        return summary[:80] if summary else ""

    # fallback: 頂層常見欄位
    for key in ("message", "reason", "summary", "description"):
        val = details.get(key)
        if isinstance(val, str) and val:
            return val[:80]

    # 最後 fallback: 把 details 第一個 string value 截取
    for val in details.values():
        if isinstance(val, str) and val:
            return val[:80]

    return ""


def _to_governance_event(row: AiGovernanceEvent) -> GovernanceEvent:
    details = row.details or {}
    return GovernanceEvent(
        id=row.id,
        event_type=row.event_type,
        severity=map_severity(row.event_type),
        triggered_at=row.triggered_at,
        resolved=row.resolved,
        resolved_at=row.resolved_at,
        impact=_extract_impact(details),
        details=details,
        remediation=details.get("remediation"),
        dispatch_ids=details.get("dispatch_ids", []),
    )


# =============================================================================
# Endpoint 1: events
# =============================================================================

async def query_governance_events(
    *,
    event_types: list[str] | None = None,
    from_dt: datetime | None = None,
    to_dt: datetime | None = None,
    status: str | None = None,         # "resolved" | "unresolved"
    severity: str | None = None,       # "critical" | "warning" | "info"
    page: int = 1,
    size: int = 20,
) -> GovernanceEventsResponse:
    """
    查詢 ai_governance_events 表，支援多維度過濾與分頁。

    severity 過濾在 Python 層完成（event_type 映射）；
    其他過濾在 SQL 層完成（效能優先）。
    """
    async with get_db_context() as db:
        stmt = select(AiGovernanceEvent)

        if event_types:
            stmt = stmt.where(AiGovernanceEvent.event_type.in_(event_types))

        if from_dt is not None:
            stmt = stmt.where(AiGovernanceEvent.triggered_at >= from_dt)

        if to_dt is not None:
            stmt = stmt.where(AiGovernanceEvent.triggered_at <= to_dt)

        if status == "resolved":
            stmt = stmt.where(AiGovernanceEvent.resolved.is_(True))
        elif status == "unresolved":
            stmt = stmt.where(AiGovernanceEvent.resolved.is_(False))

        stmt = stmt.order_by(AiGovernanceEvent.triggered_at.desc())

        # 取全部結果，severity 在 Python 層過濾（避免 DB 不認識 mapping 邏輯）
        result = await db.execute(stmt)
        all_rows = result.scalars().all()

    events = [_to_governance_event(r) for r in all_rows]

    # severity 過濾（Python 層）
    if severity:
        from src.models.governance import _CRITICAL_TYPES, _WARNING_TYPES

        if severity == "critical":
            events = [e for e in events if e.event_type in _CRITICAL_TYPES]
        elif severity == "warning":
            events = [e for e in events if e.event_type in _WARNING_TYPES]
        elif severity == "info":
            events = [
                e for e in events
                if e.event_type not in _CRITICAL_TYPES and e.event_type not in _WARNING_TYPES
            ]

    total = len(events)
    offset = (page - 1) * size
    page_items = events[offset: offset + size]

    return GovernanceEventsResponse(
        items=page_items,
        total=total,
        page=page,
        size=size,
    )


# =============================================================================
# Endpoint 2: queue
# =============================================================================

async def query_governance_queue(
    *,
    dispatch_status: str = "pending",
    page: int = 1,
    size: int = 20,
) -> GovernanceQueueResponse:
    """
    查詢 governance_remediation_dispatch 表。

    Track D 進行中，表可能尚未建立。
    捕捉 ProgrammingError → 回傳 table_pending=True 的空 response。

    proposed_action 從 decision_context JSONB 抽取（Track D 完成後可改為真實 join）。
    """
    try:
        return await _query_dispatch_table(
            dispatch_status=dispatch_status,
            page=page,
            size=size,
        )
    except ProgrammingError as exc:
        logger.warning(
            "governance_dispatch_table_not_ready",
            error=str(exc),
        )
        return GovernanceQueueResponse(
            items=[],
            total=0,
            page=page,
            size=size,
            table_pending=True,
        )
    except ImportError as exc:
        logger.warning(
            "governance_dispatch_model_not_ready",
            error=str(exc),
        )
        return GovernanceQueueResponse(
            items=[],
            total=0,
            page=page,
            size=size,
            table_pending=True,
        )


async def _query_dispatch_table(
    *,
    dispatch_status: str,
    page: int,
    size: int,
) -> GovernanceQueueResponse:
    """實際查詢 governance_remediation_dispatch 表（不含 graceful fallback）."""
    # 動態 import：Track D 完成前 ORM class 可能不存在
    # 使用 raw SQL 降低 ORM 模型缺失的耦合風險
    sql = text("""
        SELECT
            d.id,
            d.governance_event_id,
            e.event_type,
            d.dispatch_status,
            d.decision_context,
            d.playbook_id,
            d.created_at,
            d.dispatched_at,
            d.completed_at,
            d.operator_note
        FROM governance_remediation_dispatch d
        JOIN ai_governance_events e ON e.id = d.governance_event_id
        WHERE d.dispatch_status = :dispatch_status
        ORDER BY d.created_at DESC
    """)

    count_sql = text("""
        SELECT count(*) AS cnt
        FROM governance_remediation_dispatch
        WHERE dispatch_status = :dispatch_status
    """)

    async with get_db_context() as db:
        count_row = await db.execute(count_sql, {"dispatch_status": dispatch_status})
        total = int(count_row.scalar_one_or_none() or 0)

        rows = await db.execute(
            sql.bindparams(dispatch_status=dispatch_status),
        )
        all_rows = rows.fetchall()

    offset = (page - 1) * size
    page_rows = all_rows[offset: offset + size]

    items: list[DispatchItem] = []
    for row in page_rows:
        decision_ctx: dict = (row.decision_context or {}) if hasattr(row, "decision_context") else {}
        proposed_action = _extract_proposed_action(decision_ctx)

        # playbook_trust: Track D 完成後改為 JOIN playbooks 表取 trust_score
        # 現階段從 decision_context 取 mock 值
        playbook_trust_raw = decision_ctx.get("playbook_trust")
        try:
            playbook_trust = float(playbook_trust_raw) if playbook_trust_raw is not None else None
        except (TypeError, ValueError):
            playbook_trust = None

        items.append(DispatchItem(
            id=str(row.id),
            governance_event_id=str(row.governance_event_id),
            event_type=str(row.event_type),
            dispatch_status=str(row.dispatch_status),
            proposed_action=proposed_action,
            playbook_id=str(row.playbook_id) if row.playbook_id else None,
            playbook_trust=playbook_trust,
            created_at=row.created_at,
            dispatched_at=row.dispatched_at,
            completed_at=row.completed_at,
            operator_note=row.operator_note,
        ))

    return GovernanceQueueResponse(
        items=items,
        total=total,
        page=page,
        size=size,
        table_pending=False,
    )


def _extract_proposed_action(decision_ctx: dict) -> str:
    """
    從 decision_context JSONB 抽取 proposed_action，≤120 字。

    Track D 完成後此函式可改為從真實欄位讀取。
    """
    for key in ("proposed_action", "action", "suggestion", "description", "summary"):
        val = decision_ctx.get(key)
        if isinstance(val, str) and val:
            return val[:120]
    return "（待補充）"


# =============================================================================
# Endpoint 3: summary
# =============================================================================

async def query_governance_summary(*, days: int = 30) -> GovernanceSummaryResponse:
    """
    過去 N 天 SLO 違反時序統計 + compliance_rate。

    compliance_rate = 1 - unresolved / total（total=0 時回 1.0）
    """
    since = now_taipei() - timedelta(days=days)

    async with get_db_context() as db:
        # 總數 & 未解決數
        count_stmt = select(
            func.count().label("total"),
            func.count().filter(AiGovernanceEvent.resolved.is_(False)).label("unresolved"),
        ).where(AiGovernanceEvent.triggered_at >= since)

        count_row = await db.execute(count_stmt)
        counts = count_row.one()
        total_events = int(counts.total)
        unresolved_count = int(counts.unresolved)

        # 每日計數（DATE_TRUNC 在 Postgres 端執行）
        daily_sql = text("""
            SELECT
                DATE_TRUNC('day', triggered_at AT TIME ZONE 'Asia/Taipei')::date AS day,
                event_type,
                count(*) AS cnt
            FROM ai_governance_events
            WHERE triggered_at >= :since
            GROUP BY day, event_type
            ORDER BY day ASC
        """)
        daily_result = await db.execute(daily_sql, {"since": since})
        daily_rows = daily_result.fetchall()

    # 彙整每日資料
    daily_map: dict[str, dict[str, int]] = {}
    for row in daily_rows:
        day_str = row.day.strftime("%Y-%m-%d") if hasattr(row.day, "strftime") else str(row.day)
        if day_str not in daily_map:
            daily_map[day_str] = {}
        daily_map[day_str][row.event_type] = int(row.cnt)

    daily_counts = [
        DailyCount(
            date=day_str,
            total=sum(by_type.values()),
            by_type=by_type,
        )
        for day_str, by_type in sorted(daily_map.items())
    ]

    if total_events == 0:
        compliance_rate = 1.0
    else:
        compliance_rate = round(1.0 - unresolved_count / total_events, 4)

    return GovernanceSummaryResponse(
        compliance_rate=compliance_rate,
        total_events=total_events,
        unresolved_count=unresolved_count,
        daily_counts=daily_counts,
    )
