-----------------------------------------------------------------------------
-- 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 TURBINE_ROLE_PERMISSION CASCADE;
drop table TURBINE_PERMISSION CASCADE;
drop table TURBINE_USER_GROUP_ROLE CASCADE;
drop table TURBINE_ROLE CASCADE;
drop table TURBINE_GROUP CASCADE;
drop table TURBINE_USER CASCADE;
drop table TURBINE_SCHEDULED_JOB CASCADE;
drop table ID_TABLE CASCADE;
DROP TABLE COFFEES CASCADE;
DROP TABLE EMAIL_INBOX CASCADE;
DROP TABLE JETSPEED_ROLE_PROFILE;
DROP TABLE JETSPEED_GROUP_PROFILE;
DROP TABLE JETSPEED_USER_PROFILE;

drop sequence TURBINE_PERMISSION_SEQ;
drop sequence TURBINE_ROLE_SEQ;
drop sequence TURBINE_GROUP_SEQ;    
drop sequence TURBINE_USER_SEQ;
drop sequence COFFEES_SEQ;
drop sequence EMAIL_INBOX_SEQ;
drop sequence JETSPEED_ROLE_PROFILE_SEQ;
drop sequence JETSPEED_GROUP_PROFILE_SEQ;
drop sequence JETSPEED_USER_PROFILE_SEQ;

-----------------------------------------------------------------------------
-- TURBINE_PERMISSION
-----------------------------------------------------------------------------
CREATE SEQUENCE TURBINE_PERMISSION_SEQ START WITH 10;
CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID integer primary key default nextval('TURBINE_PERMISSION_SEQ'),
    PERMISSION_NAME varchar (99) NOT NULL,
    OBJECTDATA BYTEA,
    UNIQUE (PERMISSION_NAME)
);

    
-----------------------------------------------------------------------------
-- TURBINE_ROLE
-----------------------------------------------------------------------------
CREATE SEQUENCE TURBINE_ROLE_SEQ START WITH 10;
CREATE TABLE TURBINE_ROLE
(
    ROLE_ID integer primary key default nextval('TURBINE_ROLE_SEQ'),
    ROLE_NAME varchar (99) NOT NULL,
    OBJECTDATA BYTEA,
    UNIQUE (ROLE_NAME)
);

    
-----------------------------------------------------------------------------
-- TURBINE_GROUP
-----------------------------------------------------------------------------
CREATE SEQUENCE TURBINE_GROUP_SEQ START WITH 10;
CREATE TABLE TURBINE_GROUP
(
    GROUP_ID integer primary key default nextval('TURBINE_GROUP_SEQ'),
    GROUP_NAME varchar (99) NOT NULL,
    OBJECTDATA BYTEA,
    UNIQUE (GROUP_NAME)
);

    
-----------------------------------------------------------------------------
-- TURBINE_ROLE_PERMISSION
-----------------------------------------------------------------------------
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  INDEX ROLE_PERMISSION_INDEX ON TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID);

    
-----------------------------------------------------------------------------
-- TURBINE_USER
-----------------------------------------------------------------------------
CREATE SEQUENCE TURBINE_USER_SEQ START WITH 10;
CREATE TABLE TURBINE_USER
(
    USER_ID integer primary key default nextval('TURBINE_USER_SEQ'),
    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 VARCHAR(1),
    OBJECTDATA BYTEA,
    PASSWORD_CHANGED timestamp,    
    UNIQUE (LOGIN_NAME)
);
   
-----------------------------------------------------------------------------
-- TURBINE_USER_GROUP_ROLE
-----------------------------------------------------------------------------
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  INDEX USER_GROUP_ROLE_INDEX ON TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID);

    
-----------------------------------------------------------------------------
-- TURBINE_SCHEDULED_JOB
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_SCHEDULED_JOB
(
    JOB_ID integer NOT NULL,
    SECOND integer default -1 NOT NULL, 
    MINUTE integer default -1 NOT NULL,
    HOUR integer default -1 NOT NULL,
    WEEK_DAY integer default -1 NOT NULL,
    DAY_OF_MONTH integer default -1 NOT NULL,
    TASK varchar (99) NOT NULL,
    EMAIL varchar (99),
    PROPERTY BYTEA,
    PRIMARY KEY(JOB_ID)
);

-----------------------------------------------------------------------------
-- ID_TABLE
-----------------------------------------------------------------------------
CREATE TABLE ID_TABLE
(
    ID_TABLE_ID integer NOT NULL,
    TABLE_NAME varchar (255) NOT NULL,
    NEXT_ID integer,
    QUANTITY integer,
    PRIMARY KEY (ID_TABLE_ID),
    UNIQUE (TABLE_NAME)
);

-----------------------------------------------------------------------------
-- COFFEES
-----------------------------------------------------------------------------
CREATE SEQUENCE COFFEES_SEQ START WITH 20;
CREATE TABLE COFFEES
(
    COFFEE_ID integer primary key default nextval('COFFEES_SEQ'),
    COFFEE_NAME varchar (50),
    SUPPLIER_ID integer,
    PRICE float,
    SALES integer,
    TOTAL integer
);

-----------------------------------------------------------------------------
-- EMAIL
-----------------------------------------------------------------------------
CREATE SEQUENCE EMAIL_INBOX_SEQ START WITH 10;
CREATE TABLE EMAIL_INBOX (
  EMAIL_INBOX_ID integer primary key default nextval('EMAIL_INBOX_SEQ'),
  MESSAGE_ID varchar(255) default NULL,
  FILENAME varchar(255) default NULL,
  ATTACHMENT BYTEA,
  READFLAG integer default NULL
);

-----------------------------------------------------------------------------
-- JETSPEED_USER_PROFILE
-----------------------------------------------------------------------------
CREATE SEQUENCE JETSPEED_USER_PROFILE_SEQ;
CREATE TABLE JETSPEED_USER_PROFILE
(
    PSML_ID integer primary key default nextval('JETSPEED_USER_PROFILE_SEQ'),
    USER_NAME VARCHAR (32) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE BYTEA,
    UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

-----------------------------------------------------------------------------
-- JETSPEED_GROUP_PROFILE
-----------------------------------------------------------------------------
CREATE SEQUENCE JETSPEED_GROUP_PROFILE_SEQ;
CREATE TABLE JETSPEED_GROUP_PROFILE
(
    PSML_ID integer primary key default nextval('JETSPEED_GROUP_PROFILE_SEQ'),
    GROUP_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE BYTEA,
    UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

-----------------------------------------------------------------------------
-- JETSPEED_ROLE_PROFILE
-----------------------------------------------------------------------------
CREATE SEQUENCE JETSPEED_ROLE_PROFILE_SEQ;
CREATE TABLE JETSPEED_ROLE_PROFILE
(
    PSML_ID integer primary key default nextval('JETSPEED_ROLE_PROFILE_SEQ'),
    ROLE_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE BYTEA,
    UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

