ai_logistic_outlook_integration/files/redaction_23ai.sql

 CREATE TABLE PROCESSED_EMAILS (
         ID NUMBER GENERATED ALWAYS AS IDENTITY,
         DATE_SENT TIMESTAMP,
         BOOKING VARCHAR2(100),
         EMAIL_FROM VARCHAR2(500),
         EMAIL_TO VARCHAR2(1000),
         SUBJECT VARCHAR2(1000),
         STATUS VARCHAR2(100),
         INSERT_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (ID)
     );

 ALTER TABLE PROCESSED_EMAILS ADD (
         BRIEF_DESCRIPTION VARCHAR2(1000),
         ALERT VARCHAR2(10),
         THREAD_INDEX NUMERIC(10),
         FILE_NAME VARCHAR(400)
               );

CREATE OR REPLACE VIEW PROCESSED_EMAILS_REDACTED AS
SELECT
----------------------------------------------------------------------
-- EMAIL_TO: remove names, parentheses, and mask all email addresses
----------------------------------------------------------------------
REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            -- 1️⃣ Remove names and content inside parentheses before the <email>
                                                                      EMAIL_TO,
'([[:alpha:]][[:alpha:] .''()_-]*)<([^>]+)>',
'<\2>'
),
-- 2️⃣ Mask email addresses
'([A-Za-z])([A-Za-z0-9._%+-]*)@([A-Za-z])([A-Za-z0-9.-]*)(\.[A-Za-z]{2,}(?:\.[A-Za-z]{2,})*)',
'\1******@\3******\5',
1, 0, 'i'
),
-- 3️⃣ Normalize ";" separators
'\s*;\s*', '; '
) AS EMAIL_TO,

----------------------------------------------------------------------
-- EMAIL_FROM: same treatment (remove names, parentheses, mask)
----------------------------------------------------------------------
REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            EMAIL_FROM,
            '([[:alpha:]][[:alpha:] .''()_-]*)<([^>]+)>',
            '<\2>'
        ),
        '([A-Za-z])([A-Za-z0-9._%+-]*)@([A-Za-z])([A-Za-z0-9.-]*)(\.[A-Za-z]{2,}(?:\.[A-Za-z]{2,})*)',
        '\1******@\3******\5',
        1, 0, 'i'
    ),
    '\s*;\s*', '; '
) AS EMAIL_FROM,

SUBJECT,

----------------------------------------------------------------------
-- Mask BOOKING (for example, a code or sensitive text)
----------------------------------------------------------------------
REGEXP_REPLACE(
    BOOKING,
    '([A-Za-z0-9])([A-Za-z0-9 _.-]*)',
    '***'
) AS BOOKING,

DATE_SENT,
STATUS,
FILE_NAME,
THREAD_INDEX,
INSERT_DATE,
BRIEF_DESCRIPTION,
ALERT
FROM PROCESSED_EMAILS;