-- ============================================================
-- 30_agent_ords_vector_tag_vpd_test.sql
-- Unit-style checks for TAG rules on CB_VECTOR_SEARCH_DOCUMENTS.
--
-- Run as ADMIN after 26_agent_ords_security_dynamic_vpd_filter.sql and
-- 28_agent_ords_vector_tag_vpd_setup.sql.
-- This script only creates temporary permission-plane rows in the 458xxx
-- range, checks the generated predicates, and removes those rows.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON
SET DEFINE OFF
SET SERVEROUTPUT ON

PROMPT === Preparing TAG rule test data ===

BEGIN
  cb_agent_ctx_pkg.clear_user;

  DELETE FROM cb_permission_rule WHERE perm_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_permission      WHERE perm_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_user_role       WHERE role_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_app_role        WHERE role_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_app_user        WHERE user_id BETWEEN 458000 AND 458999;

  INSERT INTO cb_app_user(user_id, user_name, employee_no, dept_code, can_read_contents, active)
  VALUES (458101, 'tag_test_or', 'E458101', 'QA', 'N', 'Y');
  INSERT INTO cb_app_user(user_id, user_name, employee_no, dept_code, can_read_contents, active)
  VALUES (458102, 'tag_test_deny', 'E458102', 'QA', 'N', 'Y');
  INSERT INTO cb_app_user(user_id, user_name, employee_no, dept_code, can_read_contents, active)
  VALUES (458103, 'tag_test_none', 'E458103', 'QA', 'N', 'Y');

  INSERT INTO cb_app_role(role_id, role_name) VALUES (458101, 'TAG_TEST_OR');
  INSERT INTO cb_app_role(role_id, role_name) VALUES (458102, 'TAG_TEST_ALLOW_DENY');
  INSERT INTO cb_app_role(role_id, role_name) VALUES (458103, 'TAG_TEST_NONE');

  INSERT INTO cb_user_role(user_id, role_id) VALUES (458101, 458101);
  INSERT INTO cb_user_role(user_id, role_id) VALUES (458102, 458102);
  INSERT INTO cb_user_role(user_id, role_id) VALUES (458103, 458103);

  INSERT INTO cb_permission(perm_id, role_id, target_name, action_name, permission_effect)
  VALUES (458101, 458101, 'CB_VECTOR_SEARCH_DOCUMENTS', 'SELECT', 'ALLOW');
  INSERT INTO cb_permission(perm_id, role_id, target_name, action_name, permission_effect)
  VALUES (458102, 458102, 'CB_VECTOR_SEARCH_DOCUMENTS', 'SELECT', 'ALLOW');
  INSERT INTO cb_permission(perm_id, role_id, target_name, action_name, permission_effect)
  VALUES (458103, 458102, 'CB_VECTOR_SEARCH_DOCUMENTS', 'SELECT', 'DENY');

  INSERT INTO cb_permission(perm_id, role_id, target_name, action_name, permission_effect)
  VALUES (458104, 458103, 'CB_VECTOR_SEARCH_DOCUMENTS', 'SELECT', 'DENY');

  INSERT INTO cb_permission_rule(rule_id, perm_id, rule_column, rule_type, rule_value)
  VALUES (458101, 458101, NULL, 'TAG', 'SPRING_BOOT');
  INSERT INTO cb_permission_rule(rule_id, perm_id, rule_column, rule_type, rule_value)
  VALUES (458102, 458101, NULL, 'TAG', 'ORACLE_VPD');

  INSERT INTO cb_permission_rule(rule_id, perm_id, rule_column, rule_type, rule_value)
  VALUES (458103, 458102, NULL, 'TAG', 'SPRING_BOOT');
  INSERT INTO cb_permission_rule(rule_id, perm_id, rule_column, rule_type, rule_value)
  VALUES (458104, 458102, NULL, 'TAG', 'ORACLE_VPD');

  INSERT INTO cb_permission_rule(rule_id, perm_id, rule_column, rule_type, rule_value)
  VALUES (458105, 458103, NULL, 'TAG', 'ORDS');
  INSERT INTO cb_permission_rule(rule_id, perm_id, rule_column, rule_type, rule_value)
  VALUES (458106, 458104, NULL, 'TAG', 'SPRING_BOOT');

  COMMIT;
END;
/

PROMPT === Checking ALLOW TAG OR semantics and DENY precedence ===

DECLARE
  PROCEDURE assert_contains(
      p_label    IN VARCHAR2,
      p_user_id  IN NUMBER,
      p_expected IN VARCHAR2
  ) IS
    v_actual VARCHAR2(32767);
  BEGIN
    cb_agent_ctx_pkg.set_user(p_user_id);
    v_actual := cb_agent_doc_vpd_filter('ADMIN', 'CB_VECTOR_SEARCH_DOCUMENTS');
    IF INSTR(v_actual, p_expected) = 0 THEN
      RAISE_APPLICATION_ERROR(-20458,
        p_label || ' expected [' || p_expected || '] actual [' || v_actual || ']');
    END IF;
    DBMS_OUTPUT.PUT_LINE('PASS ' || p_label || ': ' || v_actual);
  END;

  PROCEDURE assert_equals(
      p_label    IN VARCHAR2,
      p_user_id  IN NUMBER,
      p_expected IN VARCHAR2
  ) IS
    v_actual VARCHAR2(32767);
  BEGIN
    cb_agent_ctx_pkg.set_user(p_user_id);
    v_actual := cb_agent_doc_vpd_filter('ADMIN', 'CB_VECTOR_SEARCH_DOCUMENTS');
    IF v_actual != p_expected THEN
      RAISE_APPLICATION_ERROR(-20458,
        p_label || ' expected [' || p_expected || '] actual [' || v_actual || ']');
    END IF;
    DBMS_OUTPUT.PUT_LINE('PASS ' || p_label || ': ' || v_actual);
  END;
BEGIN
  assert_contains('TWO_ALLOW_TAGS_ARE_OR', 458101, 'OR');
  assert_contains('TWO_ALLOW_TAGS_INCLUDE_SPRING', 458101, 'REGEXP_LIKE(UPPER(TECH_TAG), ''(^|,)SPRING_BOOT(,|$)'')');
  assert_contains('TWO_ALLOW_TAGS_INCLUDE_VPD', 458101, 'REGEXP_LIKE(UPPER(TECH_TAG), ''(^|,)ORACLE_VPD(,|$)'')');
  assert_contains('DENY_TAG_IS_SUBTRACTED', 458102, 'AND NOT');
  assert_contains('DENY_TAG_DETAIL', 458102, 'REGEXP_LIKE(UPPER(TECH_TAG), ''(^|,)ORDS(,|$)'')');
  assert_equals('DENY_ONLY_FAILS_CLOSED', 458103, '1 = 0');
  cb_agent_ctx_pkg.clear_user;
END;
/

PROMPT === Cleaning TAG rule test data ===

BEGIN
  cb_agent_ctx_pkg.clear_user;
  DELETE FROM cb_permission_rule WHERE perm_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_permission      WHERE perm_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_user_role       WHERE role_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_app_role        WHERE role_id BETWEEN 458000 AND 458999;
  DELETE FROM cb_app_user        WHERE user_id BETWEEN 458000 AND 458999;
  COMMIT;
END;
/

PROMPT === TAG rule checks passed ===
EXIT;
