-- ============================================================
-- 43_agent_ords_probe_id_handler_patch.sql
-- Adds X-VPD-Probe-Id to existing generic cb.object.query handlers.
--
-- Run as CB_ORDS after 22_agent_ords_security_ords_handler_setup.sql.
-- The dedicated document/vector handlers are updated by 22/29 themselves.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON
SET FEEDBACK ON

PROMPT === Patching generic object-query handlers for FGA correlation ===
DECLARE
  v_source CLOB;
BEGIN
  FOR r IN (
    SELECT m.name AS module_name,
           t.uri_template,
           h.method,
           h.source
    FROM   user_ords_handlers h
           JOIN user_ords_templates t ON t.id = h.template_id
           JOIN user_ords_modules m ON m.id = t.module_id
    WHERE  m.name = 'cb.object.query'
      AND  h.method = 'POST'
      AND  INSTR(h.source, 'cb_ords_handler_pkg.set_vpd_context(:auth_header)') > 0
  ) LOOP
    v_source := REPLACE(
      r.source,
      'cb_ords_handler_pkg.set_vpd_context(:auth_header)',
      'cb_ords_handler_pkg.set_vpd_context(:auth_header, :probe_id)'
    );

    ORDS.DEFINE_HANDLER(
      p_module_name    => r.module_name,
      p_pattern        => r.uri_template,
      p_method         => r.method,
      p_source_type    => ORDS.source_type_plsql,
      p_source         => v_source,
      p_items_per_page => 0
    );
    ORDS.DEFINE_PARAMETER(
      p_module_name        => r.module_name,
      p_pattern            => r.uri_template,
      p_method             => r.method,
      p_name               => 'X-VPD-Probe-Id',
      p_bind_variable_name => 'probe_id',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN'
    );
  END LOOP;
END;
/

COMMIT;

PROMPT === Generic handler patch complete ===
EXIT;
