-- =============================================================================
-- OSCAL v1.2.2 -> PostgreSQL 關聯式 schema（重新設計）
-- 本檔範圍（單一完整檔，dbdiagram.io 一次匯入）：
--   * OSCAL 八大 root 物件表（每個 model 一張）
--   * 完整 Catalog 階層（catalog -> group -> control -> part，每層自巢狀）
--   * 共用、值得查詢的子物件（metadata / role / party / resource）
--   * Profile imports 關聯化（SECTION C2：profile_imports）
--   * 完整 SSP 子樹（SECTION D：system-characteristics / system-implementation /
--     control-implementation，共 12 表）
--   * 解決「AO（Assessment Object）放哪」-> 見 SECTION B3（catalog_control_parts）
--
-- 目標框架：CMMC 2.0 + ISO/IEC 27001:2022（與 NIST 800-53 結構相容）
-- 技術棧：Flask + SQLAlchemy 2.0（mapped_column）+ PostgreSQL，schema 命名空間 = oscal
--
-- 註解語言：DDL 內 `--` code comment 用英文；COMMENT ON（ERD 工具顯示用）用中文。
--
-- ------------------------------------------------------------------------------
-- 必填/選填鐵則（依官方 json-outline / schema 的 required 陣列逐欄校對）：
--   * OSCAL required [1]   -> 欄位 NOT NULL（標 [1]）
--   * OSCAL 選填 [0..1/0..*] -> 欄位 nullable（標 [0..1] / [0..*]）
--   * 系統 / 結構 / 產品欄（id、containment FK、sort_order、status、audit）的 NOT NULL
--     與 OSCAL 無關，於註解標明（避免被當成 OSCAL 必填）。containment FK 為 NOT NULL 是
--     關聯式結構需求（子物件不能脫離父物件存在），非 OSCAL 欄位必填。
--   * 各 model 的 required 速查（schema required 陣列）：
--       catalog : [uuid, metadata]
--       profile : [uuid, metadata, imports]
--       comp-def: [uuid, metadata]
--       ssp     : [uuid, metadata, import-profile, system-characteristics,
--                  system-implementation, control-implementation]
--       ap      : [uuid, metadata, import-ssp, reviewed-controls]
--       ar      : [uuid, metadata, import-ap, results]
--       poam    : [uuid, metadata, poam-items]（import-ssp 選填）
--       mapping : [uuid, metadata, mappings]
--       metadata: [title, last-modified, version, oscal-version]（其餘全選填）
--       role    : [id, title]   party: [uuid, type]   resource: [uuid]
--       control : [id, title]   group: [title]   part: [name]   parameter: []（無必填）
--       import-profile/ssp/ap: [href]
--
-- ------------------------------------------------------------------------------
-- 已拍板設計規則（原樣套用，不再討論）：
--   1. PK 一律 `id`，BIGSERIAL，autoincrement。整個 schema 鐵律。
--   2. OSCAL 物件自己的 token id（NCName，如 "ac-2"、"ac-2.1"）存成 `{model}_id`
--      （control_id / group_id / part_id / param_id / role_id），每個都加 token 註解。
--      Root 物件帶 `uuid`（UUIDDatatype），不是 token。
--   3. 每個 FK 指向目標表的 `id`（serial PK），絕不指向 token 欄。
--   4. Audit 欄來自共用 mixin：created_at / updated_at / created_user / updated_user。
--   5. Python 保留字：
--        OSCAL `class`    -> attr class_,    mapped_column("class", ...)    DB 欄 = "class"
--        OSCAL `metadata` -> attr metadata_（Base.metadata 為 SQLAlchemy 保留屬性）；
--                            FK 欄是 `metadata_id`，relationship attr 是 metadata_。
--   6. JSONB vs 關聯化判準：「會不會對它的內部欄位做 WHERE / JOIN / FK / 約束 / 排序聚合？」
--        會 -> 關聯化表；不會（整包存取顯示）-> JSONB（選填者 nullable）。
--        - props / links            -> 每個 owner 上的 JSONB（OSCAL 選填 -> nullable）
--          ⚠️ 文件 root（catalog/profile/ssp/...）本身「沒有」props/links —— 它們在 metadata
--             與子物件（group/control/part/param/role/party 等）上；root 表不要加。
--             例外：resource 有 props 但「沒有」links（它用 rlinks）。
--        - parts                    -> 關聯化（內容會被 evidence / SSP / AP 綁定，是 FK target）
--   7. 保真：JSONB 欄原樣保存未知 / extension 的 props 與 links。
--
-- 軟式 join 政策：
--   * 同一份文件內的包含關係（catalog->group->control->part、root->metadata）用真 FK +
--     ON DELETE CASCADE -> dbdiagram.io 會畫成關係線。
--   * 參照存「關係」不存「字串」：OSCAL 的跨/同文件參照（import-*.href、by-component.
--     component-uuid、link href="#uuid"、party-uuid…）在關聯式裡一律以 FK 指向 target.id 表達；
--     OSCAL 的 href / uuid 參照字串屬「序列化」概念，匯出時由 target.uuid 衍生（#uuid），不落欄。
--       - import-*（profile/ssp/ap）：在本產品一定指向本地文件 -> import_*_id 真 FK（SSP/AP/AR
--         設 NOT NULL、畫實線）即完整表達，不存 href。日後要匯入「外部不可解析」文件再 additive
--         加回 import_*_href。POA&M 的 import-ssp 在 OSCAL 本就選填 -> import_ssp_id 維持 nullable。
--       - 真正存不了 FK 的（target 可能在別份文件 / DB 外，如 mapping source/target）才退回軟式
--         text/uuid 欄、不建 FK。
--     注意：物件「自己的」uuid（component.uuid、statement.uuid…是身分）要存；指向「別人」的
--     uuid 參照才換成 FK。
--   * back-matter resource 掛在 metadata（每份文件 1:1）-> resources.metadata_id 真 FK。
--
-- 展開範圍：八大 model 主要子實體皆已關聯化（SECTION A~I，共 45 表）。
--   深層宣告式 / leaf 結構（risk 的 characterizations/facets/risk-log、observation 的
--   methods/subjects/relevant-evidence、task 的 timing/dependencies、AP 的 assessment-subjects/
--   assessment-assets/terms-and-conditions、profile merge/modify、責任鏈 export/inherited/
--   satisfied 等）依規則 6 留 JSONB（要查詢再單獨關聯化）。
-- =============================================================================

CREATE SCHEMA IF NOT EXISTS oscal;


-- #############################################################################
-- # SECTION A -- 共用核心（值得查詢的子物件，以軟式 join 接到各 root）
-- #############################################################################

-- A1. metadata -----------------------------------------------------------------
-- required=[title, last-modified, version, oscal-version]；其餘全選填。
CREATE TABLE oscal.metadata (
    id                  bigserial    PRIMARY KEY,                          -- 系統 PK（非 OSCAL）
    title               text         NOT NULL,                            -- [1]
    published           timestamptz,                                      -- [0..1]
    last_modified       timestamptz  NOT NULL,                            -- [1]
    version             text         NOT NULL,                            -- [1]
    oscal_version       text         NOT NULL,                            -- [1]
    remarks             text,                                             -- [0..1]
    -- 選填子集合以 JSONB 保存（OSCAL 選填 -> nullable）：
    revisions           jsonb,                                            -- [0..*] metadata.revisions[]
    locations           jsonb,                                            -- [0..*] metadata.locations[]
    responsible_parties jsonb,                                            -- [0..*] metadata.responsible-parties[]
    document_ids        jsonb,                                            -- [0..*] metadata.document-ids[]
    actions             jsonb,                                            -- [0..*] metadata.actions[]
    props               jsonb,                                            -- [0..*] metadata.props[]
    links               jsonb,                                            -- [0..*] metadata.links[]
    -- audit mixin（系統欄，非 OSCAL）------------------------------------------
    created_at          timestamptz  NOT NULL DEFAULT now(),
    updated_at          timestamptz  NOT NULL DEFAULT now(),
    created_user        varchar(255),
    updated_user        varchar(255)
);
COMMENT ON TABLE  oscal.metadata IS 'OSCAL 文件後設資料（各 model 路徑皆為 <model>.metadata）：八大 model 共用，每份 OSCAL 文件（catalog / profile / ssp / ap ...）各對應一筆，記錄文件的標題、版本、修改時間等「文件本身」的資訊。各 root 表以 metadata_id 指回本表（1:1）。required=[title, last-modified, version, oscal-version]，其餘全選填。roles / parties 因會被查詢與交叉參照而另拆 oscal.roles / oscal.parties 子表；back-matter 資源拆到 oscal.resources；其餘選填子集合整包留 JSONB。';
COMMENT ON COLUMN oscal.metadata.id                  IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK（roles / parties / resources 的 metadata_id、各 root 表的 metadata_id）';
COMMENT ON COLUMN oscal.metadata.title               IS '[必填] metadata.title：文件名稱（MarkupLine），工具顯示與導覽用。例：「CMMC 2.0 Catalog」';
COMMENT ON COLUMN oscal.metadata.published           IS '[選填] metadata.published：文件正式對外發布的日期時間（timestamptz）';
COMMENT ON COLUMN oscal.metadata.last_modified       IS '[必填] metadata.last-modified：文件最近一次修改儲存的日期時間；OSCAL 規定內容變動時必須更新';
COMMENT ON COLUMN oscal.metadata.version             IS '[必填] metadata.version：這份文件本身的版本字串（非 OSCAL 規範版本），例：框架版本「5.1.1」';
COMMENT ON COLUMN oscal.metadata.oscal_version       IS '[必填] metadata.oscal-version：文件遵循的 OSCAL 規範版本，例：「1.2.2」';
COMMENT ON COLUMN oscal.metadata.remarks             IS '[選填] metadata.remarks：補充說明（MarkupMultiline，多行 Markdown）';
COMMENT ON COLUMN oscal.metadata.revisions           IS '[選填] metadata.revisions[]：文件修訂歷史，JSONB 陣列；每筆子欄 {version(required), title, published, last-modified, oscal-version, props, links, remarks}，依新到舊排列';
COMMENT ON COLUMN oscal.metadata.locations           IS '[選填] metadata.locations[]：實體據點（辦公室 / 機房），JSONB 陣列；每筆子欄 {uuid(required), title, address, email-addresses, telephone-numbers, urls, props, links, remarks}；party.location-uuids 軟參照其 uuid';
COMMENT ON COLUMN oscal.metadata.responsible_parties IS '[選填] metadata.responsible-parties[]：文件層級的「角色 → 當事方」指派，JSONB 陣列；每筆子欄 {role-id, party-uuids}（兩者皆 required），role-id 對應 oscal.roles.role_id、party-uuids 對應 oscal.parties.uuid';
COMMENT ON COLUMN oscal.metadata.document_ids        IS '[選填] metadata.document-ids[]：文件的外部識別碼（如 DOI），JSONB 陣列；每筆子欄 {identifier(required), scheme}';
COMMENT ON COLUMN oscal.metadata.actions             IS '[選填] metadata.actions[]：對文件的處置紀錄（如核准 approval），JSONB 陣列；每筆子欄 {uuid, type, system}（皆 required）+ {date, props, links, responsible-parties, remarks}';
COMMENT ON COLUMN oscal.metadata.props               IS '[選填] metadata.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）+ {uuid, ns, class, group, remarks}。未知 / extension props 原樣保真';
COMMENT ON COLUMN oscal.metadata.links               IS '[選填] metadata.links[]：對內外資源的超連結，JSONB 陣列；每筆子欄 {href(required), rel, media-type, resource-fragment, text}。href 為「#uuid」時指向 back-matter resource，原樣保真';
COMMENT ON COLUMN oscal.metadata.created_at          IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.metadata.updated_at          IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.metadata.created_user        IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.metadata.updated_user        IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- A2. roles --------------------------------------------------------------------
-- required=[id, title]
CREATE TABLE oscal.roles (
    id              bigserial    PRIMARY KEY,                              -- 系統 PK
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- containment FK（結構必填，非 OSCAL）
    sort_order      int          NOT NULL DEFAULT 0,                       -- 保序（系統欄，非 OSCAL）
    role_id         varchar(255) NOT NULL,                                 -- [1] OSCAL token id
    title           text         NOT NULL,                                 -- [1]
    short_name      text,                                                  -- [0..1]
    description     text,                                                  -- [0..1]
    props           jsonb,                                                 -- [0..*]
    links           jsonb,                                                 -- [0..*]
    remarks         text,                                                  -- [0..1]
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.roles IS 'metadata.roles[]：文件內定義的「職能角色」，例如 prepared-by（文件編製者）、system-owner（系統擁有者）、assessor（評估員）。一筆 = 一個角色定義，以 metadata_id 掛在所屬文件下。role_id 是文件內其他地方（如 responsible-party.role-id）交叉參照的 target，因此自 metadata 拆成獨立表。required=[id, title]。';
COMMENT ON COLUMN oscal.roles.id           IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.roles.metadata_id  IS '結構性父參照 → 指向 oscal.metadata.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位';
COMMENT ON COLUMN oscal.roles.sort_order   IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.roles.role_id      IS '[必填] role.id：角色的 OSCAL token id（NCName），例：「system-owner」、「assessor」；被 responsible-party.role-id 等軟參照（規則 2 存為 role_id）';
COMMENT ON COLUMN oscal.roles.title        IS '[必填] role.title：角色顯示名稱（MarkupLine），例：「系統擁有者」';
COMMENT ON COLUMN oscal.roles.short_name   IS '[選填] role.short-name：角色簡稱 / 縮寫 / 代號';
COMMENT ON COLUMN oscal.roles.description  IS '[選填] role.description：角色用途與職責摘要（MarkupMultiline）';
COMMENT ON COLUMN oscal.roles.props        IS '[選填] role.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）+ {uuid, ns, class, group, remarks}';
COMMENT ON COLUMN oscal.roles.links        IS '[選填] role.links[]：相關資源連結，JSONB 陣列；每筆子欄 {href(required), rel, media-type, text}';
COMMENT ON COLUMN oscal.roles.remarks      IS '[選填] role.remarks：補充說明（MarkupMultiline）';
COMMENT ON COLUMN oscal.roles.created_at   IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.roles.updated_at   IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.roles.created_user IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.roles.updated_user IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- A3. parties ------------------------------------------------------------------
-- required=[uuid, type]（OSCAL metaschema 規範 uuid+type 必填）
CREATE TABLE oscal.parties (
    id                       bigserial    PRIMARY KEY,                     -- 系統 PK
    metadata_id              bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- containment FK（結構必填）
    sort_order               int          NOT NULL DEFAULT 0,             -- 保序（系統）
    uuid                     uuid         NOT NULL,                       -- [1] party uuid；*.party-uuid(s) 軟參照 target
    type                     varchar(20)  NOT NULL,                       -- [1] person | organization
    name                     text,                                       -- [0..1]
    short_name               text,                                       -- [0..1]
    email_addresses          text[],                                     -- [0..*] 純量陣列
    telephone_numbers        jsonb,                                      -- [0..*]
    addresses                jsonb,                                      -- [0..*]
    external_ids             jsonb,                                      -- [0..*]
    location_uuids           uuid[],                                     -- [0..*] 軟參照 locations.uuid
    member_of_organizations  uuid[],                                     -- [0..*] 軟參照 parties.uuid
    props                    jsonb,                                      -- [0..*]
    links                    jsonb,                                      -- [0..*]
    remarks                  text,                                       -- [0..1]
    created_at               timestamptz  NOT NULL DEFAULT now(),
    updated_at               timestamptz  NOT NULL DEFAULT now(),
    created_user             varchar(255),
    updated_user             varchar(255)
);
COMMENT ON TABLE  oscal.parties IS 'metadata.parties[]：文件中提及的人員（person）或組織（organization），例如評估機構、供應商、系統負責人本人。一筆 = 一個當事方，以 metadata_id 掛在所屬文件下。party.uuid 被各 model 廣泛軟參照（responsible-party.party-uuids、AP/AR 的 actor 等），因此自 metadata 拆成獨立表。required=[uuid, type]。角色與當事方的對應關係不在本表，在 metadata.responsible_parties（JSONB）。';
COMMENT ON COLUMN oscal.parties.id                      IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.parties.metadata_id             IS '結構性父參照 → 指向 oscal.metadata.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位';
COMMENT ON COLUMN oscal.parties.sort_order              IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.parties.uuid                    IS '[必填] party.uuid：當事方全域唯一識別碼（UUID v4）；文件內外的 party-uuid 參照都軟指向本欄（不建 FK）';
COMMENT ON COLUMN oscal.parties.type                    IS '[必填] party.type：person（個人）或 organization（組織）';
COMMENT ON COLUMN oscal.parties.name                    IS '[選填] party.name：當事方正式全名，例：「Billows Technology」、「王小明」';
COMMENT ON COLUMN oscal.parties.short_name              IS '[選填] party.short-name：簡稱 / 縮寫 / 代號';
COMMENT ON COLUMN oscal.parties.email_addresses         IS '[選填] party.email-addresses[]：電子郵件清單，text 純量陣列（非 JSONB）';
COMMENT ON COLUMN oscal.parties.telephone_numbers       IS '[選填] party.telephone-numbers[]：電話清單，JSONB 陣列；每筆子欄 {number(required), type}，type 如 home / office / mobile';
COMMENT ON COLUMN oscal.parties.addresses               IS '[選填] party.addresses[]：郵寄地址清單，JSONB 陣列；每筆子欄 {type, addr-lines[], city, state, postal-code, country}（皆選填）';
COMMENT ON COLUMN oscal.parties.external_ids            IS '[選填] party.external-ids[]：外部系統識別碼（如 ORCID），JSONB 陣列；每筆子欄 {scheme(required), id}';
COMMENT ON COLUMN oscal.parties.location_uuids          IS '[選填] party.location-uuids[]：所在據點的 uuid 清單（uuid 陣列），軟參照 metadata.locations[] 內的 uuid';
COMMENT ON COLUMN oscal.parties.member_of_organizations IS '[選填] party.member-of-organizations[]：所屬組織的 party uuid 清單（uuid 陣列），軟參照同文件 oscal.parties.uuid（被指向者 type 應為 organization）';
COMMENT ON COLUMN oscal.parties.props                   IS '[選填] party.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）+ {uuid, ns, class, group, remarks}';
COMMENT ON COLUMN oscal.parties.links                   IS '[選填] party.links[]：相關資源連結，JSONB 陣列；每筆子欄 {href(required), rel, media-type, text}';
COMMENT ON COLUMN oscal.parties.remarks                 IS '[選填] party.remarks：補充說明（MarkupMultiline）';
COMMENT ON COLUMN oscal.parties.created_at              IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.parties.updated_at              IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.parties.created_user            IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.parties.updated_user            IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- A4. resources (back-matter) --------------------------------------------------
-- resource required=[uuid]。掛在 metadata（每份文件 1:1）-> 一根真 FK 畫線。
CREATE TABLE oscal.resources (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- containment FK（結構必填）：所屬文件
    sort_order      int          NOT NULL DEFAULT 0,                      -- 保序（系統）
    uuid            uuid         NOT NULL,                                -- [1] resource uuid；文件內以 #uuid 參照
    title           text,                                                -- [0..1]
    description     text,                                                -- [0..1]
    citation        jsonb,                                               -- [0..1] resource.citation {text, props, links} (required=[text])
    rlinks          jsonb,                                               -- [0..*] resource.rlinks[]
    base64          jsonb,                                               -- [0..1] resource.base64
    document_ids    jsonb,                                               -- [0..*] resource.document-ids[]
    props           jsonb,                                               -- [0..*]
    remarks         text,                                                -- [0..1]
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.resources IS '各 model 的 back-matter.resources[]：文件附件區，存放被文件引用的外部或內嵌資源——法規引文、原始 PDF、佐證文件等。一筆 = 一個 resource；文件內以 link href=「#uuid」的方式引用本表的列。掛在 metadata（每份文件 1:1）→ 一張表服務所有 model，且以真 FK 畫線。resource required=[uuid]。注意 resource 沒有 links 欄位（改用 rlinks 指向實體位置）。';
COMMENT ON COLUMN oscal.resources.id          IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.resources.metadata_id IS '結構性父參照 → 指向 oscal.metadata.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位；經 metadata 1:1 定位所屬文件 root';
COMMENT ON COLUMN oscal.resources.sort_order  IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.resources.uuid        IS '[必填] resource.uuid：資源全域唯一識別碼（UUID v4）；文件內 link.href 以「#uuid」片段解析到本筆';
COMMENT ON COLUMN oscal.resources.title       IS '[選填] resource.title：資源顯示名稱（MarkupLine），例：「NIST SP 800-171 R2 原始 PDF」';
COMMENT ON COLUMN oscal.resources.description IS '[選填] resource.description：資源內容與用途摘要（MarkupMultiline）';
COMMENT ON COLUMN oscal.resources.citation    IS '[選填] resource.citation {text, props, links}（required=[text]）：書目引文，JSONB；text 為引文字串（MarkupLine）';
COMMENT ON COLUMN oscal.resources.rlinks      IS '[選填] resource.rlinks[]：資源實體所在位置的指標清單，JSONB 陣列；每筆子欄 {href(required), media-type, hashes[]}，hashes 子欄 {algorithm, value}（皆 required）供完整性驗證';
COMMENT ON COLUMN oscal.resources.base64      IS '[選填] resource.base64：資源以 base64 編碼直接內嵌於文件，JSONB；子欄 {value(required), filename, media-type}';
COMMENT ON COLUMN oscal.resources.document_ids IS '[選填] resource.document-ids[]：資源的外部識別碼（如 DOI），JSONB 陣列；每筆子欄 {identifier(required), scheme}';
COMMENT ON COLUMN oscal.resources.props       IS '[選填] resource.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）。常見以 type prop 標記資源型別（logo / law / evidence 等）';
COMMENT ON COLUMN oscal.resources.remarks     IS '[選填] resource.remarks：補充說明（MarkupMultiline）';
COMMENT ON COLUMN oscal.resources.created_at  IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.resources.updated_at  IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.resources.created_user IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.resources.updated_user IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';


-- #############################################################################
-- # SECTION B -- Catalog model（catalog -> group -> control -> part 自巢狀）
-- #############################################################################

-- B0. catalogs (root)  required=[uuid, metadata] ------------------------------
CREATE TABLE oscal.catalogs (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    uuid            uuid         NOT NULL UNIQUE,                         -- [1] catalog uuid（root 識別碼；非 token）
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1] metadata（1:1；ORM attr metadata_）
    status          varchar(20)  NOT NULL DEFAULT 'draft',               -- 產品生命週期（非 OSCAL）
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
    -- 子物件（反向 FK 由子表持有）：groups[]、controls[]、back-matter；
    -- 注意 catalog 層 params[] 本設計未承接（known gap，見審查報告）
);
COMMENT ON TABLE  oscal.catalogs IS 'OSCAL Catalog root（path: catalog）：一份控制項目錄文件 = 一筆，例如 CMMC 2.0、ISO/IEC 27001:2022、NIST 800-53 R5 各為一筆。是整個控制層的源頭：Profile 從這裡挑選控制項組 baseline，SSP / AP 再往下游引用。required=[uuid, metadata]。子物件由子表持反向 FK：catalog_groups（分組）、catalog_controls（控制項）、resources（back-matter，經 metadata）。注意：catalog 層的 params[] 本設計未承接（known gap，見審查報告）。';
COMMENT ON COLUMN oscal.catalogs.id           IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK（catalog_groups.catalog_id / catalog_controls.catalog_id）';
COMMENT ON COLUMN oscal.catalogs.uuid         IS '[必填] catalog.uuid：目錄文件的全域唯一識別碼（UUID v4，root 識別碼、非 token）；OSCAL 規定內容變更時應換新 uuid';
COMMENT ON COLUMN oscal.catalogs.metadata_id  IS '[必填] catalog.metadata：FK → oscal.metadata.id（1:1），文件標題 / 版本 / 修改時間存在那邊。ORM attr 為 metadata_（規則 5，避開 SQLAlchemy 保留屬性）';
COMMENT ON COLUMN oscal.catalogs.status       IS '系統欄（非 OSCAL）：產品生命週期 draft / published / deprecated';
COMMENT ON COLUMN oscal.catalogs.created_at   IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.catalogs.updated_at   IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.catalogs.created_user IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.catalogs.updated_user IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- B1. catalog_groups（自巢狀）  required=[title] ------------------------------
CREATE TABLE oscal.catalog_groups (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    catalog_id      bigint       NOT NULL REFERENCES oscal.catalogs(id) ON DELETE CASCADE,  -- containment FK（結構必填）
    parent_id       bigint       REFERENCES oscal.catalog_groups(id) ON DELETE CASCADE,     -- self-FK；NULL = 頂層分組
    sort_order      int          NOT NULL DEFAULT 0,                      -- 保序（系統）
    group_id        varchar(255),                                        -- [0..1] OSCAL token id
    "class"         varchar(255),                                        -- [0..1] OSCAL class；ORM attr class_
    title           text         NOT NULL,                               -- [1]
    props           jsonb,                                               -- [0..*]
    links           jsonb,                                               -- [0..*]
    remarks         text,                                                -- (OSCAL 無此欄)
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.catalog_groups IS 'catalog.groups[]：控制項的階層分組，例如 CMMC 的 domain（AC 存取控制）、ISO 27001 的條款（clause 5 組織控制）、800-53 的 family。一筆 = 一個分組；以 catalog_id 掛在目錄下，以 parent_id 自巢狀支援分組底下再分組（對應 group.groups[]）。required=[title]。控制項由 catalog_controls.catalog_group_id 反向掛進來。注意：group 層的 params[] / parts[] 本設計未承接（known gap，目標框架未用）。';
COMMENT ON COLUMN oscal.catalog_groups.id           IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK（catalog_controls.catalog_group_id、自身 parent_id）';
COMMENT ON COLUMN oscal.catalog_groups.catalog_id   IS '結構性父參照 → 指向 oscal.catalogs.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位';
COMMENT ON COLUMN oscal.catalog_groups.parent_id    IS 'self-FK → 指向 oscal.catalog_groups.id（上層分組）；NULL = 頂層分組。對應 OSCAL group.groups[] 巢狀結構，非 OSCAL 欄位（ON DELETE CASCADE）';
COMMENT ON COLUMN oscal.catalog_groups.sort_order   IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.catalog_groups.group_id     IS '[選填] group.id：分組的 OSCAL token id，供文件內交叉連結，例：「ac」（800-53 family）、「5」（ISO clause）（規則 2 存為 group_id）';
COMMENT ON COLUMN oscal.catalog_groups."class"      IS '[選填] group.class：分組的子型別標籤，例：「family」；ORM attr class_（規則 5）';
COMMENT ON COLUMN oscal.catalog_groups.title        IS '[必填] group.title：分組顯示標題（MarkupLine），例：「Access Control（存取控制）」';
COMMENT ON COLUMN oscal.catalog_groups.props        IS '[選填] group.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）+ {uuid, ns, class, group, remarks}';
COMMENT ON COLUMN oscal.catalog_groups.links        IS '[選填] group.links[]：相關資源連結，JSONB 陣列；每筆子欄 {href(required), rel, media-type, text}';
COMMENT ON COLUMN oscal.catalog_groups.remarks      IS '⚠️ OSCAL group 無此欄位（幽靈欄，匯出時無處可放；待決策移除）';
COMMENT ON COLUMN oscal.catalog_groups.created_at   IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.catalog_groups.updated_at   IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.catalog_groups.created_user IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.catalog_groups.updated_user IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- B2. catalog_controls（自巢狀=enhancement）  required=[id, title] ------------
CREATE TABLE oscal.catalog_controls (
    id                  bigserial    PRIMARY KEY,                         -- 系統 PK
    catalog_id          bigint       NOT NULL REFERENCES oscal.catalogs(id) ON DELETE CASCADE,        -- containment FK（結構必填）
    catalog_group_id    bigint       REFERENCES oscal.catalog_groups(id) ON DELETE CASCADE,           -- 所屬分組；NULL = 直屬 catalog
    parent_id           bigint       REFERENCES oscal.catalog_controls(id) ON DELETE CASCADE,         -- self-FK；非 NULL = control enhancement
    sort_order          int          NOT NULL DEFAULT 0,                  -- 保序（系統）
    control_id          varchar(255) NOT NULL,                            -- [1] OSCAL token id（如 ac-2、ac-2.1）
    "class"             varchar(255),                                     -- [0..1] OSCAL class；ORM attr class_
    title               text         NOT NULL,                            -- [1]
    props               jsonb,                                            -- [0..*] 存 OSCAL label / sort-id 等
    links               jsonb,                                            -- [0..*]
    created_at          timestamptz  NOT NULL DEFAULT now(),
    updated_at          timestamptz  NOT NULL DEFAULT now(),
    created_user        varchar(255),
    updated_user        varchar(255)
);
COMMENT ON TABLE  oscal.catalog_controls IS 'Catalog 控制項（catalog.controls[] / group.controls[] / control.controls[]）：合規要求的最小管理單位，例如 800-53 的 ac-2（帳號管理）、CMMC 的 AC.L1-3.1.1、ISO 27001 的 A.5.1。一筆 = 一個控制項。required=[id, title]。catalog_group_id 指向所屬分組（NULL = 直屬 catalog 頂層）；parent_id 自巢狀表達 control enhancement（如 ac-2.1 掛在 ac-2 下）。控制項內文（statement / guidance / AO）在子表 catalog_control_parts，參數在子表 catalog_control_params。';
COMMENT ON COLUMN oscal.catalog_controls.id               IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK（catalog_control_parts / catalog_control_params 的 catalog_control_id、自身 parent_id）';
COMMENT ON COLUMN oscal.catalog_controls.catalog_id       IS '結構性父參照 → 指向 oscal.catalogs.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位；任何巢狀深度的控制項都直掛所屬目錄，方便整目錄掃描';
COMMENT ON COLUMN oscal.catalog_controls.catalog_group_id IS '所屬分組 FK → 指向 oscal.catalog_groups.id；NULL = 直屬 catalog 頂層（OSCAL 允許 control 不掛 group）。結構參照，非 OSCAL 欄位（ON DELETE CASCADE）';
COMMENT ON COLUMN oscal.catalog_controls.parent_id        IS 'self-FK → 指向 oscal.catalog_controls.id；非 NULL = 本筆為該控制項的 enhancement（子控制項），對應 OSCAL control.controls[] 巢狀，例：ac-2.1 的 parent 是 ac-2。非 OSCAL 欄位（ON DELETE CASCADE）';
COMMENT ON COLUMN oscal.catalog_controls.sort_order       IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.catalog_controls.control_id       IS '[必填] control.id：控制項的 OSCAL token id，例：「ac-2」、「ac-2.1」；Profile 選控制項、SSP 實作描述、AP 評估範圍都以此參照本控制項（規則 2 存為 control_id）';
COMMENT ON COLUMN oscal.catalog_controls."class"          IS '[選填] control.class：控制項的子型別標籤，例：「SP800-53」；ORM attr class_（規則 5）';
COMMENT ON COLUMN oscal.catalog_controls.title            IS '[必填] control.title：控制項標題（MarkupLine），例：「Account Management（帳號管理）」';
COMMENT ON COLUMN oscal.catalog_controls.props            IS '[選填] control.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）。常見存 label（顯示編號，如 AC-2）與 sort-id（排序鍵）';
COMMENT ON COLUMN oscal.catalog_controls.links            IS '[選填] control.links[]：相關資源連結（如 rel=related 指向其他控制項、reference 指向 back-matter），JSONB 陣列；每筆子欄 {href(required), rel, media-type, text}';
COMMENT ON COLUMN oscal.catalog_controls.created_at       IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.catalog_controls.updated_at       IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.catalog_controls.created_user     IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.catalog_controls.updated_user     IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- B3. catalog_control_parts  <<< Assessment Object（AO）放這裡 >>>  required=[name]
-- OSCAL catalog 沒有獨立 AO assembly：唯一遞迴容器是 control-common `part`（required=[name]）。
-- 800-53A 的評估目標/方法/物件全是 part，靠 name 區分。AO = 本表 name 為 assessment-* 的列。
CREATE TABLE oscal.catalog_control_parts (
    id                  bigserial    PRIMARY KEY,                         -- 系統 PK
    catalog_control_id  bigint       NOT NULL REFERENCES oscal.catalog_controls(id) ON DELETE CASCADE,  -- containment FK（結構必填）：反正規化到所有後代
    parent_id           bigint       REFERENCES oscal.catalog_control_parts(id) ON DELETE CASCADE,      -- self-FK；NULL = 控制項頂層 part
    sort_order          int          NOT NULL DEFAULT 0,                  -- 保序（系統）
    part_id             varchar(255),                                     -- [0..1] OSCAL part.id（token；規則 2 只前綴 id）
    name                varchar(255) NOT NULL,                            -- [1] OSCAL part.name；AO 判別欄
    ns                  text,                                             -- [0..1] qualifies name 的 namespace URI
    "class"             varchar(255),                                     -- [0..1] OSCAL class；ORM attr class_
    title               text,                                             -- [0..1]
    prose               text,                                             -- [0..1]（OSCAL part.prose 選填）
    props               jsonb,                                            -- [0..*] assessment-method 的 method=EXAMINE/INTERVIEW/TEST 存這
    links               jsonb,                                            -- [0..*]
    created_at          timestamptz  NOT NULL DEFAULT now(),
    updated_at          timestamptz  NOT NULL DEFAULT now(),
    created_user        varchar(255),
    updated_user        varchar(255)
);
COMMENT ON TABLE  oscal.catalog_control_parts IS 'control.parts[]：控制項的結構化內文，一筆 = 一個 part，以 parent_id 自巢狀（part 可再含 parts[]）。OSCAL 把控制要求本文（statement）、補充指引（guidance）、評估目標（assessment-objective）、評估方法 / 物件（assessment-method / assessment-objects）全部用同一種遞迴 part 結構表達，靠 name 區分語意。required=[name]。Assessment Object（AO）放這裡：name 屬 (assessment-objective, assessment-method, assessment-objects, objective) 的列即為 AO。【界定】本表僅承接 catalog 的 control 層 part；group 層 parts[]（與 params[]）本設計未承接（known gap，目標框架未用）；profile 的 part 在 profiles.merge/modify（JSONB）；AP/AR 的 part（objectives-and-methods 的 control-common Part、terms-and-conditions/attestations/assessment-method 的 uuid-keyed Assessment Part）屬 AP/AR 層、不與本表共用（欄位可共用 SQLAlchemy mixin，但各為獨立表）。';
COMMENT ON COLUMN oscal.catalog_control_parts.id                 IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK（自身 parent_id；evidence / SSP / AP 綁定 AO 時也指向本表）';
COMMENT ON COLUMN oscal.catalog_control_parts.catalog_control_id IS '結構性父參照 → 指向 oscal.catalog_controls.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位；反正規化到每個後代 part（不只頂層），使依控制項查 AO 為 indexed flat scan';
COMMENT ON COLUMN oscal.catalog_control_parts.parent_id          IS 'self-FK → 指向 oscal.catalog_control_parts.id（上層 part）；NULL = 控制項頂層 part。對應 OSCAL part.parts[] 巢狀，非 OSCAL 欄位（ON DELETE CASCADE）';
COMMENT ON COLUMN oscal.catalog_control_parts.sort_order         IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.catalog_control_parts.part_id            IS '[選填] part.id：part 的 OSCAL token id，例：「ac-2_smt.a」（ac-2 statement 第 a 項）；供細粒度交叉參照（規則 2 存為 part_id）';
COMMENT ON COLUMN oscal.catalog_control_parts.name          IS '[必填] part.name：token，標示 part 的語意型別，也是 AO 判別欄：statement（控制要求本文）/ guidance（補充指引）/ objective、assessment-objective（評估目標）/ assessment-method（評估方法）/ assessment-objects（評估物件）/ item / ...';
COMMENT ON COLUMN oscal.catalog_control_parts.ns                 IS '[選填] part.ns：限定 name 語意空間的 namespace URI，讓不同組織可用同名 name 而不衝突；未填視為 NIST 預設 namespace';
COMMENT ON COLUMN oscal.catalog_control_parts."class"            IS '[選填] part.class：part 的子型別 / 分類標籤；ORM attr class_（規則 5）';
COMMENT ON COLUMN oscal.catalog_control_parts.title              IS '[選填] part.title：part 顯示標題（MarkupLine）';
COMMENT ON COLUMN oscal.catalog_control_parts.prose              IS '[選填] part.prose：part 內文（MarkupMultiline，Markdown）——控制項敘述文字主要存在這欄；純結構容器 part 可為 NULL。文中可含 {{ insert: param, <param_id> }} 參數佔位';
COMMENT ON COLUMN oscal.catalog_control_parts.props              IS '[選填] part.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）。800-53A 評估方法的 method=EXAMINE / INTERVIEW / TEST 即存這裡';
COMMENT ON COLUMN oscal.catalog_control_parts.links              IS '[選填] part.links[]：相關資源連結，JSONB 陣列；每筆子欄 {href(required), rel, media-type, text}';
COMMENT ON COLUMN oscal.catalog_control_parts.created_at         IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.catalog_control_parts.updated_at         IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.catalog_control_parts.created_user       IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.catalog_control_parts.updated_user       IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- B4. catalog_control_params（control-scoped）  required=[]（無必填）------
CREATE TABLE oscal.catalog_control_params (
    id                  bigserial    PRIMARY KEY,                         -- 系統 PK
    catalog_control_id  bigint       NOT NULL REFERENCES oscal.catalog_controls(id) ON DELETE CASCADE,  -- containment FK（結構必填）
    sort_order          int          NOT NULL DEFAULT 0,                  -- 保序（系統）
    param_id            varchar(255),                                     -- [0..1] OSCAL token id（如 ac-2_prm_1）
    "class"             varchar(255),                                     -- [0..1] ORM attr class_
    depends_on          varchar(255),                                     -- [0..1]（OSCAL 已棄用）
    label               text,                                             -- [0..1]
    usage               text,                                             -- [0..1]
    select_how_many     varchar(20),                                      -- [0..1] one | one-or-more
    select_choices      jsonb,                                            -- [0..*] parameter.select.choice[]（與 param_values 互斥）
    param_values        jsonb,                                            -- [0..*] parameter.values[]（與 select 互斥）
    constraints         jsonb,                                            -- [0..*] parameter.constraints[]
    guidelines          jsonb,                                            -- [0..*] parameter.guidelines[]
    props               jsonb,                                            -- [0..*]
    links               jsonb,                                            -- [0..*]
    remarks             text,                                             -- [0..1]
    created_at          timestamptz  NOT NULL DEFAULT now(),
    updated_at          timestamptz  NOT NULL DEFAULT now(),
    created_user        varchar(255),
    updated_user        varchar(255)
);
COMMENT ON TABLE  oscal.catalog_control_params IS 'control.params[]：控制項參數（parameter），讓控制項文字中的可變值（如稽核頻率、密碼長度、鎖定時間）留白給採用組織自行指定。一筆 = 一個參數；控制項內文（part.prose）以 {{ insert: param, <id> }} 佔位引用，下游 Profile / SSP 以 set-parameter 對其賦值。parameter required=[]（無必填）。內部 constraints / guidelines / values / select 留 JSONB。注意 catalog 層與 group 層的 params[]（罕見；目標框架未用）本設計未承接。';
COMMENT ON COLUMN oscal.catalog_control_params.id                 IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。供其他表建 FK 之用（本表目前無子表參照）';
COMMENT ON COLUMN oscal.catalog_control_params.catalog_control_id IS '結構性父參照 → 指向 oscal.catalog_controls.id，子物件不能脫離父物件存在（ON DELETE CASCADE 連動刪除），非 OSCAL 欄位';
COMMENT ON COLUMN oscal.catalog_control_params.sort_order         IS '保留 OSCAL 陣列原始順序用（匯出時 ORDER BY），系統欄非 OSCAL';
COMMENT ON COLUMN oscal.catalog_control_params.param_id           IS '[選填] parameter.id：參數的 OSCAL token id，例：「ac-2_prm_1」；控制項 prose 的 {{ insert: param }} 佔位與 profile/SSP 的 set-parameter 以此參照（規則 2 存為 param_id）';
COMMENT ON COLUMN oscal.catalog_control_params."class"            IS '[選填] parameter.class：參數的型別 / 用途 / 範圍分類標籤；ORM attr class_（規則 5）';
COMMENT ON COLUMN oscal.catalog_control_params.depends_on         IS '[選填] parameter.depends-on（OSCAL 已棄用，不應再使用）：引用本參數的另一參數 token id';
COMMENT ON COLUMN oscal.catalog_control_params.label              IS '[選填] parameter.label：參數的短佔位名稱（MarkupLine），未賦值時可代為顯示，例：「organization-defined frequency」';
COMMENT ON COLUMN oscal.catalog_control_params.usage              IS '[選填] parameter.usage：參數的用途與使用方式說明（MarkupMultiline）';
COMMENT ON COLUMN oscal.catalog_control_params.select_how_many    IS '[選填] parameter.select.how-many：限定可選幾項，one | one-or-more；未填視為只允許一項。搭配 select_choices 使用。values 與 select 在 OSCAL 為互斥（anyOf 兩分支），互斥由應用層保證';
COMMENT ON COLUMN oscal.catalog_control_params.select_choices     IS '[選填] parameter.select.choice[]：候選值清單（MarkupLine 陣列），JSONB——參數值必須從這些選項中挑。values 與 select 在 OSCAL 為互斥（anyOf 兩分支），互斥由應用層保證';
COMMENT ON COLUMN oscal.catalog_control_params.param_values       IS '[選填] parameter.values[]：參數的既定值清單（字串陣列），JSONB——目錄層直接給定值時用。values 與 select 在 OSCAL 為互斥（anyOf 兩分支），互斥由應用層保證';
COMMENT ON COLUMN oscal.catalog_control_params.constraints        IS '[選填] parameter.constraints[]：參數值的限制條件，JSONB 陣列；每筆子欄 {description, tests[]}，tests 子欄 {expression(required), remarks} 為工具可評估的測試式';
COMMENT ON COLUMN oscal.catalog_control_params.guidelines         IS '[選填] parameter.guidelines[]：參數使用建議，JSONB 陣列；每筆子欄 {prose}（required=[prose]，MarkupMultiline）';
COMMENT ON COLUMN oscal.catalog_control_params.props              IS '[選填] parameter.props[]：name/value 擴充屬性，JSONB 陣列；每筆子欄 {name, value}（皆 required）+ {uuid, ns, class, group, remarks}';
COMMENT ON COLUMN oscal.catalog_control_params.links              IS '[選填] parameter.links[]：相關資源連結，JSONB 陣列；每筆子欄 {href(required), rel, media-type, text}';
COMMENT ON COLUMN oscal.catalog_control_params.remarks            IS '[選填] parameter.remarks：補充說明（MarkupMultiline）';
COMMENT ON COLUMN oscal.catalog_control_params.created_at         IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.catalog_control_params.updated_at         IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.catalog_control_params.created_user      IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.catalog_control_params.updated_user       IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';


-- #############################################################################
-- # SECTION C -- 其餘 7 張 OSCAL root 表（root 層；內層 assembly 留下一刀）
-- #############################################################################

-- C1. profiles  required=[uuid, metadata, imports] ----------------------------
CREATE TABLE oscal.profiles (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    uuid            uuid         NOT NULL UNIQUE,                         -- [1]
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status          varchar(20)  NOT NULL DEFAULT 'draft',               -- 產品生命週期（非 OSCAL）
    -- imports[]（[1..*] required）已關聯化 -> 見 oscal.profile_imports（SECTION C2），不放 root
    merge           jsonb,                                               -- [0..1] profile.merge {as-is|flat|custom}
    modify          jsonb,                                               -- [0..1] profile.modify {set-parameters[], alters[]}
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.profiles IS 'OSCAL Profile（基準線 baseline）文件 root。Profile 是從一份或多份 Catalog（控制項全集）挑選控制項子集、並可調整參數與內容的「裁縫」文件；在 Catalog→Profile→SSP→AP→AR→POA&M 資訊流中位於第二站，承接 Catalog、供 SSP 的 import-profile 引用。本產品的合規資源庫 Module Frame 即對應此概念。required=[uuid, metadata, imports]。imports[]（[1..*]）已關聯化為 oscal.profile_imports（SECTION C2，至少一筆由應用層保證）；merge/modify 為宣告式客製區塊，留 JSONB。';
COMMENT ON COLUMN oscal.profiles.id           IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.profiles.uuid         IS '[必填] OSCAL profile.uuid：本份 Profile 文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新；其他 OSCAL 文件跨文件參照本 Profile 時即指向此 uuid';
COMMENT ON COLUMN oscal.profiles.metadata_id  IS '[必填] OSCAL profile.metadata：文件層中繼資料（title / last-modified / version / oscal-version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.profiles.status       IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.profiles.merge        IS '[選填] OSCAL profile.merge：宣告 imports 選入的控制項要如何合併組織。JSON shape：{combine?, flat? | as-is? | custom?}（三種結構模式擇一），無 required 子欄。例：{"as-is": true} 表示沿用來源 catalog 的原始群組結構';
COMMENT ON COLUMN oscal.profiles.modify       IS '[選填] OSCAL profile.modify：宣告式調整規則。JSON shape：{set-parameters[], alters[]} — set-parameters 改寫參數值、alters 對個別控制項增刪 parts/props，無 required 子欄';
COMMENT ON COLUMN oscal.profiles.created_at   IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.profiles.updated_at   IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.profiles.created_user IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.profiles.updated_user IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- C2. component_definitions  required=[uuid, metadata] ------------------------
CREATE TABLE oscal.component_definitions (
    id                              bigserial    PRIMARY KEY,             -- 系統 PK
    uuid                            uuid         NOT NULL UNIQUE,         -- [1]
    metadata_id                     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status                          varchar(20)  NOT NULL DEFAULT 'draft', -- 產品（非 OSCAL）
    import_component_definitions     jsonb,                              -- [0..*] import-component-definitions[] [{href}]
    created_at                      timestamptz  NOT NULL DEFAULT now(),
    updated_at                      timestamptz  NOT NULL DEFAULT now(),
    created_user                    varchar(255),
    updated_user                    varchar(255)
);
COMMENT ON TABLE  oscal.component_definitions IS 'OSCAL Component Definition 文件 root。用途是預先描述可重用元件（軟體、硬體、服務、政策、流程等）能滿足哪些控制項與如何滿足，供撰寫 SSP 時直接引用、避免重複撰寫；屬 Implementation Layer，與 SSP 同層但不綁定特定系統，不在 Catalog→Profile→SSP→AP→AR→POA&M 主資訊流上。required=[uuid, metadata]。子物件 components[]（required=[uuid, type, title, description]）與 capabilities[]（required=[uuid, name, description]）已關聯化 → 見 SECTION E（oscal.cd_components / oscal.cd_capabilities / oscal.cd_control_implementations 及其下層）。';
COMMENT ON COLUMN oscal.component_definitions.id                           IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.component_definitions.uuid                         IS '[必填] OSCAL component-definition.uuid：本份文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新';
COMMENT ON COLUMN oscal.component_definitions.metadata_id                  IS '[必填] OSCAL component-definition.metadata：文件層中繼資料（title / last-modified / version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.component_definitions.status                       IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.component_definitions.import_component_definitions IS '[選填] OSCAL import-component-definitions[]：引用其他 Component Definition 文件的連結清單，JSON shape：[{href}]（href 為 required 子欄，URI 指向外部文件）。引用對象多為外部文件，故不做 FK 關聯化、留 JSONB';
COMMENT ON COLUMN oscal.component_definitions.created_at                   IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.component_definitions.updated_at                   IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.component_definitions.created_user                 IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.component_definitions.updated_user                 IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- C3. system_security_plans  required=[uuid, metadata, import-profile, ...] ---
-- （現行 production 表 FR-035 後叫 oscal.ssps；此處保留 OSCAL 完整名以利對照）
CREATE TABLE oscal.system_security_plans (
    id                  bigserial    PRIMARY KEY,                         -- 系統 PK
    uuid                uuid         NOT NULL UNIQUE,                     -- [1]
    metadata_id         bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status              varchar(20)  NOT NULL DEFAULT 'draft',           -- 產品（非 OSCAL）
    import_profile_id   bigint       NOT NULL REFERENCES oscal.profiles(id),  -- 來源 profile（本產品 SSP 必由本地 profile 建立 → NOT NULL；import-profile 真實 anchor + 畫實線）
    created_at          timestamptz  NOT NULL DEFAULT now(),
    updated_at          timestamptz  NOT NULL DEFAULT now(),
    created_user        varchar(255),
    updated_user        varchar(255)
);
COMMENT ON TABLE  oscal.system_security_plans IS 'OSCAL System Security Plan（SSP，系統安全計畫）文件 root（現行 production 表 FR-035 後更名 oscal.ssps，本文件保留 OSCAL 全名以利對照）。SSP 描述「一個資訊系統如何實作某條 baseline 的各控制項」，是 Implementation Layer 的核心文件；在 Catalog→Profile→SSP→AP→AR→POA&M 資訊流中承接 Profile（import-profile），並作為 AP（import-ssp）的評估標的。required=[uuid, metadata, import-profile, system-characteristics, system-implementation, control-implementation]。三個必填 1:1 子樹已關聯化 → 見 SECTION D（oscal.ssp_system_characteristics / oscal.ssp_system_implementations / oscal.ssp_control_implementations 及其下層）。';
COMMENT ON COLUMN oscal.system_security_plans.id                  IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.system_security_plans.uuid                IS '[必填] OSCAL system-security-plan.uuid：本份 SSP 文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新；AP 的 import-ssp 跨文件參照即指向此 uuid';
COMMENT ON COLUMN oscal.system_security_plans.metadata_id         IS '[必填] OSCAL system-security-plan.metadata：文件層中繼資料（title / last-modified / version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.system_security_plans.status              IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.system_security_plans.import_profile_id   IS '[必填] OSCAL import-profile：原文是 import-profile.href（URI 參照本 SSP 要套用的 baseline），本設計改存 FK 指向本地 oscal.profiles.id — 本產品 SSP 必由本地 Profile 建立，故收緊為 NOT NULL，並以此 FK 作為 ERD 實線 anchor；匯出時由目標 profile.uuid 衍生 #uuid 形式的 href';
COMMENT ON COLUMN oscal.system_security_plans.created_at          IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.system_security_plans.updated_at          IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.system_security_plans.created_user        IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.system_security_plans.updated_user        IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- C4. assessment_plans  required=[uuid, metadata, import-ssp, reviewed-controls]
CREATE TABLE oscal.assessment_plans (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    uuid            uuid         NOT NULL UNIQUE,                         -- [1]
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status          varchar(20)  NOT NULL DEFAULT 'draft',               -- 產品（非 OSCAL）
    import_ssp_id   bigint       NOT NULL REFERENCES oscal.system_security_plans(id),  -- 來源 SSP（AP 必由本地 SSP 建立 → NOT NULL；import-ssp 真實 anchor + 畫實線）
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.assessment_plans IS 'OSCAL Assessment Plan（AP，評估計畫）文件 root。AP 描述「要評估哪份 SSP、評估哪些控制項、用什麼活動與排程進行評估」，是 Assessment Layer 的起點；在 Catalog→Profile→SSP→AP→AR→POA&M 資訊流中承接 SSP（import-ssp），其執行結果產出 AR。本產品 GRC 專案啟動時即建立 AP 並從 Profile clone 控制項。required=[uuid, metadata, import-ssp, reviewed-controls]。子樹已關聯化 → 見 SECTION F（oscal.ap_reviewed_controls / oscal.ap_local_objectives / oscal.ap_assessment_activities / oscal.ap_tasks）。注意：runtime AO（evidence 綁定的 AP task）屬本層，啟動專案時從 oscal.catalog_control_parts（name 為 assessment-*）clone。';
COMMENT ON COLUMN oscal.assessment_plans.id              IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.assessment_plans.uuid            IS '[必填] OSCAL assessment-plan.uuid：本份 AP 文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新；AR 的 import-ap 跨文件參照即指向此 uuid';
COMMENT ON COLUMN oscal.assessment_plans.metadata_id     IS '[必填] OSCAL assessment-plan.metadata：文件層中繼資料（title / last-modified / version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.assessment_plans.status          IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.assessment_plans.import_ssp_id   IS '[必填] OSCAL import-ssp：原文是 import-ssp.href（URI 參照受評估的 SSP），本設計改存 FK 指向本地 oscal.system_security_plans.id — AP 必由本地 SSP 建立，故收緊為 NOT NULL，並以此 FK 作為 ERD 實線 anchor；匯出時由目標 ssp.uuid 衍生 #uuid 形式的 href';
COMMENT ON COLUMN oscal.assessment_plans.created_at      IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.assessment_plans.updated_at      IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.assessment_plans.created_user    IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.assessment_plans.updated_user    IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- C5. assessment_results  required=[uuid, metadata, import-ap, results] -------
CREATE TABLE oscal.assessment_results (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    uuid            uuid         NOT NULL UNIQUE,                         -- [1]
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status          varchar(20)  NOT NULL DEFAULT 'draft',               -- 產品（非 OSCAL）
    import_ap_id    bigint       NOT NULL REFERENCES oscal.assessment_plans(id),  -- 來源 AP（AR 必由本地 AP 建立 → NOT NULL；import-ap 真實 anchor + 畫實線）
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.assessment_results IS 'OSCAL Assessment Results（AR，評估結果）文件 root。AR 記錄一次或多次評估執行的產出 — observations（觀察）、findings（發現）、risks（風險）；在 Catalog→Profile→SSP→AP→AR→POA&M 資訊流中承接 AP（import-ap），不符合的 findings 後續流向 POA&M 追蹤矯正。required=[uuid, metadata, import-ap, results]。results[]（[1..*]）已關聯化 → 見 SECTION G（oscal.ar_results）與 G2 共用表（oscal.assessment_observations / assessment_risks / assessment_findings）；results[] 至少一筆由應用層保證。AR root 層的 local-definitions（objectives-and-methods / activities）本設計未承接（known gap）。';
COMMENT ON COLUMN oscal.assessment_results.id             IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.assessment_results.uuid           IS '[必填] OSCAL assessment-results.uuid：本份 AR 文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新';
COMMENT ON COLUMN oscal.assessment_results.metadata_id    IS '[必填] OSCAL assessment-results.metadata：文件層中繼資料（title / last-modified / version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.assessment_results.status         IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.assessment_results.import_ap_id   IS '[必填] OSCAL import-ap：原文是 import-ap.href（URI 參照本 AR 依據的評估計畫），本設計改存 FK 指向本地 oscal.assessment_plans.id — AR 必由本地 AP 建立，故收緊為 NOT NULL，並以此 FK 作為 ERD 實線 anchor；匯出時由目標 ap.uuid 衍生 #uuid 形式的 href';
COMMENT ON COLUMN oscal.assessment_results.created_at     IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.assessment_results.updated_at     IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.assessment_results.created_user   IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.assessment_results.updated_user   IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- C6. poams（OSCAL plan-of-action-and-milestones）  required=[uuid, metadata, poam-items]
-- 與扁平產品表 compliance.poams 不同；這是 OSCAL POA&M 文件 root。
CREATE TABLE oscal.poams (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    uuid            uuid         NOT NULL UNIQUE,                         -- [1]
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status          varchar(20)  NOT NULL DEFAULT 'draft',               -- 產品（非 OSCAL）
    import_ssp_id   bigint       REFERENCES oscal.system_security_plans(id),  -- [0..1] 內部解析：本地 SSP（畫線用；外部匯入時 NULL）
    system_id       text,                                                -- [0..1] system-id.id
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.poams IS 'OSCAL Plan of Action and Milestones（POA&M，矯正行動計畫）文件 root。POA&M 追蹤評估後待矯正的缺失：每條 poam-item 對應一項待處理弱點及其矯正里程碑，是 Catalog→Profile→SSP→AP→AR→POA&M 資訊流的終點，內容多由 AR 的不符合 findings 轉入。required=[uuid, metadata, poam-items]；import-ssp 選填。poam-items[]（[1..*]）已關聯化 → 見 SECTION H（oscal.poam_items），至少一筆由應用層保證；observations[]/risks[]/findings[] 共用 G2 表。POA&M root 的 local-definitions（components / inventory-items / assessment-assets）本設計未承接（known gap）。注意：與扁平的 compliance.poams 產品表不同，本表是 OSCAL 文件 root。';
COMMENT ON COLUMN oscal.poams.id              IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.poams.uuid            IS '[必填] OSCAL plan-of-action-and-milestones.uuid：本份 POA&M 文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新';
COMMENT ON COLUMN oscal.poams.metadata_id     IS '[必填] OSCAL plan-of-action-and-milestones.metadata：文件層中繼資料（title / last-modified / version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.poams.status          IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.poams.import_ssp_id   IS '[選填] OSCAL import-ssp（[0..1]）：原文是 import-ssp.href（URI 參照本 POA&M 所屬系統的 SSP），本設計改存 FK 指向本地 oscal.system_security_plans.id（內部解析、ERD 畫線用）；外部匯入、解析不到本地 SSP 時為 NULL。匯出時由目標 ssp.uuid 衍生 #uuid 形式的 href';
COMMENT ON COLUMN oscal.poams.system_id       IS '[選填] OSCAL system-id：受追蹤系統的人讀識別碼（如 FedRAMP 系統編號）。OSCAL system-id 是 {id, identifier-type} 結構，本欄只存 id，identifier-type 未保留（known gap，待決策）';
COMMENT ON COLUMN oscal.poams.created_at      IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.poams.updated_at      IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.poams.created_user    IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.poams.updated_user    IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';

-- C7. mapping_collections（OSCAL 實驗性 model）  required=[uuid, metadata, mappings]
CREATE TABLE oscal.mapping_collections (
    id              bigserial    PRIMARY KEY,                             -- 系統 PK
    uuid            uuid         NOT NULL UNIQUE,                         -- [1]
    metadata_id     bigint       NOT NULL REFERENCES oscal.metadata(id) ON DELETE CASCADE,  -- [1]
    status          varchar(20)  NOT NULL DEFAULT 'draft',               -- 產品（非 OSCAL）
    provenance      jsonb        NOT NULL,                               -- [1] mapping-provenance{method, matching-rationale, status, mapping-description, ...}（JSONB）
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON COLUMN oscal.mapping_collections.provenance IS '[必填] OSCAL mapping-provenance：描述整份對應集是怎麼做出來的（對應方法與品質宣告）。JSON shape：{method, matching-rationale, status, mapping-description, confidence-score?, coverage?, responsible-parties[]?}；required 子欄=[method, matching-rationale, status, mapping-description]。matching-rationale 依 NIST IR 8477 集合論關係定義（equal / subset-of / superset-of 等）';
COMMENT ON TABLE  oscal.mapping_collections IS 'OSCAL Control Mapping collection 文件 root（實驗性 model，規範仍在演進）。用途是宣告兩個框架文件（source 與 target，各為 catalog 或 profile）控制項之間的對應關係，支撐跨框架合規（如 ISO 27001 ↔ NIST 800-53 對照）；不在 Catalog→Profile→SSP→AP→AR→POA&M 主資訊流上，屬 Control Layer 的橫向文件。required=[uuid, metadata, provenance, mappings]。mappings[]（[1..*]）已關聯化 → 見 SECTION I（oscal.mappings / oscal.mapping_maps），至少一筆由應用層保證；map.source/target 為軟式 id-ref + type（跨框架，不建 FK）。';
COMMENT ON COLUMN oscal.mapping_collections.id           IS '系統流水號主鍵（BIGSERIAL，非 OSCAL 欄位）。其他表以此建 FK';
COMMENT ON COLUMN oscal.mapping_collections.uuid         IS '[必填] OSCAL mapping-collection.uuid：本份對應集文件的全域唯一識別碼（RFC 4122 v4），文件內容修訂時應換新';
COMMENT ON COLUMN oscal.mapping_collections.metadata_id  IS '[必填] OSCAL mapping-collection.metadata：文件層中繼資料（title / last-modified / version 等），共用層已關聯化 → FK 指向 oscal.metadata.id（ORM 屬性名 metadata_，避開 SQLAlchemy 保留字）';
COMMENT ON COLUMN oscal.mapping_collections.status       IS '產品生命週期欄（draft/published/deprecated），非 OSCAL；是否保留待決策';
COMMENT ON COLUMN oscal.mapping_collections.created_at   IS '系統稽核欄（非 OSCAL）：建立時間';
COMMENT ON COLUMN oscal.mapping_collections.updated_at   IS '系統稽核欄（非 OSCAL）：最後更新時間';
COMMENT ON COLUMN oscal.mapping_collections.created_user IS '系統稽核欄（非 OSCAL）：建立者帳號（login_name）';
COMMENT ON COLUMN oscal.mapping_collections.updated_user IS '系統稽核欄（非 OSCAL）：最後更新者帳號（login_name）';


-- #############################################################################
-- # 索引（FK 查詢、AO 判別欄、import 內部解析 FK）
-- #############################################################################
CREATE INDEX idx_roles_metadata             ON oscal.roles (metadata_id);
CREATE INDEX idx_parties_metadata           ON oscal.parties (metadata_id);
CREATE INDEX idx_parties_uuid               ON oscal.parties (uuid);
CREATE INDEX idx_resources_metadata         ON oscal.resources (metadata_id);
CREATE INDEX idx_resources_uuid             ON oscal.resources (uuid);

CREATE INDEX idx_catalog_groups_catalog     ON oscal.catalog_groups (catalog_id);
CREATE INDEX idx_catalog_groups_parent      ON oscal.catalog_groups (parent_id);
CREATE INDEX idx_catalog_controls_catalog   ON oscal.catalog_controls (catalog_id);
CREATE INDEX idx_catalog_controls_group     ON oscal.catalog_controls (catalog_group_id);
CREATE INDEX idx_catalog_controls_parent    ON oscal.catalog_controls (parent_id);
CREATE INDEX idx_catalog_controls_token     ON oscal.catalog_controls (control_id);
CREATE INDEX idx_catalog_parts_control      ON oscal.catalog_control_parts (catalog_control_id);
CREATE INDEX idx_catalog_parts_parent       ON oscal.catalog_control_parts (parent_id);
-- AO 快查：「control X 的所有 assessment object」
CREATE INDEX idx_catalog_parts_ao           ON oscal.catalog_control_parts (catalog_control_id, name);
CREATE INDEX idx_catalog_params_control     ON oscal.catalog_control_params (catalog_control_id);

-- 跨文件 import 的內部解析 FK（畫線 + 內部 join 用）
CREATE INDEX idx_ssp_import_profile         ON oscal.system_security_plans (import_profile_id);
CREATE INDEX idx_ap_import_ssp              ON oscal.assessment_plans (import_ssp_id);
CREATE INDEX idx_ar_import_ap               ON oscal.assessment_results (import_ap_id);
CREATE INDEX idx_poam_import_ssp            ON oscal.poams (import_ssp_id);


-- #############################################################################
-- # SECTION C2 -- Profile imports（imports[] 關聯化；merge/modify 仍在 profiles JSONB）
-- #############################################################################

-- profile.imports[]（[1..*]）：import.href -> 來源 catalog/profile（參照原則 FK）+ 選取規則
CREATE TABLE oscal.profile_imports (
    id                  bigserial   PRIMARY KEY,
    profile_id          bigint      NOT NULL REFERENCES oscal.profiles(id) ON DELETE CASCADE,  -- 所屬 profile（containment）
    sort_order          int         NOT NULL DEFAULT 0,
    source_catalog_id   bigint      REFERENCES oscal.catalogs(id),    -- import.href -> 本地 catalog（參照原則；與 source_profile_id 擇一）
    source_profile_id   bigint      REFERENCES oscal.profiles(id),    -- import.href -> 另一個 profile（profile 可 import profile；擇一）
    include_all         boolean,                                      -- include-all：是否全部納入
    include_controls    jsonb,                                        -- [0..*] select-control-by-id[]: {with-ids[], matching[], with-child-controls}
    exclude_controls    jsonb,                                        -- [0..*] 排除規則（同上）
    created_at          timestamptz NOT NULL DEFAULT now(),
    updated_at          timestamptz NOT NULL DEFAULT now(),
    created_user        varchar(255),
    updated_user        varchar(255)
);
COMMENT ON TABLE  oscal.profile_imports IS 'profile.imports[]（[1..*]）：baseline 從哪份來源（catalog 或 profile）選取控制項。import.href -> source_catalog_id / source_profile_id FK（參照原則，擇一）；include-controls/exclude-controls（select-control-by-id：with-ids/matching/with-child-controls）留 JSONB。至少一筆由應用層保證。';
COMMENT ON COLUMN oscal.profile_imports.profile_id        IS 'containment FK -> oscal.profiles.id';
COMMENT ON COLUMN oscal.profile_imports.source_catalog_id IS 'import.href -> 本地 catalog 的 FK（參照原則；與 source_profile_id 擇一非空）';
COMMENT ON COLUMN oscal.profile_imports.source_profile_id IS 'import.href -> 另一個 profile 的 FK（profile 可 import profile；擇一）';
COMMENT ON COLUMN oscal.profile_imports.include_all       IS 'include-all：是否全部納入';
COMMENT ON COLUMN oscal.profile_imports.include_controls  IS '[選填] select-control-by-id[]：{with-ids, matching, with-child-controls}，JSONB';
COMMENT ON COLUMN oscal.profile_imports.exclude_controls  IS '[選填] 排除規則，JSONB';
COMMENT ON COLUMN oscal.profile_imports.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.profile_imports.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.profile_imports.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.profile_imports.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.profile_imports.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.profile_imports.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

CREATE INDEX idx_profile_imports_profile    ON oscal.profile_imports (profile_id);
CREATE INDEX idx_profile_imports_catalog    ON oscal.profile_imports (source_catalog_id);





-- #############################################################################
-- # SECTION D1 -- system-characteristics（1:1）+ information-types + diagrams
-- #############################################################################

-- D1.0 ssp_system_characteristics ----------------------------------------------
-- required=[authorization-boundary, description, status, system-ids, system-information, system-name]
CREATE TABLE oscal.ssp_system_characteristics (
    id                              bigserial   PRIMARY KEY,
    ssp_id                          bigint      NOT NULL UNIQUE REFERENCES oscal.system_security_plans(id) ON DELETE CASCADE,  -- 1:1
    system_name                     text        NOT NULL,                 -- [1] system-name
    system_name_short               text,                                 -- [0..1]
    description                     text        NOT NULL,                 -- [1]
    security_sensitivity_level      text,                                 -- [0..1] FIPS-199 整體分類（low/moderate/high）
    date_authorized                 date,                                 -- [0..1]
    system_ids                      jsonb       NOT NULL,                 -- [1..*] system-ids[]：[{id, identifier-type}]（JSONB；通常 1 筆）
    status_state                    varchar(40) NOT NULL,                 -- [1] status.state：operational/under-development/under-major-modification/disposition/other
    status_remarks                  text,                                 -- [0..1]
    authorization_boundary_description text     NOT NULL,                 -- [1] authorization-boundary.description（auth-boundary 必填、其 description 必填）
    network_architecture_description   text,                             -- [0..1] network-architecture.description（network-architecture 選填）
    data_flow_description              text,                             -- [0..1] data-flow.description（data-flow 選填）
    security_objective_confidentiality text,                             -- [0..1] security-impact-level（選填；若有則三軸皆必填，應用層保證）
    security_objective_integrity       text,                             -- [0..1]
    security_objective_availability    text,                             -- [0..1]
    responsible_parties             jsonb,                                -- [0..*]
    props                           jsonb,                                -- [0..*]
    links                           jsonb,                                -- [0..*]
    remarks                         text,                                 -- [0..1]
    created_at                      timestamptz NOT NULL DEFAULT now(),
    updated_at                      timestamptz NOT NULL DEFAULT now(),
    created_user                    varchar(255),
    updated_user                    varchar(255)
);
COMMENT ON TABLE  oscal.ssp_system_characteristics IS 'SSP system-characteristics（1:1）：系統名稱/描述/狀態/FIPS-199 分類。required=[authorization-boundary, description, status, system-ids, system-information, system-name]。information-types → ssp_information_types；diagrams → ssp_diagrams。注意：auth-boundary/network/data-flow 的 props/links/remarks 暫未展開（僅存 description）。';
COMMENT ON COLUMN oscal.ssp_system_characteristics.ssp_id                     IS 'containment FK -> oscal.system_security_plans.id（1:1，UNIQUE）';
COMMENT ON COLUMN oscal.ssp_system_characteristics.system_name               IS '[必填] 系統全名';
COMMENT ON COLUMN oscal.ssp_system_characteristics.description               IS '[必填] 系統摘要';
COMMENT ON COLUMN oscal.ssp_system_characteristics.system_ids                IS '[必填] system-ids[]（[1..*]）：[{id, identifier-type}]，JSONB';
COMMENT ON COLUMN oscal.ssp_system_characteristics.status_state              IS '[必填] status.state：operational/under-development/under-major-modification/disposition/other';
COMMENT ON COLUMN oscal.ssp_system_characteristics.authorization_boundary_description IS '[必填] authorization-boundary.description';
COMMENT ON COLUMN oscal.ssp_system_characteristics.security_objective_confidentiality IS '[選填] security-impact-level：有 SIL 時三軸（C/I/A）皆必填（應用層保證）';
COMMENT ON COLUMN oscal.ssp_system_characteristics.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_system_characteristics.system_name_short IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.security_sensitivity_level IS '[選填] FIPS-199 整體分類（low/moderate/high）';
COMMENT ON COLUMN oscal.ssp_system_characteristics.date_authorized IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.status_remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.network_architecture_description IS '[選填] network-architecture.description（network-architecture 選填）';
COMMENT ON COLUMN oscal.ssp_system_characteristics.data_flow_description IS '[選填] data-flow.description（data-flow 選填）';
COMMENT ON COLUMN oscal.ssp_system_characteristics.security_objective_integrity IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.security_objective_availability IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.responsible_parties IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_characteristics.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_system_characteristics.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_system_characteristics.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_system_characteristics.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D1.1 ssp_information_types（FIPS-199）  required=[description, title] ---------
CREATE TABLE oscal.ssp_information_types (
    id                          bigserial   PRIMARY KEY,
    system_characteristics_id   bigint      NOT NULL REFERENCES oscal.ssp_system_characteristics(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid,                                     -- [0..1] information-type.uuid
    title                       text        NOT NULL,                     -- [1]
    description                 text        NOT NULL,                     -- [1]
    categorizations             jsonb,                                    -- [0..*] [{system, information-type-ids}]（categorization.required=[system]）
    confidentiality_impact_base text,                                     -- [0..1] confidentiality-impact.base（impact.required=[base]）
    confidentiality_impact_selected text,                                 -- [0..1]
    integrity_impact_base       text,                                     -- [0..1]
    integrity_impact_selected   text,                                     -- [0..1]
    availability_impact_base    text,                                     -- [0..1]
    availability_impact_selected text,                                    -- [0..1]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ssp_information_types IS 'system-information.information-types[]（FIPS-199 / NIST 800-60）：系統處理的資訊類型 + C/I/A 衝擊。required=[description, title]。每個 C/I/A impact 若提供則 base 必填。categorizations JSONB（categorization.required=[system]）。';
COMMENT ON COLUMN oscal.ssp_information_types.title                       IS '[必填] information-type.title';
COMMENT ON COLUMN oscal.ssp_information_types.description                 IS '[必填] information-type.description';
COMMENT ON COLUMN oscal.ssp_information_types.confidentiality_impact_base IS '[選填] confidentiality-impact.base（有此 impact 則必填）：fips-199-low/moderate/high';
COMMENT ON COLUMN oscal.ssp_information_types.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_information_types.system_characteristics_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_information_types.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_information_types.uuid IS '[選填] information-type.uuid';
COMMENT ON COLUMN oscal.ssp_information_types.categorizations IS '[選填] [{system, information-type-ids}]（categorization.required=[system]）';
COMMENT ON COLUMN oscal.ssp_information_types.confidentiality_impact_selected IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.integrity_impact_base IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.integrity_impact_selected IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.availability_impact_base IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.availability_impact_selected IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_information_types.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_information_types.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_information_types.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_information_types.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D1.2 ssp_diagrams  required=[uuid] ------------------------------------------
CREATE TABLE oscal.ssp_diagrams (
    id                          bigserial   PRIMARY KEY,
    system_characteristics_id   bigint      NOT NULL REFERENCES oscal.ssp_system_characteristics(id) ON DELETE CASCADE,
    diagram_context             varchar(30) NOT NULL,                     -- 判別：authorization-boundary / network-architecture / data-flow
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] diagram.uuid（身分；被 #uuid 參照）
    description                 text,                                     -- [0..1]
    caption                     text,                                     -- [0..1]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ssp_diagrams IS 'authorization-boundary / network-architecture / data-flow 三段共用的 diagrams[]，以 diagram_context 判別所屬段。required=[uuid]。';
COMMENT ON COLUMN oscal.ssp_diagrams.diagram_context IS '判別欄：authorization-boundary / network-architecture / data-flow';
COMMENT ON COLUMN oscal.ssp_diagrams.uuid            IS '[必填] diagram.uuid（自身身分，被 #uuid 參照）';
COMMENT ON COLUMN oscal.ssp_diagrams.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_diagrams.system_characteristics_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_diagrams.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_diagrams.description IS '[選填]';
COMMENT ON COLUMN oscal.ssp_diagrams.caption IS '[選填]';
COMMENT ON COLUMN oscal.ssp_diagrams.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_diagrams.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_diagrams.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_diagrams.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_diagrams.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_diagrams.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_diagrams.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # SECTION D2 -- system-implementation（1:1）+ leveraged-auth/users/components/inventory
-- #############################################################################

-- D2.0 ssp_system_implementations（1:1 wrapper）  required=[components] --------
CREATE TABLE oscal.ssp_system_implementations (
    id              bigserial   PRIMARY KEY,
    ssp_id          bigint      NOT NULL UNIQUE REFERENCES oscal.system_security_plans(id) ON DELETE CASCADE,  -- 1:1
    props           jsonb,                                                -- [0..*]
    links           jsonb,                                                -- [0..*]
    remarks         text,                                                 -- [0..1]
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.ssp_system_implementations IS 'SSP system-implementation（1:1 wrapper）。required=[components]（components 至少一筆，由 ssp_components 應用層保證）。children: leveraged-authorizations/users/components/inventory-items。';
COMMENT ON COLUMN oscal.ssp_system_implementations.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_system_implementations.ssp_id IS '1:1';
COMMENT ON COLUMN oscal.ssp_system_implementations.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_implementations.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_implementations.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_implementations.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_system_implementations.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_system_implementations.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_system_implementations.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D2.1 ssp_leveraged_authorizations  required=[date-authorized, party-uuid, title, uuid]
CREATE TABLE oscal.ssp_leveraged_authorizations (
    id                      bigserial   PRIMARY KEY,
    system_implementation_id bigint     NOT NULL REFERENCES oscal.ssp_system_implementations(id) ON DELETE CASCADE,
    sort_order              int         NOT NULL DEFAULT 0,
    uuid                    uuid        NOT NULL,                         -- [1] 自身身分
    title                   text        NOT NULL,                         -- [1]
    party_uuid              uuid        NOT NULL,                         -- [1] 軟式參照 oscal.parties.uuid（依主檔政策 party-uuid 不建 FK）
    date_authorized         date        NOT NULL,                         -- [1]
    props                   jsonb,                                        -- [0..*]
    links                   jsonb,                                        -- [0..*]
    remarks                 text,                                         -- [0..1]
    created_at              timestamptz NOT NULL DEFAULT now(),
    updated_at              timestamptz NOT NULL DEFAULT now(),
    created_user            varchar(255),
    updated_user            varchar(255)
);
COMMENT ON TABLE  oscal.ssp_leveraged_authorizations IS 'system-implementation.leveraged-authorizations[]：本系統繼承能力的另一個已授權系統（common control provider）。required=[date-authorized, party-uuid, title, uuid]。';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.party_uuid IS '[必填] 軟式參照 oscal.parties.uuid（party-uuid 全系統皆軟式，不建 FK）';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.system_implementation_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.title IS '[必填]';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.date_authorized IS '[必填]';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_leveraged_authorizations.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D2.2 ssp_system_users  required=[uuid] --------------------------------------
CREATE TABLE oscal.ssp_system_users (
    id                      bigserial   PRIMARY KEY,
    system_implementation_id bigint     NOT NULL REFERENCES oscal.ssp_system_implementations(id) ON DELETE CASCADE,
    sort_order              int         NOT NULL DEFAULT 0,
    uuid                    uuid        NOT NULL,                         -- [1] 自身身分
    title                   text,                                        -- [0..1]
    short_name              text,                                        -- [0..1]
    description             text,                                        -- [0..1]
    role_ids                jsonb,                                       -- [0..*] role-ids[]：軟式參照 roles.role_id（JSONB）
    authorized_privileges   jsonb,                                       -- [0..*] [{title, description, functions-performed[]}]（authorized-privilege.required=[functions-performed, title]）
    props                   jsonb,                                        -- [0..*]
    links                   jsonb,                                        -- [0..*]
    remarks                 text,                                         -- [0..1]
    created_at              timestamptz NOT NULL DEFAULT now(),
    updated_at              timestamptz NOT NULL DEFAULT now(),
    created_user            varchar(255),
    updated_user            varchar(255)
);
COMMENT ON TABLE  oscal.ssp_system_users IS 'system-implementation.users[]：與系統互動的使用者類型。required=[uuid]。role-ids/authorized-privileges 留 JSONB。';
COMMENT ON COLUMN oscal.ssp_system_users.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_system_users.system_implementation_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_system_users.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_system_users.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ssp_system_users.title IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_users.short_name IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_users.description IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_users.role_ids IS '[選填] role-ids[]：軟式參照 roles.role_id（JSONB）';
COMMENT ON COLUMN oscal.ssp_system_users.authorized_privileges IS '[選填] [{title, description, functions-performed[]}]（authorized-privilege.required=[functions-performed, title]）';
COMMENT ON COLUMN oscal.ssp_system_users.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_users.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_users.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_system_users.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_system_users.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_system_users.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_system_users.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D2.3 ssp_components  required=[description, status, title, type, uuid] -------
CREATE TABLE oscal.ssp_components (
    id                      bigserial   PRIMARY KEY,
    system_implementation_id bigint     NOT NULL REFERENCES oscal.ssp_system_implementations(id) ON DELETE CASCADE,
    sort_order              int         NOT NULL DEFAULT 0,
    uuid                    uuid        NOT NULL,                         -- [1] 自身身分（被 by-component.component-uuid 參照）
    type                    text        NOT NULL,                         -- [1] component.type（如 software/hardware/service/this-system）
    title                   text        NOT NULL,                         -- [1]
    description             text        NOT NULL,                         -- [1]
    purpose                 text,                                        -- [0..1]
    status_state            varchar(40) NOT NULL,                        -- [1] status.state：under-development/operational/disposition/other
    status_remarks          text,                                        -- [0..1]
    responsible_roles       jsonb,                                       -- [0..*]
    protocols               jsonb,                                       -- [0..*] protocol[]：{uuid, name, title, port-ranges[]}
    props                   jsonb,                                        -- [0..*]
    links                   jsonb,                                        -- [0..*]
    remarks                 text,                                         -- [0..1]
    created_at              timestamptz NOT NULL DEFAULT now(),
    updated_at              timestamptz NOT NULL DEFAULT now(),
    created_user            varchar(255),
    updated_user            varchar(255)
);
COMMENT ON TABLE  oscal.ssp_components IS 'system-implementation.components[]：系統元件。required=[description, status, title, type, uuid]。protocols/responsible-roles 留 JSONB。特殊「this-system」元件亦以一列表示。';
COMMENT ON COLUMN oscal.ssp_components.uuid         IS '[必填] component.uuid（自身身分；by-component 以 component_id FK 指回此列）';
COMMENT ON COLUMN oscal.ssp_components.status_state IS '[必填] status.state：under-development/operational/disposition/other';
COMMENT ON COLUMN oscal.ssp_components.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_components.system_implementation_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_components.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_components.type IS '[必填] component.type（如 software/hardware/service/this-system）';
COMMENT ON COLUMN oscal.ssp_components.title IS '[必填]';
COMMENT ON COLUMN oscal.ssp_components.description IS '[必填]';
COMMENT ON COLUMN oscal.ssp_components.purpose IS '[選填]';
COMMENT ON COLUMN oscal.ssp_components.status_remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_components.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.ssp_components.protocols IS '[選填] protocol[]：{uuid, name, title, port-ranges[]}';
COMMENT ON COLUMN oscal.ssp_components.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_components.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_components.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_components.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_components.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_components.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_components.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D2.4 ssp_inventory_items  required=[description, uuid] ----------------------
CREATE TABLE oscal.ssp_inventory_items (
    id                      bigserial   PRIMARY KEY,
    system_implementation_id bigint     NOT NULL REFERENCES oscal.ssp_system_implementations(id) ON DELETE CASCADE,
    sort_order              int         NOT NULL DEFAULT 0,
    uuid                    uuid        NOT NULL,                         -- [1] 自身身分
    description             text        NOT NULL,                         -- [1]
    responsible_parties     jsonb,                                       -- [0..*]
    implemented_components  jsonb,                                       -- [0..*] [{component-uuid, props, links}]：軟式參照 components（JSONB）
    props                   jsonb,                                        -- [0..*]
    links                   jsonb,                                        -- [0..*]
    remarks                 text,                                         -- [0..1]
    created_at              timestamptz NOT NULL DEFAULT now(),
    updated_at              timestamptz NOT NULL DEFAULT now(),
    created_user            varchar(255),
    updated_user            varchar(255)
);
COMMENT ON TABLE  oscal.ssp_inventory_items IS 'system-implementation.inventory-items[]：受管理的盤點項目。required=[description, uuid]。implemented-components 留 JSONB（軟式參照 components）。';
COMMENT ON COLUMN oscal.ssp_inventory_items.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_inventory_items.system_implementation_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_inventory_items.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_inventory_items.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ssp_inventory_items.description IS '[必填]';
COMMENT ON COLUMN oscal.ssp_inventory_items.responsible_parties IS '[選填]';
COMMENT ON COLUMN oscal.ssp_inventory_items.implemented_components IS '[選填] [{component-uuid, props, links}]：軟式參照 components（JSONB）';
COMMENT ON COLUMN oscal.ssp_inventory_items.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_inventory_items.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_inventory_items.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_inventory_items.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_inventory_items.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_inventory_items.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_inventory_items.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # SECTION D3 -- control-implementation（1:1）+ implemented-requirements/statements/by-components
-- #############################################################################

-- D3.0 ssp_control_implementations（1:1）  required=[description, implemented-requirements]
CREATE TABLE oscal.ssp_control_implementations (
    id              bigserial   PRIMARY KEY,
    ssp_id          bigint      NOT NULL UNIQUE REFERENCES oscal.system_security_plans(id) ON DELETE CASCADE,  -- 1:1
    description     text        NOT NULL,                                 -- [1] control-implementation.description
    set_parameters  jsonb,                                                -- [0..*] [{param-id, values[]}]（set-parameter.required=[param-id, values]）
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255)
);
COMMENT ON TABLE  oscal.ssp_control_implementations IS 'SSP control-implementation（1:1）：系統如何滿足一組控制項。required=[description, implemented-requirements]（後者至少一筆，由 ssp_implemented_requirements 保證）。control-implementation 無 props/links，僅 description + set-parameters。';
COMMENT ON COLUMN oscal.ssp_control_implementations.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_control_implementations.ssp_id IS '1:1';
COMMENT ON COLUMN oscal.ssp_control_implementations.description IS '[必填] control-implementation.description';
COMMENT ON COLUMN oscal.ssp_control_implementations.set_parameters IS '[選填] [{param-id, values[]}]（set-parameter.required=[param-id, values]）';
COMMENT ON COLUMN oscal.ssp_control_implementations.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_control_implementations.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_control_implementations.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_control_implementations.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D3.1 ssp_implemented_requirements  required=[control-id, uuid] --------------
CREATE TABLE oscal.ssp_implemented_requirements (
    id                          bigserial   PRIMARY KEY,
    control_implementation_id   bigint      NOT NULL REFERENCES oscal.ssp_control_implementations(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    control_id                  varchar(255) NOT NULL,                    -- [1] OSCAL token：指向 imported profile/catalog 的控制項（跨文件，保留 token text）
    set_parameters              jsonb,                                    -- [0..*]
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ssp_implemented_requirements IS 'control-implementation.implemented-requirements[]：逐控制項說明系統如何滿足。required=[control-id, uuid]。children: statements / by-components。';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.control_id IS '[必填] OSCAL control token id（如 ac-2）。指向 imported profile/catalog 的控制項屬跨文件參照 → 保留 token text，不建 FK';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.control_implementation_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.set_parameters IS '[選填]';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_implemented_requirements.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D3.2 ssp_statements  required=[statement-id, uuid] --------------------------
CREATE TABLE oscal.ssp_statements (
    id                          bigserial   PRIMARY KEY,
    implemented_requirement_id  bigint      NOT NULL REFERENCES oscal.ssp_implemented_requirements(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    statement_id                varchar(255) NOT NULL,                    -- [1] OSCAL token：指向控制項的某 statement（跨文件，token text）
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ssp_statements IS 'implemented-requirement.statements[]：指明處理了控制項中的哪些 statement。required=[statement-id, uuid]。child: by-components。';
COMMENT ON COLUMN oscal.ssp_statements.statement_id IS '[必填] OSCAL statement token（如 ac-2_smt.a）。跨文件參照 → 保留 token text';
COMMENT ON COLUMN oscal.ssp_statements.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_statements.implemented_requirement_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_statements.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_statements.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ssp_statements.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.ssp_statements.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_statements.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_statements.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_statements.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_statements.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_statements.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_statements.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- D3.3 ssp_by_components  required=[component-uuid, description, uuid] ---------
-- by-component 同時可掛在 implemented-requirement 或 statement 底下 -> 兩個 nullable FK（擇一）。
CREATE TABLE oscal.ssp_by_components (
    id                          bigserial   PRIMARY KEY,
    implemented_requirement_id  bigint      REFERENCES oscal.ssp_implemented_requirements(id) ON DELETE CASCADE,  -- 父：擇一
    statement_id                bigint      REFERENCES oscal.ssp_statements(id) ON DELETE CASCADE,               -- 父：擇一
    component_id                bigint      NOT NULL REFERENCES oscal.ssp_components(id) ON DELETE CASCADE,        -- by-component.component-uuid -> FK（本 SSP 內參照，參照原則）
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    description                 text        NOT NULL,                     -- [1] 此元件如何實作該控制項/statement
    set_parameters              jsonb,                                    -- [0..*]
    implementation_status_state varchar(40),                             -- [0..1] implementation-status.state：implemented/partial/planned/alternative/not-applicable
    implementation_status_remarks text,                                  -- [0..1]
    export                      jsonb,                                    -- [0..1] 責任鏈：export{description, provided[], responsibilities[]}（JSONB，本刀決策）
    inherited                   jsonb,                                    -- [0..*] 責任鏈：inherited[]（JSONB）
    satisfied                   jsonb,                                    -- [0..*] 責任鏈：satisfied[]（JSONB）
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ssp_by_components IS 'by-component：某元件如何實作一組控制項/statement。required=[component-uuid, description, uuid]。父為 implemented-requirement 或 statement（兩 FK 擇一）。component-uuid -> component_id FK（本 SSP 內參照）。leveraged 責任鏈 export/inherited/satisfied 先以 JSONB 保存（含 provided/responsibility），要完整繼承追蹤再單獨關聯化。';
COMMENT ON COLUMN oscal.ssp_by_components.component_id IS '[必填] FK -> oscal.ssp_components.id（OSCAL by-component.component-uuid 的關聯式表達；參照原則）';
COMMENT ON COLUMN oscal.ssp_by_components.implementation_status_state IS '[選填] implementation-status.state：implemented/partial/planned/alternative/not-applicable';
COMMENT ON COLUMN oscal.ssp_by_components.export IS '[選填] leveraged 責任鏈 export{description, provided[], responsibilities[]}，JSONB';
COMMENT ON COLUMN oscal.ssp_by_components.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_by_components.implemented_requirement_id IS '父：擇一';
COMMENT ON COLUMN oscal.ssp_by_components.statement_id IS '父：擇一';
COMMENT ON COLUMN oscal.ssp_by_components.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ssp_by_components.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ssp_by_components.description IS '[必填] 此元件如何實作該控制項/statement';
COMMENT ON COLUMN oscal.ssp_by_components.set_parameters IS '[選填]';
COMMENT ON COLUMN oscal.ssp_by_components.implementation_status_remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_by_components.inherited IS '[選填] 責任鏈：inherited[]（JSONB）';
COMMENT ON COLUMN oscal.ssp_by_components.satisfied IS '[選填] 責任鏈：satisfied[]（JSONB）';
COMMENT ON COLUMN oscal.ssp_by_components.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.ssp_by_components.props IS '[選填]';
COMMENT ON COLUMN oscal.ssp_by_components.links IS '[選填]';
COMMENT ON COLUMN oscal.ssp_by_components.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ssp_by_components.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ssp_by_components.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ssp_by_components.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ssp_by_components.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # 索引
-- #############################################################################
CREATE INDEX idx_ssp_syschar_ssp            ON oscal.ssp_system_characteristics (ssp_id);
CREATE INDEX idx_ssp_infotypes_syschar      ON oscal.ssp_information_types (system_characteristics_id);
CREATE INDEX idx_ssp_diagrams_syschar       ON oscal.ssp_diagrams (system_characteristics_id);
CREATE INDEX idx_ssp_sysimpl_ssp            ON oscal.ssp_system_implementations (ssp_id);
CREATE INDEX idx_ssp_levauth_sysimpl        ON oscal.ssp_leveraged_authorizations (system_implementation_id);
CREATE INDEX idx_ssp_users_sysimpl          ON oscal.ssp_system_users (system_implementation_id);
CREATE INDEX idx_ssp_components_sysimpl     ON oscal.ssp_components (system_implementation_id);
CREATE INDEX idx_ssp_components_uuid        ON oscal.ssp_components (uuid);
CREATE INDEX idx_ssp_inventory_sysimpl      ON oscal.ssp_inventory_items (system_implementation_id);
CREATE INDEX idx_ssp_ctrlimpl_ssp           ON oscal.ssp_control_implementations (ssp_id);
CREATE INDEX idx_ssp_impreq_ctrlimpl        ON oscal.ssp_implemented_requirements (control_implementation_id);
CREATE INDEX idx_ssp_impreq_control         ON oscal.ssp_implemented_requirements (control_id);
CREATE INDEX idx_ssp_stmt_impreq            ON oscal.ssp_statements (implemented_requirement_id);
CREATE INDEX idx_ssp_bycomp_impreq          ON oscal.ssp_by_components (implemented_requirement_id);
CREATE INDEX idx_ssp_bycomp_stmt            ON oscal.ssp_by_components (statement_id);
CREATE INDEX idx_ssp_bycomp_component       ON oscal.ssp_by_components (component_id);


-- #############################################################################
-- # SECTION E -- Component Definition 子樹（components / capabilities / control-impl）
-- #############################################################################

-- E1. cd_components（defined-component）  required=[description, title, type, uuid]
CREATE TABLE oscal.cd_components (
    id                          bigserial   PRIMARY KEY,
    component_definition_id     bigint      NOT NULL REFERENCES oscal.component_definitions(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    type                        text        NOT NULL,                     -- [1] 元件類別
    title                       text        NOT NULL,                     -- [1]
    description                 text        NOT NULL,                     -- [1]
    purpose                     text,                                     -- [0..1]
    responsible_roles           jsonb,                                    -- [0..*]
    protocols                   jsonb,                                    -- [0..*] protocol[]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.cd_components IS 'component-definition.components[]（defined-component）：可重用元件。required=[description, title, type, uuid]。control-implementations -> cd_control_implementations。';
COMMENT ON COLUMN oscal.cd_components.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_components.component_definition_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_components.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_components.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.cd_components.type IS '[必填] 元件類別';
COMMENT ON COLUMN oscal.cd_components.title IS '[必填]';
COMMENT ON COLUMN oscal.cd_components.description IS '[必填]';
COMMENT ON COLUMN oscal.cd_components.purpose IS '[選填]';
COMMENT ON COLUMN oscal.cd_components.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.cd_components.protocols IS '[選填] protocol[]';
COMMENT ON COLUMN oscal.cd_components.props IS '[選填]';
COMMENT ON COLUMN oscal.cd_components.links IS '[選填]';
COMMENT ON COLUMN oscal.cd_components.remarks IS '[選填]';
COMMENT ON COLUMN oscal.cd_components.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.cd_components.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.cd_components.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.cd_components.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- E2. cd_capabilities（capability）  required=[description, name, uuid]
CREATE TABLE oscal.cd_capabilities (
    id                          bigserial   PRIMARY KEY,
    component_definition_id     bigint      NOT NULL REFERENCES oscal.component_definitions(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    name                        text        NOT NULL,                     -- [1] capability 名稱
    description                 text        NOT NULL,                     -- [1]
    incorporates_components     jsonb,                                    -- [0..*] [{component-uuid, description}]（軟式參照 cd_components）
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.cd_capabilities IS 'component-definition.capabilities[]（capability）：由元件組成的能力。required=[description, name, uuid]。incorporates-components 留 JSONB（軟式參照 cd_components）。';
COMMENT ON COLUMN oscal.cd_capabilities.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_capabilities.component_definition_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_capabilities.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_capabilities.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.cd_capabilities.name IS '[必填] capability 名稱';
COMMENT ON COLUMN oscal.cd_capabilities.description IS '[必填]';
COMMENT ON COLUMN oscal.cd_capabilities.incorporates_components IS '[選填] [{component-uuid, description}]（軟式參照 cd_components）';
COMMENT ON COLUMN oscal.cd_capabilities.props IS '[選填]';
COMMENT ON COLUMN oscal.cd_capabilities.links IS '[選填]';
COMMENT ON COLUMN oscal.cd_capabilities.remarks IS '[選填]';
COMMENT ON COLUMN oscal.cd_capabilities.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.cd_capabilities.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.cd_capabilities.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.cd_capabilities.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- E3. cd_control_implementations（control-impl；父=component 或 capability）  required=[description, implemented-requirements, source, uuid]
CREATE TABLE oscal.cd_control_implementations (
    id                          bigserial   PRIMARY KEY,
    cd_component_id             bigint      REFERENCES oscal.cd_components(id) ON DELETE CASCADE,    -- 父：擇一
    cd_capability_id            bigint      REFERENCES oscal.cd_capabilities(id) ON DELETE CASCADE,  -- 父：擇一
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    source                      text        NOT NULL,                     -- [1] source：指向 catalog/profile 的 href（跨文件，保留 text）
    description                 text        NOT NULL,                     -- [1]
    set_parameters              jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.cd_control_implementations IS 'component/capability.control-implementations[]：宣告該元件/能力實作哪個來源(source)的控制項集合。required=[description, implemented-requirements, source, uuid]。source 為跨文件 href（保留 text）。父為 component 或 capability（兩 FK 擇一）。';
COMMENT ON COLUMN oscal.cd_control_implementations.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_control_implementations.cd_component_id IS '父：擇一';
COMMENT ON COLUMN oscal.cd_control_implementations.cd_capability_id IS '父：擇一';
COMMENT ON COLUMN oscal.cd_control_implementations.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_control_implementations.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.cd_control_implementations.source IS '[必填] source：指向 catalog/profile 的 href（跨文件，保留 text）';
COMMENT ON COLUMN oscal.cd_control_implementations.description IS '[必填]';
COMMENT ON COLUMN oscal.cd_control_implementations.set_parameters IS '[選填]';
COMMENT ON COLUMN oscal.cd_control_implementations.props IS '[選填]';
COMMENT ON COLUMN oscal.cd_control_implementations.links IS '[選填]';
COMMENT ON COLUMN oscal.cd_control_implementations.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.cd_control_implementations.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.cd_control_implementations.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.cd_control_implementations.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- E4. cd_implemented_requirements（implemented-requirement）  required=[control-id, description, uuid]
CREATE TABLE oscal.cd_implemented_requirements (
    id                          bigserial   PRIMARY KEY,
    cd_control_implementation_id bigint     NOT NULL REFERENCES oscal.cd_control_implementations(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    control_id                  varchar(255) NOT NULL,                    -- [1] OSCAL control token（跨文件，保留 token text）
    description                 text        NOT NULL,                     -- [1]
    set_parameters              jsonb,                                    -- [0..*]
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.cd_implemented_requirements IS 'control-implementation.implemented-requirements[]（component-def 版）。required=[control-id, description, uuid]。statements -> cd_statements。';
COMMENT ON COLUMN oscal.cd_implemented_requirements.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_implemented_requirements.cd_control_implementation_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_implemented_requirements.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_implemented_requirements.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.cd_implemented_requirements.control_id IS '[必填] OSCAL control token（跨文件，保留 token text）';
COMMENT ON COLUMN oscal.cd_implemented_requirements.description IS '[必填]';
COMMENT ON COLUMN oscal.cd_implemented_requirements.set_parameters IS '[選填]';
COMMENT ON COLUMN oscal.cd_implemented_requirements.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.cd_implemented_requirements.props IS '[選填]';
COMMENT ON COLUMN oscal.cd_implemented_requirements.links IS '[選填]';
COMMENT ON COLUMN oscal.cd_implemented_requirements.remarks IS '[選填]';
COMMENT ON COLUMN oscal.cd_implemented_requirements.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.cd_implemented_requirements.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.cd_implemented_requirements.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.cd_implemented_requirements.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- E5. cd_statements（statement）  required=[description, statement-id, uuid]
CREATE TABLE oscal.cd_statements (
    id                          bigserial   PRIMARY KEY,
    cd_implemented_requirement_id bigint    NOT NULL REFERENCES oscal.cd_implemented_requirements(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    statement_id                varchar(255) NOT NULL,                    -- [1] OSCAL statement token（跨文件）
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    description                 text        NOT NULL,                     -- [1]
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.cd_statements IS 'implemented-requirement.statements[]（component-def 版）。required=[description, statement-id, uuid]。';
COMMENT ON COLUMN oscal.cd_statements.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_statements.cd_implemented_requirement_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_statements.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.cd_statements.statement_id IS '[必填] OSCAL statement token（跨文件）';
COMMENT ON COLUMN oscal.cd_statements.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.cd_statements.description IS '[必填]';
COMMENT ON COLUMN oscal.cd_statements.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.cd_statements.props IS '[選填]';
COMMENT ON COLUMN oscal.cd_statements.links IS '[選填]';
COMMENT ON COLUMN oscal.cd_statements.remarks IS '[選填]';
COMMENT ON COLUMN oscal.cd_statements.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.cd_statements.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.cd_statements.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.cd_statements.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # SECTION F -- Assessment Plan 子樹（reviewed-controls / tasks / local-objectives / activities）
-- #############################################################################

-- F1. ap_reviewed_controls（1:1；required reviewed-controls）  required=[control-selections]
CREATE TABLE oscal.ap_reviewed_controls (
    id                          bigserial   PRIMARY KEY,
    assessment_plan_id          bigint      NOT NULL UNIQUE REFERENCES oscal.assessment_plans(id) ON DELETE CASCADE,  -- 1:1
    description                 text,                                     -- [0..1]
    control_selections          jsonb       NOT NULL,                     -- [1..*] select-control-by-id[]（include/exclude/matching），JSONB
    control_objective_selections jsonb,                                   -- [0..*] 評估目標選取，JSONB
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ap_reviewed_controls IS 'assessment-plan.reviewed-controls（1:1，AP root required）：本次評估涵蓋哪些控制項/目標。required=[control-selections]。control-selections/objective-selections 為選取規則，留 JSONB。';
COMMENT ON COLUMN oscal.ap_reviewed_controls.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_reviewed_controls.assessment_plan_id IS '1:1';
COMMENT ON COLUMN oscal.ap_reviewed_controls.description IS '[選填]';
COMMENT ON COLUMN oscal.ap_reviewed_controls.control_selections IS '[必填] select-control-by-id[]（include/exclude/matching），JSONB';
COMMENT ON COLUMN oscal.ap_reviewed_controls.control_objective_selections IS '[選填] 評估目標選取，JSONB';
COMMENT ON COLUMN oscal.ap_reviewed_controls.props IS '[選填]';
COMMENT ON COLUMN oscal.ap_reviewed_controls.links IS '[選填]';
COMMENT ON COLUMN oscal.ap_reviewed_controls.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ap_reviewed_controls.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ap_reviewed_controls.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ap_reviewed_controls.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ap_reviewed_controls.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- F2. ap_local_objectives（local-definitions.objectives-and-methods -> local-objective）  required=[control-id, parts]
CREATE TABLE oscal.ap_local_objectives (
    id                          bigserial   PRIMARY KEY,
    assessment_plan_id          bigint      NOT NULL REFERENCES oscal.assessment_plans(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    control_id                  varchar(255) NOT NULL,                    -- [1] 指向控制項 token（跨文件）
    description                 text,                                     -- [0..1]
    parts                       jsonb       NOT NULL,                     -- [1..*] 本地定義的評估目標 parts（control-common part），JSONB
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ap_local_objectives IS 'AP local-definitions.objectives-and-methods（local-objective）：在 AP 內就地定義/覆寫某控制項的評估目標。required=[control-id, parts]。parts 為 control-common part（評估目標主體），JSONB。';
COMMENT ON COLUMN oscal.ap_local_objectives.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_local_objectives.assessment_plan_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_local_objectives.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_local_objectives.control_id IS '[必填] 指向控制項 token（跨文件）';
COMMENT ON COLUMN oscal.ap_local_objectives.description IS '[選填]';
COMMENT ON COLUMN oscal.ap_local_objectives.parts IS '[必填] 本地定義的評估目標 parts（control-common part），JSONB';
COMMENT ON COLUMN oscal.ap_local_objectives.props IS '[選填]';
COMMENT ON COLUMN oscal.ap_local_objectives.links IS '[選填]';
COMMENT ON COLUMN oscal.ap_local_objectives.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ap_local_objectives.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ap_local_objectives.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ap_local_objectives.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ap_local_objectives.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- F3. ap_assessment_activities（local-definitions.activities -> activity）  required=[description, uuid]
CREATE TABLE oscal.ap_assessment_activities (
    id                          bigserial   PRIMARY KEY,
    assessment_plan_id          bigint      NOT NULL REFERENCES oscal.assessment_plans(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    title                       text,                                     -- [0..1]
    description                 text        NOT NULL,                     -- [1]
    steps                       jsonb,                                    -- [0..*] 評估步驟，JSONB
    related_controls            jsonb,                                    -- [0..1] reviewed-controls 子集，JSONB
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ap_assessment_activities IS 'AP local-definitions.activities（activity）：可重用的評估活動。required=[description, uuid]。steps/related-controls 留 JSONB。';
COMMENT ON COLUMN oscal.ap_assessment_activities.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_assessment_activities.assessment_plan_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_assessment_activities.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_assessment_activities.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ap_assessment_activities.title IS '[選填]';
COMMENT ON COLUMN oscal.ap_assessment_activities.description IS '[必填]';
COMMENT ON COLUMN oscal.ap_assessment_activities.steps IS '[選填] 評估步驟，JSONB';
COMMENT ON COLUMN oscal.ap_assessment_activities.related_controls IS '[選填] reviewed-controls 子集，JSONB';
COMMENT ON COLUMN oscal.ap_assessment_activities.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.ap_assessment_activities.props IS '[選填]';
COMMENT ON COLUMN oscal.ap_assessment_activities.links IS '[選填]';
COMMENT ON COLUMN oscal.ap_assessment_activities.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ap_assessment_activities.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ap_assessment_activities.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ap_assessment_activities.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ap_assessment_activities.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- F4. ap_tasks（task；自巢狀）  required=[title, type, uuid]
CREATE TABLE oscal.ap_tasks (
    id                          bigserial   PRIMARY KEY,
    assessment_plan_id          bigint      NOT NULL REFERENCES oscal.assessment_plans(id) ON DELETE CASCADE,
    parent_id                   bigint      REFERENCES oscal.ap_tasks(id) ON DELETE CASCADE,         -- self-FK；NULL=頂層 task
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    type                        varchar(40) NOT NULL,                     -- [1] task.type：milestone / action
    title                       text        NOT NULL,                     -- [1]
    description                 text,                                     -- [0..1]
    timing                      jsonb,                                    -- [0..1] 時程（on-date/within-period），JSONB
    dependencies                jsonb,                                    -- [0..*] 相依 task，JSONB
    associated_activities       jsonb,                                    -- [0..*] 關聯 activity，JSONB
    subjects                    jsonb,                                    -- [0..*] 評估對象，JSONB
    responsible_roles           jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ap_tasks IS 'assessment-plan.tasks[]（task；可自巢狀）：評估工作項。required=[title, type, uuid]，type=milestone/action。timing/dependencies/activities/subjects 留 JSONB。';
COMMENT ON COLUMN oscal.ap_tasks.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_tasks.assessment_plan_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_tasks.parent_id IS 'self-FK；NULL=頂層 task';
COMMENT ON COLUMN oscal.ap_tasks.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ap_tasks.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ap_tasks.type IS '[必填] task.type：milestone / action';
COMMENT ON COLUMN oscal.ap_tasks.title IS '[必填]';
COMMENT ON COLUMN oscal.ap_tasks.description IS '[選填]';
COMMENT ON COLUMN oscal.ap_tasks.timing IS '[選填] 時程（on-date/within-period），JSONB';
COMMENT ON COLUMN oscal.ap_tasks.dependencies IS '[選填] 相依 task，JSONB';
COMMENT ON COLUMN oscal.ap_tasks.associated_activities IS '[選填] 關聯 activity，JSONB';
COMMENT ON COLUMN oscal.ap_tasks.subjects IS '[選填] 評估對象，JSONB';
COMMENT ON COLUMN oscal.ap_tasks.responsible_roles IS '[選填]';
COMMENT ON COLUMN oscal.ap_tasks.props IS '[選填]';
COMMENT ON COLUMN oscal.ap_tasks.links IS '[選填]';
COMMENT ON COLUMN oscal.ap_tasks.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ap_tasks.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ap_tasks.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ap_tasks.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ap_tasks.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- 註：AP 的 terms-and-conditions / assessment-subjects / assessment-assets /
--     local-definitions(components/inventory/users) 為宣告式/罕用，留待需要時關聯化（本刀未開）。


-- #############################################################################
-- # SECTION G -- Assessment Results 子樹（results）+ assessment-common（obs/risk/finding）
-- #############################################################################

-- G1. ar_results（result）  required=[description, reviewed-controls, start, title, uuid]
CREATE TABLE oscal.ar_results (
    id                          bigserial   PRIMARY KEY,
    assessment_result_id        bigint      NOT NULL REFERENCES oscal.assessment_results(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    title                       text        NOT NULL,                     -- [1]
    description                 text        NOT NULL,                     -- [1]
    start                       timestamptz NOT NULL,                     -- [1] 評估起
    "end"                       timestamptz,                              -- [0..1] 評估迄
    reviewed_controls           jsonb       NOT NULL,                     -- [1] 本結果涵蓋的控制項選取，JSONB
    attestations                jsonb,                                    -- [0..*] 聲明，JSONB
    assessment_log              jsonb,                                    -- [0..1] 評估歷程，JSONB
    local_definitions           jsonb,                                    -- [0..1] 本結果本地定義，JSONB
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.ar_results IS 'assessment-results.results[]（result）：一次評估的結果集。required=[description, reviewed-controls, start, title, uuid]。observations/risks/findings -> 共用表 assessment_*（owner=ar_result）。reviewed-controls/attestations/assessment-log/local-definitions 留 JSONB。';
COMMENT ON COLUMN oscal.ar_results.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.ar_results.assessment_result_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.ar_results.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.ar_results.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.ar_results.title IS '[必填]';
COMMENT ON COLUMN oscal.ar_results.description IS '[必填]';
COMMENT ON COLUMN oscal.ar_results.start IS '[必填] 評估起';
COMMENT ON COLUMN oscal.ar_results."end" IS '[選填] 評估迄';
COMMENT ON COLUMN oscal.ar_results.reviewed_controls IS '[必填] 本結果涵蓋的控制項選取，JSONB';
COMMENT ON COLUMN oscal.ar_results.attestations IS '[選填] 聲明，JSONB';
COMMENT ON COLUMN oscal.ar_results.assessment_log IS '[選填] 評估歷程，JSONB';
COMMENT ON COLUMN oscal.ar_results.local_definitions IS '[選填] 本結果本地定義，JSONB';
COMMENT ON COLUMN oscal.ar_results.props IS '[選填]';
COMMENT ON COLUMN oscal.ar_results.links IS '[選填]';
COMMENT ON COLUMN oscal.ar_results.remarks IS '[選填]';
COMMENT ON COLUMN oscal.ar_results.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.ar_results.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.ar_results.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.ar_results.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # SECTION H -- POA&M 子樹（poam-items）
-- #############################################################################

-- H1. poam_items（poam-item）  required=[description, title]（uuid 在 OSCAL 為選填）
CREATE TABLE oscal.poam_items (
    id                          bigserial   PRIMARY KEY,
    poam_id                     bigint      NOT NULL REFERENCES oscal.poams(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid,                                     -- [0..1] 自身身分（POA&M 的 poam-item.uuid 選填）
    title                       text        NOT NULL,                     -- [1]
    description                 text        NOT NULL,                     -- [1]
    origins                     jsonb,                                    -- [0..*] 來源 actor，JSONB
    related_findings            jsonb,                                    -- [0..*] 關聯 finding（finding-uuid），JSONB
    related_observations        jsonb,                                    -- [0..*] 關聯 observation，JSONB
    related_risks               jsonb,                                    -- [0..*] 關聯 risk，JSONB
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.poam_items IS 'plan-of-action-and-milestones.poam-items[]（poam-item）：矯正項目。required=[description, title]（uuid 選填）。related-findings/observations/risks 留 JSONB。POA&M 的 observations/risks/findings -> 共用表 assessment_*（owner=poam）。';
COMMENT ON COLUMN oscal.poam_items.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.poam_items.poam_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.poam_items.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.poam_items.uuid IS '[選填] 自身身分（POA&M 的 poam-item.uuid 選填）';
COMMENT ON COLUMN oscal.poam_items.title IS '[必填]';
COMMENT ON COLUMN oscal.poam_items.description IS '[必填]';
COMMENT ON COLUMN oscal.poam_items.origins IS '[選填] 來源 actor，JSONB';
COMMENT ON COLUMN oscal.poam_items.related_findings IS '[選填] 關聯 finding（finding-uuid），JSONB';
COMMENT ON COLUMN oscal.poam_items.related_observations IS '[選填] 關聯 observation，JSONB';
COMMENT ON COLUMN oscal.poam_items.related_risks IS '[選填] 關聯 risk，JSONB';
COMMENT ON COLUMN oscal.poam_items.props IS '[選填]';
COMMENT ON COLUMN oscal.poam_items.links IS '[選填]';
COMMENT ON COLUMN oscal.poam_items.remarks IS '[選填]';
COMMENT ON COLUMN oscal.poam_items.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.poam_items.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.poam_items.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.poam_items.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # SECTION G2 -- assessment-common 共用（observation / risk / finding）
-- #   AR 與 POA&M 共用同一套；owner 用兩個 nullable FK（ar_result 或 poam，擇一）。
-- #############################################################################

-- G2.1 assessment_observations（observation）  required=[collected, description, methods, uuid]
CREATE TABLE oscal.assessment_observations (
    id                          bigserial   PRIMARY KEY,
    ar_result_id                bigint      REFERENCES oscal.ar_results(id) ON DELETE CASCADE,  -- owner：擇一
    poam_id                     bigint      REFERENCES oscal.poams(id) ON DELETE CASCADE,       -- owner：擇一
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    title                       text,                                     -- [0..1]
    description                 text        NOT NULL,                     -- [1]
    methods                     jsonb       NOT NULL,                     -- [1..*] 觀察方法（EXAMINE/INTERVIEW/TEST），JSONB
    types                       jsonb,                                    -- [0..*]
    origins                     jsonb,                                    -- [0..*]
    subjects                    jsonb,                                    -- [0..*]
    relevant_evidence           jsonb,                                    -- [0..*] 佐證（href/description），JSONB
    collected                   timestamptz NOT NULL,                     -- [1] 蒐集時間
    expires                     timestamptz,                              -- [0..1]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.assessment_observations IS 'assessment-common observation（AR result / POA&M 共用）。required=[collected, description, methods, uuid]。owner=ar_result_id 或 poam_id（擇一）。methods/origins/subjects/relevant-evidence 留 JSONB。';
COMMENT ON COLUMN oscal.assessment_observations.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.assessment_observations.ar_result_id IS 'owner：擇一';
COMMENT ON COLUMN oscal.assessment_observations.poam_id IS 'owner：擇一';
COMMENT ON COLUMN oscal.assessment_observations.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.assessment_observations.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.assessment_observations.title IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.description IS '[必填]';
COMMENT ON COLUMN oscal.assessment_observations.methods IS '[必填] 觀察方法（EXAMINE/INTERVIEW/TEST），JSONB';
COMMENT ON COLUMN oscal.assessment_observations.types IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.origins IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.subjects IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.relevant_evidence IS '[選填] 佐證（href/description），JSONB';
COMMENT ON COLUMN oscal.assessment_observations.collected IS '[必填] 蒐集時間';
COMMENT ON COLUMN oscal.assessment_observations.expires IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.props IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.links IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.remarks IS '[選填]';
COMMENT ON COLUMN oscal.assessment_observations.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.assessment_observations.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.assessment_observations.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.assessment_observations.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- G2.2 assessment_risks（risk）  required=[description, statement, status, title, uuid]
CREATE TABLE oscal.assessment_risks (
    id                          bigserial   PRIMARY KEY,
    ar_result_id                bigint      REFERENCES oscal.ar_results(id) ON DELETE CASCADE,  -- owner：擇一
    poam_id                     bigint      REFERENCES oscal.poams(id) ON DELETE CASCADE,       -- owner：擇一
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    title                       text        NOT NULL,                     -- [1]
    description                 text        NOT NULL,                     -- [1]
    statement                   text        NOT NULL,                     -- [1] 風險陳述
    status                      varchar(40) NOT NULL,                     -- [1] risk status（open/investigating/remediating/deviation-*/closed）
    origins                     jsonb,                                    -- [0..*]
    threat_ids                  jsonb,                                    -- [0..*]
    characterizations           jsonb,                                    -- [0..*] 特徵（facets），JSONB
    mitigating_factors          jsonb,                                    -- [0..*]
    deadline                    timestamptz,                              -- [0..1]
    remediations                jsonb,                                    -- [0..*] 緩解/回應（response），JSONB
    risk_log                    jsonb,                                    -- [0..1] 風險歷程，JSONB
    related_observations        jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.assessment_risks IS 'assessment-common risk（AR result / POA&M 共用）。required=[description, statement, status, title, uuid]。owner=ar_result_id 或 poam_id（擇一）。characterizations/mitigating-factors/remediations/risk-log/threat-ids 留 JSONB。';
COMMENT ON COLUMN oscal.assessment_risks.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.assessment_risks.ar_result_id IS 'owner：擇一';
COMMENT ON COLUMN oscal.assessment_risks.poam_id IS 'owner：擇一';
COMMENT ON COLUMN oscal.assessment_risks.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.assessment_risks.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.assessment_risks.title IS '[必填]';
COMMENT ON COLUMN oscal.assessment_risks.description IS '[必填]';
COMMENT ON COLUMN oscal.assessment_risks.statement IS '[必填] 風險陳述';
COMMENT ON COLUMN oscal.assessment_risks.status IS '[必填] risk status（open/investigating/remediating/deviation-*/closed）';
COMMENT ON COLUMN oscal.assessment_risks.origins IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.threat_ids IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.characterizations IS '[選填] 特徵（facets），JSONB';
COMMENT ON COLUMN oscal.assessment_risks.mitigating_factors IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.deadline IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.remediations IS '[選填] 緩解/回應（response），JSONB';
COMMENT ON COLUMN oscal.assessment_risks.risk_log IS '[選填] 風險歷程，JSONB';
COMMENT ON COLUMN oscal.assessment_risks.related_observations IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.props IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.links IS '[選填]';
COMMENT ON COLUMN oscal.assessment_risks.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.assessment_risks.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.assessment_risks.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.assessment_risks.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- G2.3 assessment_findings（finding）  required=[description, target, title, uuid]；target 折成欄
CREATE TABLE oscal.assessment_findings (
    id                          bigserial   PRIMARY KEY,
    ar_result_id                bigint      REFERENCES oscal.ar_results(id) ON DELETE CASCADE,  -- owner：擇一
    poam_id                     bigint      REFERENCES oscal.poams(id) ON DELETE CASCADE,       -- owner：擇一
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    title                       text        NOT NULL,                     -- [1]
    description                 text        NOT NULL,                     -- [1]
    target_type                 varchar(60) NOT NULL,                     -- [1] finding-target.type（statement-id/objective-id）
    target_id                   varchar(255) NOT NULL,                    -- [1] finding-target.target-id
    target_status_state         varchar(40) NOT NULL,                     -- [1] finding-target.status.state（satisfied/not-satisfied）
    target_status               jsonb,                                    -- [0..1] finding-target 其餘（status reason / implementation-status / props），JSONB
    origins                     jsonb,                                    -- [0..*]
    related_observations        jsonb,                                    -- [0..*]
    related_risks               jsonb,                                    -- [0..*]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.assessment_findings IS 'assessment-common finding（AR result / POA&M 共用）。required=[description, target, title, uuid]；finding-target(required type/target-id/status) 折成 target_* 欄，其餘 JSONB。owner=ar_result_id 或 poam_id（擇一）。';
COMMENT ON COLUMN oscal.assessment_findings.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.assessment_findings.ar_result_id IS 'owner：擇一';
COMMENT ON COLUMN oscal.assessment_findings.poam_id IS 'owner：擇一';
COMMENT ON COLUMN oscal.assessment_findings.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.assessment_findings.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.assessment_findings.title IS '[必填]';
COMMENT ON COLUMN oscal.assessment_findings.description IS '[必填]';
COMMENT ON COLUMN oscal.assessment_findings.target_type IS '[必填] finding-target.type（statement-id/objective-id）';
COMMENT ON COLUMN oscal.assessment_findings.target_id IS '[必填] finding-target.target-id';
COMMENT ON COLUMN oscal.assessment_findings.target_status_state IS '[必填] finding-target.status.state（satisfied/not-satisfied）';
COMMENT ON COLUMN oscal.assessment_findings.target_status IS '[選填] finding-target 其餘（status reason / implementation-status / props），JSONB';
COMMENT ON COLUMN oscal.assessment_findings.origins IS '[選填]';
COMMENT ON COLUMN oscal.assessment_findings.related_observations IS '[選填]';
COMMENT ON COLUMN oscal.assessment_findings.related_risks IS '[選填]';
COMMENT ON COLUMN oscal.assessment_findings.props IS '[選填]';
COMMENT ON COLUMN oscal.assessment_findings.links IS '[選填]';
COMMENT ON COLUMN oscal.assessment_findings.remarks IS '[選填]';
COMMENT ON COLUMN oscal.assessment_findings.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.assessment_findings.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.assessment_findings.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.assessment_findings.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # SECTION I -- Control Mapping 子樹（mappings / maps）
-- #############################################################################

-- I1. mappings（mapping）  required=[maps, source-resource, target-resource, uuid]
CREATE TABLE oscal.mappings (
    id                          bigserial   PRIMARY KEY,
    mapping_collection_id       bigint      NOT NULL REFERENCES oscal.mapping_collections(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    source_resource_href        text        NOT NULL,                     -- [1] source-resource.href（來源框架，跨文件 href）
    source_resource_type        varchar(40),                              -- source-resource.type
    target_resource_href        text        NOT NULL,                     -- [1] target-resource.href（目標框架）
    target_resource_type        varchar(40),                              -- target-resource.type
    method                      varchar(40),                              -- [0..1]
    status                      varchar(40),                              -- [0..1]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.mappings IS 'mapping-collection.mappings[]（mapping）：一組來源框架↔目標框架的對應。required=[maps, source-resource, target-resource, uuid]。source/target-resource 為跨文件 href（保留 text）。maps -> mapping_maps。';
COMMENT ON COLUMN oscal.mappings.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.mappings.mapping_collection_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.mappings.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.mappings.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.mappings.source_resource_href IS '[必填] source-resource.href（來源框架，跨文件 href）';
COMMENT ON COLUMN oscal.mappings.source_resource_type IS 'source-resource.type';
COMMENT ON COLUMN oscal.mappings.target_resource_href IS '[必填] target-resource.href（目標框架）';
COMMENT ON COLUMN oscal.mappings.target_resource_type IS 'target-resource.type';
COMMENT ON COLUMN oscal.mappings.method IS '[選填]';
COMMENT ON COLUMN oscal.mappings.status IS '[選填]';
COMMENT ON COLUMN oscal.mappings.props IS '[選填]';
COMMENT ON COLUMN oscal.mappings.links IS '[選填]';
COMMENT ON COLUMN oscal.mappings.remarks IS '[選填]';
COMMENT ON COLUMN oscal.mappings.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.mappings.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.mappings.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.mappings.updated_user IS 'Audit（系統欄）：最後更新者 login_name';

-- I2. mapping_maps（map）  required=[relationship, sources, targets, uuid]
CREATE TABLE oscal.mapping_maps (
    id                          bigserial   PRIMARY KEY,
    mapping_id                  bigint      NOT NULL REFERENCES oscal.mappings(id) ON DELETE CASCADE,
    sort_order                  int         NOT NULL DEFAULT 0,
    uuid                        uuid        NOT NULL,                     -- [1] 自身身分
    relationship                varchar(60) NOT NULL,                     -- [1] equivalent-to/subset-of/superset-of/intersects-with...
    sources                     jsonb       NOT NULL,                     -- [1..*] mapping-item[]：{type, id-ref}（跨框架控制項參照），JSONB
    targets                     jsonb       NOT NULL,                     -- [1..*] 同上（目標側）
    matching_rationale          text,                                     -- [0..1]
    qualifiers                  jsonb,                                    -- [0..*]
    confidence_score            jsonb,                                    -- [0..1]
    coverage                    jsonb,                                    -- [0..1]
    props                       jsonb,                                    -- [0..*]
    links                       jsonb,                                    -- [0..*]
    remarks                     text,                                     -- [0..1]
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    created_user                varchar(255),
    updated_user                varchar(255)
);
COMMENT ON TABLE  oscal.mapping_maps IS 'mapping.maps[]（map）：單筆來源↔目標控制項對應。required=[relationship, sources, targets, uuid]。sources/targets 為 mapping-item（type+id-ref，跨框架參照），JSONB。';
COMMENT ON COLUMN oscal.mapping_maps.id IS '系統 PK（serial，非 OSCAL）';
COMMENT ON COLUMN oscal.mapping_maps.mapping_id IS 'containment FK（結構必填，非 OSCAL）';
COMMENT ON COLUMN oscal.mapping_maps.sort_order IS '陣列保序欄（系統，非 OSCAL）';
COMMENT ON COLUMN oscal.mapping_maps.uuid IS '[必填] 自身身分';
COMMENT ON COLUMN oscal.mapping_maps.relationship IS '[必填] equivalent-to/subset-of/superset-of/intersects-with...';
COMMENT ON COLUMN oscal.mapping_maps.sources IS '[必填] mapping-item[]：{type, id-ref}（跨框架控制項參照），JSONB';
COMMENT ON COLUMN oscal.mapping_maps.targets IS '[必填] 同上（目標側）';
COMMENT ON COLUMN oscal.mapping_maps.matching_rationale IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.qualifiers IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.confidence_score IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.coverage IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.props IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.links IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.remarks IS '[選填]';
COMMENT ON COLUMN oscal.mapping_maps.created_at IS 'Audit（系統欄）：建立時間';
COMMENT ON COLUMN oscal.mapping_maps.updated_at IS 'Audit（系統欄）：最後更新時間';
COMMENT ON COLUMN oscal.mapping_maps.created_user IS 'Audit（系統欄）：建立者 login_name';
COMMENT ON COLUMN oscal.mapping_maps.updated_user IS 'Audit（系統欄）：最後更新者 login_name';


-- #############################################################################
-- # 索引（SECTION E~I）
-- #############################################################################
CREATE INDEX idx_cd_components_cd           ON oscal.cd_components (component_definition_id);
CREATE INDEX idx_cd_capabilities_cd         ON oscal.cd_capabilities (component_definition_id);
CREATE INDEX idx_cd_ctrlimpl_component      ON oscal.cd_control_implementations (cd_component_id);
CREATE INDEX idx_cd_ctrlimpl_capability     ON oscal.cd_control_implementations (cd_capability_id);
CREATE INDEX idx_cd_impreq_ctrlimpl         ON oscal.cd_implemented_requirements (cd_control_implementation_id);
CREATE INDEX idx_cd_stmt_impreq             ON oscal.cd_statements (cd_implemented_requirement_id);
CREATE INDEX idx_ap_reviewed_ap             ON oscal.ap_reviewed_controls (assessment_plan_id);
CREATE INDEX idx_ap_localobj_ap             ON oscal.ap_local_objectives (assessment_plan_id);
CREATE INDEX idx_ap_activities_ap           ON oscal.ap_assessment_activities (assessment_plan_id);
CREATE INDEX idx_ap_tasks_ap                ON oscal.ap_tasks (assessment_plan_id);
CREATE INDEX idx_ap_tasks_parent            ON oscal.ap_tasks (parent_id);
CREATE INDEX idx_ar_results_ar              ON oscal.ar_results (assessment_result_id);
CREATE INDEX idx_poam_items_poam            ON oscal.poam_items (poam_id);
CREATE INDEX idx_obs_arresult               ON oscal.assessment_observations (ar_result_id);
CREATE INDEX idx_obs_poam                   ON oscal.assessment_observations (poam_id);
CREATE INDEX idx_risk_arresult              ON oscal.assessment_risks (ar_result_id);
CREATE INDEX idx_risk_poam                  ON oscal.assessment_risks (poam_id);
CREATE INDEX idx_finding_arresult           ON oscal.assessment_findings (ar_result_id);
CREATE INDEX idx_finding_poam               ON oscal.assessment_findings (poam_id);
CREATE INDEX idx_mappings_collection        ON oscal.mappings (mapping_collection_id);
CREATE INDEX idx_mapping_maps_mapping       ON oscal.mapping_maps (mapping_id);



-- =============================================================================
-- 結束。本檔（單一完整檔，dbdiagram.io 一次匯入）= OSCAL v1.2.2 八大 model 全展開，共 45 表：
--   共用核心 4（metadata/role/party/resource）+ Catalog 樹 5 + 8 root + profile_imports 1
--   + SSP 子樹 12 + Component-Def 5（E）+ AP 4（F）+ AR 1（G）+ assessment-common 3（G2）
--   + POA&M 1（H）+ Mapping 2（I）。
-- 必填/選填依官方 v1.2.2 schema required 逐欄校對：NOT NULL ⟺ OSCAL [1]（或系統/結構欄）。
-- 全 45 表、673 欄皆有 COMMENT；深層 leaf 結構依規則 6 留 JSONB（見檔頭）。
-- =============================================================================
