"""
B5 整合測試 — 核心流程驗收
============================
5 個 E2E 整合測試，使用真實 PostgreSQL (awoooi_dev)
每個測試後自動 rollback，無副作用

禁止 Mock 鐵律:
- 所有 DB 操作使用真實連線
- 只有外部 API (Telegram/K8s/Ollama) 可選擇跳過 (mark.skip)

2026-04-10 Claude Sonnet 4.6 Asia/Taipei
"""
import pytest
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from tests.factories import (
    create_approval,
    create_incident,
    create_knowledge_entry,
    create_rag_chunk,
)

pytestmark = pytest.mark.integration


# =============================================================================
# T1: Incident 建立與查詢
# =============================================================================

@pytest.mark.asyncio
async def test_incident_create_and_query(db_session: AsyncSession):
    """
    T1: 建立 Incident → 從 DB 查詢 → 驗證欄位正確
    驗證: incidents 表 INSERT + SELECT 完整鏈路
    """
    inc = await create_incident(
        db_session,
        anomaly_type="pod_crash",
        host="mon",
        severity="P1",
    )

    result = await db_session.execute(
        text("SELECT incident_id, status, severity FROM incidents WHERE incident_id = :id"),
        {"id": inc["incident_id"]},
    )
    row = result.one()

    assert row.incident_id == inc["incident_id"]
    assert row.status == "INVESTIGATING"
    assert row.severity == "P1"


# =============================================================================
# T2: Approval 建立、查詢、狀態更新
# =============================================================================

@pytest.mark.asyncio
async def test_approval_lifecycle(db_session: AsyncSession):
    """
    T2: 建立 Approval → 更新狀態 approved → 查詢確認
    驗證: approval_records 完整 CRUD 鏈路
    """
    approval = await create_approval(
        db_session,
        action="RESTART_POD",
        risk_level="MEDIUM",
        status="PENDING",
    )

    # 更新狀態為 APPROVED
    await db_session.execute(
        text("UPDATE approval_records SET status = CAST('APPROVED' AS approvalstatus) WHERE id = :id"),
        {"id": approval.id},
    )
    await db_session.flush()

    result = await db_session.execute(
        text("SELECT id, status, action FROM approval_records WHERE id = :id"),
        {"id": approval.id},
    )
    row = result.one()

    assert row.id == approval.id
    assert row.status == "APPROVED"
    assert row.action == "RESTART_POD"


# =============================================================================
# T3: Knowledge Entry CRUD + view_count 遞增
# =============================================================================

@pytest.mark.asyncio
async def test_knowledge_entry_view_count(db_session: AsyncSession):
    """
    T3: 建立知識條目 → 累加 view_count → 驗證
    驗證: knowledge_entries 讀寫 + 數值欄位更新
    """
    entry = await create_knowledge_entry(
        db_session,
        title="K3s HA 設定指南",
        category="operations",
        entry_type="RUNBOOK",
    )

    # 模擬三次瀏覽
    await db_session.execute(
        text("UPDATE knowledge_entries SET view_count = view_count + 3 WHERE id = :id"),
        {"id": entry.id},
    )
    await db_session.flush()

    result = await db_session.execute(
        text("SELECT view_count, title FROM knowledge_entries WHERE id = :id"),
        {"id": entry.id},
    )
    row = result.one()

    assert row.view_count == 3
    assert row.title == "K3s HA 設定指南"


# =============================================================================
# T4: Incident → Approval 關聯查詢
# =============================================================================

@pytest.mark.asyncio
async def test_incident_approval_association(db_session: AsyncSession):
    """
    T4: 建立 Incident + 關聯 Approval → JOIN 查詢
    驗證: 跨表關聯完整性
    """
    inc = await create_incident(db_session, severity="P0", status="INVESTIGATING")

    approval = await create_approval(
        db_session,
        action="DELETE_POD",
        risk_level="high",
    )

    # 手動關聯 (approval_records 的 incident_id 欄位不在 ORM，用 raw SQL)
    await db_session.execute(
        text("UPDATE approval_records SET extra_metadata = :meta WHERE id = :id"),
        {"id": approval.id, "meta": f'{{"incident_id": "{inc["incident_id"]}"}}'.replace('"', '"')},
    )
    await db_session.flush()

    # 驗證兩個物件都在 DB 中
    r1 = await db_session.execute(
        text("SELECT COUNT(*) FROM incidents WHERE incident_id = :id"),
        {"id": inc["incident_id"]},
    )
    r2 = await db_session.execute(
        text("SELECT COUNT(*) FROM approval_records WHERE id = :id"),
        {"id": approval.id},
    )

    assert r1.scalar() == 1
    assert r2.scalar() == 1


# =============================================================================
# T5: RAG chunk 建立 + 全文統計
# =============================================================================

@pytest.mark.asyncio
async def test_rag_chunks_stats(db_session: AsyncSession):
    """
    T5: 建立多筆 RAG chunks → 查詢統計
    驗證: rag_chunks 表寫入 + COUNT/GROUP BY
    """
    # 建立 3 個不同 source 的 chunks
    await create_rag_chunk(db_session, source="adr", source_id="adr-001", chunk_text="ADR-001 content A")
    await create_rag_chunk(db_session, source="adr", source_id="adr-001", chunk_text="ADR-001 content B")
    await create_rag_chunk(db_session, source="runbook", source_id="rb-001", chunk_text="Runbook content")

    result = await db_session.execute(
        text("""
            SELECT source, COUNT(*) as n
            FROM rag_chunks
            WHERE source IN ('adr', 'runbook')
            GROUP BY source
            ORDER BY source
        """),
    )
    rows = {row.source: row.n for row in result}

    # adr 至少 2 筆 (可能有 prod 資料，所以用 >=)
    assert rows.get("adr", 0) >= 2
    assert rows.get("runbook", 0) >= 1
