-- MySQL Schema Changes for advanced attachment support
--

USE fbt;
## begin tran fbtupdate;

ALTER TABLE FBT_ATTACHMENT
    ADD COLUMN FBT_VERSION VARCHAR(255),
    ADD COLUMN FBT_FOLDER_ID BIGINT,
    ADD COLUMN FBT_ATTACHMENT_VERSION_ID BIGINT;

-- table for holding attachment version info (ie: A1.att)
CREATE TABLE FBT_ATTACHMENT_VERSION (
    CONTEXT_ID INT,
    FBT_ATTACHMENT_ID BIGINT,
    FBT_STRUCT_ID BIGINT,
    FBT_CREATED_BY VARCHAR(255),
    FBT_ATTACH_FILENAME VARCHAR(255),
    FBT_ORIGINAL_FILENAME VARCHAR(255),
    FBT_CONTENT_TYPE VARCHAR(255),
    FBT_ATTACHMENT_DATE DATETIME,
    FBT_COMMENTS VARCHAR(255),
    FBT_VERSION VARCHAR(255),
    FBT_FOLDER_ID BIGINT,
    FBT_ATTACHMENT_VERSION_ID BIGINT,
    FBT_CONTENT  LONGBLOB,
    FBT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,FBT_ATTACHMENT_VERSION_ID)
);

-- table for holding attachment folder info (ie: f1.fol)
CREATE TABLE FBT_ATTACHMENT_FOLDER (
    CONTEXT_ID INT,
    FBT_FOLDER_ID BIGINT,
    FBT_STRUCT_ID BIGINT,
    FBT_GLOBAL INT,
    FBT_CREATED_BY VARCHAR(255),
    FBT_FOLDER_DATE DATETIME,
    FBT_FOLDER_NAME VARCHAR(255),
    FBT_PARENT_FOLDER_ID BIGINT,
    FBT_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,FBT_FOLDER_ID)
);

ALTER TABLE TS_ATTACHMENT
    ADD COLUMN TS_VERSION VARCHAR(255),
    ADD COLUMN TS_FOLDER_ID BIGINT,
    ADD COLUMN TS_ATTACHMENT_VERSION_ID BIGINT;

-- table for holding attachment version info (ie: A1.att)
CREATE TABLE TS_ATTACHMENT_VERSION (
    CONTEXT_ID INT,
    TS_ATTACHMENT_ID BIGINT,
    TS_STRUCT_ID BIGINT,
    TS_CREATED_BY VARCHAR(255),
    TS_ATTACH_FILENAME VARCHAR(255),
    TS_ORIGINAL_FILENAME VARCHAR(255),
    TS_CONTENT_TYPE VARCHAR(255),
    TS_ATTACHMENT_DATE DATETIME,
    TS_COMMENTS VARCHAR(255),
    TS_VERSION VARCHAR(255),
    TS_FOLDER_ID BIGINT,
    TS_ATTACHMENT_VERSION_ID BIGINT,
    TS_CONTENT  LONGBLOB,
    TS_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,TS_ATTACHMENT_VERSION_ID)
);

-- table for holding attachment folder info (ie: f1.fol)
CREATE TABLE TS_ATTACHMENT_FOLDER (
    CONTEXT_ID INT,
    TS_FOLDER_ID BIGINT,
    TS_STRUCT_ID BIGINT,
    TS_GLOBAL INT,
    TS_CREATED_BY VARCHAR(255),
    TS_FOLDER_DATE DATETIME,
    TS_FOLDER_NAME VARCHAR(255),
    TS_PARENT_FOLDER_ID BIGINT,
    TS_ARCHIVED INT,
    PRIMARY KEY (CONTEXT_ID,TS_FOLDER_ID)
);

## commit tran fbtupdate;