declare
  cursor c_task_details is
  SELECT id, details FROM task
  WHERE details IS NOT NULL;

  temp_clob CLOB;
begin
	--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')
		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
	WHERE name NOT IN ('bond', 'system', 'computed.update', 'deployment.executor');
	COMMIT;

	--remove user photos
	EXECUTE IMMEDIATE 'TRUNCATE TABLE USERS_SMALL_PHOTO_BLOBS';
	COMMIT;
	EXECUTE IMMEDIATE '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;

	--rename projects
	UPDATE existing
	SET name = 'Project' || proj_id, key_name = 'K-' || proj_id
	WHERE name != 'codeBeamer Review Project';

	COMMIT;

	--remove jira synch
	EXECUTE IMMEDIATE '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;
  --
  for r_task_details in c_task_details loop
    begin
      dbms_lob.createtemporary(temp_clob, true);
      dbms_lob.append(temp_clob, 'Obfuscated details');
      for c in 1..floor(length(r_task_details.details)/4000) loop
        dbms_lob.append(temp_clob, DBMS_RANDOM.string('a',4000));
      end loop;
      if (MOD(length(r_task_details.details),4000) > 0)  then
        dbms_lob.append(temp_clob, DBMS_RANDOM.string('a',MOD(length(r_task_details.details),4000)));
      end if;

      UPDATE task
      SET details = temp_clob
      WHERE id = r_task_details.id;
      COMMIT;
      dbms_lob.freetemporary(temp_clob);
    exception
      when others then
        ROLLBACK;
        UPDATE task
        SET details = 'Obfuscated details'
        WHERE id = r_task_details.id;
        COMMIT;
    end;
  end loop;

	--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, 4) || ' :' || 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, 4) || ' :' || 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, 4) || ' :' || LENGTH(new_value))
				END)
			ELSE NULL
			END
			)
	WHERE label_id IN (3, 80) OR (label_id >= 10000 AND label_id <= 20000);

	COMMIT;

	--remove report jobs
	EXECUTE IMMEDIATE '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
	EXECUTE IMMEDIATE 'TRUNCATE TABLE application_configuration';

	COMMIT;

end;
/
