FR-056.1 檢測工具管理（config schema）Implementation Plan

-- Date: 2026-07-26
-- FR-056.1 檢測工具管理 config schema — 新建 config schema + 三張表
--   config.detection_tools               ：檢測工具目錄（取代 FE 寫死清單，平台層維護）
--   config.tenant_detection_tool_configs ：租戶各自的工具設定與憑證（credentials 加密存放）
--   config.detection_tool_param_schemas  ：任務執行時要填的掃描參數定義（版更用）
-- tenant_detection_tool_configs 含 tenant_id + org_unit_id（org_unit 預留，FR-056.1 邏輯只做租戶層級）+ RLS
-- detection_tools / detection_tool_param_schemas 為平台層目錄，不含 tenant_id / RLS

-- ===== 0. Schema =====
CREATE SCHEMA IF NOT EXISTS config;
GRANT USAGE ON SCHEMA config TO cm_app;

-- ===== 1. 工具目錄（平台層，無 tenant）=====
CREATE TABLE config.detection_tools (
    id                  BIGSERIAL PRIMARY KEY,
    uid                 VARCHAR(36)  NOT NULL UNIQUE,
    code                VARCHAR(50)  NOT NULL UNIQUE,           -- openvas / nessus / sonarqube
    name                VARCHAR(255) NOT NULL,
    description         TEXT,
    connection_type     VARCHAR(20)  NOT NULL,                  -- API / CLI
    config_field_schema JSONB        NOT NULL DEFAULT '[]'::jsonb, -- 設定頁欄位定義
    status              VARCHAR(20)  NOT NULL DEFAULT 'coming_soon', -- available / coming_soon
    enabled             BOOLEAN      NOT NULL DEFAULT TRUE,
    created_user        VARCHAR(255),
    updated_user        VARCHAR(255),
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ  NOT NULL DEFAULT now()
);
COMMENT ON TABLE config.detection_tools IS 'FR-056.1 檢測工具目錄（平台層維護，取代 FE 寫死清單）';
COMMENT ON COLUMN config.detection_tools.connection_type IS 'API / CLI — 決定 Agent executor 走哪條';
COMMENT ON COLUMN config.detection_tools.config_field_schema IS '租戶設定頁要填的欄位定義 JSON array，每項 {key,label,type,required,secret}';
COMMENT ON COLUMN config.detection_tools.status IS 'available=可設定 / coming_soon=灰掉不給設定';

GRANT SELECT, INSERT, UPDATE, DELETE ON config.detection_tools TO cm_app;
GRANT USAGE, SELECT ON SEQUENCE config.detection_tools_id_seq TO cm_app;

-- ===== 2. 租戶工具設定 + 憑證（tenant-scoped + RLS）=====
CREATE TABLE config.tenant_detection_tool_configs (
    id                 BIGSERIAL PRIMARY KEY,
    uid                VARCHAR(36)  NOT NULL UNIQUE,
    tenant_id          BIGINT       NOT NULL,
    org_unit_id        BIGINT,                                 -- 預留部門層級（FR-056.1 不使用）
    detection_tool_id  BIGINT       NOT NULL,                  -- soft-ref → config.detection_tools.id（同 schema，可建 FK）
    credentials_encrypted TEXT,                                -- 加密後的 url/key/token/帳密 JSON 密文
    field_values       JSONB        NOT NULL DEFAULT '{}'::jsonb, -- 非機敏欄位值
    status             VARCHAR(20)  NOT NULL DEFAULT 'disabled', -- enabled / disabled
    last_tested_at     TIMESTAMPTZ,
    last_test_result   VARCHAR(20),                            -- success / fail
    created_user       VARCHAR(255),
    updated_user       VARCHAR(255),
    created_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CONSTRAINT fk_tdtc_detection_tool FOREIGN KEY (detection_tool_id)
        REFERENCES config.detection_tools (id),
    CONSTRAINT uq_tdtc_tenant_tool UNIQUE (tenant_id, detection_tool_id)  -- 每租戶每工具一筆
);
COMMENT ON TABLE config.tenant_detection_tool_configs IS 'FR-056.1 租戶各自的檢測工具設定與憑證（credentials 加密）';
COMMENT ON COLUMN config.tenant_detection_tool_configs.credentials_encrypted IS 'FernetCrypto 加密後的憑證 JSON 密文（金鑰 env DETECTION_TOOL_ENCRYPTION_KEY）';
COMMENT ON COLUMN config.tenant_detection_tool_configs.org_unit_id IS '預留部門層級，FR-056.1 邏輯只做租戶層級';

GRANT SELECT, INSERT, UPDATE, DELETE ON config.tenant_detection_tool_configs TO cm_app;
GRANT USAGE, SELECT ON SEQUENCE config.tenant_detection_tool_configs_id_seq TO cm_app;

ALTER TABLE config.tenant_detection_tool_configs ENABLE ROW LEVEL SECURITY;
CREATE POLICY tdtc_tenant_isolation ON config.tenant_detection_tool_configs
    USING (
        current_setting('app.is_super_admin', TRUE) = 'true'
        OR tenant_id = ANY (string_to_array(current_setting('app.allowed_tenant_paths', TRUE), ',')::BIGINT[])
    );

-- ===== 3. 掃描參數定義（平台層，版更用）=====
CREATE TABLE config.detection_tool_param_schemas (
    id                 BIGSERIAL PRIMARY KEY,
    uid                VARCHAR(36)  NOT NULL UNIQUE,
    detection_tool_id  BIGINT       NOT NULL,
    version            INTEGER      NOT NULL DEFAULT 1,
    param_schema       JSONB        NOT NULL DEFAULT '[]'::jsonb, -- 任務執行時參數欄位定義
    is_current         BOOLEAN      NOT NULL DEFAULT TRUE,
    created_user       VARCHAR(255),
    updated_user       VARCHAR(255),
    created_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CONSTRAINT fk_dtps_detection_tool FOREIGN KEY (detection_tool_id)
        REFERENCES config.detection_tools (id),
    CONSTRAINT uq_dtps_tool_version UNIQUE (detection_tool_id, version)
);
COMMENT ON TABLE config.detection_tool_param_schemas IS 'FR-056.1 檢測工具任務參數定義（版更不覆蓋舊版，FR-056.2 消費）';
COMMENT ON COLUMN config.detection_tool_param_schemas.is_current IS '當前生效版本（同工具僅一筆 true）';

GRANT SELECT, INSERT, UPDATE, DELETE ON config.detection_tool_param_schemas TO cm_app;
GRANT USAGE, SELECT ON SEQUENCE config.detection_tool_param_schemas_id_seq TO cm_app;

-- ===== 4. Seed OpenVAS（available）+ 其餘工具（coming_soon）=====
INSERT INTO config.detection_tools (uid, code, name, description, connection_type, config_field_schema, status, enabled)
VALUES
  (gen_random_uuid()::text, 'openvas', 'OpenVAS', '開源弱點掃描器', 'API',
   '[{"key":"base_url","label":"服務 URL","type":"text","required":true,"secret":false},{"key":"username","label":"帳號","type":"text","required":true,"secret":false},{"key":"password","label":"密碼","type":"password","required":true,"secret":true},{"key":"port","label":"連接埠","type":"number","required":false,"secret":false}]'::jsonb,
   'available', TRUE),
  (gen_random_uuid()::text, 'nessus', 'Nessus', '商用弱點掃描器（敬請期待）', 'API', '[]'::jsonb, 'coming_soon', TRUE),
  (gen_random_uuid()::text, 'sonarqube', 'SonarQube', '靜態程式碼分析（敬請期待）', 'API', '[]'::jsonb, 'coming_soon', TRUE)
ON CONFLICT (code) DO NOTHING;

-- ===== 5. Record migration =====
INSERT INTO public.schema_migrations(filename, note) VALUES
  ('2026-07-26-fr056-1-detection-tools-config-schema.sql',
   'FR-056.1 新建 config schema + detection_tools / tenant_detection_tool_configs / detection_tool_param_schemas 三表 + seed OpenVAS')
ON CONFLICT (filename) DO NOTHING;

PGPASSWORD=<cmmgr_pw> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev \
  --single-transaction -v ON_ERROR_STOP=1 \
  -f scripts/sql/2026-07-26-fr056-1-detection-tools-config-schema.sql

PGPASSWORD=<cmmgr_pw> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -c "\dt config.*"
PGPASSWORD=<cmmgr_pw> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -c "SELECT code,name,status FROM config.detection_tools ORDER BY id;"
PGPASSWORD=<cmmgr_pw> psql -h 192.168.50.188 -p 25432 -U cmmgr -d guidant_ai_dev -c "SELECT relname,relrowsecurity FROM pg_class WHERE relname='tenant_detection_tool_configs';"

git add scripts/sql/2026-07-26-fr056-1-detection-tools-config-schema.sql
git commit -m "feat(fr056): add config schema + detection tool tables (T-1.1)

新建 config schema 與三表（detection_tools 目錄 / tenant_detection_tool_configs 加密憑證 / detection_tool_param_schemas 版更參數），seed OpenVAS available + Nessus/SonarQube coming_soon。tenant_detection_tool_configs 含 RLS + org_unit_id 預留。"

from jedi_common.enums.base_code import BaseCode

class DetectionToolsErrorCode(BaseCode):
    DETECTION_TOOL_NOT_FOUND = ("Detection tool not found", "DETECTION_TOOLS_404001")
    DETECTION_TOOL_CONFIG_NOT_FOUND = ("Tenant detection tool config not found", "DETECTION_TOOLS_404002")
    DETECTION_TOOL_NAME_REQUIRED = ("Detection tool code is required", "DETECTION_TOOLS_400001")
    DETECTION_TOOL_NOT_AVAILABLE = ("Detection tool is not available for configuration", "DETECTION_TOOLS_400002")
    DETECTION_TOOL_CONFIG_EXISTS = ("Config for this tool already exists", "DETECTION_TOOLS_409001")

from jedi_common.session.database.db import transaction
from domain.detection_tools.service.detection_tool_domain_service import DetectionToolDomainService

class DetectionToolService:
    def __init__(self, detection_tool_domain_service: DetectionToolDomainService):
        self._domain = detection_tool_domain_service

    @transaction
    def list_tools(self) -> list:
        # 目錄全撈（含 coming_soon），FE 自行灰掉；只回 enabled=True
        return self._domain.get_tools(enabled=True)

    @transaction
    def get_tool(self, uid: str):
        return self._domain.verify_tool_is_exist(uid)

from unittest.mock import MagicMock, patch
import pytest
from app.detection_tools.service.detection_tool_service import DetectionToolService
from domain.detection_tools.entity.detection_tool_entity import DetectionToolEntity

@pytest.fixture(autouse=True)
def _patch_transaction():
    # @transaction 需要 session context；測試以 no-op 包裝
    with patch("app.detection_tools.service.detection_tool_service.transaction", lambda f: f):
        yield

def test_list_tools_returns_enabled_only():
    domain = MagicMock()
    domain.get_tools.return_value = [DetectionToolEntity(uid="u1", code="openvas", status="available")]
    svc = DetectionToolService(domain)
    result = svc.list_tools()
    domain.get_tools.assert_called_once_with(enabled=True)
    assert result[0].code == "openvas"

def test_get_tool_delegates_to_verify():
    domain = MagicMock()
    domain.verify_tool_is_exist.return_value = DetectionToolEntity(uid="u1", code="openvas")
    svc = DetectionToolService(domain)
    result = svc.get_tool("u1")
    domain.verify_tool_is_exist.assert_called_once_with("u1")
    assert result.uid == "u1"

# 重啟 BE（main_app.py，port 8000）後
curl -s -H "Authorization: Bearer <dev_jwt>" http://localhost:8000/api/1.0/detection-tools | python3 -m json.tool

git add infra/detection_tools/ domain/detection_tools/ app/detection_tools/ api/detection_tools/ common/code/detection_tools_error_code.py di_containers/detection_tools/ di_containers/containers.py config/app_modules.py test/test_detection_tool_service.py
git commit -m "feat(fr056): detection_tools catalog read-only API (T-1.2)

複製 remote_agent 九層骨架建 detection_tools 模組（目錄唯讀 GET 清單/單筆），清單改由 DB 提供。entity 守幽靈 WHERE/覆寫兩陷阱。"

DETECTION_TOOL_ENCRYPTION_KEY = os.getenv("DETECTION_TOOL_ENCRYPTION_KEY", "")

from infra.cloud_integration.crypto.fernet_crypto import FernetCrypto
...
    detection_tool_crypto = providers.Singleton(FernetCrypto, key=config.DETECTION_TOOL_ENCRYPTION_KEY)

def test_create_config_encrypts_credentials():
    """建立設定時，credentials 明文經 crypto.encrypt 後才落 DB。"""
    crypto = MagicMock()
    crypto.encrypt.return_value = "ENC(secret)"
    domain = MagicMock()
    svc = DetectionToolService(detection_tool_domain_service=MagicMock(),
                               tenant_config_domain_service=domain,
                               crypto=crypto)
    svc.create_tenant_config(tool_uid="t1", payload={"credentials": {"password": "p"}, "field_values": {}}, curr_user="u", tenant_id=1)
    crypto.encrypt.assert_called_once()
    saved_entity = domain.create_config.call_args[0][0]
    assert saved_entity.credentials_encrypted == "ENC(secret)"
    assert getattr(saved_entity, "credentials", None) in (None, {"password": "p"})  # 明文不落 repo

def test_partial_update_does_not_wipe_credentials():
    """只改 status 的 partial update，不可把 credentials_encrypted 蓋成 None（幽靈覆寫）。"""
    crypto = MagicMock()
    domain = MagicMock()
    domain.verify_config_is_exist.return_value = MagicMock()
    svc = DetectionToolService(detection_tool_domain_service=MagicMock(),
                               tenant_config_domain_service=domain, crypto=crypto)
    svc.update_tenant_config(config_uid="c1", payload={"status": "disabled"}, curr_user="u", tenant_id=1)
    crypto.encrypt.assert_not_called()  # payload 無 credentials → 不加密
    updated_entity = domain.update_config.call_args[0][0]
    assert updated_entity.credentials_encrypted is None  # None → update() 會 skip，DB 值保留

@transaction
def create_tenant_config(self, tool_uid, payload, curr_user, tenant_id):
    tool = self._domain.verify_tool_is_exist(tool_uid)
    if tool.status != "available":
        raise BadRequestError(DetectionToolsErrorCode.DETECTION_TOOL_NOT_AVAILABLE)
    creds = payload.get("credentials")
    entity = TenantDetectionToolConfigEntity(
        uid=str(uuid.uuid4()),
        detection_tool_id=tool.id,
        credentials_encrypted=self._crypto.encrypt(json.dumps(creds)) if creds else None,
        field_values=payload.get("field_values") or {},
        status=payload.get("status") or "disabled",
        tenant_id=tenant_id,
        created_user=curr_user, updated_user=curr_user,
    )
    return self._tenant_config_domain.create_config(entity)

@transaction
def update_tenant_config(self, config_uid, payload, curr_user, tenant_id):
    self._tenant_config_domain.verify_config_is_exist(config_uid)
    creds = payload.get("credentials")
    entity = TenantDetectionToolConfigEntity(
        uid=config_uid,
        credentials_encrypted=self._crypto.encrypt(json.dumps(creds)) if creds else None,  # 無 creds → None → skip
        field_values=payload.get("field_values"),  # None → skip
        status=payload.get("status"),               # None → skip
        updated_user=curr_user,
    )
    return self._tenant_config_domain.update_config(entity)

@transaction
def reset_tenant_config_key(self, config_uid, curr_user):
    """重置：清空憑證（credentials_encrypted 設空字串，非 None，才能真的清）。"""
    self._tenant_config_domain.verify_config_is_exist(config_uid)
    # 註：清空要用特殊 sentinel 或專用 repo 方法，因 None 會被 update() skip。
    return self._tenant_config_domain.clear_credentials(config_uid, curr_user)

@require_capability("detection-tools-manage.create")   # POST 建設定
@require_capability("detection-tools-manage.update")   # PUT 改設定
@require_capability("detection-tools-manage.delete")   # DELETE / 重置

GET /detection-tools/configs            # 列出本租戶所有工具設定含狀態（FE 管理頁渲染用）
GET /detection-tools/configs/<uid>      # 單筆設定（編輯 Dialog 帶入用）

git add infra/detection_tools/ domain/detection_tools/ app/detection_tools/ api/detection_tools/ di_containers/detection_tools/ config/config.py test/test_tenant_detection_tool_config_service.py
git commit -m "feat(fr056): tenant detection tool config CRUD + credential encryption (T-1.3)

租戶工具設定 CRUD，憑證用 FernetCrypto 加密（新金鑰 DETECTION_TOOL_ENCRYPTION_KEY）。寫入掛 capability 守門。response 不回憑證明文。重置 key 用專用 clear_credentials 避開 partial-update None-skip 陷阱。"

def test_test_connection_success():
    crypto = MagicMock(); crypto.decrypt.return_value = '{"base_url":"http://ov","username":"u","password":"p"}'
    probe = MagicMock(); probe.check.return_value = True
    domain = MagicMock(); domain.verify_config_is_exist.return_value = MagicMock(credentials_encrypted="ENC")
    svc = DetectionToolService(..., tenant_config_domain_service=domain, crypto=crypto, openvas_probe=probe)
    result = svc.test_connection("c1")
    assert result["success"] is True
    probe.check.assert_called_once()

def test_count_referencing_tasks_stub_returns_zero():
    """FR-056.1 階段引用數固定 0（TODO FR-056.2/.3 回填）。"""
    svc = DetectionToolService(..., tenant_config_domain_service=MagicMock())
    assert svc.count_referencing_tasks("c1") == 0

@transaction
def test_connection(self, config_uid):
    config = self._tenant_config_domain.verify_config_is_exist(config_uid)
    if not config.credentials_encrypted:
        return {"success": False, "message": "尚未設定憑證"}
    creds = json.loads(self._crypto.decrypt(config.credentials_encrypted))
    ok = self._openvas_probe.check(creds)
    self._tenant_config_domain.record_test_result(config_uid, "success" if ok else "fail")
    return {"success": ok}

@transaction
def count_referencing_tasks(self, config_uid) -> int:
    # TODO(FR-056.2/.3): 查 agent_tasks / 任務綁定表回真實引用數。
    # FR-056.1 階段這些表尚未建立，先回 0（軟提醒，不擋）。
    return 0

# 測試連線
curl -s -X POST -H "Authorization: Bearer <dev_jwt>" \
  http://localhost:8000/api/1.0/detection-tools/configs/<config_uid>/test-connection | python3 -m json.tool
# Expected: {"code":1,"data":{"success":true|false}}

# 引用任務數（FR-056.1 階段固定回 0）
curl -s -H "Authorization: Bearer <dev_jwt>" \
  http://localhost:8000/api/1.0/detection-tools/configs/<config_uid>/referencing-tasks-count | python3 -m json.tool
# Expected: {"code":1,"data":{"count":0}}

git add infra/detection_tools/connector/ app/detection_tools/ api/detection_tools/ test/test_detection_tool_connection.py
git commit -m "feat(fr056): connection test + referencing-task count stub (T-1.4)

測試連線 endpoint（實際探測 OpenVAS 可連）；停用/重置引用任務數查詢先回 0 stub（TODO FR-056.2/.3 回填）。"

DETECTION_TOOLS: getUrl('/detection-tools'),
DETECTION_TOOL_CONFIG: getUrl('/detection-tools/configs'),
DETECTION_TOOL_TEST_CONN: (uid) => getUrl(`/detection-tools/configs/${uid}/test-connection`),
DETECTION_TOOL_REF_COUNT: (uid) => getUrl(`/detection-tools/configs/${uid}/referencing-tasks-count`),

import { BaseService } from './BaseService';
import { API } from '@/config/api/api';
class DetectionToolService extends BaseService {
    listTools() { return this.get(API.DETECTION_TOOLS); }
    listConfigs() { return this.get(API.DETECTION_TOOL_CONFIG); }
    createConfig(data) { return this.post(API.DETECTION_TOOL_CONFIG, data); }
    updateConfig(uid, data) { return this.put(API.DETECTION_TOOL_CONFIG + '/' + uid, data); }
    testConnection(uid) { return this.post(API.DETECTION_TOOL_TEST_CONN(uid)); }
    getRefCount(uid) { return this.get(API.DETECTION_TOOL_REF_COUNT(uid)); }
}
export default new DetectionToolService();

git add src/components/detection-tools/DetectionConfigField.vue src/views/plugin/ToolPluginManage.vue src/service/DetectionToolService.js src/config/api/api.js src/config/locales/i18n/zh-tw/pages.json src/config/locales/i18n/en/pages.json
git commit -m "feat(fr056): rebuild detection tool management page with real API (T-1.5)

檢測工具管理頁綠地重寫：接真後端、config_field_schema 驅動動態欄位、測試連線、啟用/停用/重置、coming_soon 灰掉。新 DetectionConfigField 動態欄位 renderer（text/password/number/select）。修掉現況搜尋變數名 bug。"