#drop all constraints since it is simpler to get it right
alter table COMMENT drop constraint FK63717A3F1811CB9D;
alter table COMMENT drop constraint FK63717A3F5E1A5F2E;
alter table COMMENT_FIELD drop constraint FKADAF9ADAA18837F5;
alter table COMMENT_FIELD drop constraint FKADAF9ADA999D94C6;
alter table COMPLETED_FRX drop constraint FKC51038F8BBB0C6CE;
alter table COMPLETED_FRX drop constraint FKC51038F8D91F70A1;
alter table DEFECT drop constraint FK77F6BD71E71841CE;
alter table DEFECT drop constraint FK77F6BD71ABA6D25E;
alter table DEFECT_COMMENT drop constraint FKA1FDD715E1A5F2E;
alter table DEFECT_COMMENT drop constraint FKA1FDD71999D94C6;
alter table DEFECT_FIELD drop constraint FK20EE308C5E1A5F2E;
alter table DEFECT_FIELD drop constraint FK20EE308CA18837F5;
alter table FR_EXTRA drop constraint FKFF633EBDE71841CE;
alter table FR_EXTRA drop constraint FKFF633EBD89A8D6E2;
alter table FR_EXTRA drop constraint FKFF633EBD44B2EB10;
alter table NOTIFICATION drop constraint FKAD9970EBBE1FB2F1;
alter table NOTIFICATION drop constraint FKAD9970EB92041DC4;
alter table RECIPIENT drop constraint FKD6F981F9210DC959;
alter table RECIPIENT drop constraint FKD6F981F9FAA6A011;
alter table REPLY drop constraint FK4A41ACA999D94C6;
alter table REPLY drop constraint FK4A41ACA4A6A205B;
alter table REVIEW drop constraint FK8FE1EB585E255DBA;
alter table REVIEW_COMMENT drop constraint FKD84E3458E71841CE;
alter table REVIEW_COMMENT drop constraint FKD84E3458999D94C6;
alter table REVIEW_FIELD drop constraint FK90C31533E71841CE;
alter table REVIEW_FIELD drop constraint FK90C31533A18837F5;
alter table REVIEW_PARTICIPANT drop constraint FK936188AC210DC959;
alter table REVIEW_PARTICIPANT drop constraint FK936188ACE71841CE;
alter table REVISION_COMMENT drop constraint FKD00FBDDBD9181D66;
alter table REVISION_COMMENT drop constraint FKD00FBDDB9CAB902A;
alter table REVISION_COMMENT drop constraint FKD00FBDDBD91F70A1;

#drop all indexes since it is simpler to get it right
drop index idx_comment_defect if exists;
drop index idx_comment_deleted if exists;
drop index idx_comment_draft if exists;
drop index idx_comment_user if exists;
drop index idx_comment_defectApproved if exists;
drop index idx_comment_defectRaised if exists;
drop index idx_defect_fureview if exists;
drop index idx_defect_review if exists;
drop index idx_field_name if exists;
drop index idx_field_scope if exists;
drop index idx_frx_fromfr if exists;
drop index idx_frx_fr if exists;
drop index idx_frx_review if exists;
drop index notification_ref1_idx if exists;
drop index notification_ref2_idx if exists;
drop index notifiaction_review_idx if exists;
drop index recipient_email_idx if exists;
drop index recipient_uiack_idx if exists;
drop index fr_revs_idx if exists;
drop index fr_result_idx if exists;
drop index fr_source_idx if exists;
drop index fr_path_idx if exists;
drop index idx_rc_revision if exists;
drop index idx_rc_comment if exists;
drop index idx_rc_frx if exists;
drop index scl_review_idx if exists;
drop index idx_user_name if exists;

#create the new tables

create table NEW_REVIEW (REVIEW_ID integer not null, name varchar(255), repoName varchar(255), state varchar(255), description varchar(255), summary varchar(255), CREATE_DATE timestamp, metricsVersion integer, parentReview integer, creator integer, primary key (REVIEW_ID));
insert into NEW_REVIEW (REVIEW_ID, name, repoName, state, description, summary, CREATE_DATE, metricsVersion, parentReview, creator)
  select REVIEW_ID, name, repoName, state, description, summary, CREATE_DATE, metricsVersion, null, creator
  from REVIEW;

#set the parent review from defects the simplest way
update NEW_REVIEW set parentReview =
  (select limit 0 1 d.REVIEW_ID from NEW_REVIEW r, DEFECT d
   where d.FU_REVIEW_ID = NEW_REVIEW.REVIEW_ID order by r.REVIEW_ID);

#create review comments from defects ignoring defect fields
$max = select COALESCE(max(COMMENT_ID)+1,0) from COMMENT;
create sequence temp start with $max;

create table TEMP_COMMENT (COMMENT_ID integer, CREATE_DATE timestamp, message longvarchar, draft bit, deleted bit, defectRaised bit, defectApproved bit, USER_ID integer, REVIEW_ID integer, DEFECT_ID integer);

#make comments from defects
insert into TEMP_COMMENT (COMMENT_ID, CREATE_DATE, message, draft, deleted, defectRaised, defectApproved, USER_ID, REVIEW_ID, DEFECT_ID)
  select next value for temp, d.CREATE_DATE, '(Created from defect DF-'||d.DEFECT_ID||') Title: '||d.title||', Description: '||d.summary, false, false, true, true, p.user, d.REVIEW_ID,d.DEFECT_ID
  from DEFECT d, REVIEW r, REVIEW_PARTICIPANT p
  where r.REVIEW_ID = d.REVIEW_ID and r.REVIEW_ID = p.REVIEW_ID and p.moderator = true;

#create the comments
insert into COMMENT (COMMENT_ID, CREATE_DATE, message, draft, deleted, defectRaised, defectApproved, USER_ID)
  select COMMENT_ID, CREATE_DATE, message, draft, deleted, defectRaised, defectApproved, USER_ID
  from TEMP_COMMENT;

#link the comments to the review
insert into REVIEW_COMMENT (REVIEW_ID, COMMENT_ID) select REVIEW_ID, COMMENT_ID from TEMP_COMMENT;

#link defect comments as review comments, thier replies will follow
insert into REVIEW_COMMENT (REVIEW_ID, COMMENT_ID) select t.REVIEW_ID, c.COMMENT_ID
  from TEMP_COMMENT t, DEFECT d, DEFECT_COMMENT c
  where t.DEFECT_ID = d.DEFECT_ID and d.DEFECT_ID = c.DEFECT_ID;

#clean up
drop sequence temp;
drop table TEMP_COMMENT;

#replace old review table
drop table REVIEW if exists;
alter table NEW_REVIEW rename to REVIEW;

#drop author and moderator cols from review
alter table COMMENT DROP COLUMN DEFECT_ID;

#drop defect tables
drop table DEFECT if exists;
drop table DEFECT_COMMENT if exists;
drop table DEFECT_FIELD if exists;

#create new constraints and indexes
create index idx_comment_deleted on COMMENT (deleted);
create index idx_comment_draft on COMMENT (draft);
create index idx_comment_user on COMMENT (USER_ID);
create index idx_comment_defectApproved on COMMENT (defectApproved);
create index idx_comment_defectRaised on COMMENT (defectRaised);
alter table COMMENT add constraint FK63717A3F1811CB9D foreign key (USER_ID) references USER;
alter table COMMENT_FIELD add constraint FKADAF9ADAA18837F5 foreign key (FIELD_ID) references FIELD;
alter table COMMENT_FIELD add constraint FKADAF9ADA999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table COMPLETED_FRX add constraint FKC51038F8BBB0C6CE foreign key (PARTICIPANT_ID) references REVIEW_PARTICIPANT;
alter table COMPLETED_FRX add constraint FKC51038F8D91F70A1 foreign key (FRX_ID) references FR_EXTRA;
create index idx_field_name on FIELD (name);
create index idx_field_scope on FIELD (fieldScope);
create index idx_frx_fromfr on FR_EXTRA (fromRevision);
create index idx_frx_fr on FR_EXTRA (fileRevision);
create index idx_frx_review on FR_EXTRA (REVIEW_ID);
alter table FR_EXTRA add constraint FKFF633EBDE71841CE foreign key (REVIEW_ID) references REVIEW;
alter table FR_EXTRA add constraint FKFF633EBD89A8D6E2 foreign key (fileRevision) references REVISION;
alter table FR_EXTRA add constraint FKFF633EBD44B2EB10 foreign key (fromRevision) references REVISION;
create index notification_ref1_idx on NOTIFICATION (ref1);
create index notification_ref2_idx on NOTIFICATION (ref2);
create index notifiaction_review_idx on NOTIFICATION (review);
alter table NOTIFICATION add constraint FKAD9970EBBE1FB2F1 foreign key (actioner) references USER;
alter table NOTIFICATION add constraint FKAD9970EB92041DC4 foreign key (review) references REVIEW;
create index recipient_email_idx on RECIPIENT (email);
create index recipient_uiack_idx on RECIPIENT (uiACK);
alter table RECIPIENT add constraint FKD6F981F9210DC959 foreign key (user) references USER;
alter table RECIPIENT add constraint FKD6F981F9FAA6A011 foreign key (NOTIFICATION_ID) references NOTIFICATION;
alter table REPLY add constraint FK4A41ACA999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table REPLY add constraint FK4A41ACA4A6A205B foreign key (REPLY_ID) references COMMENT;
alter table REVIEW add constraint FK8FE1EB585E255DBA foreign key (creator) references USER;
alter table REVIEW add constraint FK8FE1EB583D2C7EEE foreign key (parentReview) references REVIEW;
alter table REVIEW_COMMENT add constraint FKD84E3458E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table REVIEW_COMMENT add constraint FKD84E3458999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table REVIEW_FIELD add constraint FK90C31533E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table REVIEW_FIELD add constraint FK90C31533A18837F5 foreign key (FIELD_ID) references FIELD;
alter table REVIEW_PARTICIPANT add constraint FK936188AC210DC959 foreign key (user) references USER;
alter table REVIEW_PARTICIPANT add constraint FK936188ACE71841CE foreign key (REVIEW_ID) references REVIEW;
create index fr_revs_idx on REVISION (revision);
create index fr_result_idx on REVISION (reviewResult);
create index fr_source_idx on REVISION (source);
create index fr_path_idx on REVISION (path);
create index idx_rc_revision on REVISION_COMMENT (revision);
create index idx_rc_comment on REVISION_COMMENT (comment);
create index idx_rc_frx on REVISION_COMMENT (FRX_ID);
alter table REVISION_COMMENT add constraint FKD00FBDDBD9181D66 foreign key (revision) references REVISION;
alter table REVISION_COMMENT add constraint FKD00FBDDB9CAB902A foreign key (comment) references COMMENT;
alter table REVISION_COMMENT add constraint FKD00FBDDBD91F70A1 foreign key (FRX_ID) references FR_EXTRA;
create index scl_review_idx on STATE_CHANGE (reviewId);
create index idx_user_name on USER (userName);

# and finally update the db version
UPDATE CRUVERSION SET version = 12 WHERE version_id = 1;
UPDATE METRIC_DEFINITION set xml = '<metrics version="1" xmlns="http://www.cenqua.com/crucible/metrics-1">\u000a    <comment>\u000a        <field type="INTEGER">\u000a            <name>classification</name>\u000a            <label>Classification</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Missing">1</namedValue>\u000a            <namedValue name="Extra (superfluous)">2</namedValue>\u000a            <namedValue name="Ambiguous">3</namedValue>\u000a            <namedValue name="Inconsistent">4</namedValue>\u000a            <namedValue name="Improvement desirable">5</namedValue>\u000a            <namedValue name="Not conforming to standards">6</namedValue>\u000a            <namedValue name="Risk-prone">7</namedValue>\u000a            <namedValue name="Factually incorrect">8</namedValue>\u000a            <namedValue name="Not implementable">9</namedValue>\u000a            <namedValue name="Editorial">10</namedValue>\u000a            <defaultValue>Improvement desirable</defaultValue>\u000a        </field>\u000a        <field type="BOOLEAN">\u000a            <name>rank</name>\u000a            <label>Ranking</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Major">true</namedValue>\u000a            <namedValue name="Minor">false</namedValue>\u000a            <defaultValue>Minor</defaultValue>\u000a        </field>\u000a    </comment>\u000a</metrics>' where METRIC_ID =1;
