#first drop changed constraints on the existing tables
#
alter table AI_ITR_COMMENT drop constraint FK42D630F0F020303B;
alter table AI_ITR_COMMENT drop constraint FK42D630F0999D94C6;
alter table NOTIFICATION drop constraint FKAD9970EBBE1FB2F1;
alter table NOTIFICATION drop constraint FKAD9970EB92041DC4;
alter table RECIPIENT drop constraint FKD6F981F9210DC959;
alter table RECIPIENT drop constraint FKD6F981F931D229EE;
drop index recipient_email_idx if exists;
drop index recipient_uiack_idx if exists;
#
# change AI_ITR_COMMENT to AI_REVIEW_COMMENT
#
alter table AI_ITR_COMMENT rename to AI_REVIEW_COMMENT;
#
# Add columns to crucible user
alter table USER ADD COLUMN dontSendMeMyNotifications bit;
alter table USER ADD COLUMN stateChangeEmailNotification integer;
alter table USER ADD COLUMN commentEmailNotification integer;
alter table USER ADD COLUMN replyEmailNotification integer;
alter table USER ADD COLUMN generalEmailNotification integer;
alter table USER ADD COLUMN completeEmailNotification integer;
#
# Now set default values for those columns
#
update USER SET dontSendMeMyNotifications = 'TRUE';
update USER SET stateChangeEmailNotification = 1;
update USER SET commentEmailNotification = 1;
update USER SET replyEmailNotification = 1;
update USER SET generalEmailNotification = 1;
update USER SET completeEmailNotification = 1;
#
# create new notifications table, re-populate drop the old one rename the new
#
create table TEMP (NOTIFICATION_ID integer not null, NOTE_TYPE varchar(255) not null, CREATE_DATE timestamp, actioner integer, note varchar(255), review integer, ref1 integer, ref2 integer, primary key (NOTIFICATION_ID));
insert into TEMP (NOTIFICATION_ID, NOTE_TYPE, CREATE_DATE, actioner, note, review, ref1, ref2) select note.NOTIFICATION_ID, note.type, note.CREATE_DATE, note.actioner, note.note, note.review, note.ref1, note.ref2 from NOTIFICATION note;
drop table NOTIFICATION if exists;
alter table TEMP rename to NOTIFICATION;
#
# Now just add new constraints and indexes
#
alter table AI_REVIEW_COMMENT add constraint FKFE74454F999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table AI_REVIEW_COMMENT add constraint FKFE74454FF020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table NOTIFICATION add constraint FKAD9970EBBE1FB2F1 foreign key (actioner) references USER;
alter table NOTIFICATION add constraint FKAD9970EB92041DC4 foreign key (review) references REVIEW;
create index notification_ref1_idx on NOTIFICATION (ref1);
create index notification_ref2_idx on NOTIFICATION (ref2);
create index notifiaction_review_idx on NOTIFICATION (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 FKD6F981F931D229EE foreign key (NOTIFICATION_ID) references NOTIFICATION;
#
# and finally update the db version
#
UPDATE CRUVERSION SET version = 5 WHERE version_id = 1;