#first drop changed constraints on the existing tables
#
alter table ACTION_ITEM drop constraint FKE077AB7CE71841CE;
alter table AI_COMMENT drop constraint FK3E2A8128999D94C6;
alter table AI_COMMENT drop constraint FK3E2A8128F020303B;
alter table AI_FIELD drop constraint FK1BEC5603A18837F5;
alter table AI_FIELD drop constraint FK1BEC5603F020303B;
alter table AI_REVIEW_COMMENT drop constraint FKFE74454F999D94C6;
alter table AI_REVIEW_COMMENT drop constraint FKFE74454FF020303B;
alter table AI_REVISION_COMMENT drop constraint FK4F56D12F020303B;
alter table AI_REVISION_COMMENT drop constraint FK4F56D1298F3AE86;
alter table COMMENT drop constraint FK63717A3F1811CB9D;
#
# just drop the action item table as it has no meaningful data
#
drop table ACTION_ITEM if exists;
drop table AI_COMMENT if exists;
drop table AI_FIELD if exists;
drop table AI_REVIEW_COMMENT if exists;
drop table AI_REVISION_COMMENT if exists;
#
# and create the new action item tables
#
create table ACTION_ITEM (ACTION_ITEM_ID integer not null, CREATE_DATE timestamp, REVIEW_ID integer not null, primary key (ACTION_ITEM_ID));
create table AI_COMMENT (ACTION_ITEM_ID integer not null, COMMENT_ID integer not null, primary key (ACTION_ITEM_ID, COMMENT_ID));
create table AI_FIELD (ACTION_ITEM_ID integer not null, FIELD_ID integer not null, FIELD_ORDER integer not null, primary key (ACTION_ITEM_ID, FIELD_ORDER));
create table AI_RELATED_COMMENT (ACTION_ITEM_ID integer not null, COMMENT_ID integer not null, primary key (ACTION_ITEM_ID, COMMENT_ID));
#
# drop the action field and add the draft column to comment
#
alter table COMMENT DROP COLUMN action;
alter table COMMENT ADD COLUMN draft bit;
alter table COMMENT ADD COLUMN deleted bit;
#
# Now set default values for draft column
#
update COMMENT SET draft = 'FALSE';
update COMMENT SET deleted = 'FALSE';
#
#fix recipients (a constraint is introduced in the upgrade, but not if the db is created from scratch)
#
drop table RECIPIENT if exists;
create table RECIPIENT (RECIPIENT_ID integer not null, email integer, uiACK bit, NOTIFICATION_ID integer not null, user integer, primary key (RECIPIENT_ID));
#
# Add back constraints and indexes
#
alter table ACTION_ITEM add constraint FKE077AB7CE71841CE foreign key (REVIEW_ID) references REVIEW;
alter table AI_COMMENT add constraint FK3E2A8128999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table AI_COMMENT add constraint FK3E2A8128F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table AI_FIELD add constraint FK1BEC5603A18837F5 foreign key (FIELD_ID) references FIELD;
alter table AI_FIELD add constraint FK1BEC5603F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table AI_RELATED_COMMENT add constraint FKFB11BF4999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table AI_RELATED_COMMENT add constraint FKFB11BF4F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
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);
alter table COMMENT add constraint FK63717A3F1811CB9D foreign key (USER_ID) references USER;
alter table RECIPIENT add constraint FKD6F981F9210DC959 foreign key (user) references USER;
alter table RECIPIENT add constraint FKD6F981F9FAA6A011 foreign key (NOTIFICATION_ID) references NOTIFICATION;
create index recipient_email_idx on RECIPIENT (email);
create index recipient_uiack_idx on RECIPIENT (uiACK);
#
# and finally update the db version
#
UPDATE CRUVERSION SET version = 6 WHERE version_id = 1;