USE peter;

begin tran fbtsetup;

-- saved color codes needed for Version 5.17
CREATE TABLE FBT_COLORCODE_DEF (
    FBT_COLORCODE_ID NUMBER PRIMARY KEY,
    FBT_COLORCODE_FIELD VARCHAR(255),
    FBT_COLORCODE_INFO LONG
);

-- history changes needed for Version 6.0
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_SUBJECT VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_PRIORITY INT;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_PROJECT VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_AREA VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_ENVIRONMENT VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_VERSION VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_NOTIFYLIST VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_PARENT_ID BIGINT;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_REQUESTED_DUE_DATE DATETIME;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_ACTUAL_COMPLETION_DATE DATETIME;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_ESTIMATED_HOURS BIGINT;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_ACTUAL_HOURS BIGINT;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_PERCENT_COMPLETE BIGINT;
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_ATTACHMENTS VARCHAR(255);
ALTER TABLE FBT_BUG_STRUCT_ENTRY ADD FBT_NOTES VARCHAR(255);


-- fix old schema (which incorrectly used BIGINT) to allow decimal precision to SQL Server numeric fields
alter table FBT_BUG_STRUCT alter column FBT_ESTIMATED_HOURS FLOAT;
alter table FBT_BUG_STRUCT alter column FBT_ACTUAL_HOURS FLOAT;
alter table FBT_BUG_STRUCT alter column FBT_PERCENT_COMPLETE FLOAT;

alter table FBT_BUG_STRUCT_ENTRY alter column FBT_ESTIMATED_HOURS FLOAT;
alter table FBT_BUG_STRUCT_ENTRY alter column FBT_ACTUAL_HOURS FLOAT;
alter table FBT_BUG_STRUCT_ENTRY alter column FBT_PERCENT_COMPLETE FLOAT;

alter table FBT_BUG_USER_FIELD alter column FBT_NUMBER_VALUE FLOAT;
alter table FBT_BUG_USER_FIELD_ENTRY alter column FBT_NUMBER_VALUE FLOAT;

commit tran fbtsetup;