"""
AOL → alert_rule_catalog Confidence EWMA Writeback Job
========================================================
ADR-091 Task T2 — 飛輪斷鏈 C2 修復

每 1 小時從 automation_operation_log 聚合 alertname 執行成功率，
用 EWMA 回灌 alert_rule_catalog.confidence，並對低成功率規則標記 'draft'
（等待人工審查）。

流程:
  1. 撈 AOL 過去 24h，group by alertname，算 ok/total
  2. EWMA: new_confidence = 0.7 * old_confidence + 0.3 * recent_success_rate
     若 confidence IS NULL，初始值用 recent_success_rate
  3. recent_success_rate < 0.3 且 sample >= 5 → review_status = 'draft'
     （schema CHECK 只允許 draft/approved/deprecated/retired，'draft' 語義
      等同「需要人工審查」，Hermes 可設定撈 draft 觸發 advisory）
  4. 寫 automation_operation_log summary

Feature Flag:
  ENABLE_AOL_WRITEBACK_JOB=false（預設）— 先寫 code 後人工驗證才開啟

設計鐵律:
  - ENABLE_AOL_WRITEBACK_JOB=false 時完全 skip，不碰 DB
  - 只 UPDATE，不 INSERT（alert_rule_catalog 必須先由 rule_catalog_sync 建立）
  - EWMA alpha=0.3（新資料權重），穩定性優先
  - sample < 5 → 不降 review_status（避免少量資料誤判）
  - 任何 DB 失敗 → log warning，下次重試，不 crash 主程序

排程:
  - 啟動延遲 360s（等 rule_catalog_sync + rule_stats_updater 先跑）
  - 每 3600s（每 1 小時）

schema 依賴（已存在，不需要 migration）:
  - alert_rule_catalog.confidence NUMERIC(3,2)  — 行 279 adr090_asset_inventory_foundation.sql
  - alert_rule_catalog.review_status TEXT CHECK (draft/approved/deprecated/retired)
  - automation_operation_log.input, .status, .tags, .operation_type

W2 PR-R2 2026-04-28 ogt + Claude Sonnet 4.6 Asia/Taipei
ADR-091 Task T2 飛輪斷鏈 C2 修復 — AOL 命中率回灌
"""
from __future__ import annotations

import asyncio
import json as _json
import time as _time
from typing import Any

import structlog

from src.core.config import settings

logger = structlog.get_logger(__name__)

# ============================================================================
# 排程參數
# ============================================================================
_WRITEBACK_INTERVAL_SEC = 3600      # 每 1 小時
_FIRST_DELAY_SEC = 360              # 啟動後等 360s（讓 rule_catalog_sync + rule_stats 先完成）
_LOOP_BACKOFF_SEC = 300             # 錯誤後重試間隔
_AOL_WINDOW_HOURS = 24              # 聚合 AOL 的時間窗口

# EWMA 參數
_EWMA_ALPHA = 0.3                   # 新資料權重 (0.3 = 保守更新)
_LOW_SUCCESS_THRESHOLD = 0.3        # 低成功率閾值
_MIN_SAMPLE_SIZE = 5                # 樣本不足不降 review_status

# AOL 操作類型白名單（只計「真正執行了操作」的 log）
_RELEVANT_OP_TYPES = (
    "alert_resolved",
    "action_executed",
    "auto_repair_success",
    "auto_repair_failed",
    "auto_repair_skipped",
)

# review_status 降級用值（schema CHECK 允許的值中語義最接近「需要人工審查」）
_NEEDS_REVIEW_STATUS = "draft"


# ============================================================================
# Public entry — main.py lifespan 呼叫
# ============================================================================

async def run_aol_writeback_loop() -> None:
    """
    永久迴圈：每 _WRITEBACK_INTERVAL_SEC 秒執行一次 AOL → catalog 回灌.

    Feature Flag ENABLE_AOL_WRITEBACK_JOB=false 時，進入迴圈但每次 sleep 後立即 skip.
    """
    logger.info(
        "aol_to_catalog_writeback_loop_started",
        interval_sec=_WRITEBACK_INTERVAL_SEC,
        flag_enabled=settings.ENABLE_AOL_WRITEBACK_JOB,
    )
    await asyncio.sleep(_FIRST_DELAY_SEC)

    while True:
        try:
            await run_aol_writeback_once()
        except Exception as e:
            logger.exception("aol_writeback_loop_error", error=str(e))
            await asyncio.sleep(_LOOP_BACKOFF_SEC)
            continue
        await asyncio.sleep(_WRITEBACK_INTERVAL_SEC)


async def run_aol_writeback_once() -> dict[str, Any]:
    """
    執行一次 AOL → alert_rule_catalog confidence EWMA 回灌.

    Returns:
        {
            "skipped": True/False,       # feature flag 停用時回傳 skipped=True
            "rules_sampled": N,          # AOL 中找到的 alertname 數
            "rules_updated": M,          # 成功 UPDATE confidence 的 rule 數
            "rules_flagged_draft": K,    # 低成功率被標 draft 的 rule 數
            "error": None | str,
        }
    """
    if not settings.ENABLE_AOL_WRITEBACK_JOB:
        logger.debug("aol_writeback_skipped_flag_disabled")
        return {"skipped": True, "rules_sampled": 0, "rules_updated": 0, "rules_flagged_draft": 0, "error": None}

    started_ms = _time.time()
    stats: dict[str, Any] = {
        "skipped": False,
        "rules_sampled": 0,
        "rules_updated": 0,
        "rules_flagged_draft": 0,
        "error": None,
    }

    try:
        samples = await _fetch_aol_samples()
        stats["rules_sampled"] = len(samples)

        for sample in samples:
            updated, flagged = await _update_catalog_confidence(sample)
            if updated:
                stats["rules_updated"] += 1
            if flagged:
                stats["rules_flagged_draft"] += 1

    except Exception as e:
        stats["error"] = f"{type(e).__name__}: {e}"[:1000]
        logger.exception("aol_writeback_once_failed", error=stats["error"])

    duration_ms = int((_time.time() - started_ms) * 1000)
    await _log_aol_summary(stats, duration_ms)

    logger.info(
        "aol_writeback_once_done",
        rules_sampled=stats["rules_sampled"],
        rules_updated=stats["rules_updated"],
        rules_flagged_draft=stats["rules_flagged_draft"],
        duration_ms=duration_ms,
        error=stats["error"],
    )
    return stats


# ============================================================================
# 資料查詢
# ============================================================================

async def _fetch_aol_samples() -> list[dict[str, Any]]:
    """
    從 automation_operation_log 聚合過去 24h 的 alertname 成功率.

    查詢條件:
      - operation_type IN (alert_resolved, action_executed, auto_repair_*)
      - tags @> '["auto_execute"]'
      - created_at > NOW() - INTERVAL '24h'

    回傳: [{alertname, ok, total, recent_success_rate}, ...]
    """
    from sqlalchemy import text as _sql
    from src.db.base import get_db_context

    # 動態拼 operation_type IN 清單（parameterized，避免 SQL 注入）
    # SQLAlchemy bindparam 不支援 INTERVAL 的數字插值，INTERVAL 用 f-string literal 拼接
    op_list = list(_RELEVANT_OP_TYPES)
    placeholders = ", ".join(f":op{i}" for i in range(len(op_list)))
    params: dict[str, Any] = {f"op{i}": v for i, v in enumerate(op_list)}

    sql = f"""
        SELECT
            input->>'alertname'                                AS alertname,
            SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS ok,
            COUNT(*)                                            AS total
        FROM automation_operation_log
        WHERE operation_type IN ({placeholders})
          AND tags @> '["auto_execute"]'
          AND created_at > NOW() - INTERVAL '{_AOL_WINDOW_HOURS} hours'
          AND input->>'alertname' IS NOT NULL
          AND input->>'alertname' != ''
        GROUP BY input->>'alertname'
        HAVING COUNT(*) > 0
        ORDER BY COUNT(*) DESC
    """

    try:
        async with get_db_context() as db:
            result = await db.execute(_sql(sql), params)
            rows = result.fetchall()

        samples = []
        for row in rows:
            alertname = row.alertname
            ok = int(row.ok or 0)
            total = int(row.total or 0)
            recent_success_rate = ok / total if total > 0 else 0.0
            samples.append({
                "alertname": alertname,
                "ok": ok,
                "total": total,
                "recent_success_rate": recent_success_rate,
            })
        return samples
    except Exception as e:
        logger.warning("aol_fetch_samples_failed", error=str(e))
        return []


# ============================================================================
# EWMA 更新
# ============================================================================

async def _update_catalog_confidence(sample: dict[str, Any]) -> tuple[bool, bool]:
    """
    對單一 alertname 執行 EWMA 更新 + 低成功率降級.

    Returns:
        (updated: bool, flagged_draft: bool)
        updated = True 表示 confidence 有被更新
        flagged_draft = True 表示 review_status 被設為 'draft'
    """
    from sqlalchemy import text as _sql
    from src.db.base import get_db_context

    alertname = sample["alertname"]
    recent_sr = sample["recent_success_rate"]
    total = sample["total"]

    try:
        async with get_db_context() as db:
            # 先讀現有 confidence 與 review_status
            row = await db.execute(
                _sql("""
                    SELECT rule_id, confidence, review_status
                    FROM alert_rule_catalog
                    WHERE rule_name = :rn
                    LIMIT 1
                """),
                {"rn": alertname},
            )
            existing = row.one_or_none()
            if existing is None:
                # rule 不在 catalog → skip（等 rule_catalog_sync 先建）
                logger.debug("aol_writeback_rule_not_in_catalog", alertname=alertname)
                return False, False

            old_confidence = float(existing.confidence) if existing.confidence is not None else None
            current_review_status = existing.review_status

            # EWMA 計算
            if old_confidence is None:
                new_confidence = recent_sr
            else:
                new_confidence = (1 - _EWMA_ALPHA) * old_confidence + _EWMA_ALPHA * recent_sr

            # 限制到 [0.00, 1.00]（NUMERIC(3,2) 最大 9.99，但 confidence 語義 0-1）
            new_confidence = max(0.0, min(1.0, new_confidence))

            # 判斷是否需要降級 review_status
            should_flag = (
                recent_sr < _LOW_SUCCESS_THRESHOLD
                and total >= _MIN_SAMPLE_SIZE
                and current_review_status not in (_NEEDS_REVIEW_STATUS, "deprecated", "retired")
            )

            if should_flag:
                await db.execute(
                    _sql("""
                        UPDATE alert_rule_catalog
                        SET confidence = :conf,
                            review_status = :rs,
                            updated_at = NOW()
                        WHERE rule_name = :rn
                    """),
                    {
                        "conf": round(new_confidence, 2),
                        "rs": _NEEDS_REVIEW_STATUS,
                        "rn": alertname,
                    },
                )
                logger.info(
                    "aol_writeback_flagged_draft",
                    alertname=alertname,
                    old_confidence=old_confidence,
                    new_confidence=round(new_confidence, 2),
                    recent_sr=round(recent_sr, 3),
                    sample=total,
                )
                return True, True
            else:
                await db.execute(
                    _sql("""
                        UPDATE alert_rule_catalog
                        SET confidence = :conf,
                            updated_at = NOW()
                        WHERE rule_name = :rn
                    """),
                    {
                        "conf": round(new_confidence, 2),
                        "rn": alertname,
                    },
                )
                logger.debug(
                    "aol_writeback_confidence_updated",
                    alertname=alertname,
                    old_confidence=old_confidence,
                    new_confidence=round(new_confidence, 2),
                    recent_sr=round(recent_sr, 3),
                    sample=total,
                )
                return True, False

    except Exception as e:
        logger.warning(
            "aol_writeback_update_failed",
            alertname=alertname,
            error=str(e),
        )
        return False, False


# ============================================================================
# AOL 稽核 log
# ============================================================================

async def _log_aol_summary(stats: dict[str, Any], duration_ms: int) -> None:
    """寫一筆 summary 到 automation_operation_log（每次 writeback 只寫 1 筆）."""
    if stats.get("skipped"):
        return  # flag 停用時不留 log，避免污染

    try:
        from sqlalchemy import text as _sql
        from src.db.base import get_db_context

        aol_status = "failed" if stats.get("error") else "success"
        async with get_db_context() as db:
            await db.execute(
                _sql("""
                    INSERT INTO automation_operation_log (
                        operation_type, actor, status,
                        input, output, duration_ms, error, tags
                    ) VALUES (
                        'rule_updated',
                        'aol_to_catalog_writeback',
                        :st,
                        CAST(:input AS jsonb),
                        CAST(:output AS jsonb),
                        :dur, :err, :tags
                    )
                """),
                {
                    "st": aol_status,
                    "input": _json.dumps(
                        {"window_hours": _AOL_WINDOW_HOURS, "ewma_alpha": _EWMA_ALPHA},
                        ensure_ascii=False,
                    ),
                    "output": _json.dumps(
                        {
                            "rules_sampled": stats.get("rules_sampled", 0),
                            "rules_updated": stats.get("rules_updated", 0),
                            "rules_flagged_draft": stats.get("rules_flagged_draft", 0),
                        },
                        ensure_ascii=False,
                    ),
                    "dur": duration_ms,
                    "err": (stats.get("error") or "")[:2000] if stats.get("error") else None,
                    "tags": ["rule_catalog", "aol_writeback", "ewma", "confidence"],
                },
            )
    except Exception as e:
        logger.warning("aol_writeback_log_aol_failed", error=str(e))
