/*remove all file content except: vintage reports*/
UPDATE object_revision r
SET r.file_content = null
WHERE EXISTS(SELECT 1 FROM object o WHERE o.id = r.object_id AND o.type_id NOT IN (5));

COMMIT;

/*update name of artifacts except: 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'
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 (17, 18, 19, 21, 23, 25, 26, 33, 35));
	
COMMIT;

/*update description of artifacts*/
UPDATE object_revision r
SET r.description = REGEXP_REPLACE (r.description, '"description":"[^"]*"', '"description":"Obfuscated description"');

/*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', 
	lastname = 'First', 
	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,
	photo_small = null,
	photo_large = null;
	
COMMIT;
	
/*remove user preferences*/
TRUNCATE TABLE user_pref;

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, details = 'Description' || id;

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 'Test string'
		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 'Old Test string'
			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 'New Test string'
			END)
		ELSE NULL
		END
		)
WHERE label_id IN (3, 80) OR (label_id >= 10000 AND label_id <= 20000);

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;
