# Phase A2 Design — Excel Parser + 解析 API

> **Phase**：A2（Track A：Excel 匯入第二階段）
> **級別**：中型（design 段落 + plan，跳過 brainstorm）
> **狀態**：design draft
> **前置**：A1 已 ship（兩個下載 endpoint + 9 sheet 結構 + TEMPLATE_VERSION v1.1.0）
> **依賴本 phase**：A3（共用 matcher 抽取）/ A4（5 領域鉤稽）/ A5（預覽 UI + Confirm）皆建在 A2 parse_uid + parse_job 基礎上

---

## 1. 為什麼要做 A2

A1 已產出可下載的 Excel 樣板兩條 path：
- **S1 訪談前**：framework_version-scoped superset 樣板（無 MF context，列 framework 全 catalog controls）
- **S3 MF 已存在**：MF-scoped blank / filled 樣板（profile-scoped subset）

但 A1 是單向匯出，匯入回 BE 仍未實作。A2 補上匯入下半段：

1. **上傳 + parse**：使用者上傳填好的 Excel → BE openpyxl 解析 → 寫入 parse_job 表（24h TTL）→ 回 parse_uid
2. **取解析結果**：GET parse_uid → 回完整 parsed_result 給 A5 預覽 UI 顯示鉤稽結果
3. **確認匯入**：POST parse_uid/confirm → 依 decisions 寫入 MF + profile + parties + items + control_defaults

A2 範圍只覆蓋「parse + 解析 API」骨架，**A3/A4 補 matcher 邏輯、A5 補預覽 UI + Confirm 寫入完整實作**。本 phase 把 confirm 主流程刻通即可，鉤稽複雜度留 A3/A4。

> **關鍵設計差異於 A1**：A1 是單向 read（DB → xlsx），A2 是雙向 write（xlsx → DB）。寫入端有兩條 import flow：**superset upload** vs **MF update upload**，§3 詳述。

---

## 2. 範圍 / 不在範圍

### In Scope

- 4 個 endpoint：upload+parse / get / delete / confirm（mirror ssp-docx-import 既有 pattern）
- 新 `oscal.ssp_excel_parse_jobs` table（mirror `oscal.ssp_docx_parse_jobs` schema）
- Excel parser：openpyxl `load_workbook(data_only=True)` 拿 cached value，按 9 個 sheet 解析成 dataclass
- 樣板版本驗證：讀 `00_說明` R1 取 TEMPLATE_VERSION，比對 parser 支援範圍（>=1.0.0, <2.0.0）
- A1 樣板 minor bump v1.1.0 → **v1.2.0**：07 sheet 加 `include_in_profile` checkbox 欄位（**A2 prerequisite，§3.2 + §6.4**）
- 03_參與人員 role enum validation（issue 修補 A 必做，§7）
- 兩條 import flow 區分（§3）：
  - **superset upload**（`source_type=framework_version`）→ confirm 時建新 MF + 從 superset 依 `include_in_profile=TRUE` 篩 subset profile
  - **MF update upload**（`source_type=module_frame`）→ confirm 時 upsert 既有 MF；**不支援改 profile 內容**（§3.3，已知限制，列 follow-up §12）
- parse_uid 24h TTL（`_PARSE_JOB_TTL_HOURS = 24`，mirror docx 既值）
- 檔案大小限制 10MB（mirror docx 既值）
- File storage 透過 `file_upload_service.upload_file()` 走 MinIO / local
- 權限交既有 RBAC 中介層處理，A2 endpoint 不另寫 permission check

### Out of Scope（後續 phase）

| 不在 A2 的事 | 哪 phase 處理 |
|-------------|--------------|
| Parties / org-units 鉤稽 matcher（fuzzy match + matched_user / matched_org_unit）抽共用層 | A3 |
| devices / info_systems / leveraged / controls / AOs 鉤稽（matched_device 等）| A4 |
| 預覽 UI（差異對照表 + decisions 收集）| A5（FE 主） |
| Confirm 階段完整寫入（含 parties append/update/skip、items upsert、control_default 更新等 decisions）| A5（BE 主，A2 只刻骨架） |
| Excel 匯入後的 dirty data 清理工作流（既有 docx parser 雜亂 role normalize、DB audit）| Phase 2 完工後「統整優化」issue 4.2-4.3 |

---

## 3. 兩條 Import Flow（**A2 核心拍板**）

### 3.1 Flow 對照表

| Flow | 觸發條件 | source_type | source_uid | 樣板來源 | Confirm 動作 |
|------|---------|-------------|-----------|---------|--------------|
| **Superset upload** | 顧問訪談後上傳 | `framework_version` | **必填**（framework_version_uid）| A1 S1 下載（FW-version-scoped）| **建新** MF + 建新 subset profile + 寫 parties/items/control_defaults |
| **MF update upload** | 既有 MF 維護 | `module_frame` | **必填**（MF uid）| A1 S3 下載（MF-scoped blank/filled）| **更新既有** MF + 既有 profile / parties / items / control_defaults |

> **判別方式**：API 由 caller 在 `POST /parse` 帶 `source_type` + `source_uid` 兩個 form field 指定。
> A2 不做「自動判別」（從 Excel 內容反推）— FE Wizard step 已知道走哪條 flow，直接帶 source_type 即可。

### 3.2 Superset Flow — 建 MF + Profile（**核心新增邏輯**）

訪談後使用者拿著 framework_version 全 catalog 的 superset 樣板，**透過 07 sheet 新增 `include_in_profile` checkbox 欄位明確標記**哪些 control 要納入 profile（v1.2.0 樣板新增欄位，§13）。上傳後：

```
POST /ssp-excel-imports/parse
  multipart/form-data:
    file=<xlsx>
    source_type=framework_version
    source_uid=<framework_version_uid>
    metadata={"target_mf_name": "ACME 合規資源庫", "target_group": "...", ...}
   ↓
parse 階段：
  - 樣板版本驗證（v1.2.0+ 才有 include_in_profile 欄位；v1.0/v1.1 fallback「全部納入」）
  - openpyxl 解析 9 sheet → parsed_result dict
  - controls sheet：07 sheet user 在 `include_in_profile=TRUE` 的 control 收集成 `target_control_ids`
  - 寫 parse_job(status=awaiting_review, parsed_result=...)
   ↓
GET /ssp-excel-import/<parse_uid>
  → 回 parsed_result 給 FE 預覽（含目標 MF 名稱 / framework_version 名 / 將納入 profile 的 controls 數）
   ↓
POST /ssp-excel-import/<parse_uid>/confirm
  decisions: ...
   ↓
confirm 階段（A2 骨架，A5 補完）：
  1. 新建 MF + Profile（**T0.3 verify 後簡化**）：
     ModuleFrameService.add_module_frame(curr_user, {
       "name": target_mf_name, "group": target_group, "version": ...,
       "frequency": ..., "description": ..., "provider": "Billows-Official",
       "oscal_framework_version_uid": fw_version.uid,
       "include_controls": target_control_ids,   # ← 內部自動建 profile + 建 workflow_template
       "status": "draft",
     })
     → 內部 call oscal_profile_service.add_profile() 建 profile（含 profile_controls）
     → 建 module_frame (oscal_profile_uid=新 profile uid)
     → 建 workflow_template + profile_assessment_workflow
  2. 寫 parties / responsible_parties (context_type='module_frame', context_id=新 MF.id)
  3. 寫 items (scope_type='module_frame', scope_id=新 MF.id)
  4. 寫 module_frame_control_defaults + module_frame_control_objective_defaults
  5. 寫 module_frame_reference_documents（08 sheet）
  6. parse_job 寫 import_summary(mf_uid=新 MF.uid, profile_uid=新 profile.uid, ...) + status=completed
```

### 3.3 MF Update Flow — Upsert 既有 MF

```
POST /ssp-excel-imports/parse
  multipart/form-data:
    file=<xlsx>
    source_type=module_frame
    source_uid=<mf_uid>
   ↓
parse 階段：
  - 樣板版本驗證
  - openpyxl 解析 9 sheet → parsed_result dict
  - 控制項 sheet：對齊既有 MF profile 的 control 範圍（profile 不變更，**A2 不支援透過 Excel 改 profile 內容**）
  - 寫 parse_job(status=awaiting_review)
   ↓
GET / Confirm 同 superset，差別在 confirm 動作走 upsert 而非新建
   ↓
confirm 階段：
  1. 沿用既有 MF / 既有 profile（不動）
  2. parties / items / control_defaults 走 upsert（依 decisions 決定 append / update / skip）
  3. parse_job 寫 import_summary + status=completed
```

> **MF update flow 不支援改 profile 內容**（**已知限制**）：profile 是 MF 的「控制項涵蓋範圍」，改 profile 等於改 MF 本質 — 屬於另一個 use case（重新 onboarding）。要改 profile 走 Superset flow 重新匯入即可。
>
> **MF-scoped 樣板的 `include_in_profile` 欄位行為**：v1.2.0 樣板統一帶該欄位 — MF-scoped 樣板因為已是 profile-scoped subset，所有出現的 control 都已在 profile 內，generator filled mode 預填 `TRUE`（user 改了也無效，因為 MF update flow parser 忽略此欄位變動）。Superset 樣板才是 user 真正勾選的入口。詳見 §3.5。
>
> 此限制收尾優化階段（A5 完成後）再考慮支援改 profile，列 follow-up §12。

### 3.4 設計決策：為什麼不合併兩條 flow

| 選項 | 評估 | 結論 |
|-----|------|-----|
| A. 一條 endpoint + 自動判別 | 從 Excel 內容反推 source_type（如 01 sheet 有 MF UID = update flow）容易誤判；既有資料不全的樣板有歧義 | ❌ |
| B. 兩條 endpoint | URL 拆 `/from-framework-version` 跟 `/from-module-frame`，但 API 重複度高 | ❌ |
| **C. 一條 endpoint + form field 指定** | source_type + source_uid 明確帶；mirror docx import 既有 pattern | ✅ 採用 |

### 3.5 `include_in_profile` 欄位在兩條 flow 行為對照

| Generator 路徑 | 樣板來源 | 預填值 | Parser 行為 |
|---------------|---------|--------|-----------|
| MF-scoped blank | A1 §3.1 (MF 既有 + profile-scoped) | **TRUE**（subset 範圍內所有 control）| Update flow parser **忽略** — profile 不變 |
| MF-scoped filled | 同上 | **TRUE** | 同上 |
| FW-version-scoped blank | A1 §3.3 (superset, 全 catalog) | **FALSE**（user 顧問訪談時逐筆勾）| Superset flow parser **讀取** — TRUE 的進 profile |

> 設計動機：欄位 schema 在兩條 flow 統一（避免 minor bump 出現 schema 分支），但語意端點上 parser 依 source_type 決定要不要讀。MF-scoped 預填 TRUE 是「事實表述」而非 user input，user 操作對 MF update flow 無作用 — A5 預覽 UI 在 MF update flow 應隱藏 / disable 此欄位 user 互動。

---

## 4. Endpoint 規格

### 4.1 POST `/api/1.0/ssp-excel-imports/parse`

| Form Field | Type | Required | 說明 |
|-----------|------|---------|------|
| `file` | multipart file (xlsx) | ✅ | 樣板 xlsx；大小 ≤ 10MB |
| `source_type` | string | ✅ | `framework_version` (superset) \| `module_frame` (update) |
| `source_uid` | string (UUID) | ✅ | 對應 framework_version.uid 或 module_frame.uid |
| `metadata` | JSON-encoded string | ❌ | Superset flow 用：`{target_mf_name, target_group, target_version, target_frequency, ...}` — confirm 時組成新 MF 的欄位 |

**Response**：
```json
{
  "status": true,
  "data": {
    "parse_uid": "<uuid>",
    "status": "awaiting_review" | "failed",
    "file_uid": "<upload_uid>",        // 給 FE 顯示「下載已上傳檔」用
    "summary": {                        // status=awaiting_review 才有
      "metadata_present": true,
      "parties_org_count": 5,
      "parties_person_count": 12,
      "devices_count": 8,
      "info_systems_count": 3,
      "leveraged_count": 2,
      "controls_with_aos_count": 42,
      "ref_docs_count": 4,
      "target_profile_control_count": 17   // Superset only — user 標納入 profile 的 control 數
    },
    "error_code": null,                 // status=failed 才有
    "error_message": null
  }
}
```

**錯誤碼**：
- 400 `GRC_EXCEL_INVALID_FILE` — 副檔名不是 .xlsx / 解析失敗
- 400 `GRC_EXCEL_FILE_TOO_LARGE` — 超過 10MB
- 400 `GRC_EXCEL_SOURCE_TYPE_INVALID` — source_type 非 `framework_version` / `module_frame`
- 400 `GRC_EXCEL_SOURCE_UID_REQUIRED` — source_uid 缺失
- 400 `GRC_EXCEL_TEMPLATE_VERSION_UNSUPPORTED` — TEMPLATE_VERSION 不在 parser 支援範圍
- 404 `GRC_MODULE_FRAME_NOT_FOUND` / `GRC_FRAMEWORK_VERSION_NOT_FOUND`
- 403 由既有 RBAC middleware 處理（A2 endpoint 不另寫 permission check）

### 4.2 GET `/api/1.0/ssp-excel-import/<parse_uid>`

**Response**：
```json
{
  "status": true,
  "data": {
    "uid": "<parse_uid>",
    "source_type": "framework_version",
    "source_uid": "<uid>",
    "status": "awaiting_review",
    "file_uid": "<upload_uid>",
    "file_name": "ssp_template_acme_blank_20260520.xlsx",
    "parsed_result": {
      "template_version": "v1.1.0",
      "metadata": {...},                 // 01 sheet
      "parties_org": [...],              // 02 sheet
      "parties_person": [...],           // 03 sheet
      "devices": [...],                  // 04 sheet
      "info_systems": [...],             // 05 sheet
      "leveraged": [...],                // 06 sheet
      "controls_with_aos": [...],        // 07 sheet（含父子 row）
      "ref_docs": [...],                 // 08 sheet
      "validation_errors": [             // parser 抓到的 row-level 錯誤
        {"sheet": "03_參與人員", "row": 5, "field": "role", "code": "INVALID_ROLE", "message": "role 'admin' 不在 manager/reviewer/auditor/viewer 範圍"}
      ]
    },
    "created_at": "...",
    "ttl_expires_at": "..."             // created_at + 24h
  }
}
```

**錯誤碼**：
- 404 `GRC_EXCEL_PARSE_JOB_NOT_FOUND` — uid 不存在 / 跨 tenant / TTL 過期 / is_active=False

### 4.3 DELETE `/api/1.0/ssp-excel-import/<parse_uid>`

軟刪（is_active=False），mirror docx import discard pattern。

### 4.4 POST `/api/1.0/ssp-excel-import/<parse_uid>/confirm`

**Request body**：
```json
{
  "decisions": [                          // 對應 A3/A4 matcher 結果 user 拍板
    {"section": "parties_person", "row_index": 0, "action": "create_new"},
    {"section": "parties_person", "row_index": 1, "action": "link_existing", "target_uid": "<user_uid>"},
    {"section": "devices", "row_index": 3, "action": "skip"}
    // ... A5 補完整 decision schema
  ],
  "overrides": {                          // user 在預覽 UI 直接改的欄位
    "metadata": {"target_mf_name": "ACME 合規資源庫 v2"}
  }
}
```

**A2 範圍**：confirm endpoint 骨架先刻；decisions / overrides 完整套用邏輯由 A5 補。A2 只要能把 Excel parsed_result 直接寫入即可（不靠 decisions 拆 append/update/skip）。

**Response**：
```json
{
  "status": true,
  "data": {
    "parse_uid": "<uid>",
    "status": "completed",
    "module_frame_uid": "<新或既有 MF uid>",
    "profile_uid": "<新 profile uid，superset flow 才有>",
    "import_summary": {
      "mode": "create" | "update",
      "parties_created": 12,
      "parties_updated": 0,
      "devices_created": 8,
      "items_created": 11,
      "control_defaults_upserted": 17,
      "ref_docs_created": 4
    }
  }
}
```

**錯誤碼**：
- 404 `GRC_EXCEL_PARSE_JOB_NOT_FOUND`
- 412 `GRC_EXCEL_PARSE_JOB_NOT_AWAITING` — status 不是 `awaiting_review` 或 is_active=False
- 412 `GRC_EXCEL_PARSE_JOB_TTL_EXPIRED` — 超過 24h
- 400 `GRC_EXCEL_VALIDATION_ERRORS_BLOCKING` — parsed_result.validation_errors 有阻斷級錯誤（如 role 不合法）user 必須先修 Excel 重傳

---

## 5. `oscal.ssp_excel_parse_jobs` Table 規格

Mirror `oscal.ssp_docx_parse_jobs`：

```sql
-- 1. 建立主 table (2026-05-20)
CREATE TABLE IF NOT EXISTS oscal.ssp_excel_parse_jobs (
    id              SERIAL PRIMARY KEY,
    uid             VARCHAR(36) NOT NULL UNIQUE,
    source_type     VARCHAR(20) NOT NULL,        -- 'framework_version' | 'module_frame'
    source_uid      VARCHAR(36) NOT NULL,
    template_version VARCHAR(20),                 -- 從 00_說明 R1 解出
    metadata        JSONB,                        -- 上傳時 caller 帶的 target_mf_name / target_group 等
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    file_path       VARCHAR(255),                 -- 實際存上傳檔的 file_upload uid（沿用 docx 慣例）
    file_name       VARCHAR(255),
    file_size       BIGINT,
    parsed_result   JSONB,
    error_code      VARCHAR(40),
    error_message   TEXT,
    import_summary  JSONB,
    tenant_id       INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_user    VARCHAR(100),
    updated_user    VARCHAR(100),
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. tenant + status 索引 (2026-05-20)
CREATE INDEX idx_ssp_excel_parse_jobs_tenant_status
    ON oscal.ssp_excel_parse_jobs(tenant_id, status, is_active);

-- 3. source 索引 (2026-05-20)
CREATE INDEX idx_ssp_excel_parse_jobs_source
    ON oscal.ssp_excel_parse_jobs(source_type, source_uid);

-- 4. 權限 (2026-05-20)
GRANT SELECT, INSERT, UPDATE, DELETE ON oscal.ssp_excel_parse_jobs TO cm_app;
GRANT USAGE, SELECT ON SEQUENCE oscal.ssp_excel_parse_jobs_id_seq TO cm_app;

-- 5. RLS — 沿用 tenant_id 既有 policy（pending verify 既有 policy file 是否動態 enable）
ALTER TABLE oscal.ssp_excel_parse_jobs ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY ssp_excel_parse_jobs_tenant_policy ON oscal.ssp_excel_parse_jobs
--   USING (tenant_id = ANY(current_setting('app.allowed_tenant_paths')::int[]));
-- ↑ 視既有 docx parse_jobs policy 模式同步處理（T0 verify）
```

> **為何不複用 `ssp_docx_parse_jobs`**：docx 跟 excel 的 `parsed_result` 結構差異大（docx 有 baseline_controls / adapter_status；excel 是按 9 sheet 結構化）— 分表清楚，避免單表撐多型造成 JSONB 結構互卡。

---

## 6. Excel Parser 邏輯

### 6.1 整體流程

```
ExcelParser.parse(file_path: str, source_type: str, source_uid: str) -> ParsedExcel
  │
  ▼
  1. wb = load_workbook(file_path, data_only=True)
     → 拿 VLOOKUP / INDEX-MATCH cached value，不解析公式
  2. 讀 00_說明 R1 取 template_version
     → 驗證版本：semver.match(supported_range)
        不支援 → BadRequestError(GRC_EXCEL_TEMPLATE_VERSION_UNSUPPORTED)
  3. 依 9 個 sheet definition 解析
     for sheet_def in EXCEL_PARSER_SHEETS:
         ws = wb[sheet_def.sheet_name]
         rows = _parse_sheet(ws, sheet_def)   # 依 ColumnDef 結構 read cell
         result[sheet_def.parsed_key] = rows
  4. 07 sheet 特殊：父子 row 邏輯（statement_id 空 = control，非空 = AO）
  5. validate（row-level）
     - 03 role enum validation（§7）
     - 必填欄位空值偵測
     - 未知 enum value 偵測
  6. return ParsedExcel(template_version, sections, validation_errors)
```

### 6.2 Hidden columns 讀取

A1 樣板 hidden 但 parser 仍要讀的欄位：
- `01_基本資料` 表 E 欄 `framework_version_uid` + F 欄 `profile_uid`（superset flow 不靠這兩欄；MF update flow 用來 sanity-check Excel 跟 source MF 一致）
- `07_控制項與AO` 表 A 欄 `statement_id`：父子判斷依據
  - 空 / None → control 父 row
  - 非空字串 → AO 子 row

### 6.2.1 v1.2.0 新增 `include_in_profile` 欄位（visible，非 hidden）

A2 引入的樣板變更，07 sheet 加新欄（建議位置：B 欄 `control_id` 之後 / `control_name` 之前；plan T1a 內拍板）：

| 欄位 | Header | Required | Type | 預填值 |
|------|--------|---------|------|--------|
| `include_in_profile` | 「納入 Profile」 | ❌（但 visible）| Boolean checkbox | MF-scoped: `TRUE`；FW-version-scoped: `FALSE` |

Generator 端：
- 用 openpyxl Data Validation `type="list", formula1='"TRUE,FALSE"'` 或 `Form Control checkbox` （T1a verify 哪個 Excel 跨版本更穩定）
- 子 row（AO）此欄留空 — checkbox 只在父 row 有意義

Parser 端：
- 讀父 row B 欄值（v1.2.0 已是 visible col B → 須對齊 A1 column index 重排）
- 值 `TRUE` / `True` / `true` / `1` / `Y` / `是` → `_target_in_profile=True`
- 其他 / 空 → `_target_in_profile=False`
- v1.0/v1.1 樣板無此欄位 → fallback 全部視為 `True`（向後相容，§13）

### 6.3 VLOOKUP autofill 處理

03/04/05 sheet 的 autofill 欄位（user 選 matched_* 後，姓名/email 由 Excel INDEX/MATCH 公式帶入）：
- `load_workbook(data_only=True)` 取 cached value → 拿到 Excel 開檔重算後的純值
- 若 Excel 未重算（VLOOKUP 算不出來），cached value 是 `None` / `#N/A` → parser fallback 看 user 是否手填同欄
- A2 parser 不解析公式邏輯本身（不需 openpyxl formula engine）

### 6.4 07_控制項與AO sheet 父子 row 解析

```python
TRUTHY = {"TRUE", "True", "true", "1", "Y", "是", True, 1}

def _is_truthy(val) -> bool:
    return val in TRUTHY

def parse_controls_sheet(ws, template_version: str) -> list[dict]:
    """
    v1.2.0 row contract（A2 引入 include_in_profile 後）：
      父 row: statement_id 空 + include_in_profile + control_id + control_name + impl_status + statement
      子 row: statement_id=AO uid 字串 + include_in_profile 留空 + control_id 同父 + objective_id ...
    v1.0/v1.1 fallback: 無 include_in_profile 欄位，視為全部 TRUE
    """
    has_include_col = _semver_ge(template_version, "1.2.0")
    controls = []
    current_control = None
    for row in ws.iter_rows(min_row=2, values_only=True):
        statement_id, include_in_profile, control_id, control_name, objective_id, objective_name, impl_status, statement, ref_doc = (
            row if has_include_col else _pad_legacy_row(row)
        )
        if not statement_id:
            # 父 row
            current_control = {
                "control_id": control_id,
                "control_name": control_name,
                "impl_status": impl_status,
                "statement": statement,
                "reference_doc": ref_doc,
                "objectives": [],
                "_target_in_profile": _is_truthy(include_in_profile) if has_include_col else True,
            }
            controls.append(current_control)
        else:
            # 子 row
            if current_control is None or current_control["control_id"] != control_id:
                # 子 row 找不到父 — 視為 parser error
                continue
            current_control["objectives"].append({
                "statement_id": statement_id,
                "objective_id": objective_id,
                "objective_name": objective_name,
                "impl_status": impl_status,
                "statement": statement,
                "reference_doc": ref_doc,
            })
    return controls
```

**`_target_in_profile` 判定**：
- v1.2.0+ 樣板：讀 `include_in_profile` checkbox cell，TRUTHY → `True`，FALSY / 空 → `False`
- v1.0/v1.1 樣板：fallback 全部視為 `True`（向後相容，§13）

**Confirm 階段使用**：
- Superset flow：`target_control_ids = [c.control_id for c in controls if c._target_in_profile]` → 餵 `ProfileService.add_profile(include_controls=...)`
- MF update flow：**忽略 `_target_in_profile`**（§3.5 已說明，profile 不變）

---

## 7. Role Enum Validation（issue 修補 A — A2 必做）

對應 issue：`docs/issues/pending/2026-05-19-person-role-cross-domain-inconsistency.md`

### 7.1 範圍

A1 樣板 03_參與人員 sheet F 欄 `role` 已對齊 `ParticipantRole` 4 值：

```python
VALID_PARTICIPANT_ROLES = {"manager", "reviewer", "auditor", "viewer"}
```

A2 parser 解析 03 sheet 時：

```python
def _validate_person_role(row_value: str, row_idx: int) -> tuple[str, Optional[dict]]:
    """A2 解析 03 sheet F 欄 role；invalid → 加 validation_error 不阻塞 parse。"""
    role = (row_value or "").strip().lower()
    if not role:
        return "", None   # 空允許（A5 預覽 UI 可提示 user 補）
    if role not in VALID_PARTICIPANT_ROLES:
        return role, {
            "sheet": "03_參與人員",
            "row": row_idx,
            "field": "role",
            "code": "INVALID_ROLE",
            "message": f"role '{role}' 不在 manager/reviewer/auditor/viewer 範圍",
            "blocking": True,   # confirm 階段阻擋
        }
    return role, None
```

### 7.2 validation_errors 處理流程

- Parse 階段：role invalid → 寫入 `parsed_result.validation_errors` 但 **不抛 exception**（讓 user 在預覽 UI 看到所有錯誤）
- Confirm 階段：if any `blocking=True` validation_error → raise `BadRequestError(GRC_EXCEL_VALIDATION_ERRORS_BLOCKING)` 拒絕匯入
- A5 預覽 UI：把 validation_errors 列在 03 sheet 對應 row，user 必須修正 Excel 重傳

### 7.3 寫入 OSCAL responsible_parties 時

A2 confirm 階段寫 `oscal_responsible_parties.role_id` 時直接用 parsed `role` 字串（已通過 enum validation）。Schema 仍是 free string，但 A2 起匯入流程保證內容對齊 enum。

### 7.4 不在 A2 範圍（issue 4.2-4.3）

- 既有 docx parser 補 role normalize → A2 完成後評估
- dev DB `oscal_responsible_parties.role_id` 髒資料清理 → A2 完成後 audit SQL 結果決定

---

## 8. DDD 層級設計

### 8.1 Layer

| Layer | 檔案 | 職責 |
|-------|------|------|
| API | `api/oscal/routes/ssp/ssp_excel_import_route.py` | 4 個 Resource（Parse / Detail+Discard / Confirm）；form / json 解析、呼叫 app service |
| API serializer | `api/oscal/serializers/ssp/ssp_excel_import.py` | RequestSchema / ResponseSchema (marshmallow) |
| App Service | `app/oscal/service/ssp_excel_import_app_service.py` | `@transaction` orchestration：upload_and_parse / get_parse_result / discard_parse / confirm_import |
| Parser | `app/oscal/service/excel_parser/` 目錄 | 純函式：`ExcelParser.parse(file_path, source_type, source_uid) -> ParsedExcel` |
| Domain | （沿用既有）`domain/oscal/service/`、`domain/oscal/strategy/`、`domain/module_frame/service/` | profile_domain_service / mf_domain_service / write_strategy 等 |
| Infra | 新建 `domain/oscal/entity/ssp_excel_parse_job_entity.py` + `infra/oscal/model/ssp_excel_parse_job.py` + `infra/oscal/repository/ssp_excel_parse_job_repo_impl.py` + mapper | parse_job table CRUD |

### 8.2 DI

```python
# di_containers/oscal/oscal_containers.py 加：
ssp_excel_parse_job_domain_service = providers.Factory(
    SspExcelParseJobDomainService,
    repository=ssp_excel_parse_job_repo,
)

ssp_excel_import_app_service = providers.Factory(
    SspExcelImportAppService,
    parse_job_domain_service=ssp_excel_parse_job_domain_service,
    parser=excel_parser,                          # 純函式 parser
    file_upload_service=file_upload_service,
    # 沿用既有：
    module_frame_domain_service=module_frame_domain_service,
    profile_service=profile_service,              # jedi-oscal ProfileService（add_profile method）
    module_frame_service=module_frame_app_service,  # 既有 add_module_frame
    party_domain_service=party_domain_service,
    responsible_party_domain_service=responsible_party_domain_service,
    oscal_framework_version_domain_service=oscal_framework_version_domain_service,
    catalog_control_domain_service=catalog_control_domain_service,
    # ... 視 confirm 階段需要動的 domain service 補
)
```

### 8.3 Strategy / Write 階段共用

Confirm 階段寫入動作（parties / items / control_defaults）**沿用既有 module_frame_write_strategy 跟 ssp_write_strategy** 路徑，**不另寫新 strategy class**。A2 endpoint 只是不同入口，最終寫入跟 docx import 共用 strategy。

---

## 9. Pre-flight Verification 結果（本 session 已驗）

| # | 假設 | 結果 | 影響 |
|---|------|-----|------|
| 1 | ssp_docx_import 是 mirror reference | ✅ `app/oscal/service/ssp_docx_import_app_service.py` + `api/oscal/routes/ssp/ssp_docx_import_route.py` — 4 endpoints / parse_job pattern / 24h TTL / 10MB max 都對齊 | A2 直接照 mirror 寫，省 30%+ 設計工時 |
| 2 | framework_parse_job 是 confirm 兩階段第二參考 | ✅ `app/oscal/service/framework_parse_job_service.py` 有 `confirm(uid, decisions, overrides, ...)` pattern；A2 confirm endpoint 介面對齊 | decisions / overrides schema 接近，A5 收尾時可直接套用 |
| 3 | `ProfileService.add_profile` 存在 | ✅ `jedi-oscal/jedi_oscal/app/services/profile/profile_service.py:88` — `add_profile(curr_user, data, locale)`；data 含 `catalog_uid` + `include_controls`(list of catalog_control_uid) | Superset flow 直接呼叫 add_profile 即可，**不需動 jedi-oscal** |
| 4 | parse_job 表結構可 mirror | ✅ `oscal.ssp_docx_parse_jobs` 完整欄位清單已查（uid / source_type / source_uid / mode / parsed_result JSONB / status / file_path / file_size / import_summary / is_active / tenant_id）— A2 新 table 直接 copy + 加 `template_version` + `metadata` JSONB | Migration 一條 SQL 即可（§5） |
| 5 | `file_upload_service.upload_file` 是 storage 入口 | ✅ docx 既有 pattern：read raw bytes → 寫 NamedTemporaryFile 給 parser → 另 wrap FileStorage 給 upload_service；A2 開檔 + parse 後同樣兩軌 | A2 沿用此 helper，不另寫 storage 邏輯 |
| 6 | Error code 序號可用範圍 | ✅ 400064 / 404032 / 412012 / 409006 已被佔；A2 新增從 400065+ / 404033+ / 412013+ 起 | §4 錯誤碼定義時用這些序號 |

> Pre-flight 結果 **直接影響 design**：原本 design 草稿假設「parse_job 表可能要新建 schema」— verify 後確認只是新表 sibling，§5 SQL 已對齊既有結構。

---

## 10. Acceptance Criteria

- [ ] `POST /api/1.0/ssp-excel-imports/parse` 上傳合法 xlsx + `source_type=framework_version` + `source_uid=<fw_version_uid>` → 回 `parse_uid` + `status=awaiting_review`
- [ ] 同 endpoint，`source_type=module_frame` + 既有 MF uid → 回 parse_uid（MF update flow）
- [ ] 樣板版本不符（00_說明 R1 = `v2.0.0`）→ 400 `GRC_EXCEL_TEMPLATE_VERSION_UNSUPPORTED`
- [ ] 檔案 > 10MB → 400 `GRC_EXCEL_FILE_TOO_LARGE`
- [ ] source_uid 對不到資源 → 404
- [ ] 03_參與人員 sheet role = `admin`（不在 enum）→ parsed_result.validation_errors 含對應 row + blocking=True
- [ ] `GET /api/1.0/ssp-excel-import/<uid>` 回完整 parsed_result + ttl_expires_at
- [ ] `DELETE` 軟刪 + 之後 GET 回 404
- [ ] `POST /<uid>/confirm` (superset flow) → 新建 MF + profile + parties + items + control_defaults，回 import_summary
- [ ] `POST /<uid>/confirm` (MF update) → upsert 既有 MF parties / items / control_defaults
- [ ] Confirm 後 parse_job.status = `completed`, import_summary 寫入
- [ ] Confirm 帶 blocking validation_error 的 parse_job → 412 `GRC_EXCEL_VALIDATION_ERRORS_BLOCKING`
- [ ] 24h TTL 過後 confirm → 412 `GRC_EXCEL_PARSE_JOB_TTL_EXPIRED`
- [ ] Tenant 隔離：tenant_a 拿 tenant_b 的 parse_uid → 404
- [ ] 父子 row contract：07 sheet 子 row 找不到對應父 row → parser 跳過 + 加 validation_error
- [ ] BE unit test ≥ 50 個 case（parser + app service + endpoint integration）
- [ ] BE smoke：起 BE → curl 兩條 flow 各跑一輪 → import_summary 正確
- [ ] Changelog 完成（feat 類 — `2026-05-XX-feat-ssp-excel-import.md`）

---

## 11. 風險 / Open Question

| 項目 | 影響 | 緩解 |
|------|-----|-----|
| Confirm 階段 transaction 跨多表寫入失敗 rollback | partial write 留垃圾 | 單一 `@transaction`；ProfileService.add_profile + ModuleFrameService.add_module_frame + write_strategy 都在同 scope；任一失敗整批 rollback |
| MF update flow 把 Excel 內舊資料覆蓋 DB 較新資料 | 衝突 | A2 第一版 confirm 直接 upsert（後寫贏）；A5 預覽 UI 補 conflict detection + user decision |
| Excel 檔案不是從 A1 樣板下載（user 自己造）| parser 假設 sheet name / column 結構 | 樣板版本驗證擋；若版本對但欄位錯 → parser 拋 row-level error 給 user 看 |
| VLOOKUP cached value 為 None（user 沒按 Ctrl+Alt+F9）| autofill 欄位空白 | parser fallback：if matched_user 有值但 email/name 空 → 從 user repo 反查補 |
| jedi-oscal `ProfileService.add_profile` 可能不接 metadata.title override | 新 profile 名稱 user 可能想自訂 | T0 verify 接口；不接 → A2 confirm 後另用 update_profile 改 title（或 ProfileService 加 helper） |
| v1.2.0 樣板 07 sheet column index 重排可能踩到 A1 既有 unit test fixture | A1 test fail | T1a verify 後同步更新 A1 test fixture；新 column 加在 B 欄而非尾端，需重排 column letter 映射 |

---

## 12. 不在 A2 但要記下的事

- [follow-up] **Superset flow 真正做 subset profile**（**A4 必補**，§15.3）：A2 skeleton confirm 階段 `include_controls=[]` 全部納入 catalog；A4 phase 補 `control_id → catalog_control_uid` 反查邏輯讓 `_target_in_profile=True` 父 row 標記真正生效
- [follow-up] **MF update flow 支援改 profile 內容**（**A2 已知限制**）：目前 update flow 完全忽略 07 sheet 的 `include_in_profile` 變動，profile 不變；未來收尾優化階段（A5 完成後）支援，需設計：差異偵測（current profile_controls vs Excel 標記 vs Excel 在 sheet 內 control 範圍）+ user 拍板決定要不要重建 profile + 影響既有 control_defaults / control_implementations 的 cascade 行為
- [follow-up] A3：parties / org-units fuzzy matcher 抽共用層（A2 confirm 階段先沿用 docx import 的 `party_reconciliation_service`，A3 重構共用）
- [follow-up] A4：devices / info_systems / leveraged 鉤稽 matcher（A2 confirm 階段先直接寫，不做 matched_device 對齊）
- [follow-up] A5：完整 decisions / overrides schema + 預覽 UI（A2 confirm 端骨架做完即可，decisions 完整邏輯 A5 收尾）
- [follow-up] 既有 docx parser 補 role normalize（issue 4.2）
- [follow-up] dev DB `oscal_responsible_parties.role_id` 髒資料 audit + 清理（issue 4.3）
- [follow-up] Excel parser 支援 v2.x 樣板（A2 第一版只支援 v1.x，遠期才考慮 major bump）

---

## 13. 樣板版本相容性 contract（呼應 A1 §14）

A1 design §14 已建立 TEMPLATE_VERSION SemVer SOP；A2 phase 同時做兩件事：

### 13.1 A2 引入的樣板變更（A1 v1.1.0 → v1.2.0）

| 變更 | 類型 | 影響 |
|------|------|-----|
| 07 sheet 加 `include_in_profile` checkbox 欄位 | MINOR（加 optional 欄位）| 舊 parser 讀新樣板：忽略未知欄；新 parser 讀舊樣板：fallback 全部 TRUE |

**版號變動**：`v1.1.0 → v1.2.0`（minor bump，遵守 A1 §14.3 SemVer rule）。

**Generator 端配合改動**（plan-A2 Task 1a，A2 prerequisite）：
- `sheet_definitions.py` SHEET_CONTROLS 加 `ColumnDef("include_in_profile", ..., required=False)`
- generator 寫入：MF-scoped filled mode 預填 `TRUE`，FW-version-scoped blank mode 預填 `FALSE`（或留空）
- `TEMPLATE_VERSION` 常數 bump v1.1.0 → v1.2.0
- A1 unit test fixture 補新欄位 column index 重排（既有 column letter 映射要 +1）

### 13.2 Parser 支援範圍

```python
SUPPORTED_TEMPLATE_VERSION_RANGE = (">=1.0.0", "<2.0.0")

def _check_template_version(parsed_version: str) -> None:
    if not _match_semver(parsed_version, SUPPORTED_TEMPLATE_VERSION_RANGE):
        raise BadRequestError(
            GrcErrorCode.GRC_EXCEL_TEMPLATE_VERSION_UNSUPPORTED,
            extra={"parsed": parsed_version, "supported": SUPPORTED_TEMPLATE_VERSION_RANGE}
        )
```

- v1.0.0 / v1.1.0（A1 既有樣板）→ 通過；07 sheet 缺 `include_in_profile` 欄位 → fallback 全部視為 TRUE（§6.4）
- v1.2.0（A2 引入）→ 通過；正式讀 `include_in_profile` 欄位
- v2.0.0+ → 拒絕，user 重下對應版本樣板

---

## 14. 跨 repo 工作

| Repo | 工作 |
|------|------|
| BE（主）| **A1 樣板 v1.2.0 patch**（sheet_definitions / generator / TEMPLATE_VERSION bump / unit test fixture）+ Migration SQL + new table + new entity/model/repo/mapper + app service + parser + route + serializer + DI + unit test |
| FE | A2 phase **不動 FE**（A1 上傳入口已存在 — 既有 docx 上傳 dialog；A5 才補預覽 UI + decisions 收集）|
| jedi-* | **不動**。讀現有 ProfileService.add_profile / ModuleFrameService.add_module_frame 等 |
| test | E2E **不在 A2**；A2 BE shipped 後 A5 收尾時補 cucumber |
| changelog | 兩份：`YYYY-MM-DD-tweak-ssp-import-template-v1.2.0.md`（樣板 minor bump）+ `YYYY-MM-DD-feat-ssp-excel-import.md`（A2 主體 feat）|

---

**下一步**：`implementation-plan-A2.md` 同時產出，task 拆解 + 預估工時 + 依賴。

---

## 15. Implementation Reality / Reconciliation

（實作過程中對原始 design 的偏離 / 擴增紀錄；A2 進度推進時逐項補。）

### 15.1 T0.3 — ModuleFrameService.add_module_frame 自動建 profile（2026-05-19）

**偏離項**：原始 design §3.2 假設 superset flow 要兩段呼叫 — 先 `ProfileService.add_profile()` 再 `ModuleFrameService.add_module_frame(oscal_profile_uid=...)`。

**T0.3 verify 實際發現**（app/module_frame/service/module_frame_service.py:120-189）：
- `add_module_frame(user, payload)` payload 接 `include_controls` (list of catalog_control_uid)
- 內部第一步直接 `self.oscal_profile_service.add_profile(user, profile_payload, locale)` 建 profile
- 拿到 profile_dto.uid 後建 MF entity
- 還會自動建 workflow_template + profile_assessment_workflow（給後續 AP 啟動用）

**修正方向**：
- §3.2 confirm flow 步驟從 7 段縮為 6 段：「ModuleFrameService.add_module_frame 帶 include_controls → 內部自動建 profile + workflow_template + MF」
- A2 confirm 階段不需要直接 inject `ProfileService`（透過 MF service 內部呼叫即可）
- 簡化 ≈ 20% confirm code

**對 plan-A2 的影響**：
- T0 結束直接修 design.md 反映此偏離
- T4 app service confirm_import superset flow 改照單一呼叫實作
- DI 拿掉 `profile_service` 直接 inject（仍可保留以備後續 update profile 需求）

### 15.2 T1a — `include_in_profile` 加在尾端 + 順手修 A1 bug（2026-05-20）

**偏離項 1（位置）**：原 plan-A2 T1a §1.2 建議「`include_in_profile` 加在 B 欄 statement_id 之後 / control_id 之前」。T1a 實作時改 **加在尾端 (column I, after reference_doc)**。

**理由**：
- 既有 A1 test fixture（test_excel_template_generator_filled.py:205 `ws.cell(row=2, column=2)` 找 control_id）會撞 column index 重排
- 加在尾端 column 順序不變，A1 既有 106 test 不用 fixture 重排
- v1.2.0 是 minor bump（加 optional 欄位），加在尾端是 standard practice
- UX 影響有限：MF-scoped 樣板 user 不會碰此欄（design §3.5 預填 TRUE）；FW-version-scoped 訪談時 user 找右端勾選

**偏離項 2（發現 A1 bug 並順手修）**：T1a 寫測試時發現 generator `mode == "blank"` 路徑漏寫 `bundle.controls_with_aos`（line 274 邏輯），跟 design-A1 §6.2.1「FW-version-scoped blank mode 列 catalog 全 controls 父子 row」不符。

**修正**：generator 加 `should_write_body = mode == "filled" or (sheet_def is SHEET_CONTROLS and bundle.controls_with_aos)`。Controls sheet 在 blank mode 也寫 body（前提 bundle 有 controls），其他 sheet 維持 mode=blank → 空 body 規範。

**影響**：
- A1 FW-version-scoped 樣板下載從此會正確列出 catalog superset 控制項清單（之前空 body — 不算 breaking，是補上原本 design 承諾但未實作的行為）
- A2 superset upload flow parser 從此可正常解析 framework_version 樣板（之前空 body 沒得解析）
- Changelog 加 `fix-ssp-import-template-fw-version-scoped-controls-missing.md` 對應這個 bug fix

**對 plan-A2 影響**：T1a 多一條 changelog；T8 不再需要「fallback 用 user 自己 key 控制項」設計分支

### 15.3 T4 — Superset flow include_controls 暫留空（control_id → catalog_control_uid 解析待 A4）（2026-05-20）

**偏離項**：原 design §3.2 假設 confirm 階段直接把 `_target_in_profile=True` 父 row 的 `control_id` 字串清單餵 `ProfileService.add_profile(include_controls=...)`。T4 實作時發現 `include_controls` 接的是 **`catalog_control_uid` (UUID 字串)** 而非 `control_id` (`AC-1` 等字面 code)。

**現況**：
- Parser 解出來的是 `control_id` 字面 code（如 `AC-1`、`AU-2`）
- `ProfileService.add_profile` 需要 catalog 表的 `catalog_control_uid` (UUID)
- 兩者需透過 `catalog_control_domain_service` 反查（依 framework_version → catalog → controls）

**A2 skeleton 處理**：`include_controls=[]`（全部納入 catalog 內 control），加 `logger.info` 標記 `target_control_ids` 供 audit。實際行為仍 ≈ Superset flow，只是不依 user 標記做 subset 篩選 — Profile 建出來是 framework 全 catalog 而非 subset。

**完整實作待 A4 phase**：A4 主題是「devices / info_systems / leveraged / 控制項 / AO 鉤稽」 — control_id ↔ catalog_control_uid 反查屬於同一範疇。實作思路：
```python
def _resolve_target_catalog_control_uids(
    target_control_ids: list[str], catalog_id: int,
) -> list[str]:
    if not target_control_ids:
        return []
    matched = self._catalog_control_domain.get_all(
        CatalogControlQueryEntity(catalog_id=catalog_id)
    )
    by_code = {c.control_id: str(c.uid) for c in matched if c.control_id}
    return [by_code[cid] for cid in target_control_ids if cid in by_code]
```
A4 落地後 superset flow 才能真正做 subset profile，A2 skeleton 暫接受「全部納入」行為差異。

**對 user 影響**：A2 第一版 superset upload 後建出來的 profile 會包含 framework_version 全 catalog controls，不是 user 標記的 subset。Acceptance 上仍走通（MF + Profile 建立），但 _target_in_profile=FALSE 的 control 也會出現在新 profile 內。A4 phase 修正。

**追加 follow-up 條目於 §12**。