-- ============================================================
-- 22_agent_ords_security_ords_handler_setup.sql
-- ORDS module/handler package for Agent ORDS security.
--
-- Run as CB_ORDS after:
--   * ADMIN VPD setup
--   * ADMIN DDS setup
--   * ADMIN ORDS schema enablement
--
-- Purpose:
--   * Common package: Authorization header -> Bearer key -> SYS_CONTEXT.
--   * Object query handlers: call the common package, then query their
--     own protected table/view.
--   * VPD demo path: a fixed demo handler for ADMIN.CB_V_SEARCH_DOCUMENTS.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON

PROMPT === Creating CB_ORDS handler package ===
CREATE OR REPLACE PACKAGE cb_ords_handler_pkg AUTHID DEFINER AS
  PROCEDURE set_vpd_context(
    p_authorization IN VARCHAR2,
    p_probe_id      IN VARCHAR2 DEFAULT NULL
  );
  FUNCTION set_vpd_context_sql(p_authorization IN VARCHAR2) RETURN NUMBER;
  PROCEDURE clear_vpd_context;
END;
/

CREATE OR REPLACE PACKAGE BODY cb_ords_handler_pkg AS
  FUNCTION extract_bearer_key(p_authorization IN VARCHAR2) RETURN VARCHAR2 AS
    v_auth VARCHAR2(4000);
  BEGIN
    v_auth := TRIM(p_authorization);

    IF v_auth IS NULL OR LOWER(SUBSTR(v_auth, 1, 7)) != 'bearer ' THEN
      RAISE_APPLICATION_ERROR(-20101, 'Authorization header must be Bearer <key>');
    END IF;

    RETURN TRIM(SUBSTR(v_auth, 8));
  END;

  PROCEDURE set_vpd_context(
    p_authorization IN VARCHAR2,
    p_probe_id      IN VARCHAR2 DEFAULT NULL
  ) AS
    v_bearer_key VARCHAR2(4000);
  BEGIN
    DBMS_SESSION.CLEAR_IDENTIFIER;
    admin.cb_agent_ctx_pkg.clear_user;
    v_bearer_key := extract_bearer_key(p_authorization);
    admin.cb_agent_ctx_pkg.set_user_by_bearer(v_bearer_key);
    IF TRIM(p_probe_id) IS NOT NULL THEN
      DBMS_SESSION.SET_IDENTIFIER(SUBSTR(TRIM(p_probe_id), 1, 64));
    END IF;
  END;

  FUNCTION set_vpd_context_sql(p_authorization IN VARCHAR2) RETURN NUMBER AS
  BEGIN
    set_vpd_context(p_authorization);
    RETURN 1;
  END;

  PROCEDURE clear_vpd_context AS
  BEGIN
    admin.cb_agent_ctx_pkg.clear_user;
    DBMS_SESSION.CLEAR_IDENTIFIER;
  END;
END;
/

SHOW ERRORS

PROMPT === Defining ORDS module and handlers ===
BEGIN
  ORDS.DELETE_MODULE(p_module_name => 'cb.agent.security');
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

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

  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'cb.agent.security',
    p_pattern     => 'vpd/documents'
  );

  ORDS.DEFINE_HANDLER(
    p_module_name    => 'cb.agent.security',
    p_pattern        => 'vpd/documents',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'!
DECLARE
  v_rows SYS_REFCURSOR;
  v_vpd_predicate VARCHAR2(32767);
  v_effective_sql VARCHAR2(32767);
BEGIN
  cb_ords_handler_pkg.set_vpd_context(:auth_header, :probe_id);

  -- The predicate below is the value returned by the same DBMS_RLS policy
  -- function that protects ADMIN.CB_V_SEARCH_DOCUMENTS.
  BEGIN
    v_vpd_predicate := admin.cb_agent_doc_vpd_filter('ADMIN', 'CB_V_SEARCH_DOCUMENTS');
    v_effective_sql := 'SELECT d.doc_id, d.title, d.owner_emp_no, d.dept_code, d.contents '
      || 'FROM admin.cb_v_search_documents d WHERE (' || v_vpd_predicate || ') '
      || 'AND ROWNUM <= LEAST(GREATEST(NVL(:row_limit, 50), 1), 500)';
  EXCEPTION
    WHEN OTHERS THEN
      v_vpd_predicate := NULL;
      v_effective_sql := NULL;
  END;

  OPEN v_rows FOR
    SELECT d.doc_id,
           d.title,
           d.owner_emp_no,
           d.dept_code,
           d.contents
    FROM   admin.cb_v_search_documents d
    WHERE  ROWNUM <= LEAST(GREATEST(NVL(:row_limit, 50), 1), 500);

  :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 := 403;
    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.security',
    p_pattern            => 'vpd/documents',
    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.security',
    p_pattern            => 'vpd/documents',
    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.security',
    p_pattern            => 'vpd/documents',
    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 === ORDS handler setup complete ===
EXIT;
