Stage 2：外部稽核（Phase 3）Implementation Plan

class AssessmentPlanStatus(StrEnum):
    ACTIVE = "active"
    AUDITING = "auditing"
    REMEDIATION = "remediation"
    CLOSED = "closed"

verdict: Mapped[Optional[ControlVerdict]] = mapped_column(
    String(30),
    nullable=True,
    comment="稽核判定結果（pass / fail / partial / na）"
)

ao_uid: Mapped[Optional[str]] = mapped_column(
    String(36),
    nullable=True,
    comment="關聯的 Assessment Object UID（矯正對象）"
)

cd ~/Projects/Jedicogy/module/jedi-python-package/jedi-oscal
# 更新 pyproject.toml 版本號（依團隊慣例）
cd /Users/chouraymond/Projects/Billows/Audit-Manager/compliance-manager-be
poetry update jedi-oscal

-- Stage 2: External Audit Migration
-- =====================================================

-- 1. AP status 資料遷移
UPDATE oscal.assessment_plans SET status = 'active' WHERE status = 'draft';
UPDATE oscal.assessment_plans SET status = 'closed' WHERE status IN ('completed', 'archived');

-- 2. AR control verdict 改 nullable
ALTER TABLE oscal.assessment_result_controls
    ALTER COLUMN verdict DROP NOT NULL;

-- 3. AR finding 新增 ao_uid
ALTER TABLE oscal.assessment_result_findings
    ADD COLUMN IF NOT EXISTS ao_uid VARCHAR(36);

-- 4. POA&M 表
CREATE TABLE IF NOT EXISTS compliance.poams (
    id                  SERIAL PRIMARY KEY,
    uid                 VARCHAR(36) NOT NULL DEFAULT gen_random_uuid(),
    assessment_plan_id  INTEGER NOT NULL,
    ar_finding_id       INTEGER NOT NULL,
    control_identifier  VARCHAR(50) NOT NULL,
    ao_uid              VARCHAR(36) NOT NULL,
    status              VARCHAR(30) NOT NULL DEFAULT 'open',
    closed_at           TIMESTAMP,
    tenant_id           INTEGER NOT NULL,
    org_unit_id         INTEGER,
    created_at          TIMESTAMP NOT NULL DEFAULT now(),
    updated_at          TIMESTAMP NOT NULL DEFAULT now(),
    created_user        VARCHAR(50),
    updated_user        VARCHAR(50)
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_poams_uid ON compliance.poams (uid);
CREATE INDEX IF NOT EXISTS ix_poams_ap_id ON compliance.poams (assessment_plan_id);
CREATE INDEX IF NOT EXISTS ix_poams_status ON compliance.poams (status);
CREATE INDEX IF NOT EXISTS ix_poams_tenant ON compliance.poams (tenant_id);

GRC_NOT_AUDITOR              = ("使用者不具備稽核員角色",                "GRC_403001")
GRC_AP_NOT_ACTIVE            = ("稽核計畫狀態不是進行中，無法啟動稽核",    "GRC_412001")
GRC_AP_NOT_AUDITING          = ("稽核計畫不在稽核中狀態",                "GRC_412002")
GRC_AR_VERDICT_INCOMPLETE    = ("尚有控制項未填寫稽核判定",              "GRC_412003")
GRC_AR_NOT_FOUND             = ("稽核結果不存在",                       "GRC_404016")
GRC_AR_CONTROL_NOT_FOUND     = ("稽核控制項不存在",                     "GRC_404017")
GRC_AR_FINDING_NOT_FOUND     = ("稽核發現不存在",                       "GRC_404018")

from datetime import datetime
from typing import Optional

from sqlalchemy import String, Integer, DateTime
from sqlalchemy.orm import Mapped, mapped_column

from jedi_common.session.database.model.base_model import BaseModel


class PoamModel(BaseModel):
    __tablename__ = "poams"
    __table_args__ = {"schema": "compliance"}

    uid: Mapped[str] = mapped_column(String(36), nullable=False)
    assessment_plan_id: Mapped[int] = mapped_column(Integer, nullable=False)
    ar_finding_id: Mapped[int] = mapped_column(Integer, nullable=False)
    control_identifier: Mapped[str] = mapped_column(String(50), nullable=False)
    ao_uid: Mapped[str] = mapped_column(String(36), nullable=False)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="open")
    closed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    tenant_id: Mapped[int] = mapped_column(Integer, nullable=False)
    org_unit_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)

class PoamEntity:
    def __init__(
        self,
        id=None,
        uid=None,
        assessment_plan_id=None,
        ar_finding_id=None,
        control_identifier=None,
        ao_uid=None,
        status="open",
        closed_at=None,
        tenant_id=None,
        org_unit_id=None,
        created_at=None,
        updated_at=None,
        created_user=None,
        updated_user=None,
    ):
        self.id = id
        self.uid = uid
        self.assessment_plan_id = assessment_plan_id
        self.ar_finding_id = ar_finding_id
        self.control_identifier = control_identifier
        self.ao_uid = ao_uid
        self.status = status
        self.closed_at = closed_at
        self.tenant_id = tenant_id
        self.org_unit_id = org_unit_id
        self.created_at = created_at
        self.updated_at = updated_at
        self.created_user = created_user
        self.updated_user = updated_user

from domain.grc.entities.poam_entity import PoamEntity


class PoamMapper:
    @staticmethod
    def to_entity(model) -> PoamEntity:
        return PoamEntity(
            id=model.id,
            uid=model.uid,
            assessment_plan_id=model.assessment_plan_id,
            ar_finding_id=model.ar_finding_id,
            control_identifier=model.control_identifier,
            ao_uid=model.ao_uid,
            status=model.status,
            closed_at=model.closed_at,
            tenant_id=model.tenant_id,
            org_unit_id=model.org_unit_id,
            created_at=model.created_at,
            updated_at=model.updated_at,
            created_user=model.created_user,
            updated_user=model.updated_user,
        )

    @staticmethod
    def to_model(entity: PoamEntity):
        from infra.grc.model.poam_model import PoamModel
        return PoamModel(
            uid=entity.uid,
            assessment_plan_id=entity.assessment_plan_id,
            ar_finding_id=entity.ar_finding_id,
            control_identifier=entity.control_identifier,
            ao_uid=entity.ao_uid,
            status=entity.status,
            tenant_id=entity.tenant_id,
            org_unit_id=entity.org_unit_id,
            created_user=entity.created_user,
            updated_user=entity.updated_user,
        )

from abc import abstractmethod
from typing import List

from domain.grc.entities.poam_entity import PoamEntity


class IPoamRepo:
    @abstractmethod
    def batch_create(self, entities: List[PoamEntity]) -> List[PoamEntity]:
        pass

import uuid
from typing import List

from jedi_common.session.database.session_context import get_session

from domain.grc.entities.poam_entity import PoamEntity
from domain.grc.repository.i_poam_repo import IPoamRepo
from infra.grc.mapper.poam_mapper import PoamMapper
from infra.grc.model.poam_model import PoamModel


class PoamRepoImpl(IPoamRepo):
    @property
    def session(self):
        return get_session()

    def batch_create(self, entities: List[PoamEntity]) -> List[PoamEntity]:
        models = []
        for entity in entities:
            entity.uid = str(uuid.uuid4())
            model = PoamMapper.to_model(entity)
            self.session.add(model)
            models.append(model)
        self.session.flush()
        return [PoamMapper.to_entity(m) for m in models]

class GrcArControlEntity:
    """AR control list item — 稽核控制項摘要"""

    def __init__(
        self,
        ar_control_uid=None,
        control_id=None,
        control_title=None,
        verdict=None,
        confidence=None,
        rationale=None,
        remarks=None,
        findings_count=0,
        group_uid=None,
        group_name=None,
    ):
        self.ar_control_uid = ar_control_uid
        self.control_id = control_id
        self.control_title = control_title
        self.verdict = verdict
        self.confidence = confidence
        self.rationale = rationale
        self.remarks = remarks
        self.findings_count = findings_count
        self.group_uid = group_uid
        self.group_name = group_name


class GrcArControlDetailEntity(GrcArControlEntity):
    """AR control detail — 含 findings、AO evidences、SSP"""

    def __init__(
        self,
        description=None,
        guidance=None,
        ssp_implementation_status=None,
        ssp_implementation_description=None,
        findings=None,
        assessment_objects=None,
        **kwargs,
    ):
        super().__init__(**kwargs)
        self.description = description
        self.guidance = guidance
        self.ssp_implementation_status = ssp_implementation_status
        self.ssp_implementation_description = ssp_implementation_description
        self.findings = findings or []
        self.assessment_objects = assessment_objects or []


class GrcArFindingEntity:
    """AR finding"""

    def __init__(
        self,
        uid=None,
        ar_control_uid=None,
        category=None,
        severity=None,
        title=None,
        description=None,
        recommendation=None,
        ao_uid=None,
        ao_name=None,
        created_at=None,
        updated_at=None,
    ):
        self.uid = uid
        self.ar_control_uid = ar_control_uid
        self.category = category
        self.severity = severity
        self.title = title
        self.description = description
        self.recommendation = recommendation
        self.ao_uid = ao_uid
        self.ao_name = ao_name
        self.created_at = created_at
        self.updated_at = updated_at


class GrcArAoEvidenceEntity:
    """AO evidence item（唯讀，來自 job_evidences）"""

    def __init__(self, uid=None, evidence_type=None, description=None, file_id=None, reference_url=None):
        self.uid = uid
        self.evidence_type = evidence_type
        self.description = description
        self.file_id = file_id
        self.reference_url = reference_url


class GrcArAoEntity:
    """AO with evidences（唯讀）"""

    def __init__(self, ao_uid=None, ao_name=None, evidences=None):
        self.ao_uid = ao_uid
        self.ao_name = ao_name
        self.evidences = evidences or []

from abc import abstractmethod
from typing import Optional, List

from jedi_common.interfaces.entities import PageDataEntity
from jedi_common.interfaces.spec.base_query_spec import PageSpec, SortSpec

from domain.grc.entities.grc_audit_entity import (
    GrcArControlEntity,
    GrcArControlDetailEntity,
    GrcArFindingEntity,
)


class IGrcAuditRepo:
    @abstractmethod
    def list_ar_controls(
        self, ap_uid: str, pager: PageSpec, sorts: List[SortSpec],
        search: str = None, verdict: str = None,
    ) -> PageDataEntity:
        pass

    @abstractmethod
    def get_ar_control_detail(self, ap_uid: str, ar_control_uid: str) -> Optional[GrcArControlDetailEntity]:
        pass

    @abstractmethod
    def update_verdict(
        self, ar_control_uid: str, verdict: str, confidence: int,
        rationale: str, remarks: str, curr_user: str,
    ) -> Optional[GrcArControlEntity]:
        pass

    @abstractmethod
    def create_finding(
        self, ar_control_uid: str, category: str, severity: str,
        title: str, description: str, recommendation: str,
        ao_uid: str, curr_user: str,
    ) -> Optional[GrcArFindingEntity]:
        pass

    @abstractmethod
    def get_finding(self, finding_uid: str) -> Optional[GrcArFindingEntity]:
        pass

    @abstractmethod
    def update_finding(
        self, finding_uid: str, category: str, severity: str,
        title: str, description: str, recommendation: str,
        ao_uid: str, curr_user: str,
    ) -> Optional[GrcArFindingEntity]:
        pass

    @abstractmethod
    def delete_finding(self, finding_uid: str) -> bool:
        pass

from domain.grc.entities.grc_audit_entity import (
    GrcArControlEntity,
    GrcArFindingEntity,
    GrcArAoEvidenceEntity,
    GrcArAoEntity,
)


class GrcAuditMapper:
    @staticmethod
    def to_ar_control_entity(ar_control, group_uid, group_name, findings_count=0) -> GrcArControlEntity:
        return GrcArControlEntity(
            ar_control_uid=str(ar_control.uid),
            control_id=ar_control.control_id,
            control_title=ar_control.control_title,
            verdict=ar_control.verdict.value if ar_control.verdict else None,
            confidence=ar_control.confidence,
            rationale=ar_control.rationale,
            remarks=ar_control.remarks,
            findings_count=findings_count,
            group_uid=group_uid,
            group_name=group_name,
        )

    @staticmethod
    def to_finding_entity(finding, ao_name=None) -> GrcArFindingEntity:
        return GrcArFindingEntity(
            uid=str(finding.uid),
            ar_control_uid=str(finding.control_result.uid) if finding.control_result else None,
            category=finding.category.value if finding.category else None,
            severity=finding.severity.value if finding.severity else None,
            title=finding.title,
            description=finding.description,
            recommendation=finding.recommendation,
            ao_uid=finding.ao_uid,
            ao_name=ao_name,
            created_at=finding.created_at,
            updated_at=finding.updated_at,
        )

    @staticmethod
    def to_ao_evidence_entity(evidence) -> GrcArAoEvidenceEntity:
        return GrcArAoEvidenceEntity(
            uid=str(evidence.uid),
            evidence_type=evidence.evidence_type,
            description=getattr(evidence, "description", None),
            file_id=evidence.file_id,
            reference_url=getattr(evidence, "reference_url", None),
        )

    @staticmethod
    def to_ao_entity(task, evidences) -> GrcArAoEntity:
        return GrcArAoEntity(
            ao_uid=str(task.uid),
            ao_name=task.title or task.task_code,
            evidences=evidences,
        )

import math
import uuid
from typing import Optional, List

from sqlalchemy import func as sa_func

from jedi_common.interfaces.entities import PageDataEntity, PageMetaEntity
from jedi_common.interfaces.spec.base_query_spec import PageSpec, SortSpec
from jedi_common.session.database.session_context import get_session

from jedi_oscal.infra.model.ap.assessment_plan import OscalAssessmentPlan
from jedi_oscal.infra.model.ap.assessment_plan_control import OscalAssessmentPlanControl
from jedi_oscal.infra.model.ap.assessment_plan_group import OscalAssessmentPlanGroup
from jedi_oscal.infra.model.ap.assessment_plan_task import OscalAssessmentPlanTask
from jedi_oscal.infra.model.ar.assessment_result import OscalAssessmentResult
from jedi_oscal.infra.model.ar.assessment_result_data import OscalAssessmentResultData
from jedi_oscal.infra.model.ar.assessment_result_control import OscalAssessmentResultControl
from jedi_oscal.infra.model.ar.assessment_result_finding import OscalAssessmentResultFinding

from domain.grc.entities.grc_audit_entity import (
    GrcArControlEntity, GrcArControlDetailEntity, GrcArFindingEntity,
    GrcArAoEntity, GrcArAoEvidenceEntity,
)
from domain.grc.repository.i_grc_audit_repo import IGrcAuditRepo
from infra.grc.mapper.grc_audit_mapper import GrcAuditMapper


class GrcAuditRepoImpl(IGrcAuditRepo):
    @property
    def session(self):
        return get_session()

    def _resolve_ar_data_id(self, ap_uid: str) -> Optional[int]:
        """ap_uid → AR → ARData(run_no=1).id"""
        row = (
            self.session.query(OscalAssessmentResultData.id)
            .join(OscalAssessmentResult, OscalAssessmentResult.id == OscalAssessmentResultData.assessment_result_id)
            .join(OscalAssessmentPlan, OscalAssessmentPlan.id == OscalAssessmentResult.assessment_plan_id)
            .filter(OscalAssessmentPlan.uid == ap_uid, OscalAssessmentResultData.run_no == 1)
            .first()
        )
        return row.id if row else None

    def list_ar_controls(self, ap_uid, pager, sorts, search=None, verdict=None):
        ar_data_id = self._resolve_ar_data_id(ap_uid)
        page = pager.page if pager else 1
        page_size = pager.page_size if pager else 25
        if ar_data_id is None:
            meta = PageMetaEntity(paging=True, page=page, page_size=page_size, total=0, total_pages=0, has_next=False, has_prev=False)
            return PageDataEntity(meta=meta, data=[])

        # findings count subquery
        findings_count_sq = (
            self.session.query(
                OscalAssessmentResultFinding.assessment_result_control_id,
                sa_func.count(OscalAssessmentResultFinding.id).label("cnt"),
            )
            .group_by(OscalAssessmentResultFinding.assessment_result_control_id)
            .subquery()
        )

        query = (
            self.session.query(
                OscalAssessmentResultControl,
                OscalAssessmentPlanGroup.uid.label("group_uid"),
                OscalAssessmentPlanGroup.name.label("group_name"),
                sa_func.coalesce(findings_count_sq.c.cnt, 0).label("findings_count"),
            )
            .filter(OscalAssessmentResultControl.assessment_result_data_id == ar_data_id)
            .outerjoin(
                OscalAssessmentPlanControl,
                OscalAssessmentPlanControl.control_id == OscalAssessmentResultControl.control_id,
            )
            .outerjoin(OscalAssessmentPlanGroup, OscalAssessmentPlanGroup.id == OscalAssessmentPlanControl.group_id)
            .outerjoin(findings_count_sq, findings_count_sq.c.assessment_result_control_id == OscalAssessmentResultControl.id)
        )

        if search:
            like = f"%{search}%"
            query = query.filter(
                (OscalAssessmentResultControl.control_id.ilike(like))
                | (OscalAssessmentResultControl.control_title.ilike(like))
            )
        if verdict:
            query = query.filter(OscalAssessmentResultControl.verdict == verdict)

        total = query.count()
        rows = query.order_by(OscalAssessmentResultControl.control_id).offset((page - 1) * page_size).limit(page_size).all()

        entities = [
            GrcAuditMapper.to_ar_control_entity(row[0], row.group_uid, row.group_name, row.findings_count)
            for row in rows
        ]
        meta = PageMetaEntity(
            paging=True, page=page, page_size=page_size, total=total,
            total_pages=math.ceil(total / page_size) if page_size else 0,
            has_next=page * page_size < total, has_prev=page > 1,
        )
        return PageDataEntity(meta=meta, data=entities)

    def get_ar_control_detail(self, ap_uid, ar_control_uid):
        ar_data_id = self._resolve_ar_data_id(ap_uid)
        if ar_data_id is None:
            return None

        ar_control = (
            self.session.query(OscalAssessmentResultControl)
            .filter(OscalAssessmentResultControl.assessment_result_data_id == ar_data_id,
                    OscalAssessmentResultControl.uid == ar_control_uid)
            .first()
        )
        if ar_control is None:
            return None

        # group info
        ap_ctrl = (
            self.session.query(OscalAssessmentPlanControl, OscalAssessmentPlanGroup)
            .join(OscalAssessmentPlanGroup, OscalAssessmentPlanGroup.id == OscalAssessmentPlanControl.group_id)
            .filter(OscalAssessmentPlanControl.control_id == ar_control.control_id)
            .first()
        )
        group_uid = str(ap_ctrl[1].uid) if ap_ctrl else None
        group_name = ap_ctrl[1].name if ap_ctrl else None

        # findings
        findings_rows = (
            self.session.query(OscalAssessmentResultFinding)
            .filter(OscalAssessmentResultFinding.assessment_result_control_id == ar_control.id)
            .all()
        )
        findings = [GrcAuditMapper.to_finding_entity(f) for f in findings_rows]

        # SSP implementation (via AP → profile → SSP)
        ssp_status = None
        ssp_desc = None
        # TODO: 查詢 ssp_control_implementations（依 AP 的 profile 關聯）

        return GrcArControlDetailEntity(
            ar_control_uid=str(ar_control.uid),
            control_id=ar_control.control_id,
            control_title=ar_control.control_title,
            verdict=ar_control.verdict.value if ar_control.verdict else None,
            confidence=ar_control.confidence,
            rationale=ar_control.rationale,
            remarks=ar_control.remarks,
            findings_count=len(findings),
            group_uid=group_uid,
            group_name=group_name,
            description=ap_ctrl[0].description if ap_ctrl else None,
            guidance=ap_ctrl[0].guidance if ap_ctrl else None,
            ssp_implementation_status=ssp_status,
            ssp_implementation_description=ssp_desc,
            findings=findings,
            assessment_objects=[],  # AO evidences 實作見下方說明
        )

    def update_verdict(self, ar_control_uid, verdict, confidence, rationale, remarks, curr_user):
        ar_control = (
            self.session.query(OscalAssessmentResultControl)
            .filter(OscalAssessmentResultControl.uid == ar_control_uid)
            .first()
        )
        if ar_control is None:
            return None
        ar_control.verdict = verdict
        ar_control.confidence = confidence
        ar_control.rationale = rationale
        ar_control.remarks = remarks
        ar_control.updated_user = curr_user
        self.session.flush()
        return GrcAuditMapper.to_ar_control_entity(ar_control, None, None)

    def create_finding(self, ar_control_uid, category, severity, title, description, recommendation, ao_uid, curr_user):
        ar_control = (
            self.session.query(OscalAssessmentResultControl)
            .filter(OscalAssessmentResultControl.uid == ar_control_uid)
            .first()
        )
        if ar_control is None:
            return None
        finding = OscalAssessmentResultFinding(
            uid=str(uuid.uuid4()),
            assessment_result_control_id=ar_control.id,
            category=category,
            severity=severity,
            title=title,
            description=description,
            recommendation=recommendation,
            ao_uid=ao_uid,
            created_user=curr_user,
            updated_user=curr_user,
        )
        self.session.add(finding)
        self.session.flush()
        return GrcAuditMapper.to_finding_entity(finding)

    def get_finding(self, finding_uid):
        finding = (
            self.session.query(OscalAssessmentResultFinding)
            .filter(OscalAssessmentResultFinding.uid == finding_uid)
            .first()
        )
        return GrcAuditMapper.to_finding_entity(finding) if finding else None

    def update_finding(self, finding_uid, category, severity, title, description, recommendation, ao_uid, curr_user):
        finding = (
            self.session.query(OscalAssessmentResultFinding)
            .filter(OscalAssessmentResultFinding.uid == finding_uid)
            .first()
        )
        if finding is None:
            return None
        finding.category = category
        finding.severity = severity
        finding.title = title
        finding.description = description
        finding.recommendation = recommendation
        finding.ao_uid = ao_uid
        finding.updated_user = curr_user
        self.session.flush()
        return GrcAuditMapper.to_finding_entity(finding)

    def delete_finding(self, finding_uid):
        finding = (
            self.session.query(OscalAssessmentResultFinding)
            .filter(OscalAssessmentResultFinding.uid == finding_uid)
            .first()
        )
        if finding is None:
            return False
        self.session.delete(finding)
        self.session.flush()
        return True

from typing import Optional, List

from jedi_common.interfaces.entities import PageDataEntity
from jedi_common.interfaces.spec.base_query_spec import PageSpec, SortSpec

from domain.grc.entities.grc_audit_entity import (
    GrcArControlEntity,
    GrcArControlDetailEntity,
    GrcArFindingEntity,
)
from domain.grc.repository.i_grc_audit_repo import IGrcAuditRepo


class GrcAuditDomainService:
    def __init__(self, grc_audit_repo: IGrcAuditRepo):
        self._repo = grc_audit_repo

    def list_ar_controls(self, ap_uid, pager, sorts, **filters) -> PageDataEntity:
        return self._repo.list_ar_controls(
            ap_uid, pager, sorts,
            search=filters.get("search"),
            verdict=filters.get("verdict"),
        )

    def get_ar_control_detail(self, ap_uid, ar_control_uid) -> Optional[GrcArControlDetailEntity]:
        return self._repo.get_ar_control_detail(ap_uid, ar_control_uid)

    def update_verdict(self, ar_control_uid, verdict, confidence, rationale, remarks, curr_user):
        return self._repo.update_verdict(ar_control_uid, verdict, confidence, rationale, remarks, curr_user)

    def create_finding(self, ar_control_uid, category, severity, title, description, recommendation, ao_uid, curr_user):
        return self._repo.create_finding(ar_control_uid, category, severity, title, description, recommendation, ao_uid, curr_user)

    def get_finding(self, finding_uid):
        return self._repo.get_finding(finding_uid)

    def update_finding(self, finding_uid, category, severity, title, description, recommendation, ao_uid, curr_user):
        return self._repo.update_finding(finding_uid, category, severity, title, description, recommendation, ao_uid, curr_user)

    def delete_finding(self, finding_uid):
        return self._repo.delete_finding(finding_uid)

from dataclasses import dataclass, field
from typing import Optional, List
from datetime import datetime


@dataclass
class ArControlDto:
    ar_control_uid: str
    control_id: str
    control_title: str
    verdict: Optional[str] = None
    confidence: Optional[int] = None
    findings_count: int = 0
    group_uid: Optional[str] = None
    group_name: Optional[str] = None

    @staticmethod
    def from_entity(entity) -> "ArControlDto":
        return ArControlDto(
            ar_control_uid=entity.ar_control_uid,
            control_id=entity.control_id,
            control_title=entity.control_title,
            verdict=entity.verdict,
            confidence=entity.confidence,
            findings_count=entity.findings_count,
            group_uid=entity.group_uid,
            group_name=entity.group_name,
        )

    @staticmethod
    def from_entity_list(entities) -> list:
        return [ArControlDto.from_entity(e) for e in entities]


@dataclass
class ArFindingDto:
    uid: str
    category: Optional[str] = None
    severity: Optional[str] = None
    title: Optional[str] = None
    description: Optional[str] = None
    recommendation: Optional[str] = None
    ao_uid: Optional[str] = None
    ao_name: Optional[str] = None
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None

    @staticmethod
    def from_entity(entity) -> "ArFindingDto":
        return ArFindingDto(
            uid=entity.uid,
            category=entity.category,
            severity=entity.severity,
            title=entity.title,
            description=entity.description,
            recommendation=entity.recommendation,
            ao_uid=entity.ao_uid,
            ao_name=entity.ao_name,
            created_at=entity.created_at,
            updated_at=entity.updated_at,
        )


@dataclass
class ArAoEvidenceDto:
    uid: str
    evidence_type: Optional[str] = None
    description: Optional[str] = None
    file_id: Optional[int] = None
    reference_url: Optional[str] = None

    @staticmethod
    def from_entity(entity) -> "ArAoEvidenceDto":
        return ArAoEvidenceDto(
            uid=entity.uid,
            evidence_type=entity.evidence_type,
            description=entity.description,
            file_id=entity.file_id,
            reference_url=entity.reference_url,
        )


@dataclass
class ArAoDto:
    ao_uid: str
    ao_name: Optional[str] = None
    evidences: List[ArAoEvidenceDto] = field(default_factory=list)

    @staticmethod
    def from_entity(entity) -> "ArAoDto":
        return ArAoDto(
            ao_uid=entity.ao_uid,
            ao_name=entity.ao_name,
            evidences=[ArAoEvidenceDto.from_entity(e) for e in entity.evidences],
        )


@dataclass
class SspImplementationDto:
    implementation_status: Optional[str] = None
    implementation_description: Optional[str] = None


@dataclass
class ArControlDetailDto(ArControlDto):
    description: Optional[str] = None
    guidance: Optional[str] = None
    rationale: Optional[str] = None
    remarks: Optional[str] = None
    ssp_implementation: Optional[SspImplementationDto] = None
    findings: List[ArFindingDto] = field(default_factory=list)
    assessment_objects: List[ArAoDto] = field(default_factory=list)

    @staticmethod
    def from_entity(entity) -> "ArControlDetailDto":
        return ArControlDetailDto(
            ar_control_uid=entity.ar_control_uid,
            control_id=entity.control_id,
            control_title=entity.control_title,
            verdict=entity.verdict,
            confidence=entity.confidence,
            findings_count=entity.findings_count,
            group_uid=entity.group_uid,
            group_name=entity.group_name,
            description=entity.description,
            guidance=entity.guidance,
            rationale=entity.rationale,
            remarks=entity.remarks,
            ssp_implementation=SspImplementationDto(
                implementation_status=entity.ssp_implementation_status,
                implementation_description=entity.ssp_implementation_description,
            ) if entity.ssp_implementation_status else None,
            findings=[ArFindingDto.from_entity(f) for f in entity.findings],
            assessment_objects=[ArAoDto.from_entity(ao) for ao in entity.assessment_objects],
        )

from jedi_common.interfaces.dto import PageDataDto, PageDto
from jedi_common.interfaces.spec.base_query_spec import PageSpec, SortSpec
from jedi_common.session.database.db import transaction

from app.grc.dto.audit_dto import ArControlDto, ArControlDetailDto, ArFindingDto
from domain.grc.service.grc_audit_domain_service import GrcAuditDomainService


class AuditService:
    def __init__(self, grc_audit_domain_service: GrcAuditDomainService):
        self._domain_service = grc_audit_domain_service

    @transaction
    def list_ar_controls(self, ap_uid: str, pager: dict, sorts: list, **filters) -> PageDataDto:
        page_spec = PageSpec(**pager) if pager else None
        sorts_spec = [SortSpec(**s) for s in sorts] if sorts else None

        result = self._domain_service.list_ar_controls(ap_uid, page_spec, sorts_spec, **filters)

        return PageDataDto(
            meta=PageDto.from_entity(result.meta),
            data=ArControlDto.from_entity_list(result.data),
        )

    @transaction
    def get_ar_control_detail(self, ap_uid: str, ar_control_uid: str):
        entity = self._domain_service.get_ar_control_detail(ap_uid, ar_control_uid)
        if entity is None:
            return None
        return ArControlDetailDto.from_entity(entity)

    @transaction
    def update_verdict(self, ar_control_uid: str, verdict: str, confidence: int, rationale: str, remarks: str, curr_user: str):
        entity = self._domain_service.update_verdict(ar_control_uid, verdict, confidence, rationale, remarks, curr_user)
        if entity is None:
            return None
        return ArControlDto.from_entity(entity)

    @transaction
    def create_finding(self, ar_control_uid: str, curr_user: str, **data):
        entity = self._domain_service.create_finding(
            ar_control_uid, data["category"], data["severity"],
            data["title"], data["description"], data.get("recommendation"),
            data["ao_uid"], curr_user,
        )
        if entity is None:
            return None
        return ArFindingDto.from_entity(entity)

    @transaction
    def get_finding(self, finding_uid: str):
        entity = self._domain_service.get_finding(finding_uid)
        if entity is None:
            return None
        return ArFindingDto.from_entity(entity)

    @transaction
    def update_finding(self, finding_uid: str, curr_user: str, **data):
        entity = self._domain_service.update_finding(
            finding_uid, data["category"], data["severity"],
            data["title"], data["description"], data.get("recommendation"),
            data["ao_uid"], curr_user,
        )
        if entity is None:
            return None
        return ArFindingDto.from_entity(entity)

    @transaction
    def delete_finding(self, finding_uid: str):
        return self._domain_service.delete_finding(finding_uid)

from marshmallow import Schema, fields

from jedi_common.interfaces.schema.common import RequestMetaSchema, EnvelopeSchema


# ── Filters ──
class ArControlFiltersSchema(Schema):
    search = fields.String(load_default=None, allow_none=True)
    verdict = fields.String(load_default=None, allow_none=True)


# ── Request ──
class ArControlListRequestSchema(RequestMetaSchema):
    filters = fields.Nested(ArControlFiltersSchema, missing={}, allow_none=True)


class VerdictRequestSchema(Schema):
    verdict = fields.String(required=True)
    confidence = fields.Integer(load_default=None, allow_none=True)
    rationale = fields.String(load_default=None, allow_none=True)
    remarks = fields.String(load_default=None, allow_none=True)


class FindingRequestSchema(Schema):
    category = fields.String(required=True)
    severity = fields.String(required=True)
    title = fields.String(required=True)
    description = fields.String(required=True)
    recommendation = fields.String(load_default=None, allow_none=True)
    ao_uid = fields.String(required=True)


class LaunchAuditRequestSchema(Schema):
    force = fields.Boolean(load_default=False)


# ── Response items ──
class ArControlResponseSchema(Schema):
    ar_control_uid = fields.String()
    control_id = fields.String()
    control_title = fields.String()
    verdict = fields.String(allow_none=True)
    confidence = fields.Integer(allow_none=True)
    findings_count = fields.Integer()
    group_uid = fields.String(allow_none=True)
    group_name = fields.String(allow_none=True)


class ArFindingResponseSchema(Schema):
    uid = fields.String()
    category = fields.String(allow_none=True)
    severity = fields.String(allow_none=True)
    title = fields.String(allow_none=True)
    description = fields.String(allow_none=True)
    recommendation = fields.String(allow_none=True)
    ao_uid = fields.String(allow_none=True)
    ao_name = fields.String(allow_none=True)
    created_at = fields.DateTime(allow_none=True)
    updated_at = fields.DateTime(allow_none=True)


class ArAoEvidenceResponseSchema(Schema):
    uid = fields.String()
    evidence_type = fields.String(allow_none=True)
    description = fields.String(allow_none=True)
    file_id = fields.Integer(allow_none=True)
    reference_url = fields.String(allow_none=True)


class ArAoResponseSchema(Schema):
    ao_uid = fields.String()
    ao_name = fields.String(allow_none=True)
    evidences = fields.Nested(ArAoEvidenceResponseSchema, many=True, dump_default=[])


class SspImplementationResponseSchema(Schema):
    implementation_status = fields.String(allow_none=True)
    implementation_description = fields.String(allow_none=True)


# ── Response envelopes ──
class ArControlListResponseSchema(EnvelopeSchema):
    data = fields.Nested(ArControlResponseSchema, many=True, dump_default=[])


class ArControlDetailInnerSchema(ArControlResponseSchema):
    description = fields.String(allow_none=True)
    guidance = fields.String(allow_none=True)
    rationale = fields.String(allow_none=True)
    remarks = fields.String(allow_none=True)
    ssp_implementation = fields.Nested(SspImplementationResponseSchema, allow_none=True)
    findings = fields.Nested(ArFindingResponseSchema, many=True, dump_default=[])
    assessment_objects = fields.Nested(ArAoResponseSchema, many=True, dump_default=[])


class ArControlDetailResponseSchema(Schema):
    data = fields.Nested(ArControlDetailInnerSchema, allow_none=True)


class FindingDetailResponseSchema(Schema):
    data = fields.Nested(ArFindingResponseSchema, allow_none=True)


class LaunchAuditResponseSchema(Schema):
    ar_uid = fields.String(allow_none=True)
    status = fields.String(allow_none=True)
    warning = fields.Boolean(dump_default=False)
    can_launch = fields.Boolean(dump_default=True)
    incomplete_tasks = fields.Integer(dump_default=0)
    total_tasks = fields.Integer(dump_default=0)


class ConfirmAuditResponseSchema(Schema):
    outcome = fields.String()
    findings_count = fields.Integer(dump_default=0)
    reverted_ao_count = fields.Integer(dump_default=0)
    poam_count = fields.Integer(dump_default=0)


class MissingVerdictSchema(Schema):
    control_id = fields.String()
    control_title = fields.String()

import logging

from dependency_injector.wiring import inject, Provide
from flask import request
from flask_apispec import MethodResource, doc, use_kwargs, marshal_with
from flask_jwt_extended import jwt_required
from jedi_common.session.auth.auth_context import get_user_context

from api.grc.serializers.audit import (
    ArControlListRequestSchema,
    ArControlListResponseSchema,
    ArControlDetailResponseSchema,
    VerdictRequestSchema,
    ArControlResponseSchema,
    FindingRequestSchema,
    ArFindingResponseSchema,
    FindingDetailResponseSchema,
    LaunchAuditRequestSchema,
    LaunchAuditResponseSchema,
    ConfirmAuditResponseSchema,
)
from app.grc.service.audit_service import AuditService
from app.project.service.oscal_audit_service import OscalAuditService
from common.enum.schema_code import AUTH_PARAMS
from common.util.response_util import return_response
from di_containers.containers import Containers

logger = logging.getLogger(__name__)


class ArControlListResource(MethodResource):
    """POST /grc/project/<pid>/ap/<ap_uid>/ar/controls/list"""

    @doc(description="取得 AR 控制項列表（含 verdict 狀態）", tags=["GRC Audit"], params=AUTH_PARAMS)
    @use_kwargs(ArControlListRequestSchema, location="json", apply=False)
    @marshal_with(ArControlListResponseSchema, apply=False)
    @jwt_required()
    @inject
    def post(
        self,
        project_uid: str,
        ap_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        payload = request.get_json(silent=True) or {}
        data = ArControlListRequestSchema().load(payload)
        pager = data.get("pager")
        sorts = data.get("sort")
        filters = data.get("filters") or {}

        result = audit_service.list_ar_controls(ap_uid, pager, sorts, **filters)
        return return_response(True, ArControlListResponseSchema().dump(result))


class ArControlDetailResource(MethodResource):
    """GET /grc/project/<pid>/ap/<ap_uid>/ar/control/<ar_control_uid>"""

    @doc(description="取得 AR 控制項詳情（含 findings、evidences、SSP）", tags=["GRC Audit"], params=AUTH_PARAMS)
    @marshal_with(ArControlDetailResponseSchema, apply=False)
    @jwt_required()
    @inject
    def get(
        self,
        project_uid: str,
        ap_uid: str,
        ar_control_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        result = audit_service.get_ar_control_detail(ap_uid, ar_control_uid)
        if result is None:
            return return_response(False, None)
        return return_response(True, ArControlDetailResponseSchema().dump({"data": result}))


class ArVerdictResource(MethodResource):
    """PUT /grc/project/<pid>/ap/<ap_uid>/ar/control/<ar_control_uid>/verdict"""

    @doc(description="寫入 / 更新稽核判定", tags=["GRC Audit"], params=AUTH_PARAMS)
    @use_kwargs(VerdictRequestSchema, location="json", apply=False)
    @marshal_with(ArControlResponseSchema, apply=False)
    @jwt_required()
    @inject
    def put(
        self,
        project_uid: str,
        ap_uid: str,
        ar_control_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        # TODO: 權限檢查 — auditor role
        # TODO: 前置條件 — AP status = auditing
        payload = request.get_json(silent=True) or {}
        data = VerdictRequestSchema().load(payload)
        user = get_user_context()

        result = audit_service.update_verdict(
            ar_control_uid, data["verdict"], data.get("confidence"),
            data.get("rationale"), data.get("remarks"), user.login_name,
        )
        if result is None:
            return return_response(False, None)
        return return_response(True, ArControlResponseSchema().dump(result))


class ArFindingCreateResource(MethodResource):
    """POST /grc/project/<pid>/ap/<ap_uid>/ar/control/<ar_control_uid>/findings"""

    @doc(description="新增稽核發現", tags=["GRC Audit"], params=AUTH_PARAMS)
    @use_kwargs(FindingRequestSchema, location="json", apply=False)
    @marshal_with(ArFindingResponseSchema, apply=False)
    @jwt_required()
    @inject
    def post(
        self,
        project_uid: str,
        ap_uid: str,
        ar_control_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        # TODO: 權限檢查 — auditor role
        # TODO: 前置條件 — AP status = auditing
        payload = request.get_json(silent=True) or {}
        data = FindingRequestSchema().load(payload)
        user = get_user_context()

        result = audit_service.create_finding(ar_control_uid, user.login_name, **data)
        if result is None:
            return return_response(False, None)
        return return_response(True, ArFindingResponseSchema().dump(result))


class ArFindingDetailResource(MethodResource):
    """GET/PUT/DELETE /grc/project/<pid>/ap/<ap_uid>/ar/finding/<finding_uid>"""

    @doc(description="取得稽核發現詳情", tags=["GRC Audit"], params=AUTH_PARAMS)
    @marshal_with(FindingDetailResponseSchema, apply=False)
    @jwt_required()
    @inject
    def get(
        self,
        project_uid: str,
        ap_uid: str,
        finding_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        result = audit_service.get_finding(finding_uid)
        if result is None:
            return return_response(False, None)
        return return_response(True, FindingDetailResponseSchema().dump({"data": result}))

    @doc(description="更新稽核發現", tags=["GRC Audit"], params=AUTH_PARAMS)
    @use_kwargs(FindingRequestSchema, location="json", apply=False)
    @marshal_with(ArFindingResponseSchema, apply=False)
    @jwt_required()
    @inject
    def put(
        self,
        project_uid: str,
        ap_uid: str,
        finding_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        # TODO: 權限檢查 — auditor role
        # TODO: 前置條件 — AP status = auditing
        payload = request.get_json(silent=True) or {}
        data = FindingRequestSchema().load(payload)
        user = get_user_context()

        result = audit_service.update_finding(finding_uid, user.login_name, **data)
        if result is None:
            return return_response(False, None)
        return return_response(True, ArFindingResponseSchema().dump(result))

    @doc(description="刪除稽核發現", tags=["GRC Audit"], params=AUTH_PARAMS)
    @jwt_required()
    @inject
    def delete(
        self,
        project_uid: str,
        ap_uid: str,
        finding_uid: str,
        audit_service: AuditService = Provide[Containers.grc_container.audit_service],
    ):
        # TODO: 權限檢查 — auditor role
        # TODO: 前置條件 — AP status = auditing
        result = audit_service.delete_finding(finding_uid)
        return return_response(True, result)


class LaunchAuditResource(MethodResource):
    """POST /grc/project/<pid>/ap/<ap_uid>/launch-audit"""

    @doc(description="啟動外部稽核", tags=["GRC Audit"], params=AUTH_PARAMS)
    @use_kwargs(LaunchAuditRequestSchema, location="json", apply=False)
    @marshal_with(LaunchAuditResponseSchema, apply=False)
    @jwt_required()
    @inject
    def post(
        self,
        project_uid: str,
        ap_uid: str,
        oscal_audit_service: OscalAuditService = Provide[
            Containers.project_container.oscal_audit_service
        ],
    ):
        # TODO: 權限檢查 — auditor / owner
        payload = request.get_json(silent=True) or {}
        data = LaunchAuditRequestSchema().load(payload)
        user = get_user_context()

        result = oscal_audit_service.launch_audit(
            ap_uid=ap_uid,
            force=data.get("force", False),
            curr_user=user.login_name,
        )
        return return_response(True, LaunchAuditResponseSchema().dump(result))


class ConfirmAuditResource(MethodResource):
    """POST /grc/project/<pid>/ap/<ap_uid>/confirm-audit"""

    @doc(description="確認稽核結案", tags=["GRC Audit"], params=AUTH_PARAMS)
    @marshal_with(ConfirmAuditResponseSchema, apply=False)
    @jwt_required()
    @inject
    def post(
        self,
        project_uid: str,
        ap_uid: str,
        oscal_audit_service: OscalAuditService = Provide[
            Containers.project_container.oscal_audit_service
        ],
    ):
        # TODO: 權限檢查 — auditor / owner
        user = get_user_context()

        result = oscal_audit_service.confirm_audit(
            ap_uid=ap_uid,
            curr_user=user.login_name,
        )
        return return_response(True, ConfirmAuditResponseSchema().dump(result))

import uuid
from datetime import datetime

from jedi_common.handler.exception import NotFound, PreconditionFailedError, BadRequestError
from jedi_common.session.database.db import transaction
from jedi_common.session.database.session_context import get_session

from common.code.grc_error_code import GrcErrorCode
from domain.grc.entities.poam_entity import PoamEntity


class OscalAuditService:
    def __init__(
        self,
        assessment_plan_service,
        assessment_result_service,
        assessment_result_data_domain_service,
        assessment_result_control_domain_service,
        assessment_result_finding_domain_service,
        workflow_execution_service,
        poam_repo,
        assessment_plan_task_workflow_execution_mapping_service,
    ):
        self._ap_service = assessment_plan_service
        self._ar_service = assessment_result_service
        self._ar_data_service = assessment_result_data_domain_service
        self._ar_control_service = assessment_result_control_domain_service
        self._ar_finding_service = assessment_result_finding_domain_service
        self._wf_service = workflow_execution_service
        self._poam_repo = poam_repo
        self._ap_task_wf_mapping_service = assessment_plan_task_workflow_execution_mapping_service

    @transaction
    def launch_audit(self, ap_uid: str, force: bool, curr_user) -> dict:
        session = get_session()

        # 1. 查 AP
        ap = self._ap_service.get_assessment_plan(uid=ap_uid)
        if ap is None:
            raise NotFound(GrcErrorCode.GRC_AP_NOT_FOUND)
        if ap.status != "active":
            raise PreconditionFailedError(GrcErrorCode.GRC_AP_NOT_ACTIVE)

        # 2. 統計未完成 tasks
        from jedi_oscal.infra.model.ap.assessment_plan_task import OscalAssessmentPlanTask
        incomplete_count = (
            session.query(OscalAssessmentPlanTask)
            .filter(
                OscalAssessmentPlanTask.assessment_plan_id == ap.id,
                OscalAssessmentPlanTask.status != "completed",
            )
            .count()
        )

        if not force and incomplete_count > 0:
            return {
                "warning": True,
                "incomplete_tasks": incomplete_count,
                "message": f"尚有 {incomplete_count} 個未完成的任務，確認要啟動稽核？",
            }

        # 3. 查現有 AR（start_oscal_project 已建立空白 AR）
        ar = self._ar_service.get_assessment_result(assessment_plan_id=ap.id)
        if ar is None:
            raise PreconditionFailedError(GrcErrorCode.GRC_AR_NOT_FOUND)

        # 4. 建立 assessment_result_datas (run_no=1)
        from jedi_oscal.domain.entity.ar.assessment_result_data_entity import AssessmentResultDataEntity
        ar_data = AssessmentResultDataEntity(
            uid=str(uuid.uuid4()),
            assessment_result_id=ar.id,
            run_no=1,
            title="稽核執行",
            started_at=datetime.now(),
        )
        ar_data = self._ar_data_service.add_assessment_result_data(ar_data, curr_user)

        # 5. 從 AP controls 初始化 ar_controls（verdict=null）
        from jedi_oscal.infra.model.ap.assessment_plan_control import OscalAssessmentPlanControl
        ap_controls = (
            session.query(OscalAssessmentPlanControl)
            .filter(OscalAssessmentPlanControl.assessment_plan_id == ap.id)
            .all()
        )

        from jedi_oscal.domain.entity.ar.assessment_result_control_entity import AssessmentResultControlEntity
        for apc in ap_controls:
            arc = AssessmentResultControlEntity(
                uid=str(uuid.uuid4()),
                assessment_result_data_id=ar_data.id,
                control_id=apc.control_id,
                control_title=apc.control_title,
                verdict=None,
            )
            self._ar_control_service.add_assessment_result_control(arc, curr_user)

        # 6. 更新 AP status → auditing
        self._ap_service.update_assessment_plan(ap.id, {"status": "auditing"}, curr_user)

        return {"warning": False, "ap_uid": ap_uid, "status": "auditing"}

    @transaction
    def confirm_audit(self, ap_uid: str, curr_user) -> dict:
        session = get_session()

        # 1. 查 AP
        ap = self._ap_service.get_assessment_plan(uid=ap_uid)
        if ap is None:
            raise NotFound(GrcErrorCode.GRC_AP_NOT_FOUND)
        if ap.status != "auditing":
            raise PreconditionFailedError(GrcErrorCode.GRC_AP_NOT_AUDITING)

        # 2. 查所有 ar_controls
        ar = self._ar_service.get_assessment_result(assessment_plan_id=ap.id)
        from jedi_oscal.infra.model.ar.assessment_result_data import OscalAssessmentResultData
        ar_data = (
            session.query(OscalAssessmentResultData)
            .filter(OscalAssessmentResultData.assessment_result_id == ar.id, OscalAssessmentResultData.run_no == 1)
            .first()
        )

        from jedi_oscal.infra.model.ar.assessment_result_control import OscalAssessmentResultControl
        ar_controls = (
            session.query(OscalAssessmentResultControl)
            .filter(OscalAssessmentResultControl.assessment_result_data_id == ar_data.id)
            .all()
        )

        # 3. 驗證全部有 verdict
        missing = [arc for arc in ar_controls if arc.verdict is None]
        if missing:
            raise BadRequestError(GrcErrorCode.GRC_VERDICT_INCOMPLETE)

        # 4. 判定結果
        verdicts = {arc.verdict for arc in ar_controls}
        all_pass = verdicts <= {"pass", "na"}
        new_status = "closed" if all_pass else "remediation"

        # 5. 設定 completed_at
        ar_data.completed_at = datetime.now()
        session.flush()

        # 6. 矯正路徑（有 fail/partial 時）
        if not all_pass:
            # 收集 findings 的 ao_uid
            from jedi_oscal.infra.model.ar.assessment_result_finding import OscalAssessmentResultFinding
            failed_control_ids = [arc.id for arc in ar_controls if arc.verdict in ("fail", "partial")]
            findings = (
                session.query(OscalAssessmentResultFinding)
                .filter(OscalAssessmentResultFinding.assessment_result_control_id.in_(failed_control_ids))
                .all()
            )

            # Revert failed AO workflows
            ao_uids = list({f.ao_uid for f in findings if f.ao_uid})
            for ao_uid in ao_uids:
                self._revert_ao_workflow(ao_uid, curr_user)

            # Batch create POA&M
            from jedi_common.session.auth.auth_context import get_user_context
            user_ctx = get_user_context()
            poam_entities = []
            for f in findings:
                arc = next((a for a in ar_controls if a.id == f.assessment_result_control_id), None)
                poam_entities.append(PoamEntity(
                    assessment_plan_id=ap.id,
                    ar_finding_id=f.id,
                    control_identifier=arc.control_id if arc else "",
                    ao_uid=f.ao_uid or "",
                    status="open",
                    tenant_id=user_ctx.tenant_id,
                    org_unit_id=user_ctx.org_unit_id,
                    created_user=str(curr_user),
                    updated_user=str(curr_user),
                ))
            if poam_entities:
                self._poam_repo.batch_create(poam_entities)

        # 7. 更新 AP status
        self._ap_service.update_assessment_plan(ap.id, {"status": new_status}, curr_user)

        return {"ap_uid": ap_uid, "status": new_status, "poam_count": len(findings) if not all_pass else 0}

    def _revert_ao_workflow(self, ao_uid: str, curr_user):
        """找到 AO 對應的 workflow_execution，revert 到第一個 USER job"""
        session = get_session()

        # ao_uid → assessment_plan_task → workflow_execution_mapping → workflow_execution
        from jedi_oscal.infra.model.ap.assessment_plan_task import OscalAssessmentPlanTask
        from infra.project.models.assessment_plan_task_workflow_execution_mapping import (
            AssessmentPlanTaskWorkflowExecutionMapping,
        )
        from jedi_flow_engine.infra.models.job_execution import JobExecution

        task = session.query(OscalAssessmentPlanTask).filter(OscalAssessmentPlanTask.uid == ao_uid).first()
        if task is None:
            return

        mapping = (
            session.query(AssessmentPlanTaskWorkflowExecutionMapping)
            .filter(AssessmentPlanTaskWorkflowExecutionMapping.assessment_plan_task_id == task.id)
            .first()
        )
        if mapping is None:
            return

        wf_exec_id = mapping.workflow_execution_id

        # 找最新的 COMPLETED 或 PROCESSING job（revert 起點）
        latest_job = (
            session.query(JobExecution)
            .filter(
                JobExecution.workflow_execution_id == wf_exec_id,
                JobExecution.type == "USER",
                JobExecution.status.in_(["COMPLETED", "PROCESSING"]),
            )
            .order_by(JobExecution.end_time.desc().nullslast())
            .first()
        )
        if latest_job is None:
            return

        # 找第一個 USER job（revert 目標）
        first_job = (
            session.query(JobExecution)
            .filter(
                JobExecution.workflow_execution_id == wf_exec_id,
                JobExecution.type == "USER",
            )
            .order_by(JobExecution.start_time.asc())
            .first()
        )
        if first_job is None or first_job.id == latest_job.id:
            return

        from jedi_common.session.auth.auth_context import get_user_context
        user = get_user_context()
        self._wf_service.revert_job(
            workflow_uid=str(wf_exec_id),
            job_id=latest_job.template_job_id,
            revert_to_job_id=first_job.template_job_id,
            comment="稽核不通過，自動退回",
            user=str(user.id),
            user_nickname=user.nickname,
        )

# ── Audit ─────────────────────────────────────────────────────────────
grc_audit_repo = providers.Singleton(GrcAuditRepoImpl)
poam_repo = providers.Singleton(PoamRepoImpl)

grc_audit_domain_service = providers.Factory(
    GrcAuditDomainService,
    grc_audit_repo=grc_audit_repo,
)

audit_service = providers.Factory(
    AuditService,
    grc_audit_domain_service=grc_audit_domain_service,
)

from app.grc.service.audit_service import AuditService
from domain.grc.service.grc_audit_domain_service import GrcAuditDomainService
from infra.grc.repository.grc_audit_repo_impl import GrcAuditRepoImpl
from infra.grc.repository.poam_repo_impl import PoamRepoImpl

oscal_audit_service = providers.Factory(
    OscalAuditService,
    assessment_plan_service=oscal_container.assessment_plan_service,
    assessment_result_service=oscal_container.assessment_result_service,
    assessment_result_data_domain_service=oscal_container.assessment_result_data_domain_service,
    assessment_result_control_domain_service=oscal_container.assessment_result_control_domain_service,
    assessment_result_finding_domain_service=oscal_container.assessment_result_finding_domain_service,
    workflow_execution_service=workflow_execution_container.workflow_execution_service,
    poam_repo=grc_container.poam_repo,
    assessment_plan_task_workflow_execution_mapping_service=associations_container.assessment_plan_task_workflow_execution_mapping_service,
)

from app.project.service.oscal_audit_service import OscalAuditService

# Override project_container to inject grc_container (for oscal_audit_service → poam_repo)
project_container.override_providers(
    grc_container=grc_container,
)

from api.grc.routes.audit_route import (
    ArControlListResource,
    ArControlDetailResource,
    ArVerdictResource,
    ArFindingCreateResource,
    ArFindingDetailResource,
    LaunchAuditResource,
    ConfirmAuditResource,
)

# --- Audit (AR) ---
api.add_resource(
    LaunchAuditResource,
    "/project/<project_uid>/ap/<ap_uid>/launch-audit",
)
api.add_resource(
    ConfirmAuditResource,
    "/project/<project_uid>/ap/<ap_uid>/confirm-audit",
)
api.add_resource(
    ArControlListResource,
    "/project/<project_uid>/ap/<ap_uid>/ar/controls/list",
)
api.add_resource(
    ArControlDetailResource,
    "/project/<project_uid>/ap/<ap_uid>/ar/control/<ar_control_uid>",
)
api.add_resource(
    ArVerdictResource,
    "/project/<project_uid>/ap/<ap_uid>/ar/control/<ar_control_uid>/verdict",
)
api.add_resource(
    ArFindingCreateResource,
    "/project/<project_uid>/ap/<ap_uid>/ar/control/<ar_control_uid>/findings",
)
api.add_resource(
    ArFindingDetailResource,
    "/project/<project_uid>/ap/<ap_uid>/ar/finding/<finding_uid>",
)

from jedi_common.handler.exception import ForbiddenError, PreconditionFailedError
from common.code.grc_error_code import GrcErrorCode


def _check_auditor_role(project_uid: str, user_id: int):
    """檢查使用者是否具有 auditor 角色。
    project_participants.project_id 來自 WorkflowExecution.id（非 compliance.projects.id），
    需透過 compliance.projects.uid → project_assessment_plan_mapping.project_id 解析。
    """
    from jedi_common.session.database.session_context import get_session
    from infra.participant.model.project_participant import ProjectParticipant
    from infra.project.models.project_assessment_plan_mapping import ProjectAssessmentPlanMapping
    from jedi_project.infra.models.project import Project

    session = get_session()

    # Step 1: project_uid → project_id (from compliance.projects)
    project = session.query(Project.id).filter(Project.uid == project_uid).first()
    if project is None:
        from jedi_common.handler.exception import NotFound
        raise NotFound(GrcErrorCode.GRC_PROJECT_NOT_FOUND)

    # Step 2: project_id → project_assessment_plan_mapping.project_id (= workflow_execution.id)
    mapping = session.query(ProjectAssessmentPlanMapping.project_id).filter(
        ProjectAssessmentPlanMapping.project_id == project.id
    ).first()
    if mapping is None:
        from jedi_common.handler.exception import NotFound
        raise NotFound(GrcErrorCode.GRC_PROJECT_NOT_FOUND)

    # Step 3: 用 mapping.project_id 查 participant（project_participants.project_id = workflow_execution.id）
    participant = session.query(ProjectParticipant).filter(
        ProjectParticipant.project_id == mapping.project_id,
        ProjectParticipant.user_id == user_id,
    ).first()

    if participant is None or participant.role != "auditor":
        raise ForbiddenError(GrcErrorCode.GRC_NOT_AUDITOR)

def _check_ap_status_auditing(ap_uid: str):
    """驗證 AP 處於 auditing 狀態"""
    from jedi_common.session.database.session_context import get_session
    from jedi_oscal.infra.model.ap.assessment_plan import OscalAssessmentPlan

    session = get_session()
    ap = session.query(OscalAssessmentPlan).filter(OscalAssessmentPlan.uid == ap_uid).first()
    if ap is None:
        from jedi_common.handler.exception import NotFound
        raise NotFound(GrcErrorCode.GRC_AP_NOT_FOUND)
    if ap.status != "auditing":
        raise PreconditionFailedError(GrcErrorCode.GRC_AP_NOT_AUDITING)

user = get_user_context()
_check_auditor_role(project_uid, user.id)
_check_ap_status_auditing(ap_uid)

python -c "
import ast, sys
files = [
    'api/grc/routes/audit_route.py',
    'api/grc/serializers/audit.py',
    'app/grc/service/audit_service.py',
    'app/grc/dto/audit_dto.py',
    'app/project/service/oscal_audit_service.py',
    'domain/grc/service/grc_audit_domain_service.py',
    'domain/grc/repository/i_grc_audit_repo.py',
    'domain/grc/entities/grc_audit_entity.py',
    'domain/grc/entities/poam_entity.py',
    'domain/grc/repository/i_poam_repo.py',
    'infra/grc/repository/grc_audit_repo_impl.py',
    'infra/grc/repository/poam_repo_impl.py',
    'infra/grc/mapper/grc_audit_mapper.py',
    'infra/grc/mapper/poam_mapper.py',
    'infra/grc/model/poam_model.py',
    'di_containers/grc/grc_containers.py',
    'di_containers/project/project_containers.py',
    'api/grc/__init__.py',
    'common/code/grc_error_code.py',
]
for f in files:
    try:
        with open(f) as fh: ast.parse(fh.read())
        print(f'OK: {f}')
    except SyntaxError as e:
        print(f'FAIL: {f} -> {e}')
        sys.exit(1)
"

python main_app.py
# 確認無 import error，server 正常啟動在 port 8000
# 開啟 http://localhost:8000/swagger-ui/ 確認新 route 出現在 GRC Audit tag 下