DELIMITER //
DROP PROCEDURE IF EXISTS replace_obfuscated_user;
//
CREATE PROCEDURE replace_obfuscated_user()
  BEGIN
    DECLARE obfuscate_audit_trial BOOLEAN DEFAULT FALSE;

    DECLARE v_name VARCHAR(255);
    DECLARE v_id INT;
    DECLARE v_result INT DEFAULT 0;

    DECLARE c_user CURSOR FOR SELECT id, name FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_result = 1;

    IF obfuscate_audit_trial
    THEN

      OPEN c_user;

      replace_user: LOOP

        FETCH c_user
        INTO v_id, v_name;

        IF v_result = 1
        THEN
          LEAVE replace_user;
        END IF;

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

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

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

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

      END LOOP replace_user;

      CLOSE c_user;
    END IF;

  END;
//
DROP FUNCTION IF EXISTS translate//
CREATE FUNCTION translate(
  tar VARCHAR(255),
  ori VARCHAR(255),
  rpl VARCHAR(255)
)
  RETURNS VARCHAR(255) CHARSET utf8mb4 DETERMINISTIC BEGIN

  DECLARE i INT UNSIGNED DEFAULT 0;
  DECLARE cur_char CHAR(1);
  DECLARE ori_idx INT UNSIGNED;
  DECLARE result VARCHAR(255);

  SET result = '';

  WHILE i <= length(tar) DO
    SET cur_char = mid(tar, i, 1);
    SET ori_idx = INSTR(ori, cur_char);
    SET result = concat(
        result,
        REPLACE(
            cur_char,
            mid(ori, ori_idx, 1),
            mid(rpl, ori_idx, 1)
        ));
    SET i = i + 1;
  END WHILE;
  RETURN result;
END
//
DROP FUNCTION IF EXISTS random_string//
CREATE FUNCTION random_string(p_length BIGINT)
  RETURNS MEDIUMTEXT CHARSET utf8
  BEGIN
    SET @returnstr = '';
    SET @allowedchars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @i = 0;

    WHILE (@i < p_length) DO
      SET @returnstr = CONCAT(@returnstr, substring(@allowedchars, FLOOR(RAND() * LENGTH(@allowedchars) + 1), 1));
      SET @i = @i + 1;
    END WHILE;

    RETURN @returnstr;
  END
//
DELIMITER ;


/*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';

/*object_reference*/
UPDATE object_reference
SET url = concat('file://', from_id)
WHERE url LIKE 'file://%';
UPDATE object_reference
SET url = concat('mailto:', from_id, '@testemail.testemail')
WHERE url LIKE 'mailto:%';
UPDATE object_reference
SET url = concat('/', from_id)
WHERE url LIKE '\/%';


COMMIT;

/*remove all file content except: vintage reports, calendar, work calendars*/
DELETE rb
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, review config template tracker*/
UPDATE object_revision r
SET r.name = concat(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 = JSON_REPLACE(r.description,
                                 '$.description',
                                 concat('Obfuscated description',
                                        random_string(LENGTH(JSON_EXTRACT(r.description, '$.description')) - 23)))
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id NOT IN (9, 10))
  AND JSON_VALID(r.description);

COMMIT;

/*update key, category of projects and trackers*/
UPDATE object_revision r
SET r.description = JSON_REPLACE(r.description,
                                 '$.keyName',
                                 concat('K-', r.proj_id),
                                 '$.category',
                                 'TestCategory')
WHERE EXISTS(SELECT 1
             FROM object o
             WHERE o.id = r.object_id
               AND o.type_id IN (22, 16))
  AND JSON_VALID(r.description);

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 NOT JSON_VALID(r.description);

/*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;

CALL replace_obfuscated_user();

/*update user data*/
UPDATE users
SET name               = concat('user-', id),
    passwd             = NULL,
    hostname           = NULL,
    firstname          = concat('First-', id),
    lastname           = concat('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              = concat('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     = concat('Project', proj_id),
    key_name = concat('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 = concat('Task', id, ' ', substr(summary, 1, 4), ' :', LENGTH(summary))
WHERE summary IS NOT NULL;
COMMIT;

UPDATE task
SET details = CONVERT(LENGTH(details), CHAR)
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 concat(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 revision - 1
          ELSE concat(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 revision
              ELSE concat(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 = concat('LABEL', id)
WHERE name NOT IN ('FINISHED_TESTRUN_GENERATION');
COMMIT;

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

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


DELIMITER //
DROP FUNCTION IF EXISTS translate//
DROP FUNCTION IF EXISTS random_string//
DROP PROCEDURE replace_obfuscated_user;//
DELIMITER ;