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;

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;

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');

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;

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;

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;

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;

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

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;

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;

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;

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;

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

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;

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;

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

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;

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;

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;


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;


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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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)', 0x[BLOB], NULL) ;
INSERT INTO FILE_IMPORT_FORMAT VALUES (2, 'CONTACTS', 'The Bat! text (CSV - English)', 0x[BLOB], NULL) ;