"""
Playbook Repository Race Condition 整合測試
=============================================
驗證 update_stats SELECT FOR UPDATE 防止 Lost Update

2026-04-25 db-expert-fix by Claude Engineer-B
"""

import asyncio
import os
import uuid

import pytest
import pytest_asyncio
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

from src.db.models import PlaybookRecord

# =============================================================================
# DB URL（與 integration conftest 一致）
# =============================================================================

DEV_DB_URL = os.environ.get(
    "TEST_DATABASE_URL",
    "postgresql+asyncpg://awoooi:awoooi_prod_2026@192.168.0.188:5432/awoooi_dev?ssl=disable",
)

# 禁止誤打 prod
assert "awoooi_prod" not in DEV_DB_URL.split("/")[-1], (
    "TEST_DATABASE_URL 不可指向 prod DB（awoooi_prod）"
)


# =============================================================================
# Helpers
# =============================================================================

def _make_factory(engine):
    return async_sessionmaker(
        bind=engine,
        class_=AsyncSession,
        expire_on_commit=False,
        autoflush=False,
    )


def _test_playbook_id() -> str:
    """每次測試使用唯一 ID，避免跨測試污染"""
    return f"PB-RACE-TEST-{uuid.uuid4().hex[:8].upper()}"


async def _insert_test_playbook(engine, playbook_id: str) -> None:
    """直接 INSERT 測試用 Playbook（不走 Repository，避免 Redis 依賴）"""
    from sqlalchemy import insert
    factory = _make_factory(engine)
    async with factory() as session:
        async with session.begin():
            stmt = insert(PlaybookRecord).values(
                playbook_id=playbook_id,
                name="Race Test Playbook",
                description="用於 race condition 測試",
                status="active",
                source="manual",
                symptom_pattern={"alert_names": ["TestAlert"], "affected_services": [], "keywords": []},
                repair_steps=[],
                estimated_duration_minutes=5,
                source_incident_ids=[],
                ai_confidence=0.8,
                success_count=0,
                failure_count=0,
                trust_score=0.5,
                requires_approval_level="auto",
                stateful_targets=[],
                requires_pre_backup=False,
                tags=[],
            )
            await session.execute(stmt)


async def _delete_test_playbook(engine, playbook_id: str) -> None:
    """清理測試資料"""
    from sqlalchemy import delete
    factory = _make_factory(engine)
    async with factory() as session:
        async with session.begin():
            stmt = delete(PlaybookRecord).where(PlaybookRecord.playbook_id == playbook_id)
            await session.execute(stmt)


async def _get_success_count(engine, playbook_id: str) -> int:
    """直接從 PG 讀取 success_count"""
    from sqlalchemy import select
    factory = _make_factory(engine)
    async with factory() as session:
        result = await session.get(PlaybookRecord, playbook_id)
        return result.success_count if result else -1


# =============================================================================
# 單一 update_stats via SELECT FOR UPDATE（不走 Redis，直接操作 ORM）
# =============================================================================

async def _update_stats_direct(engine, playbook_id: str, success: bool) -> bool:
    """
    模擬 PlaybookRepository.update_stats 的 PG 部分（SELECT FOR UPDATE）
    不走 Redis，僅測試 PG transaction 正確性
    """
    from sqlalchemy import select
    factory = _make_factory(engine)
    async with factory() as session:
        async with session.begin():
            stmt = (
                select(PlaybookRecord)
                .where(PlaybookRecord.playbook_id == playbook_id)
                .with_for_update()
            )
            result = await session.execute(stmt)
            record = result.scalar_one_or_none()
            if record is None:
                return False

            if success:
                record.success_count += 1
                record.trust_score = 0.9 * record.trust_score + 0.1 * 1.0
            else:
                record.failure_count += 1
                record.trust_score = 0.8 * record.trust_score + 0.2 * 0.0

            record.trust_score = max(0.0, min(1.0, record.trust_score))
            return True


# =============================================================================
# Race Condition Test
# =============================================================================

@pytest.mark.integration
class TestUpdateStatsRaceCondition:
    """
    驗證 SELECT FOR UPDATE 在並行情況下不會發生 Lost Update

    設計：
    - INSERT 1 筆 PlaybookRecord（success_count=0）
    - 5 個 coroutine 同時呼叫 _update_stats_direct(success=True)
    - 預期：success_count == 5（每個 update 都被序列化，無 lost update）
    """

    @pytest_asyncio.fixture(autouse=True)
    async def setup_and_teardown(self):
        """每個測試建立獨立 engine + 測試資料，結束後清理"""
        self.engine = create_async_engine(DEV_DB_URL, echo=False)
        self.playbook_id = _test_playbook_id()
        await _insert_test_playbook(self.engine, self.playbook_id)
        yield
        await _delete_test_playbook(self.engine, self.playbook_id)
        await self.engine.dispose()

    async def test_update_stats_race_condition_no_lost_update(self):
        """
        同時 5 個 successful update_stats 對同一 playbook，
        success_count 必須最終 == 5（無 lost update）

        2026-04-25 db-expert-fix by Claude Engineer-B
        """
        results = await asyncio.gather(*[
            _update_stats_direct(self.engine, self.playbook_id, success=True)
            for _ in range(5)
        ])

        # 所有 5 個必須成功
        assert all(results), f"部分 update_stats 回傳 False: {results}"

        # PG 中 success_count 必須精確 == 5（無 lost update）
        final_count = await _get_success_count(self.engine, self.playbook_id)
        assert final_count == 5, (
            f"Lost update 偵測：success_count={final_count}，預期 5。"
            "SELECT FOR UPDATE 未正確序列化並行寫入。"
        )

    async def test_update_stats_not_found_returns_false(self):
        """不存在的 playbook_id 應回傳 False"""
        result = await _update_stats_direct(self.engine, "PB-NOT-EXIST-9999", success=True)
        assert result is False

    async def test_update_stats_failure_increments_failure_count(self):
        """失敗的 update_stats 應遞增 failure_count，不影響 success_count"""
        await _update_stats_direct(self.engine, self.playbook_id, success=False)

        factory = _make_factory(self.engine)
        async with factory() as session:
            record = await session.get(PlaybookRecord, self.playbook_id)
            assert record.failure_count == 1
            assert record.success_count == 0
