#drop action items all together

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_RELATED_COMMENT drop constraint FKFB11BF4999D94C6;
alter table AI_RELATED_COMMENT drop constraint FKFB11BF4F020303B;
drop table ACTION_ITEM if exists;
drop table AI_COMMENT if exists;
drop table AI_FIELD if exists;
drop table AI_RELATED_COMMENT if exists;

#upgrades may have left an iteration table lying about
drop table ITERATION if exists;

#create defect table

create table DEFECT (DEFECT_ID integer not null, CREATE_DATE timestamp, title varchar(255), summary varchar(255), defectOrder integer not null, furDefectOrder integer not null, REVIEW_ID integer not null, FU_REVIEW_ID integer, primary key (DEFECT_ID));
create table DEFECT_COMMENT (DEFECT_ID integer not null, COMMENT_ID integer not null, primary key (DEFECT_ID, COMMENT_ID));
create table DEFECT_FIELD (DEFECT_ID integer not null, FIELD_ID integer not null, FIELD_ORDER integer not null, primary key (DEFECT_ID, FIELD_ORDER));

# Add columns and not null constraints to COMMENT safest to go for a temp table copy

create table NEWCOMMENT (COMMENT_ID integer not null, CREATE_DATE timestamp, message longvarchar not null, draft bit not null, deleted bit not null, defectRaised bit not null, defectApproved bit not null, USER_ID integer, DEFECT_ID integer, primary key (COMMENT_ID));
insert into NEWCOMMENT (COMMENT_ID, CREATE_DATE, message, draft, deleted, defectRaised, defectApproved, USER_ID, DEFECT_ID) select comment.COMMENT_ID, comment.CREATE_DATE, comment.message, comment.draft, comment.deleted, false, false, comment.USER_ID, null from COMMENT comment;

#drop all constraints and FK's to comment

alter table COMMENT drop constraint FK63717A3F1811CB9D;
alter table COMMENT_FIELD drop constraint FKADAF9ADA999D94C6;
alter table REPLY drop constraint FK4A41ACA999D94C6;
alter table REPLY drop constraint FK4A41ACA4A6A205B;
alter table REVIEW_COMMENT drop constraint FKD84E3458999D94C6;
alter table REVISION_COMMENT drop constraint FKD00FBDDB9CAB902A;

drop table COMMENT;
alter table NEWCOMMENT rename to COMMENT;

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;

#re-instate comment FK's (except defect for clarity)

alter table COMMENT_FIELD add constraint FKADAF9ADA999D94C6 foreign key (COMMENT_ID) references COMMENT;
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_COMMENT add constraint FKD84E3458999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table REVISION_COMMENT add constraint FKD00FBDDB9CAB902A foreign key (comment) references COMMENT;

#add new defect indexs and constraints

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;

UPDATE METRIC_DEFINITION set xml='<metrics version="1" xmlns="http://www.cenqua.com/crucible/metrics-1">\u000a    <review>\u000a        <field type="STRING">\u000a            <name>section</name>\u000a            <label>Department</label>\u000a            <restriction type="NON_EXCLUSIVE_SET"/>\u000a            <namedValue name="None">none</namedValue>\u000a            <namedValue name="Crucible">crucible</namedValue>\u000a            <namedValue name="Fisheye">fisheye</namedValue>\u000a            <namedValue name="Clover">clover</namedValue>\u000a        </field>\u000a        <field type="STRING">\u000a            <name>spec</name>\u000a            <label>Specification Document (URL)</label>\u000a        </field>\u000a        <field type="STRING">\u000a            <name>uml</name>\u000a            <label>UML diagram (URL)</label>\u000a        </field>\u000a        <field type="DATE">\u000a            <name>due</name>\u000a            <label>Due date</label>\u000a        </field>\u000a    </review>\u000a    <comment>\u000a        <field type="INTEGER">\u000a            <name>feedback</name>\u000a            <label>vote</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="+1">1</namedValue>\u000a            <namedValue name="0">0</namedValue>\u000a            <namedValue name="-1">-1</namedValue>\u000a            <defaultValue>0</defaultValue>\u000a        </field>\u000a        <field type="INTEGER">\u000a            <name>classification</name>\u000a            <label>Classification</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Question">0</namedValue>\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            <namedValue name="Praise">11</namedValue>\u000a            <defaultValue>Question</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    <defect>\u000a        <field type="INTEGER">\u000a            <name>classification</name>\u000a            <label>Comment Type</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    </defect>\u000a\u000a</metrics>' where METRIC_ID=1;

# and finally fix and update the db version
drop table CRUVERSION if exists;
create table CRUVERSION (VERSION_ID integer not null, VERSION integer, primary key (VERSION_ID));
insert into CRUVERSION values (1,8);