# SSP 程序書池 + 多對多關聯 Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task.

**Goal:** 將 SSP 程序書從「直接掛在 control/AO」改為「SSP 級別程序書池 + 多對多關聯」，同一份程序書可關聯多個控制項/AO，並整合 Excel 批次匯入。

**Architecture:** 現有 `ssp_reference_documents` 表改為程序書池（`context_type='ssp'`），新增 `ssp_reference_document_mappings` 表做多對多關聯。程序書池 CRUD 複用現有 domain service，關聯操作新增 infra 層。Excel 匯出新增「參考程序書」欄位。

**Tech Stack:** SQLAlchemy、Flask-RESTful、Marshmallow、openpyxl

---

## 前端溝通文件

### 概念變更

**之前：** 程序書直接上傳到控制項或 AO（一對一，重複上傳）
**現在：** 程序書上傳到 SSP 級別的池子（一次），再關聯到多個控制項/AO（多對多）

### 使用流程

```
Step 1: 管理者進入 SSP 程序書管理
  → 批次上傳程序書到池（拖拽多檔上傳）
  → 每份程序書可填寫描述/編號

Step 2: 在控制項/AO 頁面關聯程序書
  → 點擊「關聯程序書」→ Dialog 顯示池中所有程序書
  → 多選 → 確認 → 建立關聯

Step 3: 或用 Excel 批次匯入
  → 匯出 Excel 多一欄「參考程序書」
  → 填入程序書名稱（逗號分隔）
  → 匯入時自動從池中比對並建立關聯
```

### API 規格

#### 程序書池 CRUD

```
GET  /api/1.0/ssp/<ssp_uid>/document-pool
  → 列出池中所有程序書

POST /api/1.0/ssp/<ssp_uid>/document-pool
  → 上傳程序書到池
  Body: { "documents": [{"file_uid": "xxx", "description": "資訊安全政策 v3.0"}] }

DELETE /api/1.0/ssp/<ssp_uid>/document-pool/<doc_uid>
  → 從池刪除（cascade 刪除所有關聯）
```

**池清單 Response：**
```json
{
  "status": true,
  "data": [
    {
      "uid": "doc-uuid",
      "file_uid": "file-uuid",
      "file_name": "資訊安全政策v3.0.pdf",
      "file_size": 1024000,
      "description": "資訊安全政策 v3.0",
      "mapping_count": 5,
      "created_at": "2026-03-29T10:00:00"
    }
  ]
}
```

#### 控制項關聯

```
GET  /api/1.0/ssp/<ssp_uid>/control-implementation/<ctrl_id>/document-mappings
  → 列出該控制項關聯的程序書

POST /api/1.0/ssp/<ssp_uid>/control-implementation/<ctrl_id>/document-mappings
  → 批次建立關聯
  Body: { "document_uids": ["doc-uid-1", "doc-uid-2"] }

DELETE /api/1.0/ssp/<ssp_uid>/control-implementation/<ctrl_id>/document-mapping/<doc_uid>
  → 取消關聯
```

#### AO 關聯

```
GET  /api/1.0/ssp/<ssp_uid>/control-implementation/<ctrl_id>/objective/<stmt_id>/document-mappings
POST /api/1.0/ssp/<ssp_uid>/control-implementation/<ctrl_id>/objective/<stmt_id>/document-mappings
DELETE /api/1.0/ssp/<ssp_uid>/control-implementation/<ctrl_id>/objective/<stmt_id>/document-mapping/<doc_uid>
```

（格式同控制項關聯）

---

## DB Migration

### 新增 `ssp_reference_document_mappings` 表

```sql
-- Date: 2026-03-29
-- 1. 新增程序書多對多關聯表 (2026-03-29)
CREATE TABLE IF NOT EXISTS oscal.ssp_reference_document_mappings (
    id SERIAL PRIMARY KEY,
    reference_document_id INTEGER NOT NULL
        REFERENCES oscal.ssp_reference_documents(id) ON DELETE CASCADE,
    context_type VARCHAR(30) NOT NULL,
    context_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_user VARCHAR(50),
    CONSTRAINT uq_ssp_ref_doc_mapping UNIQUE (reference_document_id, context_type, context_id)
);

CREATE INDEX ix_ssp_ref_doc_mapping_context ON oscal.ssp_reference_document_mappings (context_type, context_id);
CREATE INDEX ix_ssp_ref_doc_mapping_doc_id ON oscal.ssp_reference_document_mappings (reference_document_id);

-- 2. 權限授予 (2026-03-29)
GRANT SELECT, INSERT, UPDATE, DELETE ON oscal.ssp_reference_document_mappings TO cm_app;
GRANT USAGE, SELECT ON SEQUENCE oscal.ssp_reference_document_mappings_id_seq TO cm_app;

-- 3. 遷移現有資料：將 control_implementation/objective 的程序書轉為 ssp 池 + mapping (2026-03-29)
-- 步驟 a: 將現有記錄的 context_type 改為 'ssp'，context_id 改為對應的 ssp.id
-- 步驟 b: 建立 mapping 記錄（保留原本的 control/AO 關聯）
-- 注意：此遷移需根據實際資料量決定是否分批執行
```

### `context_type` 值定義

| context_type | 用途 | context_id |
|-------------|------|-----------|
| `ssp` | 程序書池（SSP 級別） | `system_security_plans.id` |
| `control_implementation` | mapping 目標：控制項 | `system_security_plan_control_implementations.id` |
| `objective` | mapping 目標：AO | `ssp_control_implementation_objectives.id` |

---

## File Structure

| 檔案 | 職責 | 操作 |
|------|------|------|
| `scripts/sql/ssp_document_pool_migration.sql` | DB migration | **Create** |
| `infra/grc/model/ssp_reference_document_mapping.py` | ORM model | **Create** |
| `infra/grc/repository/ssp_document_pool_query.py` | infra 查詢（池清單含 mapping_count、mapping CRUD） | **Create** |
| `app/oscal/service/ssp_document_pool_service.py` | 程序書池 + 關聯 app service | **Create** |
| `api/oscal/routes/ssp/ssp_document_pool_route.py` | 池 CRUD + 關聯 CRUD routes | **Create** |
| `api/oscal/serializers/ssp/ssp_document_pool.py` | Marshmallow schemas | **Create** |
| `api/oscal/__init__.py` | 註冊 routes | **Modify** |
| `di_containers/oscal/oscal_containers.py` | DI wiring | **Modify** |
| `app/oscal/service/ssp_control_impl_import_service.py` | Excel 匯出加「參考程序書」欄、匯入加關聯 | **Modify** |

---

## Tasks

### Task 1: DB Migration SQL

**Files:**
- Create: `scripts/sql/ssp_document_pool_migration.sql`

### Task 2: ORM Model

**Files:**
- Create: `infra/grc/model/ssp_reference_document_mapping.py`

```python
"""SSP 程序書關聯 — ORM Model"""
from datetime import datetime
from typing import Optional

from sqlalchemy import String, Integer, DateTime, ForeignKey, Index, func, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from jedi_common.session.database.declarative_base import Base


class SspReferenceDocumentMapping(Base):
    """程序書與控制項/AO 的多對多關聯"""

    __tablename__ = "ssp_reference_document_mappings"
    __table_args__ = (
        UniqueConstraint("reference_document_id", "context_type", "context_id",
                         name="uq_ssp_ref_doc_mapping"),
        Index("ix_ssp_ref_doc_mapping_context", "context_type", "context_id"),
        Index("ix_ssp_ref_doc_mapping_doc_id", "reference_document_id"),
        {"schema": "oscal", "comment": "SSP 程序書與控制項/AO 的多對多關聯"},
    )

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    reference_document_id: Mapped[int] = mapped_column(
        ForeignKey("oscal.ssp_reference_documents.id", ondelete="CASCADE"),
        nullable=False,
    )
    context_type: Mapped[str] = mapped_column(String(30), nullable=False)
    context_id: Mapped[int] = mapped_column(Integer, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=func.now(), nullable=False)
    created_user: Mapped[Optional[str]] = mapped_column(String(50))

    # Relationship
    reference_document = relationship("SspReferenceDocument", lazy="selectin")
```

### Task 3: Infra Query

**Files:**
- Create: `infra/grc/repository/ssp_document_pool_query.py`

提供：
- `list_pool(ssp_id)` — 列出池中程序書，含 mapping_count
- `list_mappings(context_type, context_id)` — 列出某控制項/AO 的關聯程序書
- `add_mappings(doc_id, context_type, context_id)` — 建立關聯
- `remove_mapping(doc_id, context_type, context_id)` — 刪除關聯
- `get_pool_name_to_id_map(ssp_id)` — 名稱 → doc_id mapping（Excel 匯入用）

### Task 4: Serializers

**Files:**
- Create: `api/oscal/serializers/ssp/ssp_document_pool.py`

```python
"""SSP 程序書池 Serializers"""
from marshmallow import Schema, fields


class DocumentPoolItemSchema(Schema):
    """程序書池項目"""
    uid = fields.Str()
    file_uid = fields.Str(allow_none=True)
    file_name = fields.Str(allow_none=True)
    file_size = fields.Int(allow_none=True)
    description = fields.Str(allow_none=True)
    mapping_count = fields.Int(dump_default=0)
    created_at = fields.DateTime(allow_none=True)


class DocumentPoolUploadRequestSchema(Schema):
    """上傳程序書到池"""
    documents = fields.List(fields.Nested({
        "file_uid": fields.Str(required=True),
        "description": fields.Str(load_default=None, allow_none=True),
    }), required=True)


class DocumentMappingRequestSchema(Schema):
    """建立程序書關聯"""
    document_uids = fields.List(fields.Str(), required=True)


class DocumentMappingItemSchema(Schema):
    """關聯的程序書"""
    uid = fields.Str()
    file_uid = fields.Str(allow_none=True)
    file_name = fields.Str(allow_none=True)
    file_size = fields.Int(allow_none=True)
    description = fields.Str(allow_none=True)
```

### Task 5: App Service

**Files:**
- Create: `app/oscal/service/ssp_document_pool_service.py`

方法：
- `list_pool(ssp_uid)` — 列出池
- `add_to_pool(ssp_uid, documents, curr_user)` — 上傳到池（context_type='ssp'）
- `delete_from_pool(doc_uid)` — 從池刪除（cascade）
- `list_control_mappings(ssp_uid, ctrl_id)` — 列出控制項關聯
- `add_control_mappings(ssp_uid, ctrl_id, doc_uids, curr_user)` — 建立控制項關聯
- `remove_control_mapping(doc_uid, ctrl_id)` — 刪除控制項關聯
- `list_ao_mappings(ssp_uid, ctrl_id, stmt_id)` — 列出 AO 關聯
- `add_ao_mappings(ssp_uid, ctrl_id, stmt_id, doc_uids, curr_user)` — 建立 AO 關聯
- `remove_ao_mapping(doc_uid, stmt_id)` — 刪除 AO 關聯

### Task 6: Routes

**Files:**
- Create: `api/oscal/routes/ssp/ssp_document_pool_route.py`

Routes:
- `SspDocumentPoolRoute` — GET（列出）+ POST（上傳）
- `SspDocumentPoolDetailRoute` — DELETE（刪除）
- `SspControlDocumentMappingRoute` — GET + POST
- `SspControlDocumentMappingDetailRoute` — DELETE
- `SspAoDocumentMappingRoute` — GET + POST
- `SspAoDocumentMappingDetailRoute` — DELETE

### Task 7: Blueprint + DI

**Files:**
- Modify: `api/oscal/__init__.py`
- Modify: `di_containers/oscal/oscal_containers.py`

### Task 8: Excel 匯出匯入整合

**Files:**
- Modify: `app/oscal/service/ssp_control_impl_import_service.py`

匯出：
- 控制項行新增 I 欄「參考程序書」（逗號分隔程序書名稱，唯讀灰底）
- AO 行也顯示關聯的程序書名稱

匯入：
- 新增 I 欄解析
- 從程序書池比對名稱 → 建立 mapping

### Task 9: 變更紀錄 + 前端規格

**Files:**
- Create: `docs/changelog/2026-03-29-ssp-document-pool.md`
- Create: `docs/api_spec/2026-03-29-ssp-document-pool-frontend-spec.md`