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

{
  "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"
    }
  ]
}

-- 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 關聯）
-- 注意：此遷移需根據實際資料量決定是否分批執行

"""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")

"""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)