FR-043 稽核計畫 docx 匯入 Implementation Plan

-- Date: 2026-07-02
-- =============================================================================
-- FR-043 稽核計畫 docx 匯入 — ap_docx_parse_jobs 表
-- 1. 新建 oscal.ap_docx_parse_jobs（AP docx 解析工作記錄，TTL 24h）
-- 2. index（source / status / tenant）
-- 3. RLS + policy
-- 4. GRANT cm_app（表 + sequence）
-- 5. INSERT schema_migrations
-- =============================================================================

-- 1. 建表 (2026-07-02)
CREATE TABLE oscal.ap_docx_parse_jobs (
    id             SERIAL PRIMARY KEY,
    uid            VARCHAR(36) NOT NULL UNIQUE,                 -- UUIDv4（= parse_uid）
    tenant_id      INTEGER NOT NULL,                            -- RLS
    source_type    VARCHAR(20) NOT NULL DEFAULT 'ap',           -- 固定 'ap'
    source_uid     VARCHAR(36) NOT NULL,                        -- ap.uid
    status         VARCHAR(20) NOT NULL DEFAULT 'pending',       -- pending|awaiting_review|completed|failed|expired|discarded
    file_path      VARCHAR(255),                                 -- file upload uid
    file_name      VARCHAR(255),
    file_size      BIGINT,
    parsed_result  JSONB,                                        -- ParsedApReport + suggested payload + party 配對建議
    error_code     VARCHAR(40),
    error_message  TEXT,
    import_summary JSONB,                                        -- confirm 後寫入
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,                -- 軟刪除
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_user   VARCHAR(50) NOT NULL,
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_user   VARCHAR(50) NOT NULL
);

-- 2. index (2026-07-02)
CREATE INDEX idx_ap_docx_parse_jobs_source ON oscal.ap_docx_parse_jobs(source_type, source_uid);
CREATE INDEX idx_ap_docx_parse_jobs_status ON oscal.ap_docx_parse_jobs(status, created_at);
CREATE INDEX idx_ap_docx_parse_jobs_tenant ON oscal.ap_docx_parse_jobs(tenant_id);

-- 3. RLS (2026-07-02)
ALTER TABLE oscal.ap_docx_parse_jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY rls_ap_docx_parse_jobs ON oscal.ap_docx_parse_jobs
    USING (tenant_id::text = current_setting('app.user_id', true)
        OR current_setting('app.allowed_tenant_paths', true) LIKE '%' || tenant_id::text || '%');

-- 4. GRANT cm_app (2026-07-02)
GRANT SELECT, INSERT, UPDATE, DELETE ON oscal.ap_docx_parse_jobs TO cm_app;
GRANT USAGE, SELECT ON SEQUENCE oscal.ap_docx_parse_jobs_id_seq TO cm_app;

-- 5. schema_migrations (2026-07-02) — 真實表為 (filename, note) PK，非 version
INSERT INTO public.schema_migrations(filename, note) VALUES
  ('2026-07-02-ap-docx-parse-jobs.sql', 'FR-043 Task A1 / oscal.ap_docx_parse_jobs')
ON CONFLICT (filename) DO NOTHING;

PGPASSWORD='<查 .env>' psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev \
  --single-transaction -v ON_ERROR_STOP=1 -f scripts/sql/2026-07-02-ap-docx-parse-jobs.sql

PGPASSWORD='<查 .env>' psql -h 192.168.50.188 -p 25432 -U cm_app -d guidant_ai_dev -c \
  "SELECT count(*) FROM oscal.ap_docx_parse_jobs;"

git add scripts/sql/2026-07-02-ap-docx-parse-jobs.sql
git commit -m "feat(FR-043): ap_docx_parse_jobs 表 migration（RLS + GRANT cm_app）"

from datetime import datetime
from typing import Optional


class ApDocxParseJobEntity:
    """AP docx 解析任務 Domain Entity（FR-043）。"""

    def __init__(
        self,
        id: Optional[int] = None,
        uid: Optional[str] = None,
        tenant_id: Optional[int] = None,
        source_type: Optional[str] = "ap",
        source_uid: Optional[str] = None,      # ap.uid
        status: Optional[str] = None,
        file_path: Optional[str] = None,
        file_name: Optional[str] = None,
        file_size: Optional[int] = None,
        parsed_result: Optional[dict] = None,
        error_code: Optional[str] = None,
        error_message: Optional[str] = None,
        import_summary: Optional[dict] = None,
        is_active: bool = True,
        created_at: Optional[datetime] = None,
        created_user: Optional[str] = None,
        updated_at: Optional[datetime] = None,
        updated_user: Optional[str] = None,
    ):
        self.id = id
        self.uid = uid
        self.tenant_id = tenant_id
        self.source_type = source_type
        self.source_uid = source_uid
        self.status = status
        self.file_path = file_path
        self.file_name = file_name
        self.file_size = file_size
        self.parsed_result = parsed_result
        self.error_code = error_code
        self.error_message = error_message
        self.import_summary = import_summary
        self.is_active = is_active
        self.created_at = created_at
        self.created_user = created_user
        self.updated_at = updated_at
        self.updated_user = updated_user


class ApDocxParseJobQueryEntity:
    def __init__(
        self,
        uid: Optional[str] = None,
        tenant_id: Optional[int] = None,
        source_type: Optional[str] = None,
        source_uid: Optional[str] = None,
        status: Optional[str] = None,
        is_active: Optional[bool] = True,
    ):
        self.uid = uid
        self.tenant_id = tenant_id
        self.source_type = source_type
        self.source_uid = source_uid
        self.status = status
        self.is_active = is_active

from typing import Optional

from sqlalchemy import BigInteger, Boolean, Integer, String, Text
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column

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


class ApDocxParseJob(BaseModel, TenantScopedMixinModel):
    """AP docx 解析工作記錄（FR-043）。"""

    __tablename__ = "ap_docx_parse_jobs"
    __table_args__ = {"schema": "oscal"}

    uid: Mapped[str] = mapped_column(String(36), unique=True, nullable=False)
    source_type: Mapped[str] = mapped_column(String(20), nullable=False, default="ap")
    source_uid: Mapped[str] = mapped_column(String(36), nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending")
    file_path: Mapped[Optional[str]] = mapped_column(String(255))
    file_name: Mapped[Optional[str]] = mapped_column(String(255))
    file_size: Mapped[Optional[int]] = mapped_column(BigInteger)
    parsed_result: Mapped[Optional[dict]] = mapped_column(JSONB)
    error_code: Mapped[Optional[str]] = mapped_column(String(40))
    error_message: Mapped[Optional[str]] = mapped_column(Text)
    import_summary: Mapped[Optional[dict]] = mapped_column(JSONB)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)

import uuid
from typing import Optional

from domain.grc.entity.ap_docx_parse_job_entity import (
    ApDocxParseJobEntity,
    ApDocxParseJobQueryEntity,
)
from domain.grc.repository.i_ap_docx_parse_job_repo import IApDocxParseJobRepo


class ApDocxParseJobDomainService:
    """AP docx 解析任務 domain service（FR-043）。"""

    def __init__(self, repo: IApDocxParseJobRepo):
        self._repo = repo

    def create(self, source_uid: str, file_name: str, file_size: int,
               tenant_id: int, user: str) -> ApDocxParseJobEntity:
        return self._repo.add(ApDocxParseJobEntity(
            uid=str(uuid.uuid4()), tenant_id=tenant_id,
            source_type="ap", source_uid=source_uid,
            file_name=file_name, file_size=file_size, status="pending",
            created_user=user, updated_user=user,
        ))

    def get_one(self, uid: str) -> Optional[ApDocxParseJobEntity]:
        return self._repo.get_one(ApDocxParseJobQueryEntity(uid=uid))

    def update_status(self, uid: str, status: str, user: str, **fields):
        job = self.get_one(uid)
        if not job:
            return None
        return self._repo.update(job.id, {"status": status, **fields}, user)

    def write_parsed_result(self, uid: str, parsed_result: dict, user: str):
        return self.update_status(uid, "awaiting_review", user, parsed_result=parsed_result)

    def write_error(self, uid: str, error_code: str, error_message: str, user: str):
        return self.update_status(uid, "failed", user,
                                  error_code=error_code, error_message=error_message)

    def write_import_summary(self, uid: str, summary: dict, user: str):
        return self.update_status(uid, "completed", user, import_summary=summary)

    def deactivate(self, uid: str, user: str) -> None:
        self._repo.deactivate(uid, user)

set -a; source .env; set +a
python -c "from domain.grc.service.ap_docx_parse_job_domain_service import ApDocxParseJobDomainService; \
from infra.grc.repository.ap_docx_parse_job_repo_impl import ApDocxParseJobRepoImpl; \
from infra.grc.model.ap_docx_parse_job import ApDocxParseJob; print('ok')"

git add domain/grc/entity/ap_docx_parse_job_entity.py \
        domain/grc/repository/i_ap_docx_parse_job_repo.py \
        domain/grc/service/ap_docx_parse_job_domain_service.py \
        infra/grc/model/ap_docx_parse_job.py \
        infra/grc/mapper/ap_docx_parse_job_mapper.py \
        infra/grc/repository/ap_docx_parse_job_repo_impl.py
git commit -m "feat(FR-043): ap_docx_parse_job entity/model/mapper/repo/domain service"

from infra.grc.repository.ap_docx_parse_job_repo_impl import ApDocxParseJobRepoImpl
from domain.grc.service.ap_docx_parse_job_domain_service import ApDocxParseJobDomainService
# ...
ap_docx_parse_job_repo = providers.Singleton(ApDocxParseJobRepoImpl)
ap_docx_parse_job_domain_service = providers.Factory(
    ApDocxParseJobDomainService, repo=ap_docx_parse_job_repo,
)

mkdir -p test/fixtures/ap_docx
cp "/Users/chouraymond/Desktop/AirAsia實際稽核檔案/AP稽核計畫匯入測試檔案/亞航-CMMC L1內部稽核報告20260612(稿).docx" \
   test/fixtures/ap_docx/airasia_cmmc_l1_sample.docx

from abc import ABC, abstractmethod
from dataclasses import dataclass, field
from typing import Dict, List, Optional


@dataclass
class ParsedApReport:
    """版本無關中間結構（adapter 產出 → app service 組 payload 只依賴這個）。"""
    adapter_version: str
    title: Optional[str] = None
    audit_date: Optional[str] = None          # ISO date "2026-06-02"（無法解析則 None）
    audit_date_text: Optional[str] = None      # 時段全文（"上午10:00-12:30"）→ 進備註
    audit_org: Optional[str] = None            # "鈊安資安顧問"
    scope_text: Optional[str] = None
    methods: List[str] = field(default_factory=list)             # AUDIT_METHOD value（見 B3）
    method_guidances: Dict[str, str] = field(default_factory=dict)  # {method_value: 查核指引}
    basis_text: Optional[str] = None           # §依據 全文
    objective_text: Optional[str] = None        # §目的 全文
    raw_sections: Dict[str, str] = field(default_factory=dict)   # {header: full text}（除錯/未來擴充）


class ApReportParserAdapter(ABC):
    version: str = ""

    @abstractmethod
    def detect(self, doc) -> bool:
        """章節結構特徵偵測（header-label 命中）。"""

    @abstractmethod
    def parse(self, doc) -> ParsedApReport:
        """解析成版本無關中間結構。"""

from typing import List
import docx  # python-docx

from app.grc.service.ap_report_parser.base import ApReportParserAdapter, ParsedApReport


class ApReportUnsupportedFormat(Exception):
    """全 adapter miss。app service 捕捉 → 400 格式無法辨識 + 支援版本清單。"""
    def __init__(self, supported_versions: List[str]):
        self.supported_versions = supported_versions
        super().__init__("no adapter matched")


class ApReportParserRegistry:
    def __init__(self, adapters: List[ApReportParserAdapter]):
        self._adapters = adapters

    @property
    def supported_versions(self) -> List[str]:
        return [a.version for a in self._adapters]

    def parse_file(self, file_path: str) -> ParsedApReport:
        doc = docx.Document(file_path)
        for adapter in self._adapters:
            if adapter.detect(doc):
                return adapter.parse(doc)
        raise ApReportUnsupportedFormat(self.supported_versions)

import os
import pytest
from app.grc.service.ap_report_parser.airasia_cmmc_l1_v1 import AirAsiaCmmcL1V1Adapter
import docx

FIXTURE = os.path.join(os.path.dirname(__file__), "fixtures/ap_docx/airasia_cmmc_l1_sample.docx")


@pytest.fixture
def parsed():
    adapter = AirAsiaCmmcL1V1Adapter()
    doc = docx.Document(FIXTURE)
    assert adapter.detect(doc) is True
    return adapter.parse(doc)


def test_title(parsed):
    assert "CMMC 2.0 Level 1內部稽核報告" in parsed.title

def test_audit_date(parsed):
    assert parsed.audit_date == "2026-06-02"
    assert "10:00" in (parsed.audit_date_text or "")

def test_audit_org(parsed):
    assert parsed.audit_org == "鈊安資安顧問"

def test_scope_text(parsed):
    assert "屏東軍機性能提升案" in parsed.scope_text
    assert "AACL-CMMC-SSP-01" in parsed.scope_text

def test_methods(parsed):
    # value 對照 system_menu AUDIT_METHOD（Step 0 查到的實際 code）
    assert set(parsed.methods) >= {"INTERVIEW", "EXAMINE", "TEST"}

def test_method_guidances(parsed):
    # §稽核方法 敘述拆出的每方法查核指引（至少每個 method 有非空指引）
    for m in parsed.methods:
        assert parsed.method_guidances.get(m)

def test_basis_and_objective(parsed):
    assert "CMMC" in parsed.basis_text
    assert "DoW" in parsed.objective_text or "供應鏈" in parsed.objective_text

git add test/fixtures/ap_docx/airasia_cmmc_l1_sample.docx test/test_ap_report_parser_airasia.py \
        app/grc/service/ap_report_parser/
git commit -m "feat(FR-043): AP 報告 parser adapter registry + 亞航 CMMC L1 v1（TDD）"

GRC_AP_DOCX_FILE_TOO_LARGE   = ("上傳檔案超過大小限制（20MB）", "GRC_400090")
GRC_AP_DOCX_FORMAT_UNSUPPORTED = ("無法辨識的稽核報告格式，目前支援：{versions}", "GRC_400091")

def resolve_ap_and_check_participant(self, ap_uid: str, curr_user_id: int):
    """preview 守門：AP 存在 + user 是該 project participant（任一角色，不卡階段）。
    caller 必須在 @transaction scope 內。回傳 ApEntity。"""
    ap = self._ap_repo.get_by_uid(ap_uid)
    if ap is None:
        raise NotFound(GrcErrorCode.GRC_AP_NOT_FOUND)
    rnd = self._rounds.get_by_assessment_plan_id(ap.id)
    if rnd is None:
        raise NotFound(GrcErrorCode.GRC_ROUND_NOT_FOUND)
    participant = self._participant_domain_service.get_one(
        ProjectParticipantQueryEntity(project_id=rnd.project_id, user_id=curr_user_id))
    if participant is None:
        raise ForbiddenError(GrcErrorCode.GRC_NOT_AUDITOR)  # 或更貼切的既有 forbidden 碼
    return ap

git add common/code/grc_error_code.py app/grc/service/assessment_plan_app_service.py
git commit -m "feat(FR-043): AP docx error codes + gate helper 提為 public（auditor/participant 兩版）"

from marshmallow import Schema, fields, EXCLUDE
from api.project.serializers.audit_round import _TaskItem  # 復用行程形狀


class ApDocxImportParseResponseSchema(Schema):
    parse_uid = fields.Str()
    status = fields.Str()
    error_code = fields.Str(allow_none=True)
    error_message = fields.Str(allow_none=True)


class ApDocxImportPreviewResponseSchema(Schema):
    """GET /ap-docx-import/<parse_uid>：解析摘要 + 建議 payload + party 配對建議。"""
    parse_uid = fields.Str()
    status = fields.Str()
    adapter_version = fields.Str(allow_none=True)
    ttl_expires_at = fields.Str(allow_none=True)
    error_code = fields.Str(allow_none=True)
    error_message = fields.Str(allow_none=True)
    # 建議行程（單筆，_TaskItem 形狀）+ 章節摘要 + 參考資訊 + party 配對建議
    suggested_task = fields.Dict(allow_none=True)      # _TaskItem shape（FE 直接填表）
    sections = fields.Dict(allow_none=True)            # {header: text} 供 FE 顯示 chips
    reference_info = fields.Dict(allow_none=True)      # {basis_text, objective_text}
    party_suggestion = fields.Dict(allow_none=True)    # {name, matched_party_uid|None, candidates:[...]}


class _ConfirmPartySchema(Schema):
    class Meta:
        unknown = EXCLUDE
    name = fields.Str(required=True)
    matched_party_uid = fields.Str(allow_none=True)    # 非空 = 配對既有；None = 建新


class ApDocxImportConfirmRequestSchema(Schema):
    class Meta:
        unknown = EXCLUDE
    # user 預覽修改後的行程（單筆或多筆，append 到既有）
    tasks = fields.List(fields.Nested(_TaskItem), required=True)
    parties = fields.List(fields.Nested(_ConfirmPartySchema), load_default=list)


class ApDocxImportConfirmResponseSchema(Schema):
    parse_uid = fields.Str()
    status = fields.Str()
    import_summary = fields.Dict()

import pytest
from unittest.mock import MagicMock, patch


@pytest.fixture(autouse=True)
def _patch_logger():
    with patch("app.grc.service.ap_docx_import_app_service.logger", MagicMock()):
        yield

# 測試涵蓋（詳細期望由 feature-test-planner Phase E 展開，這裡先放關鍵行為）：
# - upload_and_parse: 非 .docx → GRC_DOCX_INVALID_FILE；>20MB → GRC_AP_DOCX_FILE_TOO_LARGE；
#   全 adapter miss → GRC_AP_DOCX_FORMAT_UNSUPPORTED；成功 → status awaiting_review + parse_uid
# - get_parse_result: TTL 過期 → status expired；非 participant → 403
# - confirm_import: 非 awaiting_review → 412；TTL 過期 → 412；
#   append 不覆蓋既有行程；party 同名走配對不呼叫 add_ap_party

suggested_task = {
    "title": parsed.title,
    "type": "action",
    "timing": parsed.audit_date,   # date 字串；set_tasks 原樣存 ap_tasks.timing
    "methods": parsed.methods,
    "steps": [{"method": m, "description": g} for m, g in parsed.method_guidances.items()],
    "controls": [],                # user 預覽時補勾
    "subjects": [],                # user 預覽時補勾
    "participants": [],            # confirm 時依 party 配對結果補（見下）
    "description": _compose_description(parsed),  # 範圍全文 + 時段 +（勾選時）依據/目的
}

def get_ap_tasks_as_task_items(self, ap_uid: str) -> list:
    """讀既有行程並轉成 set_tasks 可吃的 _TaskItem shape（append 用）。
    caller 必須在 @transaction scope 內。"""
    ap = self._ap_repo.get_by_uid(ap_uid)
    if ap is None:
        raise NotFound(GrcErrorCode.GRC_AP_NOT_FOUND)
    detail = self._ap_detail(ap)
    items = []
    for t in detail["tasks"]:
        items.append({
            "title": t["title"], "type": t.get("type"), "timing": t.get("timing"),
            "methods": t.get("methods") or [],
            "controls": t.get("controls") or [],
            "subjects": t.get("subjects") or [],
            "participants": t.get("participants") or [],
            "steps": t.get("steps") or [],
            "description": t.get("description"),
        })
    return items

git add app/grc/service/ap_docx_import_app_service.py \
        app/grc/service/assessment_plan_app_service.py \
        api/project/serializers/ap_docx_import.py \
        test/test_ap_docx_import_app_service.py
git commit -m "feat(FR-043): ApDocxImportAppService（parse/preview/confirm，append + party 配對）"

git add api/project/routes/ap_docx_import_route.py api/project/__init__.py di_containers/grc/grc_containers.py
git commit -m "feat(FR-043): AP docx import route + DI wiring"