--
--
--	database conversion
--
--	The general idea is to move data from the 2
--

--
--
--	Bug TRACKER
--
--
BEGIN;

--
--	set up bug ArtifactTypes for each group
--

UPDATE groups SET bug_due_period='2592000' WHERE bug_due_period is null;
INSERT INTO artifact_group_list 
(group_artifact_id,group_id,name,description,is_public,
allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
SELECT group_id+100000,group_id,'Bugs','Bug Tracking System',use_bugs,
1,send_all_bugs,new_bug_address,bug_due_period,1,1 
FROM groups 
WHERE status != 'I' AND status != 'P'
ORDER BY group_id ASC;

--
--	permissions
--
INSERT INTO artifact_perm
(group_artifact_id,user_id,perm_level)
SELECT group_id+100000,user_id,bug_flags 
FROM user_group;

--
--	bug groups conversion
--
INSERT INTO artifact_group (id,group_artifact_id,group_name)
SELECT bug_group_id+100000,group_id+100000,group_name FROM bug_group;

--
--	bug category conversion
--
INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
SELECT bug_category_id+100000,group_id+100000,category_name,100 FROM bug_category;

--
--	bug
--
--	bug tracker had status_id of 100 (None) and status_id=3 (closed)
--
UPDATE bug SET status_id=1 WHERE status_id=100;
INSERT INTO bug_status (status_id,status_name) VALUES (2,'Open');
UPDATE bug SET status_id=2 WHERE status_id=3;
DELETE FROM bug_status WHERE status_id=3;

UPDATE bug SET close_date=0 WHERE close_date is NULL;
UPDATE bug SET summary=0 WHERE summary is NULL;
UPDATE bug SET details='' WHERE details is NULL;

INSERT INTO artifact 
(artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
SELECT 
bug_id+100000,group_id+100000,status_id,category_id+100000,bug_group_id+100000,priority,
submitted_by,assigned_to,date,close_date,summary,details,resolution_id 
FROM bug 
ORDER BY group_id ASC;

--
--	bug_history
--
--UPDATE bug_history SET old_value=1 WHERE old_value='100' AND field_name='status_id';
UPDATE bug_history SET old_value=2 WHERE old_value='3' AND field_name='status_id';

--BEGIN;
--SELECT * from bug_history 
--WHERE NOT EXISTS (select bug_id FROM bug
--where bug.bug_id=bug_history.bug_id);
--COMMIT;

--DELETE FROM bug_history WHERE bug_id=0;

--DELETE FROM bug_history 
--WHERE bug_id+100000 NOT IN (SELECT artifact_id FROM artifact);

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
bug_id+100000,field_name,old_value,mod_by,date
FROM bug_history
WHERE field_name IN ('summary','resolution_id','priority','group_id','close_date','assigned_to','status_id');

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
bug_id+100000,'artifact_group_id',(old_value::int)+100000,mod_by,date
FROM bug_history
WHERE field_name='bug_group_id';

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
bug_id+100000,field_name,(old_value::int)+100000,mod_by,date
FROM bug_history
WHERE field_name='category_id';

--
--	bug messages
--
INSERT INTO artifact_message
(artifact_id,submitted_by,from_email,adddate,body)
SELECT
bh.bug_id+100000,bh.mod_by,users.email,bh.date,bh.old_value
FROM bug_history bh, users
WHERE bh.mod_by=users.user_id
AND bh.field_name='details';

--
--	bug canned responses
--
delete from bug_canned_responses where title is null;

INSERT INTO artifact_canned_responses
(group_artifact_id,title,body)
SELECT
group_id+100000,title,body
FROM bug_canned_responses
WHERE group_id > 0;

COMMIT;

--
--
--			SUPPORT
--
--

--
--      set up support ArtifactTypes for each group
--
BEGIN;

UPDATE groups SET support_due_period='2592000' WHERE support_due_period is null;

INSERT INTO artifact_group_list 
(group_artifact_id,group_id,name,description,is_public,
allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
SELECT group_id+200000,group_id,'Support Requests','Tech Support Tracking System',use_support,
1,send_all_support,new_support_address,support_due_period,0,2 
FROM groups  
WHERE status != 'I' AND status != 'P'
ORDER BY group_id ASC;

--
--      permissions
--
INSERT INTO artifact_perm
(group_artifact_id,user_id,perm_level)
SELECT group_id+200000,user_id,support_flags
FROM user_group;

--
--      support category conversion
--
INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
SELECT support_category_id+200000,group_id+200000,category_name,100 FROM support_category;

--
--	support
--
DELETE FROM support WHERE NOT EXISTS 
(SELECT group_id FROM groups WHERE support.group_id=groups.group_id);

UPDATE patch SET summary=0 WHERE summary is NULL;
UPDATE patch SET details='' WHERE details is NULL;

INSERT INTO artifact
(artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
SELECT 
support_id+200000,group_id+200000,support_status_id,support_category_id+200000,100,priority,
submitted_by,assigned_to,open_date,close_date,summary,'',100
FROM support
ORDER BY group_id ASC;

--
--	support_history
--
DELETE FROM support_history WHERE support_id=0;

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
support_id+200000,field_name,old_value,mod_by,date
FROM support_history
WHERE
field_name IN ('summary','priority','close_date','assigned_to');

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
support_id+200000,'category_id',(old_value::int)+200000,mod_by,date
FROM support_history
WHERE
field_name='support_category_id';

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
support_id+200000,'status_id',old_value,mod_by,date
FROM support_history
WHERE
field_name='support_status_id';

--
--      messages
--
DELETE FROM support_messages WHERE NOT EXISTS
(SELECT support_id FROM support WHERE support.support_id=support_messages.support_id);

INSERT INTO artifact_message
(artifact_id,submitted_by,from_email,adddate,body)
SELECT
support_id+200000,100,from_email,date,body
FROM support_messages;

--
--	canned messages
--
INSERT INTO artifact_canned_responses
(group_artifact_id,title,body)
SELECT
group_id+200000,title,body
FROM support_canned_responses 
WHERE group_id > 0;

COMMIT;


--
--
--      Patch Manager
--
--
--BEGIN; 

--
--      set up patch ArtifactTypes for each group
--
UPDATE groups SET patch_due_period='2592000' WHERE patch_due_period is null;

INSERT INTO artifact_group_list  
(group_artifact_id,group_id,name,description,is_public,
allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
SELECT group_id+300000,group_id,'Patches','Patch Tracking System',use_patch,
1,send_all_patches,new_patch_address,patch_due_period,1,3 
FROM groups  
WHERE status != 'I' AND status != 'P'
ORDER BY group_id ASC;

--
--      permissions
--
INSERT INTO artifact_perm
(group_artifact_id,user_id,perm_level)
SELECT group_id+300000,user_id,patch_flags
FROM user_group;

--
--      patch category conversion
--
INSERT INTO artifact_category (id,group_artifact_id,category_name,auto_assign_to)
SELECT patch_category_id+300000,group_id+300000,category_name,100 FROM patch_category;

--
--	patch table
--
--	moving the odd patch statuses to resolutions
--
ALTER TABLE patch ADD COLUMN resolution_id INT;
UPDATE patch SET resolution_id = 0;
ALTER TABLE patch ALTER COLUMN resolution_id SET NOT NULL;
ALTER TABLE patch ALTER COLUMN resolution_id SET DEFAULT 100;

UPDATE patch SET resolution_id=patch_status_id;
vacuum analyze patch;
update patch set patch_status_id=2 where patch_status_id > 3;
update patch set resolution_id=100 WHERE resolution_id < 4;
INSERT INTO artifact_resolution VALUES (102,'Accepted');
INSERT INTO artifact_resolution VALUES (103,'Out of Date');
INSERT INTO artifact_resolution VALUES (104,'Postponed');
INSERT INTO artifact_resolution VALUES (105,'Rejected');
update patch set resolution_id=104 WHERE resolution_id=4;
update patch set resolution_id=105 WHERE resolution_id=101;

delete from patch where patch_id=100000;
UPDATE patch SET details=' ' WHERE details is null;

INSERT INTO artifact
(artifact_id,group_artifact_id,status_id,category_id,artifact_group_id,priority,
submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id)
SELECT
patch_id+300000,group_id+300000,patch_status_id,patch_category_id+300000,100,5,
submitted_by,assigned_to,open_date,close_date,summary,details,resolution_id
FROM patch
ORDER BY group_id ASC;

--
--	patch history
--
INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
patch_id+300000,field_name,old_value,mod_by,date
FROM patch_history
WHERE field_name IN ('summary','close_date','assigned_to','Patch Code');

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
patch_id+300000,'status_id',old_value,mod_by,date
FROM patch_history
WHERE field_name='patch_status_id';

INSERT INTO artifact_history
(artifact_id,field_name,old_value,mod_by,entrydate)
SELECT
patch_id+300000,'category_id',(old_value::int)+300000,mod_by,date
FROM patch_history
WHERE field_name='patch_category_id';

--
--      patch messages
--
INSERT INTO artifact_message
(artifact_id,submitted_by,from_email,adddate,body)
SELECT
ph.patch_id+300000,ph.mod_by,users.email,ph.date,ph.old_value
FROM patch_history ph, users
WHERE ph.mod_by=users.user_id
AND ph.field_name='details';

--
--	patch code
--
INSERT INTO artifact_file
(artifact_id,description,bin_data,filename,filesize,filetype,adddate,submitted_by)
SELECT patch_id+300000,'None',code,'None',length(code),'text/plain',open_date,submitted_by
FROM patch 
WHERE code IS NOT NULL;

--COMMIT;

INSERT INTO artifact_counts_agg
SELECT group_artifact_id,count(*)
FROM artifact
WHERE status_id <> 3
GROUP BY group_artifact_id;

--
--
--	Feature Requests
--
--
INSERT INTO artifact_group_list
(group_artifact_id,group_id,name,description,is_public,
allow_anon,email_all_updates,email_address,due_period,use_resolution,datatype)
SELECT group_id+350000,group_id,'Feature Requests','Feature Request Tracking System',1,
1,0,'',45*24*60*60,0,4 
FROM groups  
WHERE status != 'I' AND status != 'P'
ORDER BY group_id ASC;

vacuum analyze artifact_perm;
vacuum analyze artifact_group_list;
vacuum analyze artifact;
vacuum analyze artifact_history;
vacuum analyze artifact_category;
vacuum analyze artifact_group;
vacuum analyze artifact_file;
vacuum analyze artifact_message;