DROP TABLE IF EXISTS APPLICATION;
CREATE TABLE APPLICATION (
  APP_ID char(2) NOT NULL default '',
  APP_NAME varchar(100) default NULL,
  APP_DESC text,
  APP_VERSION varchar(10) default NULL,
  APP_INSTALLDATE datetime default NULL,
  PRIMARY KEY  (APP_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS COMPANY;
CREATE TABLE COMPANY (
  COM_NAME varchar(30) NOT NULL default '',
  COM_ADDRESSSTREET varchar(50) default NULL,
  COM_ADDRESSCITY varchar(30) default NULL,
  COM_ADDRESSSTATE varchar(30) default NULL,
  COM_ADDRESSZIP varchar(10) NOT NULL default '',
  COM_ADDRESSCOUNTRY varchar(30) default NULL,
  COM_CONTACTPERSON varchar(50) default NULL,
  COM_EMAIL varchar(50) default NULL,
  COM_PHONE varchar(50) default NULL,
  COM_FAX varchar(50) default NULL,
  PRIMARY KEY  (COM_NAME)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CURRENCY;
CREATE TABLE CURRENCY (
  CUR_ID char(3) NOT NULL default '',
  CUR_NAME varchar(50) NOT NULL default '',
  PRIMARY KEY  (CUR_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO CURRENCY VALUES ('USD', 'US Dollar');

DROP TABLE IF EXISTS SCREEN;
CREATE TABLE SCREEN (
  APP_ID char(2) NOT NULL default '',
  SCR_ID varchar(10) NOT NULL default '',
  SCR_NAME varchar(100) default NULL,
  PRIMARY KEY  (APP_ID,SCR_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS USERSCREENACCESS;
CREATE TABLE USERSCREENACCESS (
  U_ID varchar(20) NOT NULL default '',
  APP_ID char(2) NOT NULL default '',
  SCR_ID varchar(10) NOT NULL default '',
  PRIMARY KEY  (U_ID,APP_ID,SCR_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS SCR_GROUP_ACCESS;
CREATE TABLE SCR_GROUP_ACCESS (
  UG_ID varchar(20) NOT NULL default '',
  APP_ID char(2) NOT NULL default '',
  SCR_ID varchar(10) NOT NULL default '',
  PRIMARY KEY  (UG_ID,APP_ID,SCR_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS WBS_USER;
CREATE TABLE WBS_USER (
  U_ID varchar(20) NOT NULL default '',
  C_ID integer,
  U_PASSWORD varchar(36) default NULL,
  U_STATUS smallint(6) default '0',
  U_SETTINGS text,
  U_SENDMAIL smallint(2) default '0',
  U_ACCESSTYPE CHAR(5) default 'IND',
  PRIMARY KEY  (U_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CONTACT;
CREATE TABLE CONTACT(
  C_ID                    INTEGER    NOT NULL,
  CF_ID                   VARCHAR(255),
  C_FIRSTNAME             VARCHAR(50),
  C_LASTNAME              VARCHAR(50),
  C_MIDDLENAME            VARCHAR(50),
  C_NICKNAME              VARCHAR(255),
  C_EMAILADDRESS          VARCHAR(255),
  C_HOMESTREET            TEXT,
  C_HOMECITY              VARCHAR(50),
  C_HOMEPOSTALCODE        VARCHAR(50),
  C_HOMESTATE             VARCHAR(50),
  C_HOMECOUNTRY           VARCHAR(50),
  C_HOMEPHONE             VARCHAR(50),
  C_HOMEFAX               VARCHAR(50),
  C_MOBILEPHONE           VARCHAR(50),
  C_PERSONALWEBPAGE       VARCHAR(255),
  C_BUSINESSSTREET        TEXT,
  C_BUSINESSCITY          VARCHAR(50),
  C_BUSINESSPOSTALCODE    VARCHAR(50),
  C_BUSINESSSTATE         VARCHAR(50),
  C_BUSINESSCOUNTRY       VARCHAR(50),
  C_BUSINESSWEBPAGE       VARCHAR(255),
  C_BUSINESSPHONE         VARCHAR(50),
  C_BUSINESSFAX           VARCHAR(50),
  C_PAGER                 VARCHAR(50),
  C_COMPANY               VARCHAR(255),
  C_JOBTITLE              VARCHAR(255),
  C_DEPARTMENT            VARCHAR(255),
  C_OFFICELOCATION        TEXT,
  C_NOTES                 TEXT,
  C_MODIFYDATETIME        DATETIME,
  C_MODIFYUSERNAME        VARCHAR(50),
  C_BIRTHDAY              DATE,
  C_STATUS                INT DEFAULT 0,
  C_X_PHOTO              TEXT,
  C_SUBSCRIBER            SMALLINT,
  C_CREATEDATETIME DATETIME NULL,
  C_CREATEUSERNAME VARCHAR(50) NULL,
  PRIMARY KEY (C_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS UNSUBSCRIBER;
CREATE TABLE UNSUBSCRIBER
(
  ENS_EMAIL varchar(255) not null
,  ENS_DATETIME datetime not null
,  PRIMARY KEY (ENS_EMAIL)
) TYPE=MyISAM DEFAULT CHARSET=utf8;;

DROP TABLE IF EXISTS CFOLDER;
CREATE TABLE CFOLDER(
  CF_ID                   VARCHAR(255) NOT NULL,
  CF_ID_PARENT VARCHAR(255),
  CF_NAME VARCHAR(255),
  CT_ID CHAR(3),
  CF_STATUS int NOT NULL default '0',
  PRIMARY KEY(CF_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CTYPE;
CREATE TABLE CTYPE(
  CT_ID                    CHAR(3) NOT NULL,
  CT_SETTINGS              TEXT,
  CT_NAME                  VARCHAR(50),
  PRIMARY KEY(CT_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CFACCESS;
CREATE TABLE CFACCESS
(
  U_ID VARCHAR(20) NOT NULL,
  CF_ID VARCHAR(255) NOT NULL,
  CFA_RIGHTS INTEGER NOT NULL,
  PRIMARY KEY (U_ID, CF_ID)
) TYPE=MyISAM  DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CFGROUPACCESS;
CREATE TABLE CFGROUPACCESS
(
  UG_ID INTEGER NOT NULL,
  CF_ID VARCHAR(255) NOT NULL,
  CFA_RIGHTS INT NULL,
  PRIMARY KEY (UG_ID, CF_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS UGROUP;
CREATE TABLE UGROUP(
  UG_ID                   INTEGER NOT NULL,
  UG_NAME                 VARCHAR(50),
  UG_SETTINGS             TEXT,
  PRIMARY KEY(UG_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS UGROUP_USER;
CREATE TABLE UGROUP_USER(
  UG_ID                   INTEGER NOT NULL,
  U_ID                    VARCHAR(20) NOT NULL,
  PRIMARY KEY(UG_ID, U_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS FILE_IMPORT_FORMAT;
CREATE TABLE FILE_IMPORT_FORMAT(
  FIF_ID                  INTEGER NOT NULL,
  FIF_LIST                CHAR(10) NOT NULL,
  FIF_NAME                VARCHAR(50) NOT NULL,
  FIF_SETTINGS            TEXT NOT NULL,
  FIF_OWNER_U_ID          VARCHAR(20),
  PRIMARY KEY(FIF_ID, FIF_LIST)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS APPSETTINGS;
CREATE TABLE APPSETTINGS(
  APP_ID                  CHAR(2) NOT NULL,
  SETTINGS                TEXT,
  PRIMARY KEY(APP_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS DISK_USAGE;
CREATE TABLE DISK_USAGE
(
  DU_USER_ID VARCHAR(20) NOT NULL
,  DU_APP_ID CHAR(10) NOT NULL
,  DU_SIZE INT NULL
,  PRIMARY KEY (DU_USER_ID, DU_APP_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS USER_DISK_QUOTA;
CREATE TABLE USER_DISK_QUOTA
(
  UDQ_USER_ID VARCHAR(20) NOT NULL
, UDQ_APP_ID CHAR(10) NOT NULL
,  UDQ_SIZE INT NULL
,  PRIMARY KEY (UDQ_USER_ID, UDQ_APP_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS SMS_BALANCE;
CREATE TABLE SMS_BALANCE (
  SMS_USER_ID varchar(20) NOT NULL default '',
  SMS_SENT int(11) NOT NULL default '0',
  SMS_BALANCE decimal(15,2) default NULL,
  PRIMARY KEY  (SMS_USER_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS SMS_CREDIT_HISTORY;
CREATE TABLE SMS_CREDIT_HISTORY (
  SMSG_ID int(11) NOT NULL auto_increment,
  SMSG_DATETIME datetime NOT NULL default '0000-00-00 00:00:00',
  SMSG_USER_ID varchar(20) NOT NULL,
  SMSG_QTY decimal(15,2) default NULL,
  SMSG_QS char(10) NOT NULL,
  SMSG_SOURCE varchar(20) NOT NULL,
  PRIMARY KEY  (SMSG_ID),
  KEY DATE (SMSG_DATETIME)
) TYPE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS SMS_HISTORY;
CREATE TABLE SMS_HISTORY (
  SMSH_ID int(11) NOT NULL auto_increment,
  SMSH_DATETIME datetime NOT NULL default '0000-00-00 00:00:00',
  SMSH_USER_ID varchar(20) NOT NULL,
  SMSH_PHONE varchar(20) NOT NULL,
  SMSH_WIDTH int(11) NOT NULL,
  SMSH_QTY int(11) NOT NULL,
  SMSH_APP varchar(5) NOT NULL,
  SMSH_MODULEID char(30) NOT NULL,
  SMSH_TEXT text NOT NULL,
  SMSH_MSGID char(50) default NULL,
  SMSH_CHARGE decimal(15,2) default NULL,
  SMSH_CHARGED tinyint(4) NOT NULL default '0',
  SMSH_UNLIM tinyint(4) NOT NULL default '0',
  SMSH_STATUS varchar(20) default NULL,
  SMSH_STATUS_TEXT varchar(100) default NULL,
  PRIMARY KEY  (SMSH_ID),
  KEY DATETIME (SMSH_DATETIME)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS AUX_GROUP_ACCESS;
CREATE TABLE AUX_GROUP_ACCESS
(
  UG_ID INT NOT NULL,
  APP_ID VARCHAR(2) NOT NULL,
  AGA_ID VARCHAR(20) NOT NULL,
  PRIMARY KEY (UG_ID, APP_ID, AGA_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS AUX_USER_ACCESS;
CREATE TABLE AUX_USER_ACCESS
(
  U_ID VARCHAR(20) NOT NULL,
  APP_ID CHAR(2) NOT NULL,
  AGA_ID VARCHAR(20) NOT NULL,
  PRIMARY KEY (U_ID, APP_ID, AGA_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CLIST;
CREATE TABLE CLIST
(
  CL_ID INT NOT NULL,
  CL_NAME VARCHAR(50) NULL,
  CL_OWNER_U_ID VARCHAR(20) NULL,
  CL_SHARED SMALLINT NULL DEFAULT 0,
  CL_MODIFYDATETIME DATETIME NULL,
  CL_MODIFYUSERNAME VARCHAR(50) NULL,
  PRIMARY KEY (CL_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CLIST_CONTACT;
CREATE TABLE CLIST_CONTACT
(
  CL_ID INT NOT NULL,
  C_ID INT NOT NULL,
  CLC_MODIFYDATETIME DATETIME NULL,
  CLC_MODIFYUSERNAME VARCHAR(50) NOT NULL,
  PRIMARY KEY (CL_ID, C_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CLIST_GROUP;
CREATE TABLE CLIST_GROUP
(
  CL_ID INT NOT NULL,
  UG_ID INT NOT NULL,
  PRIMARY KEY (CL_ID, UG_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS CLIST_FOLDER;
CREATE TABLE CLIST_FOLDER
(
  CL_ID INT NOT NULL,
  CF_ID VARCHAR(255) NOT NULL,
  PRIMARY KEY (CL_ID, CF_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS U_ACCESSRIGHTS;
CREATE TABLE U_ACCESSRIGHTS (
  AR_ID varchar(20) NOT NULL default '',
  AR_PATH varchar(255) NOT NULL default '',
  AR_OBJECT_ID varchar(50) NOT NULL default '',
  AR_VALUE int(11) NOT NULL default '0',
  AR_AUX text,
  PRIMARY KEY  (AR_PATH,AR_OBJECT_ID,AR_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS UG_ACCESSRIGHTS;
CREATE TABLE UG_ACCESSRIGHTS (
  AR_ID varchar(20) NOT NULL default '',
  AR_PATH varchar(255) NOT NULL default '',
  AR_OBJECT_ID varchar(50) NOT NULL default '',
  AR_VALUE int(11) NOT NULL default '0',
  AR_AUX text,
  PRIMARY KEY  (AR_PATH,AR_OBJECT_ID,AR_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS WG_PARAM;
CREATE TABLE WG_PARAM (
  WG_ID int(11) NOT NULL default '0',
  WGP_NAME varchar(50) NOT NULL default '',
  WGP_VALUE text NOT NULL,
  PRIMARY KEY  (WG_ID,WGP_NAME)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS WG_WIDGET;
CREATE TABLE WG_WIDGET (
  `WG_ID` int(11) NOT NULL auto_increment,
  `WT_ID` varchar(30) NOT NULL default '',
  `WST_ID` varchar(30) NOT NULL default '',
  `WG_FPRINT` varchar(100) NOT NULL default '',
  `WG_DESC` text NOT NULL,
  `WG_USER` VARCHAR (20) NOT NULL default '',
  `WG_LANG` VARCHAR( 5 ) DEFAULT 'eng' NOT NULL,
  `WG_CREATED_FROM` VARCHAR(25) DEFAULT '' NOT NULL,
  `WG_CREATED_BY` varchar(100) NOT NULL default '',
  `WG_CREATED_DATETIME` datetime NOT NULL default '0000-00-00 00:00:00',
  `WG_MODIFIED_BY` varchar(100) NOT NULL default '',
  `WG_MODIFIED_DATETIME` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`WG_ID`)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS ACCESSRIGHTS_LINK;
CREATE TABLE ACCESSRIGHTS_LINK (
`AR_PATH` VARCHAR( 255 ) NOT NULL ,
`AR_OBJECT_ID` VARCHAR( 50 ) NOT NULL ,
`LINK_AR_PATH` VARCHAR( 255 ) NOT NULL ,
`LINK_AR_OBJECT_ID` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `AR_PATH` , `AR_OBJECT_ID` ) ,
INDEX ( `LINK_AR_PATH` , `LINK_AR_OBJECT_ID` )
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS USER_SETTINGS;
CREATE TABLE `USER_SETTINGS` (
`U_ID` VARCHAR( 20 ) NOT NULL ,
`APP_ID` CHAR( 2 ) NOT NULL ,
`NAME` VARCHAR( 255 ) NOT NULL ,
`VALUE` TEXT NOT NULL ,
PRIMARY KEY ( `U_ID` , `APP_ID` , `NAME` )
) ENGINE = MYISAM CHARSET=utf8;

DROP TABLE IF EXISTS MMMESSAGE;
CREATE TABLE MMMESSAGE (
  MMM_ID int(11) NOT NULL default '0',
  MMF_ID varchar(255) NOT NULL default '0',
  MMM_STATUS int(11) NOT NULL default '0',
  MMM_DATETIME datetime default NULL,
  MMM_PRIORITY int(11) NOT NULL default '0',
  MMM_FROM varchar(128) default NULL,
  MMM_TO text,
  MMM_CC text,
  MMM_BCC text,
  MMM_LISTS varchar(255) default NULL,
  MMM_SUBJECT varchar(255) default NULL,
  MMM_LEAD varchar(255) default '',
  MMM_CONTENT text,
  MMM_ATTACHMENT text,
  MMM_IMAGES text,
  MMM_SIZE int(11) NOT NULL,
  MMM_USERID varchar(20) NOT NULL default '',
  MMM_APP_ID char(2) NOT NULL default 'MM',
  MMM_HEADER text,
  PRIMARY KEY (MMM_ID)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS MMMSENTTO;
CREATE TABLE MMMSENTTO (
  MMM_ID int(11) NOT NULL default '0',
  MMMST_EMAIL varchar(100) NOT NULL default '',
  MMMST_STATUS varchar(255) NOT NULL default '0',
  PRIMARY KEY (MMM_ID, MMMST_EMAIL)
) TYPE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS MMSENT;
CREATE TABLE MMSENT (
  MMS_DATE date NOT NULL,
  MMS_COUNT int(11) default NULL,
  PRIMARY KEY (MMS_DATE)
) TYPE=MyISAM DEFAULT CHARSET=utf8;



INSERT INTO SMS_BALANCE(SMS_USER_ID, SMS_SENT, SMS_BALANCE) VALUES ( '$SYSTEM', '0', %SMS_BALANCE% );
INSERT INTO SMS_CREDIT_HISTORY(SMSG_DATETIME, SMSG_USER_ID, SMSG_QTY, SMSG_QS, SMSG_SOURCE ) VALUES ( NOW(), '$SYSTEM', %SMS_BALANCE%, 'SET', 'ONCREATE' );

INSERT INTO FILE_IMPORT_FORMAT VALUES (3, 'CONTACTS', 'Outlook Express Text (CSV - English)', 0x2C7C7C307C7C435F46495253544E414D453D3E4669727374204E616D652626435F4C4153544E414D453D3E4C617374204E616D652626435F4D4944444C454E414D453D3E4D6964646C65204E616D652626435F4E49434B4E414D453D3E4E69636B6E616D652626435F454D41494C414444524553533D3E452D6D61696C20416464726573732626435F484F4D455354524545543D3E486F6D65205374726565742626435F484F4D45434954593D3E486F6D6520436974792626435F484F4D45504F5354414C434F44453D3E486F6D6520506F7374616C20436F64652626435F484F4D4553544154453D3E486F6D652053746174652626435F484F4D45434F554E5452593D3E486F6D6520436F756E7472792F526567696F6E2626435F484F4D4550484F4E453D3E486F6D652050686F6E652626435F484F4D454641583D3E486F6D65204661782626435F4D4F42494C4550484F4E453D3E4D6F62696C652050686F6E652626435F504552534F4E414C574542504147453D3E506572736F6E616C2057656220506167652626435F425553494E4553535354524545543D3E427573696E657373205374726565742626435F425553494E455353434954593D3E427573696E65737320436974792626435F425553494E455353504F5354414C434F44453D3E427573696E65737320506F7374616C20436F64652626435F425553494E45535353544154453D3E427573696E6573732053746174652626435F425553494E455353434F554E5452593D3E427573696E65737320436F756E7472792F526567696F6E2626435F425553494E455353574542504147453D3E427573696E6573732057656220506167652626435F425553494E45535350484F4E453D3E427573696E6573732050686F6E652626435F425553494E4553534641583D3E427573696E657373204661782626435F50414745523D3E50616765722626435F434F4D50414E593D3E436F6D70616E792626435F4A4F425449544C453D3E4A6F62205469746C652626435F4445504152544D454E543D3E4465706172746D656E742626435F4F46464943454C4F434154494F4E3D3E4F6666696365204C6F636174696F6E2626435F4E4F5445533D3E4E6F746573, NULL) ;
INSERT INTO FILE_IMPORT_FORMAT VALUES (2, 'CONTACTS', 'The Bat! text (CSV - English)', 0x2C7C7C307C7C435F46495253544E414D453D3E4669727374204E616D652626435F4C4153544E414D453D3E4C617374204E616D652626435F4D4944444C454E414D453D3E4D6964646C65204E616D652626435F454D41494C414444524553533D3E452D6D61696C2626435F4E49434B4E414D453D3E4E69636B6E616D652626435F42495254484441593D3E42697274686461792626435F434F4D50414E593D3E436F6D70616E792626435F4A4F425449544C453D3E4A6F62207469746C652626435F484F4D4550484F4E453D3E50686F6E652626435F484F4D454641583D3E4661782626435F4D4F42494C4550484F4E453D3E4D6F62696C652626435F425553494E45535350484F4E453D3E422F50686F6E652626435F425553494E4553534641583D3E422F4661782626435F50414745523D3E50616765722626435F484F4D455354524545543D3E416464726573732626435F484F4D45434954593D3E436974792626435F484F4D4553544154453D3E53746174652626435F484F4D45504F5354414C434F44453D3E5A49502626435F484F4D45434F554E5452593D3E436F756E7472792626435F4445504152544D454E543D3E4465706172746D656E742626435F4F46464943454C4F434154494F4E3D3E4F66666963652626435F425553494E4553535354524545543D3E422F416464726573732626435F425553494E455353434954593D3E422F436974792626435F425553494E45535353544154453D3E422F53746174652626435F425553494E455353504F5354414C434F44453D3E422F5A49502626435F425553494E455353434F554E5452593D3E422F436F756E7472792626435F4E4F5445533D3E4E6F7465732626435F504552534F4E414C574542504147453D3E486F6D65706167652626435F425553494E455353574542504147453D3E422F486F6D6570616765, NULL) ;