-- ============================================================
-- 28_agent_ords_vector_tag_vpd_setup.sql
-- Vector/chunk knowledge search data plane with tag-based VPD.
--
-- Run as ADMIN after:
--   * 25_agent_ords_security_backoffice_support.sql
--   * 26_agent_ords_security_dynamic_vpd_filter.sql
--
-- Target: Oracle AI Database 23ai/26ai (VECTOR data type).
-- The embedding is produced by the ingestion pipeline. The database
-- stores the chunk, its vector, and one or more normalized TECH_TAG rows.
-- The VPD policy protects a one-row-per-chunk view whose TECH_TAG value is a
-- comma-delimited tag list, so ALLOW and DENY apply to the whole chunk.
-- ============================================================
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON
SET FEEDBACK ON
SET DEFINE OFF

PROMPT === 1. Creating vector chunk and tag tables ===
BEGIN
  EXECUTE IMMEDIATE 'CREATE SEQUENCE cb_vector_chunk_seq START WITH 30000 INCREMENT BY 1 NOCACHE';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -955 THEN
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE q'!
    CREATE TABLE cb_vector_document_chunk (
      chunk_id    NUMBER PRIMARY KEY,
      document_id VARCHAR2(200) NOT NULL,
      chunk_no    NUMBER NOT NULL,
      title       VARCHAR2(500),
      chunk_text  CLOB NOT NULL,
      source_uri  VARCHAR2(1000),
      embedding   VECTOR,
      created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
      CONSTRAINT cb_vector_chunk_uk UNIQUE (document_id, chunk_no)
    )!';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -955 THEN
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE q'!
    CREATE TABLE cb_vector_document_tag (
      chunk_id NUMBER NOT NULL REFERENCES cb_vector_document_chunk(chunk_id) ON DELETE CASCADE,
      tech_tag VARCHAR2(100) NOT NULL,
      CONSTRAINT cb_vector_tag_pk PRIMARY KEY (chunk_id, tech_tag),
      CONSTRAINT cb_vector_tag_format_ck CHECK (REGEXP_LIKE(tech_tag, '^[A-Z0-9_-]+$'))
    )!';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -955 THEN
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX cb_vector_tag_ix ON cb_vector_document_tag (tech_tag, chunk_id)';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -955 THEN
      RAISE;
    END IF;
END;
/

PROMPT === 2. Seeding a small, repeatable vector search example ===
-- These four-dimensional vectors are only a smoke-test fixture. Replace
-- them with the embedding model's dimension in the real ingestion pipeline.
INSERT INTO cb_vector_document_chunk (
  chunk_id, document_id, chunk_no, title, chunk_text, source_uri, embedding
)
SELECT 28001, 'knowledge-001', 1, 'Spring Boot VPD 시작하기',
       TO_CLOB('Spring Boot 애플리케이션은 토큰으로 사용자를 식별하고 ORDS를 호출한다.'),
       'kb://security/spring-boot-vpd', TO_VECTOR('[0.10,0.20,0.30,0.40]')
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM cb_vector_document_chunk WHERE chunk_id = 28001);

INSERT INTO cb_vector_document_chunk (
  chunk_id, document_id, chunk_no, title, chunk_text, source_uri, embedding
)
SELECT 28002, 'knowledge-001', 2, 'Oracle VPD 정책 연결',
       TO_CLOB('Oracle VPD는 현재 사용자 권한에 맞는 행만 반환하도록 WHERE 조건을 동적으로 붙인다.'),
       'kb://security/oracle-vpd', TO_VECTOR('[0.20,0.30,0.40,0.50]')
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM cb_vector_document_chunk WHERE chunk_id = 28002);

INSERT INTO cb_vector_document_chunk (
  chunk_id, document_id, chunk_no, title, chunk_text, source_uri, embedding
)
SELECT 28003, 'knowledge-002', 1, 'MCP 도구 권한 설계',
       TO_CLOB('MCP 도구도 같은 역할과 기술 태그 규칙을 사용해 지식자료 검색 범위를 제한할 수 있다.'),
       'kb://platform/mcp-permission', TO_VECTOR('[0.80,0.70,0.60,0.50]')
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM cb_vector_document_chunk WHERE chunk_id = 28003);

INSERT INTO cb_vector_document_tag (chunk_id, tech_tag)
SELECT 28001, 'SPRING_BOOT' FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM cb_vector_document_tag WHERE chunk_id = 28001 AND tech_tag = 'SPRING_BOOT'
);
INSERT INTO cb_vector_document_tag (chunk_id, tech_tag)
SELECT 28001, 'ORDS' FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM cb_vector_document_tag WHERE chunk_id = 28001 AND tech_tag = 'ORDS'
);
INSERT INTO cb_vector_document_tag (chunk_id, tech_tag)
SELECT 28002, 'ORACLE_VPD' FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM cb_vector_document_tag WHERE chunk_id = 28002 AND tech_tag = 'ORACLE_VPD'
);
INSERT INTO cb_vector_document_tag (chunk_id, tech_tag)
SELECT 28002, 'ORDS' FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM cb_vector_document_tag WHERE chunk_id = 28002 AND tech_tag = 'ORDS'
);
INSERT INTO cb_vector_document_tag (chunk_id, tech_tag)
SELECT 28003, 'MCP' FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM cb_vector_document_tag WHERE chunk_id = 28003 AND tech_tag = 'MCP'
);

PROMPT === 3. Creating the tag-aware one-row-per-chunk protected view ===
CREATE OR REPLACE VIEW cb_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 === 4. Attaching dynamic VPD to the view ===
BEGIN
  DBMS_RLS.DROP_POLICY(
    object_schema => USER,
    object_name   => 'CB_VECTOR_SEARCH_DOCUMENTS',
    policy_name   => 'CB_VECTOR_TAG_SELECT_POLICY'
  );
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => USER,
    object_name     => 'CB_VECTOR_SEARCH_DOCUMENTS',
    policy_name     => 'CB_VECTOR_TAG_SELECT_POLICY',
    function_schema => USER,
    policy_function => 'CB_AGENT_DOC_VPD_FILTER',
    statement_types => 'SELECT',
    policy_type     => DBMS_RLS.DYNAMIC,
    enable          => TRUE
  );
END;
/

PROMPT === 5. Registering the protected object and its columns ===
DECLARE
  v_object_id NUMBER;

  PROCEDURE ensure_column(
      p_column_name       IN VARCHAR2,
      p_sensitive_yn      IN CHAR,
      p_sensitivity_level IN VARCHAR2,
      p_redaction_method  IN VARCHAR2
  ) IS
    v_count NUMBER;
    v_id    NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO   v_count
    FROM   cb_protected_column
    WHERE  object_id = v_object_id
    AND    column_name = p_column_name;

    IF v_count = 0 THEN
      SELECT NVL(MAX(column_id), 0) + 1
      INTO   v_id
      FROM   cb_protected_column;

      INSERT INTO cb_protected_column (
        column_id, object_id, column_name, sensitive_yn, sensitivity_level, redaction_method
      ) VALUES (
        v_id, v_object_id, p_column_name, p_sensitive_yn, p_sensitivity_level, p_redaction_method
      );
    ELSE
      UPDATE cb_protected_column
      SET    sensitive_yn = p_sensitive_yn,
             sensitivity_level = p_sensitivity_level,
             redaction_method = p_redaction_method
      WHERE  object_id = v_object_id
      AND    column_name = p_column_name;
    END IF;
  END;
BEGIN
  BEGIN
    SELECT object_id
    INTO   v_object_id
    FROM   cb_protected_object
    WHERE  object_name = 'CB_VECTOR_SEARCH_DOCUMENTS';

    UPDATE cb_protected_object
    SET    owner = USER,
           ords_path = 'cb-ords/cb-agent-vector/search',
           enabled_yn = 'Y',
           description = '기술 태그 권한이 적용된 벡터 청크 검색 전용 ORDS Handler'
    WHERE  object_id = v_object_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      SELECT NVL(MAX(object_id), 0) + 1
      INTO   v_object_id
      FROM   cb_protected_object;

      INSERT INTO cb_protected_object (
        object_id, owner, object_name, ords_path, enabled_yn, description
      ) VALUES (
        v_object_id, USER, 'CB_VECTOR_SEARCH_DOCUMENTS',
        'cb-ords/cb-agent-vector/search', 'Y',
        '기술 태그 권한이 적용된 벡터 청크 검색 전용 ORDS Handler'
      );
  END;

  -- Column policy here is only a display-policy hint. Row access is the TAG
  -- VPD rule above; the vector handler never returns EMBEDDING to the caller.
  ensure_column('CHUNK_ID', 'N', 'PUBLIC', 'NONE');
  ensure_column('DOCUMENT_ID', 'N', 'PUBLIC', 'NONE');
  ensure_column('CHUNK_NO', 'N', 'PUBLIC', 'NONE');
  ensure_column('TITLE', 'N', 'PUBLIC', 'NONE');
  ensure_column('CHUNK_TEXT', 'N', 'PUBLIC', 'NONE');
  ensure_column('SOURCE_URI', 'N', 'PUBLIC', 'NONE');
  ensure_column('TECH_TAG', 'N', 'PUBLIC', 'NONE');
  ensure_column('EMBEDDING', 'Y', 'RESTRICTED', 'NULLIFY');
END;
/

PROMPT === 6. Granting the ORDS runtime read access to the protected view ===
GRANT SELECT ON cb_vector_search_documents TO cb_ords;

COMMIT;

PROMPT === Vector/tag VPD setup complete ===
PROMPT Next: run 29_agent_ords_vector_search_ords.sql as CB_ORDS.
PROMPT Backoffice ingestion: /vector-knowledge (DEMO-4D or configured AI embeddings).
EXIT;
