#-----------------------------------------------------------------------------
#-- Copyright 2004 The Apache Software Foundation
#-- 
#-- Licensed under the Apache License, Version 2.0 (the "License");
#-- you may not use this file except in compliance with the License.
#-- You may obtain a copy of the License at
#-- 
#-- http://www.apache.org/licenses/LICENSE-2.0
#-- 
#-- Unless required by applicable law or agreed to in writing, software
#-- distributed under the License is distributed on an "AS IS" BASIS,
#-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#-- See the License for the specific language governing permissions and
#-- limitations under the License.
#-----------------------------------------------------------------------------
drop table if exists TURBINE_USER_GROUP_ROLE;
drop table if exists TURBINE_ROLE_PERMISSION;
drop table if exists TURBINE_USER;
drop table if exists TURBINE_ROLE;
drop table if exists TURBINE_GROUP;
drop table if exists TURBINE_PERMISSION;
drop table if exists JETSPEED_ROLE_PROFILE;
drop table if exists JETSPEED_GROUP_PROFILE;
drop table if exists JETSPEED_USER_PROFILE;
drop table if exists TURBINE_ROLE_PERMISSION;
drop table if exists COFFEES;

CREATE TABLE TURBINE_USER
(
    USER_ID INTEGER NOT NULL AUTO_INCREMENT,
    LOGIN_NAME VARCHAR (32) NOT NULL,
    PASSWORD_VALUE VARCHAR (32) NOT NULL,
    FIRST_NAME VARCHAR (99) NOT NULL,
    LAST_NAME VARCHAR (99) NOT NULL,
    EMAIL VARCHAR (99),
    CONFIRM_VALUE VARCHAR (99),
    MODIFIED TIMESTAMP,
    CREATED TIMESTAMP,
    LAST_LOGIN TIMESTAMP,
    DISABLED CHAR (1),
    OBJECTDATA BLOB,
    PASSWORD_CHANGED TIMESTAMP,
    PRIMARY KEY(USER_ID),
    UNIQUE (LOGIN_NAME)
);
CREATE TABLE TURBINE_ROLE
(
    ROLE_ID INTEGER NOT NULL AUTO_INCREMENT,
    ROLE_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA BLOB,
    PRIMARY KEY(ROLE_ID),
    UNIQUE (ROLE_NAME)
);
CREATE TABLE TURBINE_GROUP
(
    GROUP_ID INTEGER NOT NULL AUTO_INCREMENT,
    GROUP_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA BLOB,
    PRIMARY KEY(GROUP_ID),
    UNIQUE (GROUP_NAME)
);
CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID INTEGER NOT NULL AUTO_INCREMENT,
    PERMISSION_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA BLOB,
    PRIMARY KEY(PERMISSION_ID),
    UNIQUE (PERMISSION_NAME)
);
CREATE TABLE TURBINE_ROLE_PERMISSION
(
    ROLE_ID INTEGER NOT NULL,
    PERMISSION_ID INTEGER NOT NULL,
    PRIMARY KEY(ROLE_ID,PERMISSION_ID),
    FOREIGN KEY (ROLE_ID) REFERENCES TURBINE_ROLE (ROLE_ID) ON DELETE CASCADE,
    FOREIGN KEY (PERMISSION_ID) REFERENCES TURBINE_PERMISSION (PERMISSION_ID) ON DELETE CASCADE 
);
CREATE TABLE TURBINE_USER_GROUP_ROLE
(
    USER_ID INTEGER NOT NULL,
    GROUP_ID INTEGER NOT NULL,
    ROLE_ID INTEGER NOT NULL,
    PRIMARY KEY(USER_ID,GROUP_ID,ROLE_ID),
    FOREIGN KEY (USER_ID) REFERENCES TURBINE_USER (USER_ID) ON DELETE CASCADE ,
    FOREIGN KEY (GROUP_ID) REFERENCES TURBINE_GROUP (GROUP_ID) ON DELETE CASCADE ,
    FOREIGN KEY (ROLE_ID) REFERENCES TURBINE_ROLE (ROLE_ID) ON DELETE CASCADE 
);
CREATE TABLE JETSPEED_USER_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    USER_NAME VARCHAR (32) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE BLOB,
    PRIMARY KEY(PSML_ID),
    UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);
CREATE TABLE JETSPEED_GROUP_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    GROUP_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE BLOB,
    PRIMARY KEY(PSML_ID),
    UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);
CREATE TABLE JETSPEED_ROLE_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    ROLE_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE BLOB,
    PRIMARY KEY(PSML_ID),
    UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

CREATE TABLE COFFEES
(
   COFFEE_ID INTEGER NOT NULL AUTO_INCREMENT,
   COFFEE_NAME VARCHAR (50),
   SUPPLIER_ID INTEGER,
   PRICE FLOAT,
   SALES INTEGER,
   TOTAL INTEGER,
   PRIMARY KEY(COFFEE_ID)
);
