/*Audit trail logs*/
BEGIN
  DECLARE
    obfuscate_audit_trial BOOLEAN DEFAULT FALSE;
    v_name                VARCHAR2(50);
    v_id                  NUMBER(10);
    CURSOR c_users IS SELECT id, name FROM users;
  BEGIN
    IF obfuscate_audit_trial
    THEN
      OPEN c_users;
      LOOP
        FETCH c_users INTO v_id, v_name;
        EXIT WHEN c_users%NOTFOUND;

        -- "bond's personal wiki"
        UPDATE audit_trail_logs
        SET details = REPLACE(details, '"' || v_name || '''s Personal Wiki"', '"user-' || v_id || '''s Personal Wiki"');

        -- "bond [1]"
        UPDATE audit_trail_logs
        SET details = REPLACE(details, '"' || v_name || ' [' || v_id || ']"', '"user-' || v_id || ' [' || v_id || ']"');

        -- {"name":"bond","id":1}
        UPDATE audit_trail_logs
        SET details = REPLACE(details, '{"name":"' || v_name || '","id":' || v_id || '}',
                              '{"name":"user-' || v_id || '", "id":' || v_id || '}');

        -- {"id":1,"name":"bond"}
        UPDATE audit_trail_logs
        SET details = REPLACE(details, '{"id":' || v_id || ',"name":"' || v_name || '"}',
                              '{"id":' || v_id || ',"name":"user-' || v_id || '"}');
        COMMIT;
      END LOOP;
      CLOSE c_users;
    END IF;
  END;
END;
/

/*obfuscate acl role*/
UPDATE acl_role
SET name        = id,
    description = NULL
WHERE name <> 'codeBeamer Review Project Review Role'
  AND name <> 'Project Admin'
  AND name <> 'Developer'
  AND name <> 'Stakeholder';
COMMIT;

/*object_reference*/
UPDATE object_reference
SET url = 'file://' || from_id
WHERE url LIKE 'file://%';
UPDATE object_reference
SET url = 'mailto:' || from_id || '@testemail.testemail'
WHERE url LIKE 'mailto:%';
UPDATE object_reference
SET url = '/' || from_id
WHERE url LIKE '/%';
COMMIT;

/*remove all file content except: vintage reports, calendar, work calendars*/
DELETE
FROM object_revision_blobs rb
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = rb.id
               AND o.type_id NOT IN (5, 9, 10));
COMMIT;

/*update name of artifacts except: calendars, work calendars, roles, groups, member group, state transition, field definitions, choice option, release rank, review config, review tracker*/
UPDATE object_revision r
SET r.name = r.object_id || '-artifact ' || substr(r.name, 1, 4) || ' :' || LENGTH(r.name)
WHERE r.name NOT IN ('codeBeamer Review Project Review Tracker',
                     'codeBeamer Review Project Review Item Tracker',
                     'codeBeamer Review Project Review Config Template Tracker')
  AND EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id NOT IN (9, 10, 17, 18, 19, 21, 23, 25, 26, 33, 35));
COMMIT;

/*update description of artifacts, except: calendar, work calendar*/
UPDATE object_revision r
SET r.description = REGEXP_REPLACE(r.description, '"description":\s*"((\\"|[^"])*)"',
                                   '"description":"Obfuscated description' ||
                                   dbms_random.string('a', 120) || '"')
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id NOT IN (9, 10));
COMMIT;

/*update key, category of projects and trackers*/
UPDATE object_revision r
SET r.description = REGEXP_REPLACE(
                      REGEXP_REPLACE(r.description, '"keyName":"[^"]*"', '"keyName":"K-' || r.proj_id || '"'),
                      '"category":"[^"]*"', '"category":"TestCategory"')
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id IN (22, 16));
COMMIT;

/*delete simple comment message*/
UPDATE object_revision r
SET r.description = 'Obfuscated description'
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id IN (13, 15))
  AND TO_CHAR(substr(r.description, 1, 1)) != '{';

/*delete description of : file, folder, baseline*/
UPDATE object_revision r
SET r.description = NULL
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id IN (1, 2, 12, 34));
COMMIT;

/*update user data*/
UPDATE users
SET name               = 'user-' || id,
    passwd             = NULL,
    hostname           = NULL,
    firstname          = 'First-' || id,
    lastname           = 'Last-' || id,
    title              = NULL,
    address            = NULL,
    zip                = NULL,
    city               = NULL,
    state              = NULL,
    country            = NULL,
    language           = NULL,
    geo_country        = NULL,
    geo_region         = NULL,
    geo_city           = NULL,
    geo_latitude       = NULL,
    geo_longitude      = NULL,
    source_of_interest = NULL,
    scc                = NULL,
    team_size          = NULL,
    division_size      = NULL,
    company            = NULL,
    email              = 'user' || id || '@testemail.testemail',
    email_client       = NULL,
    phone              = NULL,
    mobil              = NULL,
    skills             = NULL,
    unused0            = NULL,
    unused1            = NULL,
    unused2            = NULL,
    referrer_url       = NULL
WHERE name NOT IN ('system', 'computed.update', 'deployment.executor');
COMMIT;

/*remove user photos*/
TRUNCATE TABLE users_small_photo_blobs;
COMMIT;
TRUNCATE TABLE users_large_photo_blobs;
COMMIT;

/*remove user preferences: DOORS_BRIDGE_LOGIN(63),JIRA_SERVER_LOGIN(67),SLACK_USER_ID(2001),SLACK_USER_TOKEN(2002)*/
DELETE
FROM user_pref
WHERE pref_id IN (63, 67, 2001, 2002);
COMMIT;

/*remove user keys*/
TRUNCATE TABLE user_key;
COMMIT;

/*rename projects*/
UPDATE existing
SET name     = 'Project' || proj_id,
    key_name = 'K-' || proj_id
WHERE name <> 'codeBeamer Review Project';
COMMIT;

/*remove jira synch*/
TRUNCATE TABLE object_job_schedule;
COMMIT;

/*update task summary and description*/
UPDATE task
SET summary = 'Task' || id || ' ' || substr(summary, 1, 4) || ' :' || LENGTH(summary)
WHERE summary IS NOT NULL;
COMMIT;

UPDATE task
SET details = TO_CHAR(LENGTH(details))
WHERE details IS NOT NULL;
COMMIT;

/*UPDATE custom field value (not choice data)*/
UPDATE task_field_value
SET field_value = (
    CASE
      WHEN TRIM(TRANSLATE(substr(field_value, 1, 100), '0123456789-,.', ' ')) IS NULL
              THEN '1'
      ELSE TO_CHAR(substr(field_value, 1, 2) || ' :' || LENGTH(field_value))
        END)
WHERE field_value IS NOT NULL;
COMMIT;

/*UPDATE summary, description and custom field value*/
UPDATE task_field_history
SET old_value = (
    CASE
      WHEN old_value IS NOT NULL THEN (
        CASE
          WHEN TRIM(TRANSLATE(substr(old_value, 1, 100), '0123456789-,.', ' ')) IS NULL
                  THEN TO_CHAR(revision - 1)
          ELSE TO_CHAR(substr(old_value, 1, 2) || ' :' || LENGTH(old_value))
            END)
      ELSE NULL END
    ),
    new_value = (
        CASE
          WHEN new_value IS NOT NULL THEN (
            CASE
              WHEN TRIM(TRANSLATE(substr(new_value, 1, 100), '0123456789-,.', ' ')) IS NULL
                      THEN TO_CHAR(revision)
              ELSE TO_CHAR(substr(new_value, 1, 2) || ' :' || LENGTH(new_value))
                END)
          ELSE NULL END
        )
WHERE label_id IN (3, 80)
   OR (label_id >= 10000 AND label_id <= 20000);
COMMIT;

/*TASK_TYPE reduce prefix to 2 characters*/
UPDATE task_type
SET prefix = substr(prefix, 1, 2);
COMMIT;

/*remove report jobs*/
TRUNCATE TABLE object_quartz_schedule;
COMMIT;

/*UPDATE tag name*/
UPDATE label
SET name = 'LABEL' || id
WHERE name NOT IN ('FINISHED_TESTRUN_GENERATION');
COMMIT;

UPDATE workingset
SET name        = 'WS-' || id,
    description = NULL
WHERE name != 'member';
COMMIT;

/*remove stored configs*/
TRUNCATE TABLE application_configuration;
COMMIT;