C1 — OSCAL SSP 角色 Master Data 化

-- Date: 2026-MM-DD
-- Purpose: C1 — seed OSCAL SSP 9 個角色到 system_menus + oscal.oscal_roles
--          system_menus：UI dropdown 用
--          oscal_roles：OSCAL 文件結構用（B 階段匯出 metadata.roles[]）
-- 帳號：cmmgr（避 RLS）

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- A. seed system_menus (2026-MM-DD) — UI / API dropdown master
-- ─────────────────────────────────────────────────────────────────────────────
-- 前置：確認 UNIQUE(group, key) constraint 已存在；若無，先加 (pre-flight 確認)
-- ALTER TABLE system_menus ADD CONSTRAINT uq_system_menus_group_key UNIQUE ("group", key);

INSERT INTO system_menus ("group", key, value, sort, enable, public) VALUES
('ssp_party_role', 'responsible-organization', 'responsible-organization', 10, 1, 1),
('ssp_party_role', 'system-owner',             'system-owner',             20, 1, 1),
('ssp_party_role', 'system-security-officer',  'system-security-officer',  30, 1, 1),
('ssp_party_role', 'authorizing-official',     'authorizing-official',     40, 1, 1),
('ssp_party_role', 'information-owner',        'information-owner',        50, 1, 1),
('ssp_party_role', 'information-provider',     'information-provider',     60, 1, 1),
('ssp_party_role', 'information-receiver',     'information-receiver',     70, 1, 1),
('ssp_party_role', 'prepared-by',              'prepared-by',              80, 1, 1),
('ssp_party_role', 'prepared-for',             'prepared-for',             90, 1, 1)
ON CONFLICT DO NOTHING;  -- idempotent

-- ─────────────────────────────────────────────────────────────────────────────
-- B. seed oscal.oscal_roles (2026-MM-DD) — OSCAL 文件結構（給 B 階段匯出用）
-- ─────────────────────────────────────────────────────────────────────────────
-- OSCAL spec: metadata.roles[] 必須含每個被引用 role 的 id + title + description
-- 內容必須跟 system_menus 同步（同 9 個 role_id），但描述可詳細些供 OSCAL 文件用

-- idempotent：role_id 既存就跳過
INSERT INTO oscal.oscal_roles (role_id, title, description) VALUES
('responsible-organization', 'Responsible Organization',
 'The organization responsible for the system''s operation at the organizational level.'),
('system-owner',             'System Owner',
 'The individual ultimately responsible for the system''s security and operation, including risk decisions.'),
('system-security-officer',  'System Security Officer',
 'The individual responsible for implementing system security controls and monitoring.'),
('authorizing-official',     'Authorizing Official',
 'The individual with authority to formally accept the system''s operational risks.'),
('information-owner',        'Information Owner',
 'The individual with authority over access, use, and disposition of specific information categories.'),
('information-provider',     'Information Provider',
 'The individual or organization providing information to the system.'),
('information-receiver',     'Information Receiver',
 'The individual or organization receiving output information from the system.'),
('prepared-by',              'Prepared By',
 'The individual responsible for preparing this SSP document.'),
('prepared-for',             'Prepared For',
 'The organization for which this SSP document is primarily prepared.')
ON CONFLICT (role_id) DO NOTHING;  -- idempotent；前提：oscal_roles 已有 UNIQUE(role_id)

-- 注意：若 oscal_roles 無 UNIQUE(role_id) constraint，pre-flight 先加：
--   ALTER TABLE oscal.oscal_roles ADD CONSTRAINT uq_oscal_roles_role_id UNIQUE (role_id);

COMMIT;

-- 驗證
-- SELECT * FROM system_menus WHERE "group" = 'ssp_party_role' ORDER BY sort;
-- → 應看到 9 筆
-- SELECT role_id, title FROM oscal.oscal_roles ORDER BY role_id;
-- → 應看到 9 筆

{
  "oscal_role": {
    "ssp_party_role": {
      "responsible-organization": {
        "label": "專案負責單位",
        "description": "對系統運作負組織級責任的單位"
      },
      "system-owner": {
        "label": "系統所有者",
        "description": "對系統的安全與營運負最終責任，包括風險決策"
      },
      "system-security-officer": {
        "label": "系統安全官 (ISSO)",
        "description": "負責執行系統安全控制與監控"
      },
      "authorizing-official": {
        "label": "授權機關 (AO)",
        "description": "有權正式接受系統運作風險的人員"
      },
      "information-owner": {
        "label": "資料所有者",
        "description": "對特定資料類別的存取、使用與處置擁有權限"
      },
      "information-provider": {
        "label": "資料提供者",
        "description": "提供資料給系統使用的人員或單位"
      },
      "information-receiver": {
        "label": "資料接收者 / 窗口",
        "description": "接收系統輸出資料的人員或單位"
      },
      "prepared-by": {
        "label": "文件撰寫者",
        "description": "負責撰寫此 SSP 文件的人員"
      },
      "prepared-for": {
        "label": "文件對象",
        "description": "此 SSP 文件主要服務的對象單位"
      }
    }
  }
}