-- ============================================================
-- 58_kb_select_ai_router_api.sql
--
-- POC_2 소유 Select AI Team을 ORDS runtime(CB_ORDS)에서 안전하게
-- 호출하기 위한 definer-rights API.
--
-- Execute as POC_2 using SQLcl after 56_kb_select_ai_router_team.sql.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === Creating POC_2 Select AI Team API ===
CREATE OR REPLACE PACKAGE kb_select_ai_router_api AUTHID DEFINER AS
  FUNCTION run_team(
    p_prompt          IN CLOB,
    p_conversation_id IN VARCHAR2 DEFAULT NULL
  ) RETURN CLOB;
END kb_select_ai_router_api;
/

CREATE OR REPLACE PACKAGE BODY kb_select_ai_router_api AS
  FUNCTION run_team(
    p_prompt          IN CLOB,
    p_conversation_id IN VARCHAR2 DEFAULT NULL
  ) RETURN CLOB AS
    v_params CLOB;
    v_conversation_id VARCHAR2(128) := TRIM(p_conversation_id);
  BEGIN
    IF p_prompt IS NULL OR DBMS_LOB.GETLENGTH(TRIM(p_prompt)) = 0 THEN
      RAISE_APPLICATION_ERROR(-20801, 'prompt is required');
    END IF;

    IF v_conversation_id IS NOT NULL THEN
      IF NOT REGEXP_LIKE(v_conversation_id, '^[A-Za-z0-9._:-]{1,128}$') THEN
        RAISE_APPLICATION_ERROR(-20802, 'conversationId contains unsupported characters');
      END IF;
      v_params := '{"conversation_id":"' || v_conversation_id || '"}';
    END IF;

    RETURN DBMS_CLOUD_AI_AGENT.RUN_TEAM(
      team_name   => 'KB_SELECT_AI_ROUTER_TEAM',
      user_prompt => p_prompt,
      params      => v_params
    );
  END run_team;
END kb_select_ai_router_api;
/

SHOW ERRORS

PROMPT === Granting only the Team API to the ORDS runtime ===
GRANT EXECUTE ON kb_select_ai_router_api TO cb_ords;

PROMPT === POC_2 Select AI Team API ready ===
EXIT