-- ============================================================
-- 29_agent_ords_vector_search_ords.sql
-- Dedicated ORDS vector search endpoint for the tag-protected view.
--
-- Run as CB_ORDS after 22_agent_ords_security_ords_handler_setup.sql and
-- 28_agent_ords_vector_tag_vpd_setup.sql.
--
-- Request:
--   POST /ords/cb-ords/cb-agent-vector/search?limit=10
--   Authorization: Bearer <key>
--   {"embedding":[0.10,0.20,0.30,0.40]}
--
-- The handler sets the application context, lets VPD filter one row per
-- chunk by the aggregated TECH_TAG list, then calculates vector distance.
-- EMBEDDING is never returned.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === Creating the vector search ORDS module ===
BEGIN
  ORDS.DELETE_MODULE(p_module_name => 'cb.agent.vector');
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

BEGIN
  ORDS.DEFINE_MODULE(
    p_module_name    => 'cb.agent.vector',
    p_base_path      => 'cb-agent-vector/',
    p_items_per_page => 25,
    p_status         => 'PUBLISHED'
  );

  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'cb.agent.vector',
    p_pattern     => 'search'
  );

  ORDS.DEFINE_HANDLER(
    p_module_name    => 'cb.agent.vector',
    p_pattern        => 'search',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'!
DECLARE
  v_rows           SYS_REFCURSOR;
  v_body_text      CLOB;
  v_embedding_text VARCHAR2(32767);
  v_vpd_predicate  VARCHAR2(32767);
  v_effective_sql  VARCHAR2(32767);
BEGIN
  cb_ords_handler_pkg.set_vpd_context(:auth_header, :probe_id);

  BEGIN
    v_vpd_predicate := admin.cb_agent_doc_vpd_filter('ADMIN', 'CB_VECTOR_SEARCH_DOCUMENTS');
    v_effective_sql := 'SELECT chunk_id, document_id, chunk_no, title, chunk_text, source_uri, tech_tag, score '
      || 'FROM (SELECT d.chunk_id, d.document_id, d.chunk_no, d.title, d.chunk_text, '
      || 'd.source_uri, d.tech_tag, VECTOR_DISTANCE(d.embedding, TO_VECTOR(:embedding), COSINE) AS score '
      || 'FROM admin.cb_vector_search_documents d '
      || 'WHERE d.embedding IS NOT NULL';
    IF TRIM(v_vpd_predicate) <> '1 = 1' THEN
      v_effective_sql := v_effective_sql || ' AND (' || v_vpd_predicate || ')';
    END IF;
    v_effective_sql := v_effective_sql
      || ' ORDER BY score) ranked_chunks '
      || 'WHERE ROWNUM <= LEAST(GREATEST(NVL(:row_limit, 10), 1), 100)';
  EXCEPTION
    WHEN OTHERS THEN
      v_vpd_predicate := NULL;
      v_effective_sql := NULL;
  END;

  -- ORDS exposes :body_text as a stream bind. Read it exactly once; referring
  -- to the stream bind twice causes ORA-17270 (Duplicate stream parameter).
  v_body_text := :body_text;

  -- JSON_QUERY accepts a numeric JSON array. The JSON_VALUE fallback also
  -- permits clients that send the vector as the text "[0.1,0.2,...]".
  v_embedding_text := JSON_QUERY(
    v_body_text,
    '$.embedding' RETURNING VARCHAR2(32767)
  );
  IF v_embedding_text IS NULL THEN
    v_embedding_text := JSON_VALUE(
      v_body_text,
      '$.embedding' RETURNING VARCHAR2(32767)
    );
  END IF;

  IF v_embedding_text IS NULL OR TRIM(v_embedding_text) IS NULL THEN
    RAISE_APPLICATION_ERROR(-20801, 'embedding is required');
  END IF;

  OPEN v_rows FOR
    SELECT chunk_id,
           document_id,
           chunk_no,
           title,
           chunk_text,
           source_uri,
           tech_tag,
           score
    FROM (
      SELECT d.chunk_id,
             d.document_id,
             d.chunk_no,
             d.title,
             d.chunk_text,
             d.source_uri,
             d.tech_tag,
             VECTOR_DISTANCE(
               d.embedding,
               TO_VECTOR(v_embedding_text),
               COSINE
             ) AS score
      FROM   admin.cb_vector_search_documents d
      WHERE  d.embedding IS NOT NULL
      ORDER BY score
    ) ranked_chunks
    WHERE ROWNUM <= LEAST(GREATEST(NVL(:row_limit, 10), 1), 100);

  :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;
  IF v_effective_sql IS NOT NULL THEN
    APEX_JSON.WRITE('vpd_predicate', v_vpd_predicate);
    APEX_JSON.WRITE('effective_sql', v_effective_sql);
  END IF;
  APEX_JSON.WRITE('items', v_rows);
  APEX_JSON.CLOSE_OBJECT;

  cb_ords_handler_pkg.clear_vpd_context;
EXCEPTION
  WHEN OTHERS THEN
    cb_ords_handler_pkg.clear_vpd_context;
    :status_code := CASE WHEN SQLCODE = -20801 THEN 400 ELSE 403 END;
    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('error', SQLERRM);
    APEX_JSON.CLOSE_OBJECT;
END;
!',
    p_items_per_page => 0
  );

  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'cb.agent.vector',
    p_pattern            => 'search',
    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        => 'cb.agent.vector',
    p_pattern            => 'search',
    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'
  );

  ORDS.DEFINE_PARAMETER(
    p_module_name        => 'cb.agent.vector',
    p_pattern            => 'search',
    p_method             => 'POST',
    p_name               => 'limit',
    p_bind_variable_name => 'row_limit',
    p_source_type        => 'URI',
    p_param_type         => 'INT',
    p_access_method      => 'IN'
  );

  COMMIT;
END;
/

PROMPT === Vector search ORDS endpoint ready ===
PROMPT Path: /ords/cb-ords/cb-agent-vector/search
EXIT;
