ALTER TABLE {table_prefix}CB_AUTH_TOKEN ADD AUTH_ROLE VARCHAR(32);

ALTER TABLE {table_prefix}CB_USER ADD DEFAULT_AUTH_ROLE VARCHAR(32) NULL;

CREATE TABLE {table_prefix}CB_TEAM
(
    TEAM_ID          VARCHAR(128) NOT NULL,
    TEAM_NAME        VARCHAR(100) NOT NULL,
    TEAM_DESCRIPTION VARCHAR(255),

    CREATE_TIME      TIMESTAMP    NOT NULL,

    PRIMARY KEY (TEAM_ID),
    FOREIGN KEY (TEAM_ID) REFERENCES {table_prefix}CB_AUTH_SUBJECT (SUBJECT_ID) ON DELETE CASCADE
);

INSERT INTO {table_prefix}CB_TEAM (TEAM_ID, TEAM_NAME, TEAM_DESCRIPTION, CREATE_TIME)
SELECT ROLE_ID, ROLE_NAME, ROLE_DESCRIPTION, CREATE_TIME FROM {table_prefix}CB_ROLE;

CREATE TABLE {table_prefix}CB_USER_TEAM
(
    USER_ID    VARCHAR(128) NOT NULL,
    TEAM_ID    VARCHAR(128) NOT NULL,

    GRANT_TIME TIMESTAMP    NOT NULL,
    GRANTED_BY VARCHAR(128) NOT NULL,

    PRIMARY KEY (USER_ID, TEAM_ID),
    FOREIGN KEY (USER_ID) REFERENCES {table_prefix}CB_USER (USER_ID) ON DELETE CASCADE,
    FOREIGN KEY (TEAM_ID) REFERENCES {table_prefix}CB_TEAM (TEAM_ID) ON DELETE NO ACTION
);

INSERT INTO {table_prefix}CB_USER_TEAM (USER_ID, TEAM_ID, GRANT_TIME, GRANTED_BY)
SELECT USER_ID, ROLE_ID, GRANT_TIME, GRANTED_BY FROM {table_prefix}CB_USER_ROLE;

CREATE TABLE {table_prefix}CB_EXTERNAL_TEAM
(
    TEAM_ID          VARCHAR(128) NOT NULL,
    EXTERNAL_TEAM_ID VARCHAR(128) NOT NULL,

    PRIMARY KEY (TEAM_ID,EXTERNAL_TEAM_ID),
    FOREIGN KEY (TEAM_ID) REFERENCES {table_prefix}CB_TEAM (TEAM_ID) ON DELETE CASCADE
);

DROP TABLE {table_prefix}CB_USER_ROLE;
DROP TABLE {table_prefix}CB_ROLE;