-- ============================================================
-- 38_agent_ords_vector_all_permission_normalization.sql
-- Normalize the seeded vector access roles to whitelist / ALL access.
--
-- The default roles 10, 20 and 30 may search every document chunk. A role
-- with no matching ALLOW permission remains fail-closed through the VPD
-- function (1 = 0). Keep row conditions such as TAG on dedicated restricted
-- roles that do not also have an ALL rule.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === Normalizing default vector roles to ALL ===
DECLARE
  PROCEDURE normalize_vector_role(p_role_id IN NUMBER) IS
    v_perm_id NUMBER;
    v_rule_id NUMBER;
  BEGIN
    SELECT MIN(perm_id)
    INTO   v_perm_id
    FROM   cb_permission
    WHERE  role_id = p_role_id
    AND    target_name = 'CB_VECTOR_SEARCH_DOCUMENTS'
    AND    action_name = 'SELECT'
    AND    permission_effect = 'ALLOW';

    IF v_perm_id IS NULL THEN
      SELECT NVL(MAX(perm_id), 0) + 1 INTO v_perm_id FROM cb_permission;
      INSERT INTO cb_permission (
        perm_id, role_id, target_name, action_name, permission_effect
      ) VALUES (
        v_perm_id, p_role_id, 'CB_VECTOR_SEARCH_DOCUMENTS', 'SELECT', 'ALLOW'
      );
    END IF;

    -- Remove only the legacy demo TAG filters from the default ALL roles.
    DELETE FROM cb_permission_rule
    WHERE  perm_id = v_perm_id
    AND    UPPER(TRIM(rule_type)) = 'TAG';

    SELECT MIN(rule_id)
    INTO   v_rule_id
    FROM   cb_permission_rule
    WHERE  perm_id = v_perm_id
    AND    UPPER(TRIM(rule_type)) = 'ALL';

    IF v_rule_id IS NULL THEN
      SELECT NVL(MAX(rule_id), 0) + 1 INTO v_rule_id FROM cb_permission_rule;
      INSERT INTO cb_permission_rule (
        rule_id, perm_id, rule_column, rule_type, rule_value
      ) VALUES (
        v_rule_id, v_perm_id, NULL, 'ALL', NULL
      );
    END IF;

    DELETE FROM cb_permission_rule
    WHERE  perm_id = v_perm_id
    AND    UPPER(TRIM(rule_type)) = 'ALL'
    AND    rule_id <> v_rule_id;
  END;
BEGIN
  normalize_vector_role(10); -- HR_DEPT_ROLE
  normalize_vector_role(20); -- FIN_SELF_ROLE
  normalize_vector_role(30); -- ALL_DOC_ROLE
  COMMIT;
END;
/

PROMPT === Verifying default vector whitelist rules ===
SELECT r.role_id,
       r.role_name,
       p.target_name,
       p.action_name,
       p.permission_effect,
       pr.rule_type,
       pr.rule_column,
       pr.rule_value
FROM   cb_app_role r
JOIN   cb_permission p
ON     p.role_id = r.role_id
LEFT JOIN cb_permission_rule pr
ON     pr.perm_id = p.perm_id
WHERE  r.role_id IN (10, 20, 30)
AND    p.target_name = 'CB_VECTOR_SEARCH_DOCUMENTS'
AND    p.action_name = 'SELECT'
ORDER BY r.role_id, p.perm_id, pr.rule_id;

PROMPT === Default vector whitelist normalization complete ===
EXIT;
