











/*==============================================================*/
/* Table : ELEMENT_TYPE                                         */
/*==============================================================*/
create table if not exists ELEMENT_TYPE
(
   ELEMENT_TYPE_ID                int                            not null,
   NAME                           varchar(255)                   not null,
   DESCRIPTION                    varchar(255),
   EXTENSION                      varchar(10)                    not null,
   MIME_TYPE                      varchar(100),
   ICON_URL                       varchar(255),
   primary key (ELEMENT_TYPE_ID)
);

/*==============================================================*/
/* Table : MAIL_FILTER                                          */
/*==============================================================*/
create table if not exists MAIL_FILTER
(
   MAIL_FILTER_ID                 int                            not null,
   ACCOUNT_ID                     numeric(6,0),
   FILTER_NAME                    varchar(255)                   not null,
   SEND_OR_RECV                   char(1),
   SEQUENCE                       int,
   ACTIVE                         char(1),
   STOP_FILTERS                   char(1),
   SPAM_FILTER                    char(1),
   primary key (MAIL_FILTER_ID)
);

/*==============================================================*/
/* Table : USER_ACTION                                          */
/*==============================================================*/
create table if not exists USER_ACTION
(
   USER_ACTION_ID                 int                            not null,
   MAIL_FILTER_ID                 int,
   USER_TEXT_1                    varchar(255),
   USER_TEXT_2                    varchar(255),
   USER_TEXT_3                    varchar(255),
   ACTION_NAME                    varchar(255)                   not null,
   primary key (USER_ACTION_ID)
);

/*==============================================================*/
/* Table : LIST_ADDRESS                                         */
/*==============================================================*/
create table if not exists LIST_ADDRESS
(
   LIST_ADDRESS_ID                int                            not null,
   EMAIL_ACCOUNT_ID               int,
   ADDRESS                        varchar(255)                   not null,
   LAST_MSG_DATE                  datetime,
   SUBSCRIBE_DATE                 datetime                       not null,
   primary key (LIST_ADDRESS_ID)
);

/*==============================================================*/
/* Table : EMAIL_SEARCH                                         */
/*==============================================================*/
create table if not exists EMAIL_SEARCH
(
   EMAIL_SEARCH_ID                int                            not null,
   ACCOUNT_ID                     numeric(6,0),
   EMAIL_ID                       int,
   primary key (EMAIL_SEARCH_ID)
);

/*==============================================================*/
/* Table : DELETED_UID                                          */
/*==============================================================*/
create table if not exists DELETED_UID
(
   EMAIL_ACCOUNT_ID               int,
   UID                            varchar(255)                   not null
);

/*==============================================================*/
/* Table : SEQUENCE                                             */
/*==============================================================*/
create table if not exists SEQUENCE
(
   SEQUENCE_ID                    int                            not null,
   NAME                           varchar(100)                   not null,
   CURRENT_VALUE                  int,
   primary key (SEQUENCE_ID)
);

/*==============================================================*/
/* Table : ACCESS_LOG                                           */
/*==============================================================*/
create table if not exists ACCESS_LOG
(
   ACCESS_LOG_ID                  int                            not null,
   ACCOUNT_ID                     int,
   ACCESS_TYPE                    varchar(255),
   INFO                           varchar(255),
   USER_IP_ADDR                   varchar(50),
   REMOTE_HOST                    varchar(200),
   ACCESSED_ON                    datetime,
   primary key (ACCESS_LOG_ID)
);

/*==============================================================*/
/* Table : USER_RULE                                            */
/*==============================================================*/
create table if not exists USER_RULE
(
   USER_RULE_ID                   int                            not null,
   MAIL_FILTER_ID                 int,
   USER_TEXT_1                    varchar(255),
   USER_TEXT_2                    varchar(255),
   USER_TEXT_3                    varchar(255),
   EXCEPTION                      char(1)                        not null,
   RULE_NAME                      varchar(255)                   not null,
   primary key (USER_RULE_ID)
);

/*==============================================================*/
/* Table : REMINDER_FREQUENCY                                   */
/*==============================================================*/
create table if not exists REMINDER_FREQUENCY
(
   REMINDER_FREQUENCY_ID          int                            not null,
   ELEMENT_ID                     int,
   START_DATE                     datetime                       not null,
   END_DATE                       datetime,
   LAST_RUN_DATE                  datetime,
   FREQUENCY                      numeric(15,0)                  not null,
   EMAIL_TO                       varchar(255)                   not null,
   REMINDER_TYPE                  char(1)                        not null,
   primary key (REMINDER_FREQUENCY_ID)
);

/*==============================================================*/
/* Table : ELEMENT_GROUP                                        */
/*==============================================================*/
create table if not exists ELEMENT_GROUP
(
   ELEMENT_GROUP_ID               int                            not null,
   ELEMENT_ID                     int                            not null,
   GROUP_NAME                     varchar(255)                   not null,
   primary key (ELEMENT_GROUP_ID)
);

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 DELETED_UID_EMAIL_ID on DELETED_UID (EMAIL_ACCOUNT_ID);
CREATE INDEX DELETED_UID_UID on DELETED_UID (UID);

commit;
