"""
Approval Repository - PostgreSQL 實作
======================================
Phase 16 R3.3: Repository 層實作

職責: ApprovalRecord 的 CRUD 操作
設計: 實作 IApprovalRepository Protocol

版本: v1.0
建立: 2026-03-26 (台北時區)
建立者: Claude Code (Phase 16 架構重構)
"""

from datetime import UTC, datetime
from typing import Any
from uuid import UUID

import structlog
from sqlalchemy import select, update

from src.db.base import get_db_context
from src.db.models import ApprovalRecord
from src.models.approval import (
    ApprovalRequest,
    ApprovalStatus,
    BlastRadius,
    DataImpact,
    DryRunCheck,
    RiskLevel,
    Signature,
)
from src.repositories.interfaces import IApprovalRepository

logger = structlog.get_logger(__name__)


# =============================================================================
# Conversion Helpers
# =============================================================================

def _record_to_request(record: ApprovalRecord) -> ApprovalRequest:
    """Convert DB ApprovalRecord to Pydantic ApprovalRequest"""
    blast_radius = None
    if record.blast_radius:
        br = record.blast_radius
        blast_radius = BlastRadius(
            affected_pods=br.get("affected_pods", 0),
            estimated_downtime=br.get("estimated_downtime", "0"),
            related_services=br.get("related_services", []),
            data_impact=DataImpact(br.get("data_impact", "none").lower())
            if br.get("data_impact")
            else DataImpact.NONE,
        )

    dry_run_checks = []
    if record.dry_run_checks:
        for check in record.dry_run_checks:
            dry_run_checks.append(
                DryRunCheck(
                    name=check.get("name", ""),
                    passed=check.get("passed", True),
                    message=check.get("message"),
                )
            )

    signatures = []
    if record.signatures:
        for sig in record.signatures:
            signatures.append(
                Signature(
                    signer_id=sig.get("signer_id", ""),
                    signer_name=sig.get("signer_name", ""),
                    timestamp=datetime.fromisoformat(sig["timestamp"])
                    if sig.get("timestamp")
                    else datetime.now(UTC),
                    comment=sig.get("comment"),
                )
            )

    return ApprovalRequest(
        id=UUID(record.id),
        action=record.action,
        description=record.description,
        status=ApprovalStatus(
            record.status.value if hasattr(record.status, "value") else record.status
        ),
        risk_level=RiskLevel(
            record.risk_level.value
            if hasattr(record.risk_level, "value")
            else record.risk_level
        ),
        blast_radius=blast_radius,
        dry_run_checks=dry_run_checks,
        required_signatures=record.required_signatures,
        current_signatures=record.current_signatures,
        signatures=signatures,
        requested_by=record.requested_by,
        created_at=record.created_at,
        expires_at=record.expires_at,
        resolved_at=record.resolved_at,
        rejection_reason=record.rejection_reason,
        metadata=record.extra_metadata,
        fingerprint=record.fingerprint,
        hit_count=record.hit_count,
        last_seen_at=record.last_seen_at,
        # B4 fix 2026-04-24 ogt + Claude Sonnet 4.6: 補回 DB 欄位（人工審核路徑讀回必要）
        incident_id=getattr(record, "incident_id", None),
        matched_playbook_id=getattr(record, "matched_playbook_id", None),
    )


# =============================================================================
# ApprovalDBRepository
# =============================================================================

class ApprovalDBRepository(IApprovalRepository):
    """
    Approval Repository - PostgreSQL 實作

    純 CRUD 操作，不含業務邏輯
    業務邏輯請放在 Service 層
    """

    async def create(
        self,
        data: dict[str, Any],
    ) -> ApprovalRequest:
        """建立新的 Approval Record"""
        async with get_db_context() as db:
            record = ApprovalRecord(**data)
            db.add(record)
            await db.flush()
            await db.refresh(record)

            logger.debug(
                "approval_created",
                id=record.id,
                status=record.status,
            )

            return _record_to_request(record)

    async def get_by_id(self, approval_id: UUID) -> ApprovalRequest | None:
        """根據 ID 取得 Approval"""
        async with get_db_context() as db:
            result = await db.execute(
                select(ApprovalRecord).where(ApprovalRecord.id == str(approval_id))
            )
            record = result.scalar_one_or_none()
            return _record_to_request(record) if record else None

    async def get_pending(self) -> list[ApprovalRequest]:
        """取得所有待審核的 Approval"""
        now = datetime.now(UTC)
        async with get_db_context() as db:
            await db.execute(
                update(ApprovalRecord)
                .where(ApprovalRecord.status == ApprovalStatus.PENDING)
                .where(ApprovalRecord.expires_at < now)
                .values(status=ApprovalStatus.EXPIRED, resolved_at=now)
            )

            result = await db.execute(
                select(ApprovalRecord)
                .where(ApprovalRecord.status == ApprovalStatus.PENDING)
                .order_by(ApprovalRecord.created_at.desc())
            )
            records = result.scalars().all()
            return [_record_to_request(r) for r in records]

    async def update_status(
        self,
        approval_id: UUID,
        status: ApprovalStatus,
        actor: str | None = None,
    ) -> ApprovalRequest | None:
        """更新 Approval 狀態"""
        async with get_db_context() as db:
            result = await db.execute(
                select(ApprovalRecord).where(ApprovalRecord.id == str(approval_id))
            )
            record = result.scalar_one_or_none()

            if not record:
                return None

            record.status = status
            if status in (ApprovalStatus.APPROVED, ApprovalStatus.REJECTED):
                record.resolved_at = datetime.now(UTC)

            await db.flush()
            await db.refresh(record)

            logger.info(
                "approval_status_updated",
                id=str(approval_id),
                status=status.value,
                actor=actor,
            )

            return _record_to_request(record)

    async def add_signature(
        self,
        approval_id: UUID,
        signer: str,
        decision: str,
        comment: str | None = None,
    ) -> ApprovalRequest | None:
        """新增簽核"""
        async with get_db_context() as db:
            result = await db.execute(
                select(ApprovalRecord).where(ApprovalRecord.id == str(approval_id))
            )
            record = result.scalar_one_or_none()

            if not record:
                return None

            # 新增簽名
            new_signature = {
                "signer_id": signer,
                "signer_name": signer,
                "timestamp": datetime.now(UTC).isoformat(),
                "decision": decision,
                "comment": comment,
            }

            signatures = record.signatures or []
            signatures.append(new_signature)
            record.signatures = signatures
            record.current_signatures = len(signatures)

            await db.flush()
            await db.refresh(record)

            logger.info(
                "signature_added",
                id=str(approval_id),
                signer=signer,
                decision=decision,
                total_signatures=record.current_signatures,
            )

            return _record_to_request(record)

    async def find_by_fingerprint(
        self,
        fingerprint: str,
    ) -> ApprovalRequest | None:
        """根據指紋查找 Approval (告警收斂用)"""
        async with get_db_context() as db:
            result = await db.execute(
                select(ApprovalRecord)
                .where(ApprovalRecord.fingerprint == fingerprint)
                .where(ApprovalRecord.status == ApprovalStatus.PENDING)
                .order_by(ApprovalRecord.created_at.desc())
                .limit(1)
            )
            record = result.scalar_one_or_none()
            return _record_to_request(record) if record else None

    async def increment_hit_count(
        self,
        approval_id: UUID,
    ) -> ApprovalRequest | None:
        """增加 hit_count (告警收斂用)"""
        async with get_db_context() as db:
            result = await db.execute(
                select(ApprovalRecord).where(ApprovalRecord.id == str(approval_id))
            )
            record = result.scalar_one_or_none()

            if not record:
                return None

            record.hit_count = (record.hit_count or 0) + 1
            record.last_seen_at = datetime.now(UTC)

            await db.flush()
            await db.refresh(record)

            return _record_to_request(record)


# =============================================================================
# Singleton
# =============================================================================

_approval_repository: ApprovalDBRepository | None = None


def get_approval_repository() -> ApprovalDBRepository:
    """取得 ApprovalDBRepository 實例 (Singleton)"""
    global _approval_repository
    if _approval_repository is None:
        _approval_repository = ApprovalDBRepository()
    return _approval_repository
