C3 — 受評範圍歸屬遷移到 SSP（廢除 project_device_mapping / project_information_systems）

-- Date: 2026-MM-DD
-- Purpose: C3.PR1 — 補洞：把 project_device_mapping / project_information_systems
--          既有資料 backfill 到對應 current SSP 的 ssp_system_implementation_items
-- 帳號：cmmgr（避 RLS）
-- 策略：idempotent — 重複資料 skip

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- Step 1 — 找每個 project 對應的 current SSP（最新 active AP 的 SSP）(2026-MM-DD)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TEMP TABLE _project_current_ssp AS
SELECT
    p.id AS project_id,
    p.uid AS project_uid,
    ssp.id AS ssp_id,
    ssp.uid AS ssp_uid,
    si.id AS system_implementation_id  -- main row
FROM compliance.projects p
JOIN compliance.project_assessment_plan_mapping papm ON papm.project_id = p.id
JOIN oscal.assessment_plans ap ON ap.id = papm.assessment_plan_id
JOIN oscal.system_security_plans ssp ON ssp.id = ap.ssp_id
JOIN oscal.ssp_system_implementations si
  ON si.scope_type = 'ssp' AND si.scope_id = ssp.id
WHERE ap.status = 'active'   -- 取 active AP 對應 SSP
  -- 若一個 project 有多個 active AP，取最新一個
  AND ap.id = (
      SELECT ap2.id FROM oscal.assessment_plans ap2
      JOIN compliance.project_assessment_plan_mapping papm2 ON papm2.assessment_plan_id = ap2.id
      WHERE papm2.project_id = p.id AND ap2.status = 'active'
      ORDER BY ap2.created_at DESC
      LIMIT 1
  );

-- 統計：應該每個有 active AP 的專案都有對應 row
-- SELECT COUNT(*) FROM _project_current_ssp;

-- ─────────────────────────────────────────────────────────────────────────────
-- Step 2 — Backfill devices (project_device_mapping → ssp_system_implementation_items) (2026-MM-DD)
-- ─────────────────────────────────────────────────────────────────────────────
INSERT INTO oscal.ssp_system_implementation_items (
    uid, system_security_plan_id, system_implementation_id,
    scope_type, scope_id, implementation_type,
    name, device_id, created_user, updated_user
)
SELECT
    gen_random_uuid(),
    pcs.ssp_id,
    pcs.system_implementation_id,
    'ssp',
    pcs.ssp_id,
    'hardware',
    d.name,
    d.id,
    'c3-backfill-2026-MM-DD',
    'c3-backfill-2026-MM-DD'
FROM compliance.project_device_mapping pdm
JOIN _project_current_ssp pcs ON pcs.project_id = pdm.project_id
JOIN public.devices d ON d.id = pdm.device_id
WHERE NOT EXISTS (
    -- idempotent：current SSP 內已有同 device_id 就 skip
    SELECT 1 FROM oscal.ssp_system_implementation_items existing
     WHERE existing.scope_type = 'ssp'
       AND existing.scope_id = pcs.ssp_id
       AND existing.implementation_type = 'hardware'
       AND existing.device_id = d.id
);

-- ─────────────────────────────────────────────────────────────────────────────
-- Step 3 — Backfill information_systems (project_information_systems → items) (2026-MM-DD)
-- ─────────────────────────────────────────────────────────────────────────────
-- 找對應 SSP characteristic id（每個 SSP 有 1:1 characteristic）
CREATE TEMP TABLE _ssp_characteristic AS
SELECT id, system_security_plan_id
FROM oscal.system_security_plans_system_characteristics;

INSERT INTO oscal.ssp_system_implementation_items (
    uid, system_security_plan_id, system_implementation_id,
    scope_type, scope_id, implementation_type,
    name, system_characteristic_id, created_user, updated_user
)
SELECT
    gen_random_uuid(),
    pcs.ssp_id,
    pcs.system_implementation_id,
    'ssp',
    pcs.ssp_id,
    'system',
    isys.name,
    sc.id,
    'c3-backfill-2026-MM-DD',
    'c3-backfill-2026-MM-DD'
FROM compliance.project_information_systems pis
JOIN _project_current_ssp pcs ON pcs.project_id = pis.project_id
JOIN compliance.information_systems isys ON isys.id = pis.information_system_id
LEFT JOIN _ssp_characteristic sc ON sc.system_security_plan_id = pcs.ssp_id
WHERE NOT EXISTS (
    SELECT 1 FROM oscal.ssp_system_implementation_items existing
     WHERE existing.scope_type = 'ssp'
       AND existing.scope_id = pcs.ssp_id
       AND existing.implementation_type = 'system'
       AND existing.name = isys.name  -- 用 name 為 idempotent key（characteristic_id 可能 NULL）
);

-- ─────────────────────────────────────────────────────────────────────────────
-- Step 4 — 驗證 (2026-MM-DD)
-- ─────────────────────────────────────────────────────────────────────────────
-- backfill 後計數
DO $$
DECLARE
    dev_backfilled int;
    sys_backfilled int;
BEGIN
    SELECT COUNT(*) INTO dev_backfilled
      FROM oscal.ssp_system_implementation_items
     WHERE updated_user = 'c3-backfill-2026-MM-DD' AND implementation_type='hardware';

    SELECT COUNT(*) INTO sys_backfilled
      FROM oscal.ssp_system_implementation_items
     WHERE updated_user = 'c3-backfill-2026-MM-DD' AND implementation_type='system';

    RAISE NOTICE 'C3 backfill done: % hardware items, % system items', dev_backfilled, sys_backfilled;
END $$;

-- 對照 — 應該大致對等（除了沒 active AP 的專案）：
-- SELECT 'project_device_mapping', COUNT(*) FROM compliance.project_device_mapping
-- UNION ALL
-- SELECT 'project_information_systems', COUNT(*) FROM compliance.project_information_systems;

DROP TABLE _project_current_ssp;
DROP TABLE _ssp_characteristic;

COMMIT;

-- C3.PR1 rollback：刪除 backfill 出來的 SSP items
BEGIN;

DELETE FROM oscal.ssp_system_implementation_items
 WHERE updated_user = 'c3-backfill-2026-MM-DD';

COMMIT;

def get_project(self, uid, ...):
    ...
    # 從 project_device_mapping 抓 devices
    dev_mappings = self._device_mapping_service.get_all_by_project_uid(uid)
    dto.devices = [DeviceSummaryDto(...) for dm in dev_mappings if dm.device]

    # 從 project_information_systems 抓 audit_systems
    pis_entities = self._pis_domain_service.get_all(
        ProjectInformationSystemQueryEntity(project_id=entity.id)
    )
    ...

def get_project(self, uid, ...):
    ...
    # 從 current AP 對應 SSP 的 implementation_items derive
    current_ssp_id = self._resolve_current_ssp_id(project_id=entity.id)
    if current_ssp_id is None:
        dto.devices = []
        dto.audit_systems = []
    else:
        items = self._ssp_item_domain_service.get_all(
            SspSystemImplementationItemQueryEntity(
                system_security_plan_id=current_ssp_id, scope_type='ssp',
            )
        )
        # devices ← items where implementation_type='hardware'
        # audit_systems ← items where implementation_type IN ('component','system','subsystem','service','software')
        dto.devices = [self._item_to_device_summary(i) for i in items if i.implementation_type == 'hardware']
        dto.audit_systems = [self._item_to_system_summary(i) for i in items if i.implementation_type in ('component','system','subsystem','service','software')]

def _resolve_current_ssp_id(self, project_id: int) -> Optional[int]:
    # 找 project → latest active AP → SSP
    ...

# 410 Gone — endpoint 已下架
GRC_PROJECT_DEVICE_DEPRECATED = (
    "專案設備寫操作已下架，請至 SSP 編輯頁維護受評範圍",
    "GRC_410001",
)
GRC_PROJECT_INFO_SYSTEM_DEPRECATED = (
    "專案資訊系統寫操作已下架，請至 SSP 編輯頁維護受評範圍",
    "GRC_410002",
)

from common.code.grc_error_code import GrcErrorCode

class ProjectDeviceRoute(MethodResource):
    def post(self):
        return self._gone_response(GrcErrorCode.GRC_PROJECT_DEVICE_DEPRECATED)

    def put(self, ...):
        return self._gone_response(GrcErrorCode.GRC_PROJECT_DEVICE_DEPRECATED)

    def delete(self, ...):
        return self._gone_response(GrcErrorCode.GRC_PROJECT_DEVICE_DEPRECATED)

    @staticmethod
    def _gone_response(error_code):
        """回傳 410 Gone，FE 用 error code 查 i18n。"""
        msg, code = error_code.value  # GrcErrorCode tuple 解構
        return {
            "code": 0,
            "msg": msg,           # zh-tw fallback（FE 優先用 code 查 i18n）
            "data": {
                "error_code": code,  # FE 用此 code 查 i18n
                "ssp_endpoints": [   # 引導 FE 改用 SSP-scoped endpoints
                    "GET /projects/<project_uid>/current-ssp-uid",
                    "POST /ssp/<ssp_uid>/ssp-resources/items"
                ]
            }
        }, 410  # HTTP 410 Gone

{
  "error": {
    "GRC_410001": "專案設備寫操作已下架，請至 SSP 編輯頁維護受評範圍",
    "GRC_410002": "專案資訊系統寫操作已下架，請至 SSP 編輯頁維護受評範圍"
  }
}

-- Date: 2026-MM-DD
-- Purpose: C3.PR2 — DROP project_device_mapping + project_information_systems
-- 帳號：cmmgr

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- DROP project_device_mapping (2026-MM-DD)
-- ─────────────────────────────────────────────────────────────────────────────
-- 確認沒有殘留 caller
-- SELECT COUNT(*) FROM compliance.project_device_mapping;
-- 此時可能還有資料但已無 caller 引用

-- 廢 RLS policy
DROP POLICY IF EXISTS project_device_mapping_select ON compliance.project_device_mapping;
DROP POLICY IF EXISTS project_device_mapping_insert ON compliance.project_device_mapping;
DROP POLICY IF EXISTS project_device_mapping_update ON compliance.project_device_mapping;
DROP POLICY IF EXISTS project_device_mapping_delete ON compliance.project_device_mapping;

-- 廢 FK / index（CASCADE 會處理大部分，但顯式列出較明確）
ALTER TABLE compliance.project_device_mapping
    DROP CONSTRAINT IF EXISTS uq_project_device_mapping;
DROP INDEX IF EXISTS compliance.ix_pdm_project_id;
DROP INDEX IF EXISTS compliance.ix_pdm_device_id;

-- 廢 table
DROP TABLE IF EXISTS compliance.project_device_mapping CASCADE;
-- DROP TABLE 會自動 drop sequence 跟 owned objects

-- ─────────────────────────────────────────────────────────────────────────────
-- DROP project_information_systems (2026-MM-DD)
-- ─────────────────────────────────────────────────────────────────────────────
DROP POLICY IF EXISTS project_information_systems_select ON compliance.project_information_systems;
DROP POLICY IF EXISTS project_information_systems_insert ON compliance.project_information_systems;
DROP POLICY IF EXISTS project_information_systems_update ON compliance.project_information_systems;
DROP POLICY IF EXISTS project_information_systems_delete ON compliance.project_information_systems;

ALTER TABLE compliance.project_information_systems
    DROP CONSTRAINT IF EXISTS uq_project_information_system;

DROP TABLE IF EXISTS compliance.project_information_systems CASCADE;

COMMIT;

-- 驗證
-- \dt compliance.project_device_mapping     -- 預期：Did not find any relation
-- \dt compliance.project_information_systems  -- 同上

-- 應該 0（PR1 後寫操作回 410，無新增）
SELECT COUNT(*) FROM compliance.project_device_mapping
WHERE created_at > '<PR1-ship-date>';

SELECT COUNT(*) FROM compliance.project_information_systems
WHERE created_at > '<PR1-ship-date>';