-- ============================================================
-- 32_dds_vector_tag_setup.sql
-- DDS counterpart of the VPD vector/tag search object.
--
-- The common backoffice stores chunks and TECH_TAG rows in
-- CB_VECTOR_DOCUMENT_CHUNK / CB_VECTOR_DOCUMENT_TAG.  DDS must not query
-- CB_VECTOR_SEARCH_DOCUMENTS because that view carries the VPD policy.
-- This script creates a DDS-only view and declares tag access with DATA
-- GRANTs.  The application uses the same user/role/TAG management journey;
-- the final enforcement is END USER -> DATA ROLE -> DATA GRANT.
--
-- Prerequisite: 28_agent_ords_vector_tag_vpd_setup.sql and 31_dds_standalone_demo_setup.sql
-- Run as ADMIN on an Oracle AI Database release with Deep Data Security.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === 1. Creating a DDS-only vector view (no VPD policy) ===
CREATE OR REPLACE VIEW cb_dds_vector_search_documents AS
SELECT c.chunk_id,
       c.document_id,
       c.chunk_no,
       c.title,
       c.chunk_text,
       c.source_uri,
       c.embedding,
       (
         SELECT LISTAGG(t.tech_tag, ',') WITHIN GROUP (ORDER BY t.tech_tag)
         FROM   cb_vector_document_tag t
         WHERE  t.chunk_id = c.chunk_id
       ) AS tech_tag
FROM   cb_vector_document_chunk c
WHERE  EXISTS (
  SELECT 1
  FROM   cb_vector_document_tag t
  WHERE  t.chunk_id = c.chunk_id
);

PROMPT === 2. Replacing DATA GRANTs for the four demo identities ===
-- The tag expression is deliberately an OR expression.  Adding another
-- allowed tag means replacing the corresponding grant expression, not
-- writing a second conflicting policy.
BEGIN
  EXECUTE IMMEDIATE 'DROP DATA GRANT admin.dds_demo_my_vector_grant';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP DATA GRANT admin.dds_demo_pg_vector_grant';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP DATA GRANT admin.dds_demo_both_vector_grant';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE OR REPLACE DATA GRANT admin.dds_demo_my_vector_grant
  AS SELECT
  ON admin.cb_dds_vector_search_documents
  WHERE REGEXP_LIKE(UPPER(tech_tag), '(^|,)(SPRING_BOOT|ORDS)(,|$)')
  TO dds_demo_my_role;

CREATE OR REPLACE DATA GRANT admin.dds_demo_pg_vector_grant
  AS SELECT
  ON admin.cb_dds_vector_search_documents
  WHERE REGEXP_LIKE(UPPER(tech_tag), '(^|,)(ORACLE_VPD|ORACLE_DDS)(,|$)')
  TO dds_demo_pg_role;

CREATE OR REPLACE DATA GRANT admin.dds_demo_both_vector_grant
  AS SELECT
  ON admin.cb_dds_vector_search_documents
  WHERE REGEXP_LIKE(UPPER(tech_tag), '(^|,)(SPRING_BOOT|ORDS|ORACLE_VPD|ORACLE_DDS|MCP)(,|$)')
  TO dds_demo_both_role;

-- dds_demo_none_role intentionally receives no grant (default deny).

PROMPT === 3. Registering the vector object in the common application permission model ===
-- The application permission tables remain the source of truth. Basic vector
-- access is a role whitelist: the seeded application roles have one ALLOW
-- permission plus an ALL rule and can search every vector chunk. TAG rules
-- are reserved for a role deliberately configured with a row condition.
DECLARE
  PROCEDURE ensure_vector_all_permission(p_role_id IN NUMBER) IS
    v_perm_id NUMBER;
    v_rule_id NUMBER;
  BEGIN
    BEGIN
      SELECT 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'
      AND    ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND 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;

    -- The old demo seed put arbitrary TECH_TAG filters on these base roles.
    -- Full access must not depend on the current list of document tags.
    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
  ensure_vector_all_permission(10);
  ensure_vector_all_permission(20);
  ensure_vector_all_permission(30);
END;
/

PROMPT === 4. Vector object inventory ===
SELECT 'DDS_VECTOR_OBJECT' AS item,
       'ADMIN.CB_DDS_VECTOR_SEARCH_DOCUMENTS' AS value
FROM dual;
SELECT grant_name, object_name, grantee
FROM   dba_data_grants
WHERE  grant_name IN (
  'DDS_DEMO_MY_VECTOR_GRANT',
  'DDS_DEMO_PG_VECTOR_GRANT',
  'DDS_DEMO_BOTH_VECTOR_GRANT'
)
ORDER BY grant_name;

PROMPT === DDS vector/tag DATA GRANT setup complete ===
EXIT;
