-- ============================================================
-- 66_kb_select_ai_vpd_query_ords.sql
--
-- POST /ords/cb-ords/kb-select-ai-vpd/query
-- Authorization: Bearer <VPD token>
-- {"prompt":"채널별 청구 건수를 보여줘", "limit":50}
--
-- Run as CB_ORDS after 65. The Bearer token is resolved before Select AI
-- generates and executes its single validated read-only SQL statement.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE OFF
SET FEEDBACK ON

PROMPT === Granting context setup to the ORDS gateway ===
GRANT EXECUTE ON cb_ords_handler_pkg TO ords_public_user;

PROMPT === Resetting only the VPD Select AI query module ===
BEGIN
  ORDS.DELETE_MODULE(p_module_name => 'kb.select.ai.vpd');
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

PROMPT === Creating VPD-aware Select AI query endpoint ===
BEGIN
  ORDS.DEFINE_MODULE(
    p_module_name    => 'kb.select.ai.vpd',
    p_base_path      => 'kb-select-ai-vpd/',
    p_items_per_page => 0,
    p_status         => 'PUBLISHED'
  );

  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'kb.select.ai.vpd',
    p_pattern     => 'query'
  );

  ORDS.DEFINE_HANDLER(
    p_module_name    => 'kb.select.ai.vpd',
    p_pattern        => 'query',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'~
DECLARE
  v_body_text     CLOB;
  v_prompt        VARCHAR2(4000);
  v_limit         PLS_INTEGER;
  v_rows          SYS_REFCURSOR;
  v_generated_sql CLOB;
  v_response      CLOB;
  v_error_code    NUMBER;
  v_error_message VARCHAR2(4000);
BEGIN
  -- Resolves the Bearer token and sets the VPD/ASO application context in
  -- this very ORDS database session before the generated SQL is parsed.
  cb_ords_handler_pkg.set_vpd_context(:auth_header, :probe_id);

  v_body_text := :body_text;
  v_prompt := JSON_VALUE(v_body_text, '$.prompt' RETURNING VARCHAR2(4000) NULL ON ERROR);
  v_limit := JSON_VALUE(v_body_text, '$.limit' RETURNING NUMBER DEFAULT 50 ON ERROR);

  IF v_prompt IS NULL OR TRIM(v_prompt) IS NULL THEN
    RAISE_APPLICATION_ERROR(-20801, 'prompt is required');
  END IF;
  IF v_limit IS NULL OR v_limit < 1 OR v_limit > 100 THEN
    RAISE_APPLICATION_ERROR(-20802, 'limit must be between 1 and 100');
  END IF;

  -- Dynamic PL/SQL keeps ORDS gateway validation limited to the local handler
  -- package while CB_ORDS invokes only the fixed POC_2 API granted to it.
  EXECUTE IMMEDIATE
    'BEGIN poc_2.kb_select_ai_vpd_query_api.open_query(:prompt, :row_limit, :rows, :sql); END;'
    USING IN v_prompt, IN v_limit, OUT v_rows, OUT v_generated_sql;

  :status_code := 200;
  OWA_UTIL.MIME_HEADER('application/json', FALSE);
  HTP.P('Cache-Control: no-store');
  OWA_UTIL.HTTP_HEADER_CLOSE;

  APEX_JSON.OPEN_OBJECT;
  APEX_JSON.WRITE('profile', 'KB_AIDP_SELECTAI_GPT54_MINI_FULLMETA_PROFILE_V1');
  APEX_JSON.WRITE('generatedSql', v_generated_sql);
  APEX_JSON.WRITE('items', v_rows);
  APEX_JSON.CLOSE_OBJECT;

  cb_ords_handler_pkg.clear_vpd_context;
EXCEPTION
  WHEN OTHERS THEN
    v_error_code := SQLCODE;
    v_error_message := SQLERRM;
    cb_ords_handler_pkg.clear_vpd_context;
    :status_code := CASE
      WHEN v_error_code IN (-20001, -20002) THEN 403
      WHEN v_error_code BETWEEN -20899 AND -20800 THEN 400
      WHEN v_error_code BETWEEN -20199 AND -20100 THEN 403
      ELSE 500
    END;
    SELECT JSON_OBJECT(
      'errorCode' VALUE v_error_code,
      'error' VALUE CASE
        WHEN v_error_code IN (-20001, -20002) THEN
          'VPD token is missing, invalid, expired, or has no access permission.'
        WHEN v_error_code BETWEEN -20899 AND -20800 THEN v_error_message
        ELSE 'Select AI query execution failed.'
      END
      RETURNING CLOB
    )
    INTO v_response
    FROM dual;
    OWA_UTIL.MIME_HEADER('application/json', FALSE);
    HTP.P('Cache-Control: no-store');
    OWA_UTIL.HTTP_HEADER_CLOSE;
    HTP.P(v_response);
END;
~',
    p_items_per_page => 0
  );

  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'kb.select.ai.vpd',
    p_pattern            => 'query',
    p_method             => 'POST',
    p_name               => 'Authorization',
    p_bind_variable_name => 'auth_header',
    p_source_type        => 'HEADER',
    p_param_type         => 'STRING',
    p_access_method      => 'IN'
  );

  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'kb.select.ai.vpd',
    p_pattern            => 'query',
    p_method             => 'POST',
    p_name               => 'X-VPD-Probe-Id',
    p_bind_variable_name => 'probe_id',
    p_source_type        => 'HEADER',
    p_param_type         => 'STRING',
    p_access_method      => 'IN'
  );

  COMMIT;
END;
/

PROMPT === VPD-aware Select AI query endpoint ready ===
PROMPT Path: /ords/cb-ords/kb-select-ai-vpd/query
EXIT
