-- =============================================================================
-- FR-038 OSCAL 重設計 — v2 schema DELTA（疊在 oscal-catalog-and-roots-schema-v2.sql 之上）
-- =============================================================================
-- 本檔 = FR-037 的 v2 base schema（42 表）尚未涵蓋、但 FR-038 需求要補的部分：
--   D-1  framework / framework_versions（base 完全沒有；FR-038 §23-24）+ catalogs FK
--   D-2  compliance.project_audit_rounds（輪次 first-class；7 態 + parent_round_id）
--   D-3  AP assessment-subjects 關聯化（D3 定案：本期做 subjects）
--   D-4  finding ↔ risk 多對多關聯表（Q2 定案：稽核員手動組風險）
--   D-5  catalog_controls.source_identifier（D6：跨框架溯源佔位）
--
-- 套用方式（D1 已定：dev 直接 drop oscal 重建）：
--   1) 先跑 base：oscal-catalog-and-roots-schema-v2.sql（建 oscal.* 42 表）
--   2) 再跑本檔（本檔的新表/ALTER）
--   最終發版時兩檔會 merge 成單一 build script。
--
-- 註解語言：DDL code comment 英文；COMMENT ON 中文（沿用 base 慣例）。
-- 必填鐵則沿用 base：NOT NULL ⟺ OSCAL [1] 或 系統/結構欄。
-- 權限：每張新表結尾補 GRANT ... TO cm_app + sequence 權限（CLAUDE.md SQL 鐵則）。
-- =============================================================================


-- #############################################################################
-- # D-1  Framework / Framework Version（控制層母版的「框架」維度）
-- #   FR-038 §23-24：oscal_frameworks→frameworks、oscal_framework_versions→
-- #   framework_versions；§26：catalogs 對應一個 framework_versions。
-- #############################################################################

-- D-1.1 frameworks（框架主檔，非 OSCAL root；產品概念）---------------------------
CREATE TABLE oscal.frameworks (
    id              bigserial    PRIMARY KEY,                              -- 系統 PK
    uid             varchar(36)  NOT NULL UNIQUE,                          -- 對外識別碼（產品層 uid，非 OSCAL uuid）
    code            varchar(64)  NOT NULL UNIQUE,                          -- 框架代碼（如 CMMC_L2 / ISO_27001_2022 / NIST_800_171_R2）
    name            text         NOT NULL,                                 -- 框架顯示名
    authority       varchar(128),                                         -- 發布機構（DoD / NIST / ISO）
    description     text,
    publish_status  varchar(20)  NOT NULL DEFAULT 'draft',                -- draft / published / deprecated
    main_version_id bigint,                                               -- 指向目前主推版本（framework_versions.id；FK 在版本表建後再 ALTER 加）
    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.frameworks IS '合規框架主檔（產品概念，非 OSCAL root）：一個合規框架一筆（CMMC 2.0 / ISO 27001:2022 / NIST 800-171 R2）。框架本身是「規則來源」，底下掛多個版本（framework_versions），每個版本對應一份 catalog。FR-038 §23：由舊 oscal.oscal_frameworks rename / 重建而來。';
COMMENT ON COLUMN oscal.frameworks.uid             IS '產品層對外識別碼（varchar 36；非 OSCAL uuid）';
COMMENT ON COLUMN oscal.frameworks.code            IS '框架代碼，全系統唯一，對應 parser adapter type（CMMC_L2 / ISO_27001_2022 / NIST_800_171_R2 …）';
COMMENT ON COLUMN oscal.frameworks.authority       IS '框架發布機構（DoD / NIST / ISO 等）';
COMMENT ON COLUMN oscal.frameworks.publish_status  IS '產品生命週期 draft / published / deprecated';
COMMENT ON COLUMN oscal.frameworks.main_version_id IS '目前主推 / 最新發布版本 → framework_versions.id（FK 於版本表建立後 ALTER 加上）';

-- D-1.2 framework_versions（框架版本，1 版本 = 1 catalog）------------------------
CREATE TABLE oscal.framework_versions (
    id              bigserial    PRIMARY KEY,                              -- 系統 PK
    uid             varchar(36)  NOT NULL UNIQUE,                          -- 產品層對外識別碼
    framework_id    bigint       NOT NULL REFERENCES oscal.frameworks(id) ON DELETE CASCADE,  -- 所屬框架
    parent_id       bigint       REFERENCES oscal.framework_versions(id),  -- 版本血緣（self-FK；舊版 pid 概念，升級沿用證據用）
    version         varchar(64)  NOT NULL,                                 -- 版本字串（如 "2.0" / "Rev.2" / "2022"）
    catalog_id      bigint       REFERENCES oscal.catalogs(id),            -- 本版本對應的 catalog（1:1；catalog 匯入後回填）
    publish_status  varchar(20)  NOT NULL DEFAULT 'draft',                -- draft / published / deprecated
    published_at    timestamptz,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255),
    UNIQUE (framework_id, version)
);
COMMENT ON TABLE  oscal.framework_versions IS '框架版本（產品概念）：一個框架的某個版本一筆，與一份 oscal.catalogs 1:1 對應（catalog 是該版本控制項全集的 OSCAL 落地）。FR-038 §24/§26：由舊 oscal.oscal_framework_versions rename / 重建；catalog_id 補上 base schema 缺的「catalog→framework_version」綁定。';
COMMENT ON COLUMN oscal.framework_versions.framework_id   IS '所屬框架 → oscal.frameworks.id';
COMMENT ON COLUMN oscal.framework_versions.parent_id      IS '版本血緣 self-FK（舊 pid 概念）；NULL = 首版。供 L1→L2 升級、跨版本證據沿用追溯';
COMMENT ON COLUMN oscal.framework_versions.catalog_id     IS '本版本對應的 catalog（oscal.catalogs.id，1:1）；建立框架版本→匯入 catalog 後回填';
COMMENT ON COLUMN oscal.framework_versions.publish_status IS '產品生命週期；資源庫只能選 published 版本（FR-038 §42）';

-- frameworks.main_version_id 的 FK（版本表已建，補上）
ALTER TABLE oscal.frameworks
    ADD CONSTRAINT fk_frameworks_main_version
    FOREIGN KEY (main_version_id) REFERENCES oscal.framework_versions(id);

-- D-1.3 catalogs 加上「對應哪個 framework_version」（FR-038 §26）------------------
-- base schema 的 oscal.catalogs 沒有指向 framework_version 的欄；補 FK。
ALTER TABLE oscal.catalogs
    ADD COLUMN framework_version_id bigint REFERENCES oscal.framework_versions(id);
COMMENT ON COLUMN oscal.catalogs.framework_version_id IS 'FR-038 §26：本 catalog 對應的框架版本 → oscal.framework_versions.id。【clone 語意已定（Wave 0）】母版 catalog 必填（應用層保證）；資源庫 / 專案 clone 出來的 catalog 副本「沿用同一指向作為 provenance（記錄來源版本，供升級追溯）」，但副本的 control/group/part 皆為獨立 rows、存內容，不 join 回母版（符合 PM 教材「不能只存 id join 回會變動母表」）。即：framework_version_id 是來源指標、非 live 依賴';

CREATE INDEX idx_framework_versions_framework ON oscal.framework_versions (framework_id);
CREATE INDEX idx_framework_versions_catalog   ON oscal.framework_versions (catalog_id);
CREATE INDEX idx_catalogs_framework_version   ON oscal.catalogs (framework_version_id);


-- #############################################################################
-- # D-2  project_audit_rounds（稽核輪次 first-class，取代「AP 當輪次」）
-- #   放 compliance schema（產品業務表，非 OSCAL 文件）。
-- #   Q4 定案：7 態狀態機 + parent_round_id 複驗血緣。
-- #############################################################################

CREATE TABLE compliance.project_audit_rounds (
    id              bigserial    PRIMARY KEY,
    project_id      bigint       NOT NULL,                                 -- → compliance.projects.id（跨 schema soft ref，比照既有慣例不建 FK constraint）
    name            varchar(255) NOT NULL,                                 -- 輪次名稱
    round_no        int          NOT NULL,                                 -- 輪次編號（1,2,3…，project 內遞增）
    round_type      varchar(20)  NOT NULL,                                 -- initial / close-out / surveillance
    status          varchar(20)  NOT NULL DEFAULT 'not_started',           -- 7 態（見下）
    parent_round_id bigint       REFERENCES compliance.project_audit_rounds(id),  -- 複驗母輪；NOT NULL ⟺ round_type=close-out
    assessment_plan_id bigint    REFERENCES oscal.assessment_plans(id),    -- 本輪所屬 engagement 的 AP（initial/surveillance 新建；close-out 沿用母輪 AP）
    ssp_id          bigint       REFERENCES oscal.system_security_plans(id),  -- 凍結 SSP 快照（啟動稽核 snapshot 時回填；NULL=尚未啟動）
    ar_result_id    bigint       REFERENCES oscal.ar_results(id),          -- 本輪對應的 AR result（1 round ↔ 1 ar_results；填判定後回填）
    start_at        timestamptz,
    end_at          timestamptz,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255),
    UNIQUE (project_id, round_no),
    -- 值域與不變式（Q4 鎖定的 7 態 / 3 type / 血緣規則，DB 層強制）：
    CONSTRAINT ck_audit_rounds_round_type
        CHECK (round_type IN ('initial', 'close-out', 'surveillance')),
    CONSTRAINT ck_audit_rounds_status
        CHECK (status IN ('not_started', 'planning', 'audit_planning',
                          'auditing', 'remediation', 'pending_reverify', 'closed')),
    -- parent_round_id NOT NULL ⟺ round_type='close-out'
    CONSTRAINT ck_audit_rounds_parent_iff_closeout
        CHECK ((round_type = 'close-out') = (parent_round_id IS NOT NULL))
);
COMMENT ON TABLE  compliance.project_audit_rounds IS 'FR-038 稽核輪次主檔（產品業務表）：取代舊「用 AP/assessment_result_datas.run_no 兼當輪次」的設計。一個專案多輪；每輪啟動稽核時 snapshot 當前 living SSP 成凍結副本（ssp_id），對應 OSCAL AR 的一筆 result（ar_result_id）。狀態機 7 態，覆核（close-out）開新輪並以 parent_round_id 串回母輪。';
COMMENT ON COLUMN compliance.project_audit_rounds.project_id      IS '→ compliance.projects.id（跨/同 schema soft ref，比照既有慣例不建 FK constraint，避免 CASCADE）';
COMMENT ON COLUMN compliance.project_audit_rounds.round_type      IS 'initial（首次完整稽核）/ close-out（覆核，只查母輪沒過的 AO）/ surveillance（年度新循環，全新 engagement）';
COMMENT ON COLUMN compliance.project_audit_rounds.status          IS '7 態：not_started（尚未開始）/ planning（專案規劃中，受評方編 SSP）/ audit_planning（稽核規劃中，稽核員寫 AP）/ auditing（稽核進行中，填 AO 判定/finding/risk）/ remediation（改善計劃中，做 POA&M）/ pending_reverify（待複驗，受評方整改完待稽核員複驗）/ closed（結案）';
COMMENT ON COLUMN compliance.project_audit_rounds.parent_round_id IS '複驗母輪 self-FK；NOT NULL ⟺ round_type=close-out（initial/surveillance 為 NULL）。覆核範圍從母輪的 not_met findings 推導；母輪結案連動沿此鏈回溯';
COMMENT ON COLUMN compliance.project_audit_rounds.assessment_plan_id IS '本輪所屬 engagement 的 AP → oscal.assessment_plans.id。engagement = initial/surveillance 起一份新 AP，其下 close-out 輪沿用同一 AP（D2 refined：AP/AR per engagement，非 per round）';
COMMENT ON COLUMN compliance.project_audit_rounds.ssp_id          IS '本輪凍結的 SSP 快照 → oscal.system_security_plans.id；PM「啟動稽核」snapshot 當前 living SSP 時回填，此後不可改';
COMMENT ON COLUMN compliance.project_audit_rounds.ar_result_id    IS '本輪對應的 AR result → oscal.ar_results.id（1 round ↔ 1 ar_results）；稽核員開始填判定時建立並回填';

CREATE INDEX idx_audit_rounds_project    ON compliance.project_audit_rounds (project_id);
CREATE INDEX idx_audit_rounds_parent     ON compliance.project_audit_rounds (parent_round_id);
CREATE INDEX idx_audit_rounds_ap         ON compliance.project_audit_rounds (assessment_plan_id);
CREATE INDEX idx_audit_rounds_ssp        ON compliance.project_audit_rounds (ssp_id);
CREATE INDEX idx_audit_rounds_ar_result  ON compliance.project_audit_rounds (ar_result_id);


-- #############################################################################
-- # D-3  AP assessment-subjects 關聯化（D3 定案：本期做 subjects，assets 後續）
-- #   base schema 把 subjects 留在 ap_tasks.subjects JSONB；FR-038 抽查名單是
-- #   AP 核心 UX，需可查詢/勾選 → 關聯化成獨立表。
-- #############################################################################

CREATE TABLE oscal.ap_assessment_subjects (
    id                  bigserial    PRIMARY KEY,
    assessment_plan_id  bigint       NOT NULL REFERENCES oscal.assessment_plans(id) ON DELETE CASCADE,  -- 所屬 AP
    sort_order          int          NOT NULL DEFAULT 0,
    subject_type        varchar(40)  NOT NULL,                             -- component / inventory-item / location / party / user
    subject_uuid        uuid         NOT NULL,                             -- 指向 SSP 快照內對應物件的 uuid（軟參照，跨文件不建 FK）
    include             boolean      NOT NULL DEFAULT true,                -- 抽查 = 只勾其中幾筆（true=納入查核範圍）
    title               text,                                             -- 顯示用快照名（從 SSP 帶入，免每次 join）
    props               jsonb,
    links               jsonb,
    remarks             text,
    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_subjects IS 'AP assessment-subject（FR-038 D3 關聯化）：稽核員那天會實際碰到哪些東西 — 抽哪幾台機器、找哪些人訪談、進哪個機房。從 SSP 快照的資產清單勾選（include=只勾其中幾筆=抽查）。OSCAL 對應 assessment-plan.assessment-subjects / task.subjects；base schema 原以 ap_tasks.subjects JSONB 兜底，本期抽出成可查詢表。assessment-assets（工具/團隊）本期不做（follow-up）。';
COMMENT ON COLUMN oscal.ap_assessment_subjects.subject_type IS 'OSCAL subject type：component / inventory-item / location / party / user';
COMMENT ON COLUMN oscal.ap_assessment_subjects.subject_uuid IS '指向 SSP 快照內對應物件的 uuid（ssp_components.uuid / ssp_inventory_items.uuid / parties.uuid…）；跨文件軟參照不建 FK';
COMMENT ON COLUMN oscal.ap_assessment_subjects.include      IS '是否納入本次查核（抽查 = 只勾其中幾筆）；OSCAL include-subjects/exclude-subjects 語意';
COMMENT ON COLUMN oscal.ap_assessment_subjects.title        IS '顯示用快照名（建立時從 SSP 物件帶入，免每次 join 回去）';

CREATE INDEX idx_ap_subjects_ap   ON oscal.ap_assessment_subjects (assessment_plan_id);
CREATE INDEX idx_ap_subjects_uuid ON oscal.ap_assessment_subjects (subject_uuid);


-- #############################################################################
-- # D-4  finding ↔ risk 多對多關聯（Q2 定案：稽核員手動建 risk、勾選哪幾條
-- #      finding 組成）
-- #   base schema 的 assessment_findings.related_risks / assessment_risks.
-- #   related_observations 是 JSONB（單向、不可 join）。系統風險總結要「一個
-- #   risk ← 多條 finding、一條 finding → 多個 risk」且要查詢，故建關聯表。
-- #############################################################################

CREATE TABLE oscal.assessment_finding_risks (
    id              bigserial    PRIMARY KEY,
    finding_id      bigint       NOT NULL REFERENCES oscal.assessment_findings(id) ON DELETE CASCADE,
    risk_id         bigint       NOT NULL REFERENCES oscal.assessment_risks(id) ON DELETE CASCADE,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    UNIQUE (finding_id, risk_id)
);
COMMENT ON TABLE  oscal.assessment_finding_risks IS 'FR-038 Q2：finding ↔ risk 多對多關聯表。稽核員手動建系統風險（assessment_risks），勾選由哪幾條 not_met finding（assessment_findings）組成；一個 risk 可關聯多條 finding、一條 finding 也可屬多個 risk。風險等級放 assessment_risks（characterizations / props，由稽核員判定）。匯出 OSCAL 時還原成 finding.related-risks / risk.related-* 的 JSONB 表示。';
COMMENT ON COLUMN oscal.assessment_finding_risks.finding_id IS '→ oscal.assessment_findings.id（通常 target_status_state=not-satisfied 的 finding）';
COMMENT ON COLUMN oscal.assessment_finding_risks.risk_id    IS '→ oscal.assessment_risks.id';

CREATE INDEX idx_finding_risks_finding ON oscal.assessment_finding_risks (finding_id);
CREATE INDEX idx_finding_risks_risk    ON oscal.assessment_finding_risks (risk_id);


-- #############################################################################
-- # D-4b  整改計畫（response）+ 里程碑（task）—— OSCAL-faithful 三層
-- #   OSCAL 路徑固定為 risk → remediations[](response) → tasks[](milestone)；
-- #   base 把整層留在 assessment_risks.remediations JSONB。本期因 PM2 要求
-- #   per-milestone assignee 且可查詢/指派，把 response + task 都關聯化，
-- #   匯出 OSCAL 1:1（不需匯出時合成 wrapper、uuid 穩定可被 #uuid 參照）。
-- #############################################################################

-- D-4b.1 assessment_remediations（response：風險回應 / 整改計畫）------------------
CREATE TABLE oscal.assessment_remediations (
    id              bigserial    PRIMARY KEY,
    risk_id         bigint       NOT NULL REFERENCES oscal.assessment_risks(id) ON DELETE CASCADE,  -- 所屬風險（OSCAL: risk.remediations[]）
    sort_order      int          NOT NULL DEFAULT 0,
    uuid            uuid         NOT NULL,                                 -- [1] response.uuid（穩定身分，匯出 / 跨文件 #uuid 參照用）
    lifecycle       varchar(20),                                          -- [0..1] response.lifecycle：recommended / planned / completed
    title           text         NOT NULL,                                 -- [1] response.title（CMMC 通常一筆「整改計畫」，UI 可自動建預設）
    description     text         NOT NULL,                                 -- [1] response.description
    origins         jsonb,                                                -- [0..*] response.origins[]
    required_assets jsonb,                                                -- [0..*] response.required-assets[]
    props           jsonb,
    links           jsonb,
    remarks         text,
    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_remediations IS 'FR-038 B 方案：OSCAL risk.remediations[]（response，風險回應 / 整改計畫）關聯化。OSCAL 規定 milestone(task) 必須掛在 response 下、不能直接掛 risk；base 原以 assessment_risks.remediations JSONB 兜底，本期抽成真表，使 milestone 有穩定父層、匯出 1:1（免合成 wrapper）。CMMC 多為一風險一整改計畫，UI 可在建立風險時自動帶一筆預設 response，使用者只感知「風險 → 里程碑」。';
COMMENT ON COLUMN oscal.assessment_remediations.risk_id   IS '→ oscal.assessment_risks.id（OSCAL risk.remediations[]）';
COMMENT ON COLUMN oscal.assessment_remediations.uuid      IS '[必填] response.uuid：穩定身分，匯出與跨文件 #uuid 參照用';
COMMENT ON COLUMN oscal.assessment_remediations.lifecycle IS '[選填] response.lifecycle：recommended（建議）/ planned（已排定）/ completed（已完成）';
COMMENT ON COLUMN oscal.assessment_remediations.title     IS '[必填] response.title';
COMMENT ON COLUMN oscal.assessment_remediations.description IS '[必填] response.description';
COMMENT ON COLUMN oscal.assessment_remediations.origins   IS '[選填] response.origins[]：來源 actor，JSONB';
COMMENT ON COLUMN oscal.assessment_remediations.required_assets IS '[選填] response.required-assets[]：整改所需資源，JSONB';

-- D-4b.2 poam_milestones（task：里程碑，掛在 response 下）-------------------------
CREATE TABLE oscal.poam_milestones (
    id              bigserial    PRIMARY KEY,
    remediation_id  bigint       NOT NULL REFERENCES oscal.assessment_remediations(id) ON DELETE CASCADE,  -- 所屬整改計畫（OSCAL: response.tasks[]）
    sort_order      int          NOT NULL DEFAULT 0,
    uuid            uuid         NOT NULL,                                 -- [1] task.uuid（自身身分，匯出用）
    task_type       varchar(20)  NOT NULL DEFAULT 'milestone',            -- [1] task.type（milestone / action…，開放 token）
    title           text         NOT NULL,                                 -- [1] 里程碑標題（M1 / M2…）
    description     text,
    assignee_user_id    bigint,                                           -- per-milestone 負責人 user id（app 層 enrich nickname，不存 DB）
    assignee_org_unit_id bigint,                                          -- per-milestone 負責單位（跨部門整改 M1→IT、M2→採購可拆）
    target_date     date,                                                 -- 預計完成日（CMMC 180 天追蹤點）→ 匯出 task.timing
    status          varchar(20)  NOT NULL DEFAULT 'open',                 -- open / in_progress / done
    completed_at    timestamptz,
    props           jsonb,
    links           jsonb,
    remarks         text,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    created_user    varchar(255),
    updated_user    varchar(255),
    CONSTRAINT ck_poam_milestones_status
        CHECK (status IN ('open', 'in_progress', 'done'))
);
COMMENT ON TABLE  oscal.poam_milestones IS 'FR-038 PM2：整改里程碑。OSCAL 對應 response.tasks[]（task，type=milestone）；掛在 assessment_remediations(response) 下而非直接掛 risk（OSCAL-faithful，B 方案）。per-milestone assignee 支援跨部門整改拆分。assignee/org_unit 的 nickname/name 依 CLAUDE.md 審計欄規範在 app service 層 enrich，不存 DB。匯出時 target_date → task.timing.on-date、assignee → task.responsible-roles（role→party）。';
COMMENT ON COLUMN oscal.poam_milestones.remediation_id   IS '→ oscal.assessment_remediations.id（OSCAL response.tasks[]；里程碑的父層是整改計畫，非直接 risk）';
COMMENT ON COLUMN oscal.poam_milestones.uuid             IS '[必填] task.uuid（自身身分，匯出 response.task 用）';
COMMENT ON COLUMN oscal.poam_milestones.task_type        IS '[必填] task.type：milestone（預設）/ action…，開放 token';
COMMENT ON COLUMN oscal.poam_milestones.assignee_user_id IS 'per-milestone 負責人 user id；response 回傳層 enrich assignee_user_name(nickname)/login_name（CLAUDE.md 審計欄規範，不存 DB）；匯出映射 task.responsible-roles→party';
COMMENT ON COLUMN oscal.poam_milestones.assignee_org_unit_id IS 'per-milestone 負責單位 id；回傳層 enrich org_unit_name';
COMMENT ON COLUMN oscal.poam_milestones.target_date      IS '預計完成日（CMMC 整改 180 天追蹤點）；匯出 → task.timing.on-date';
COMMENT ON COLUMN oscal.poam_milestones.status           IS 'open / in_progress / done（里程碑層；POA&M item 結案需所屬里程碑 done）';

CREATE INDEX idx_remediations_risk        ON oscal.assessment_remediations (risk_id);
CREATE INDEX idx_poam_milestones_remed    ON oscal.poam_milestones (remediation_id);
CREATE INDEX idx_poam_milestones_assignee ON oscal.poam_milestones (assignee_user_id);


-- #############################################################################
-- # D-5  catalog_controls.source_identifier（D6：跨框架溯源佔位）
-- #   PM 教材 §08 建議：control 表加原始出處編號（800-171r2:3.13.11 /
-- #   iso27001-2022:A.8.24），支撐 L1→L2 升級證據沿用、跨框架 crosswalk。
-- #   本期 CMMC 先加欄位佔位（D5：ISO 整合為 follow-up）。
-- #############################################################################

ALTER TABLE oscal.catalog_controls
    ADD COLUMN source_identifier varchar(128);
COMMENT ON COLUMN oscal.catalog_controls.source_identifier IS 'FR-038 D6：控制項的原始出處編號（如 800-171r2:3.13.11、iso27001-2022:A.8.24），支撐跨框架 crosswalk 與 L1→L2 升級證據沿用。本期 CMMC 先佔位，ISO 整合（follow-up）時填用';

CREATE INDEX idx_catalog_controls_source ON oscal.catalog_controls (source_identifier);


-- #############################################################################
-- # 權限（CLAUDE.md SQL 鐵則：新表必 GRANT TO cm_app + sequence 權限）
-- #############################################################################
GRANT SELECT, INSERT, UPDATE, DELETE ON
    oscal.frameworks,
    oscal.framework_versions,
    oscal.ap_assessment_subjects,
    oscal.assessment_finding_risks,
    oscal.assessment_remediations,
    oscal.poam_milestones,
    compliance.project_audit_rounds
TO cm_app;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA oscal TO cm_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA compliance TO cm_app;

-- 收尾：INSERT public.schema_migrations（實際套用時補上 version 與日期）。
-- =============================================================================
