#drop all constraints since it is simpler to get it right
alter table ACTIVE_REVIEWER drop constraint FK6381431EE71841CE;
alter table ACTIVE_REVIEWER drop constraint FK6381431E1811CB9D;
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_REVIEWER drop constraint FK4B1D6999E71841CE;
alter table COMPLETED_REVIEWER drop constraint FK4B1D69991811CB9D;
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 FK8FE1EB58CD03FF19;
alter table REVIEW drop constraint FK8FE1EB585E255DBA;
alter table REVIEW drop constraint FK8FE1EB58A9598293;
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 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, review and review_participant
create table REVIEW_PARTICIPANT (PARTICIPANT_ID integer not null, REVIEW_ID integer not null, user integer, author bit, moderator bit, reviewer bit, allComplete bit, LAST_READ timestamp, primary key (PARTICIPANT_ID), unique (REVIEW_ID, user));

#populate the tables
create sequence temp;

#first insert all author/moderators then just authors then just moderators
insert into REVIEW_PARTICIPANT (PARTICIPANT_ID, REVIEW_ID, user, author, moderator, reviewer, allComplete, LAST_READ) (select next value for temp, REVIEW_ID, author, true, true, false, false, CURRENT_TIMESTAMP from REVIEW where author = moderator);
insert into REVIEW_PARTICIPANT (PARTICIPANT_ID, REVIEW_ID, user, author, moderator, reviewer, allComplete, LAST_READ) (select next value for temp, REVIEW_ID, author, true, false, false, false, CURRENT_TIMESTAMP from REVIEW where author != moderator);
insert into REVIEW_PARTICIPANT (PARTICIPANT_ID, REVIEW_ID, user, author, moderator, reviewer, allComplete, LAST_READ) (select next value for temp, REVIEW_ID, moderator, false, true, false, false, CURRENT_TIMESTAMP from REVIEW where moderator != author);

#get active and completed reviewers
insert into REVIEW_PARTICIPANT (PARTICIPANT_ID, REVIEW_ID, user, author, moderator, reviewer, allComplete, LAST_READ) (select next value for temp, ar.REVIEW_ID, ar.USER_ID, false, false, true, false, CURRENT_TIMESTAMP from ACTIVE_REVIEWER ar, REVIEW r where ar.REVIEW_ID = r.REVIEW_ID and ar.USER_ID != r.author and ar.USER_ID != r.moderator);
insert into REVIEW_PARTICIPANT (PARTICIPANT_ID, REVIEW_ID, user, author, moderator, reviewer, allComplete, LAST_READ) (select next value for temp, cr.REVIEW_ID, cr.USER_ID, false, false, true, true, CURRENT_TIMESTAMP from COMPLETED_REVIEWER cr, REVIEW r where cr.REVIEW_ID = r.REVIEW_ID and cr.USER_ID != r.author and cr.USER_ID != r.moderator);

drop sequence temp;

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

#drop active and complete review tables
drop table ACTIVE_REVIEWER if exists;
drop table COMPLETED_REVIEWER if exists;

#create new completed frx table (empty for now)
create table COMPLETED_FRX (PARTICIPANT_ID integer not null, FRX_ID integer not null, primary key (PARTICIPANT_ID, FRX_ID));

#create new constraints and indexes
create index idx_comment_defect on COMMENT (DEFECT_ID);
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 add constraint FK63717A3F5E1A5F2E foreign key (DEFECT_ID) references DEFECT;
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_defect_fureview on DEFECT (FU_REVIEW_ID);
create index idx_defect_review on DEFECT (REVIEW_ID);
alter table DEFECT add constraint FK77F6BD71E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table DEFECT add constraint FK77F6BD71ABA6D25E foreign key (FU_REVIEW_ID) references REVIEW;
alter table DEFECT_COMMENT add constraint FKA1FDD715E1A5F2E foreign key (DEFECT_ID) references DEFECT;
alter table DEFECT_COMMENT add constraint FKA1FDD71999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table DEFECT_FIELD add constraint FK20EE308C5E1A5F2E foreign key (DEFECT_ID) references DEFECT;
alter table DEFECT_FIELD add constraint FK20EE308CA18837F5 foreign key (FIELD_ID) references FIELD;
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_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 = 11 WHERE version_id = 1;
