
drop table ELEMENT_GROUP cascade constraints
/


drop table REMINDER_FREQUENCY cascade constraints
/


drop table ELEMENT cascade constraints
/


drop table USER_RULE cascade constraints
/


drop table FOLDER cascade constraints
/


drop table ACCESS_LOG cascade constraints
/


drop table SEQUENCE cascade constraints
/


drop table DELETED_UID cascade constraints
/


drop table GATEWAY_INFO cascade constraints
/


drop table IM_PROFILE cascade constraints
/


drop table EMAIL_SEARCH cascade constraints
/


drop table LIST_ADDRESS cascade constraints
/


drop table EMAIL cascade constraints
/


drop table USER_ACTION cascade constraints
/


drop table MAIL_FILTER cascade constraints
/


drop table EMAIL_ACCOUNT cascade constraints
/


drop table ACCOUNT cascade constraints
/


drop table ELEMENT_TYPE cascade constraints
/


/*==============================================================*/
/* Table : ELEMENT_TYPE                                         */
/*==============================================================*/


create table ELEMENT_TYPE  (
   ELEMENT_TYPE_ID      INTEGER                          not null,
   NAME                 VARCHAR2(255)                    not null,
   DESCRIPTION          VARCHAR2(255),
   EXTENSION            VARCHAR2(10)                     not null,
   MIME_TYPE            VARCHAR2(100),
   ICON_URL             VARCHAR2(255),
   constraint PK_ELEMENT_TYPE primary key (ELEMENT_TYPE_ID)
)
/


/*==============================================================*/
/* Table : ACCOUNT                                              */
/*==============================================================*/


create table ACCOUNT  (
   ACCOUNT_ID           NUMBER(6,0)                      not null,
   USERNAME             VARCHAR2(50)                     not null,
   PASSWORD             VARCHAR2(50)                     not null,
   FIRSTNAME            VARCHAR2(255),
   LASTNAME             VARCHAR2(255),
   STATUS               CHAR(1)                          not null,
   ADMIN                CHAR(1),
   NEED_PWORD_CHANGE    CHAR(1),
   QUOTA_SIZE           NUMBER(15,0),
   LAST_LOGIN           DATE,
   constraint PK_ACCOUNT primary key (ACCOUNT_ID)
)
/


/*==============================================================*/
/* Table : EMAIL_ACCOUNT                                        */
/*==============================================================*/


create table EMAIL_ACCOUNT  (
   EMAIL_ACCOUNT_ID     INTEGER                          not null,
   ACCOUNT_ID           INTEGER                          not null,
   NAME                 VARCHAR2(255)                    not null,
   EMAIL_ADDRESS        VARCHAR2(255)                    not null,
   USERNAME             VARCHAR2(255)                    not null,
   PASSWORD             VARCHAR2(50)                     not null,
   FIRSTNAME            VARCHAR2(255),
   LASTNAME             VARCHAR2(255),
   POP3_SERVER          VARCHAR2(255)                    not null,
   POP3_PORT            VARCHAR2(10),
   ACCOUNT_TYPE         CHAR(1),
   SMTP_SERVER          VARCHAR2(255),
   SMTP_PORT            VARCHAR2(10),
   PRIMARY_ACCOUNT      CHAR(1),
   IS_LIST_ADDRESS      CHAR(1),
   MODERATED_LIST       CHAR(1),
   SIGNATURE            VARCHAR2(255),
   REMOVE_MSGS          CHAR(1),
   IS_ACTIVE            CHAR(1)                          not null,
   ERROR_MSG            VARCHAR2(255),
   REQUEST_RECEIPT      CHAR(1),
   REQUEST_READ         CHAR(1),
   REQUEST_DELETE       CHAR(1),
   DELETE_REMOTE_MSG    CHAR(1),
   constraint PK_EMAIL_ACCOUNT primary key (EMAIL_ACCOUNT_ID)
)
/


/*==============================================================*/
/* Table : MAIL_FILTER                                          */
/*==============================================================*/


create table MAIL_FILTER  (
   MAIL_FILTER_ID       INTEGER                          not null,
   ACCOUNT_ID           NUMBER(6,0),
   FILTER_NAME          VARCHAR2(255)                    not null,
   SEND_OR_RECV         CHAR(1),
   SEQUENCE             INTEGER,
   ACTIVE               CHAR(1),
   STOP_FILTERS         CHAR(1),
   SPAM_FILTER          CHAR(1),
   constraint PK_MAIL_FILTER primary key (MAIL_FILTER_ID)
)
/


/*==============================================================*/
/* Table : USER_ACTION                                          */
/*==============================================================*/


create table USER_ACTION  (
   USER_ACTION_ID       INTEGER                          not null,
   MAIL_FILTER_ID       INTEGER,
   USER_TEXT_1          VARCHAR2(255),
   USER_TEXT_2          VARCHAR2(255),
   USER_TEXT_3          VARCHAR2(255),
   ACTION_NAME          VARCHAR2(255)                    not null,
   constraint PK_USER_ACTION primary key (USER_ACTION_ID)
)
/


/*==============================================================*/
/* Table : EMAIL                                                */
/*==============================================================*/


create table EMAIL  (
   EMAIL_ID             INTEGER                          not null,
   ACCOUNT_ID           NUMBER(6,0),
   EMAIL_ACCOUNT_ID     INTEGER,
   FOLDER_ID            INTEGER,
   ELEMENT_TYPE_ID      INTEGER,
   NAME                 VARCHAR2(255)                    not null,
   ELEMENT_DATA         LONG RAW,
   ELEMENT_SIZE         NUMBER(15,0),
   ELEMENT_DATE         DATE                             not null,
   SENDER               VARCHAR2(255),
   RECEIVER             VARCHAR2(255),
   HAS_READ             CHAR(1),
   HAS_ATTACHMENTS      CHAR(1),
   REPLIED_TO           CHAR(1),
   FORWARDED            CHAR(1),
   ERROR_MSG            VARCHAR2(255),
   SHARED               CHAR(1),
   SHARED_PASSWORD      VARCHAR2(100),
   READ_ONLY            CHAR(1),
   REMOTE_UID           VARCHAR2(255),
   constraint PK_EMAIL primary key (EMAIL_ID)
)
/


/*==============================================================*/
/* Table : LIST_ADDRESS                                         */
/*==============================================================*/


create table LIST_ADDRESS  (
   LIST_ADDRESS_ID      INTEGER                          not null,
   EMAIL_ACCOUNT_ID     INTEGER,
   ADDRESS              VARCHAR2(255)                    not null,
   LAST_MSG_DATE        DATE,
   SUBSCRIBE_DATE       DATE                             not null,
   constraint PK_LIST_ADDRESS primary key (LIST_ADDRESS_ID)
)
/


/*==============================================================*/
/* Table : EMAIL_SEARCH                                         */
/*==============================================================*/


create table EMAIL_SEARCH  (
   EMAIL_SEARCH_ID      INTEGER                          not null,
   ACCOUNT_ID           NUMBER(6,0),
   EMAIL_ID             INTEGER,
   constraint PK_EMAIL_SEARCH primary key (EMAIL_SEARCH_ID)
)
/


/*==============================================================*/
/* Table : IM_PROFILE                                           */
/*==============================================================*/


create table IM_PROFILE  (
   IM_PROFILE_ID        INTEGER                          not null,
   ACCOUNT_ID           NUMBER(6,0),
   NAME                 VARCHAR2(255)                    not null,
   USERNAME             VARCHAR2(100)                    not null,
   PASSWORD             VARCHAR2(100)                    not null,
   SERVER_NAME          VARCHAR2(255)                    not null,
   PORT                 VARCHAR2(10)                     not null,
   IM_RESOURCE          VARCHAR2(255)                    not null,
   INVISIBLE            CHAR(1)                          not null,
   PRIMARY_PROFILE      CHAR(1)                          not null,
   AUTO_LOGIN           CHAR(1)                          not null,
   constraint PK_IM_PROFILE primary key (IM_PROFILE_ID)
)
/


/*==============================================================*/
/* Table : GATEWAY_INFO                                         */
/*==============================================================*/


create table GATEWAY_INFO  (
   GATEWAY_INFO_ID      INTEGER                          not null,
   IM_PROFILE_ID        INTEGER,
   GATEWAY_TYPE         VARCHAR2(100)                    not null,
   SERVER_JID           VARCHAR2(255)                    not null,
   USERNAME             VARCHAR2(100),
   PASSWORD             VARCHAR2(100),
   INFO_TEXT1           VARCHAR2(255),
   INFO_TEXT2           VARCHAR2(255),
   INFO_TEXT3           VARCHAR2(255),
   constraint PK_GATEWAY_INFO primary key (GATEWAY_INFO_ID)
)
/


/*==============================================================*/
/* Table : DELETED_UID                                          */
/*==============================================================*/


create table DELETED_UID  (
   EMAIL_ACCOUNT_ID     INTEGER,
   "UID"                VARCHAR2(255)                    not null
)
/


/*==============================================================*/
/* Table : SEQUENCE                                             */
/*==============================================================*/


create table SEQUENCE  (
   SEQUENCE_ID          INTEGER                          not null,
   NAME                 VARCHAR2(100)                    not null,
   CURRENT_VALUE        INTEGER,
   constraint PK_SEQUENCE primary key (SEQUENCE_ID)
)
/


/*==============================================================*/
/* Table : ACCESS_LOG                                           */
/*==============================================================*/


create table ACCESS_LOG  (
   ACCESS_LOG_ID        INTEGER                          not null,
   ACCOUNT_ID           INTEGER,
   ACCESS_TYPE          VARCHAR2(255),
   INFO                 VARCHAR2(255),
   USER_IP_ADDR         VARCHAR2(50),
   REMOTE_HOST          VARCHAR2(200),
   ACCESSED_ON          DATE,
   constraint PK_ACCESS_LOG primary key (ACCESS_LOG_ID)
)
/


/*==============================================================*/
/* Table : FOLDER                                               */
/*==============================================================*/


create table FOLDER  (
   FOLDER_ID            NUMBER(6,0)                      not null,
   PARENT_ID            INTEGER,
   ACCOUNT_ID           INTEGER,
   NAME                 VARCHAR2(255)                    not null,
   EDITABLE             CHAR(1),
   FOLDER_TYPE          CHAR(1),
   SEQUENCE             INTEGER,
   SHARED               CHAR(1),
   SHARED_PASSWORD      VARCHAR2(100),
   READ_ONLY            CHAR(1),
   constraint PK_FOLDER primary key (FOLDER_ID)
)
/


/*==============================================================*/
/* Table : USER_RULE                                            */
/*==============================================================*/


create table USER_RULE  (
   USER_RULE_ID         INTEGER                          not null,
   MAIL_FILTER_ID       INTEGER,
   USER_TEXT_1          VARCHAR2(255),
   USER_TEXT_2          VARCHAR2(255),
   USER_TEXT_3          VARCHAR2(255),
   EXCEPTION            CHAR(1)                          not null,
   RULE_NAME            VARCHAR2(255)                    not null,
   constraint PK_USER_RULE primary key (USER_RULE_ID)
)
/


/*==============================================================*/
/* Table : ELEMENT                                              */
/*==============================================================*/


create table ELEMENT  (
   ELEMENT_ID           INTEGER                          not null,
   FOLDER_ID            INTEGER,
   ELEMENT_TYPE_ID      INTEGER,
   ACCOUNT_ID           INTEGER,
   NAME                 VARCHAR2(255)                    not null,
   ELEMENT_DATA         LONG RAW,
   ELEMENT_SIZE         NUMBER(15,0),
   ELEMENT_DATE         DATE                             not null,
   SENDER               VARCHAR2(255),
   HAS_READ             CHAR(1),
   HAS_ATTACHMENTS      CHAR(1),
   ERROR_MSG            VARCHAR2(255),
   SHARED               CHAR(1),
   SHARED_PASSWORD      VARCHAR2(100),
   READ_ONLY            CHAR(1),
   constraint PK_ELEMENT primary key (ELEMENT_ID)
)
/


/*==============================================================*/
/* Table : REMINDER_FREQUENCY                                   */
/*==============================================================*/


create table REMINDER_FREQUENCY  (
   REMINDER_FREQUENCY_ID INTEGER                          not null,
   ELEMENT_ID           INTEGER,
   START_DATE           DATE                             not null,
   END_DATE             DATE,
   LAST_RUN_DATE        DATE,
   FREQUENCY            NUMBER(15,0)                     not null,
   EMAIL_TO             VARCHAR2(255)                    not null,
   REMINDER_TYPE        CHAR(1)                          not null,
   constraint PK_REMINDER_FREQUENCY primary key (REMINDER_FREQUENCY_ID)
)
/


/*==============================================================*/
/* Table : ELEMENT_GROUP                                        */
/*==============================================================*/


create table ELEMENT_GROUP  (
   ELEMENT_GROUP_ID     INTEGER                          not null,
   ELEMENT_ID           INTEGER                          not null,
   GROUP_NAME           VARCHAR2(255)                    not null,
   constraint PK_ELEMENT_GROUP primary key (ELEMENT_GROUP_ID)
)
/

insert into ACCOUNT values (1, 'admin', 'jfZQaeglm7I2k', 'Admin', 'Account', 'A', 'Y', 'Y', 10, sysdate);

insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (0,  'Text File','Plain text file', 'txt', 'text/plain', '/images/icons/icon_text.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (1,  'Acrobat PDF','Adobe Acrobat PDF', 'pdf', 'application/pdf', '/images/icons/icon_pdf.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (2,  'Image/JPEG','JPEG, JPG, JFIF, etc.', 'jpg', 'image/jpeg', '/images/icons/icon_jpg.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (3,  'Image/GIF','GIF Image.', 'gif', 'image/gif', '/images/icons/icon_gif.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (4,  'Image/BMP','Windows BMP Image', 'bmp', 'image/bmp', '/images/icons/icon_bmp.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (5,  'Image/TIF','TIFF Image.', 'tif', 'image/tif', '/images/icons/icon_jpg.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (6,  'Image/TGA','TARGA Image', 'tga', 'image/tga', '/images/icons/icon_jpg.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (7,  'MS Word','Microsoft Word Document', 'doc', 'application/msword', '/images/icons/icon_word.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (8,  'MS Excel','Microsoft Excel File', 'xls', 'application/vnd.ms-excel', '/images/icons/icon_xls.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (9,  'MS PowerPoint','Microsoft PowerPoint File', 'ppt', 'application/vnd.ms-powerpoint', '/images/icons/icon_ppt.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (10, 'MS Access Database','Microsoft Access File', 'mdb', 'application/vnd.ms-access', '/images/icons/icon_mdb.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (11, 'Visio Drawing','Microsoft Visio File', 'vsd', 'application/vnd.visio', '/images/icons/icon_vsd.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (12, 'XML Data',' XML File', 'xml', 'text/xml', '/images/icons/icon_text.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (13, 'Zip File','ZIP Compressed file', 'zip', 'application/x-zip-compressed', '/images/icons/icon_zip.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (14, 'File','Unknown file type', '.', 'application/octet', '/images/icons/icon_text.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (15, 'HTML File','HTML File', 'htm', 'text/html', '/images/icons/icon_html.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (16, 'HTML File','HTML File', 'html', 'text/html', '/images/icons/icon_html.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (17, 'GZip File','GZIP Compressed file', 'gz', 'application/x-gzip-compressed', '/images/icons/icon_zip.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (18, 'MS Excel','Microsoft Excel File', 'csv', 'application/vnd.ms-excel', '/images/icons/icon_xls.gif');
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (19, 'Note','Note', 'txt', 'text/plain', '/images/icons/sm_notes.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (20, 'Email','Email', 'eml', 'text/plain', '/images/icons/sm_email.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (21, 'Calendar Date','Calendar Date', 'date', 'text/plain', '/images/icons/sm_calendar.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (22, 'Reminder','Reminder', 'reminder', 'text/plain', '/images/icons/sm_reminder.gif' );
insert into ELEMENT_TYPE (ELEMENT_TYPE_ID, NAME, DESCRIPTION, EXTENSION, MIME_TYPE, ICON_URL) VALUES (23, 'Contact','Contact', 'contact', 'text/plain', '/images/icons/sm_addressbook.gif' );

insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (1, 'ACCOUNT_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (2, 'FOLDER_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (3, 'ELEMENT_TYPE_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (4, 'ELEMENT_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (5, 'EMAIL_ACCOUNT_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (6, 'REMINDER_FREQUENCY_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (7, 'MAIL_FILTER_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (8, 'USER_RULE_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (10, 'USER_ACTION_ID', 1000);
insert into SEQUENCE (SEQUENCE_ID, NAME, CURRENT_VALUE) values (11, 'EMAIL_ID', 1000);

CREATE INDEX FOLDER_PARENT_ID ON FOLDER (PARENT_ID);
CREATE INDEX FOLDER_ACCOUNT_ID ON FOLDER (ACCOUNT_ID);
CREATE INDEX FOLDER_NAME ON FOLDER (NAME);

CREATE INDEX ELEMENT_FOLDER_ID ON ELEMENT (FOLDER_ID);
CREATE INDEX ELEMENT_ACCOUNT_ID ON ELEMENT (ACCOUNT_ID);
CREATE INDEX ELEMENT_NAME ON ELEMENT (NAME);

CREATE INDEX EMAIL_FOLDER_ID ON EMAIL (FOLDER_ID);
CREATE INDEX EMAIL_ACCOUNT_ID ON EMAIL (ACCOUNT_ID);
CREATE INDEX EMAIL_NAME ON EMAIL (NAME);
CREATE INDEX EMAIL_HAS_READ ON EMAIL (HAS_READ);

CREATE INDEX DELETED_UID_EMAIL_ID on DELETED_UID (EMAIL_ACCOUNT_ID);
CREATE INDEX DELETED_UID_UID on DELETED_UID ("UID");

commit;
