DROP TABLE IF EXISTS {table_prefix}CB_SERVER;

CREATE TABLE IF NOT EXISTS {table_prefix}CB_SCHEMA_INFO (
    VERSION INTEGER NOT NULL,
    UPDATE_TIME TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS {table_prefix}CB_INSTANCE(
    INSTANCE_ID CHAR(36) NOT NULL,              -- Unique instance ID

    MAC_ADDRESS CHAR(12) NOT NULL,
    HOST_NAME VARCHAR(128) NOT NULL,

    PRODUCT_NAME VARCHAR(100) NOT NULL,         -- Server product name
    PRODUCT_VERSION VARCHAR(32) NOT NULL,       -- Server product version

    UPDATE_TIME TIMESTAMP NOT NULL,

    PRIMARY KEY(INSTANCE_ID)
);


CREATE TABLE IF NOT EXISTS {table_prefix}CB_INSTANCE_DETAILS(
    INSTANCE_ID CHAR(36) NOT NULL,              -- Unique instance ID
    FIELD_NAME VARCHAR(32) NOT NULL,
    FIELD_VALUE VARCHAR(255),

    PRIMARY KEY(INSTANCE_ID, FIELD_NAME),
    FOREIGN KEY(INSTANCE_ID) REFERENCES {table_prefix}CB_INSTANCE(INSTANCE_ID)
);

CREATE TABLE IF NOT EXISTS {table_prefix}CB_INSTANCE_EVENT(
    EVENT_ID BIGINT AUTO_INCREMENT NOT NULL,

    INSTANCE_ID CHAR(36) NOT NULL,              -- Unique instance ID

    EVENT_TYPE VARCHAR(16) NOT NULL,
    EVENT_TIME TIMESTAMP NOT NULL,

    EVENT_MESSAGE VARCHAR(255),

    PRIMARY KEY(EVENT_ID),
    FOREIGN KEY(INSTANCE_ID) REFERENCES {table_prefix}CB_INSTANCE(INSTANCE_ID)
);

CREATE TABLE IF NOT EXISTS {table_prefix}CB_WORKSPACE(
    WORKSPACE_ID VARCHAR(32) NOT NULL,          -- Workspace unique ID
    INSTANCE_ID CHAR(36) NOT NULL,              -- Unique instance ID

    WORKSPACE_LOCATION VARCHAR(1024) NOT NULL,

    UPDATE_TIME TIMESTAMP NOT NULL,

    PRIMARY KEY(INSTANCE_ID,WORKSPACE_ID),
    FOREIGN KEY(INSTANCE_ID) REFERENCES {table_prefix}CB_INSTANCE(INSTANCE_ID)
);

ALTER TABLE {table_prefix}CB_USER_CREDENTIALS ADD UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

ALTER TABLE {table_prefix}CB_SESSION ALTER COLUMN LAST_ACCESS_REMOTE_ADDRESS VARCHAR(128) NULL;
ALTER TABLE {table_prefix}CB_SESSION ALTER COLUMN LAST_ACCESS_USER_AGENT VARCHAR(255) NULL;


ALTER TABLE {table_prefix}CB_SESSION ADD LAST_ACCESS_INSTANCE_ID CHAR(36);
ALTER TABLE {table_prefix}CB_SESSION ADD FOREIGN KEY(LAST_ACCESS_INSTANCE_ID) REFERENCES {table_prefix}CB_INSTANCE(INSTANCE_ID);