SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE DATABASE `letterboxv2_3` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE letterboxv2_3;

CREATE TABLE `arboxes` (
  `ARBOX_ID` varchar(32) NOT NULL default '',
  `TITLE` varchar(255) default NULL,
  `SUBJECT` varchar(255) default NULL,
  `DESCRIPTION` text,
  `ARCONTAINER_ID` varchar(32) default NULL,
  `STATUS` varchar(3) NOT NULL default '',
  `CREATION_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `RETENTION_TIME` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T1` varchar(3) default NULL,
  `CUSTOM_N1` int(255) default '0',
  `CUSTOM_F1` float default '0',
  `CUSTOM_D1` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T2` varchar(3) default NULL,
  `CUSTOM_N2` int(255) default '0',
  `CUSTOM_F2` float default '0',
  `CUSTOM_D2` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T3` varchar(50) default NULL,
  `CUSTOM_N3` int(255) default '0',
  `CUSTOM_F3` float default '0',
  `CUSTOM_D3` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T4` varchar(50) default NULL,
  `CUSTOM_N4` int(255) default '0',
  `CUSTOM_F4` float default '0',
  `CUSTOM_D4` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T5` varchar(255) default NULL,
  `CUSTOM_N5` int(255) default '0',
  `CUSTOM_F5` float default '0',
  `CUSTOM_D5` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T6` varchar(255) default NULL,
  `CUSTOM_T7` varchar(255) default NULL,
  `CUSTOM_T8` varchar(255) default NULL,
  `CUSTOM_T9` varchar(255) default NULL,
  `CUSTOM_T10` varchar(255) default NULL,
  PRIMARY KEY  (`ARBOX_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Archive box (about 500 sheets)';

CREATE TABLE `arcontainers` (
  `ARCONTAINER_ID` varchar(32) NOT NULL default '',
  `TITLE` varchar(255) default NULL,
  `SUBJECT` text,
  `DESCRIPTION` text,
  `STATUS` varchar(3) NOT NULL default '',
  `STORAGE_ID` varchar(32) default NULL,
  `ADDRESS` varchar(255) default NULL,
  `CREATION_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `RETENTION_TIME` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T1` varchar(50) default NULL,
  `CUSTOM_N1` int(255) default '0',
  `CUSTOM_F1` float default '0',
  `CUSTOM_D1` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T2` varchar(3) default NULL,
  `CUSTOM_N2` int(255) default '0',
  `CUSTOM_F2` float default '0',
  `CUSTOM_D2` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T3` varchar(255) default NULL,
  `CUSTOM_N3` int(255) default '0',
  `CUSTOM_F3` float default '0',
  `CUSTOM_D3` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T4` varchar(255) default NULL,
  `CUSTOM_N4` int(255) default '0',
  `CUSTOM_F4` float default '0',
  `CUSTOM_D4` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T5` varchar(255) default NULL,
  `CUSTOM_N5` int(255) default '0',
  `CUSTOM_F5` float default '0',
  `CUSTOM_D5` datetime default '0000-00-00 00:00:00',
  `CUSTOM_T6` varchar(255) default NULL,
  `CUSTOM_T7` varchar(255) default NULL,
  `CUSTOM_T8` varchar(255) default NULL,
  `CUSTOM_T9` varchar(255) default NULL,
  `CUSTOM_T10` varchar(255) default NULL,
  PRIMARY KEY  (`ARCONTAINER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Archive container (usually 5 archive boxes)';

CREATE TABLE `authors` (
  `AUTHOR_ID` varchar(32) NOT NULL default '',
  `FIRSTNAME` varchar(255) default NULL,
  `LASTNAME` varchar(255) default NULL,
  `ENABLED` char(1) default 'Y',
  `CUSTOM_T1` text,
  `CUSTOM_T2` varchar(255) default NULL,
  `CUSTOM_T3` varchar(255) default NULL,
  `CUSTOM_T4` varchar(255) default NULL,
  `CUSTOM_T5` varchar(255) default NULL,
  `CUSTOM_T6` varchar(255) default NULL,
  `CUSTOM_T7` varchar(255) default NULL,
  `CUSTOM_T8` varchar(255) default NULL,
  `CUSTOM_T9` varchar(255) default NULL,
  `CUSTOM_T10` varchar(255) default NULL,
  `CUSTOM_D1` datetime default NULL,
  `CUSTOM_N1` double default NULL,
  PRIMARY KEY  (`AUTHOR_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Authors or creators of the resource';

CREATE TABLE `baskets` (
`RES_TABLE` varchar(32)
,`BASKET_ID` varchar(32)
,`BASKET_NAME` varchar(32)
,`BASKET_DESC` varchar(255)
,`BASKET_CLAUSE` text
,`IS_GENERIC` char(1)
,`ENABLED` char(1)
);

CREATE TABLE `docservers` (
  `DOCSERVER_ID` varchar(32) NOT NULL default '1',
  `DEVICE_TYPE` varchar(32) default NULL,
  `DEVICE_LABEL` varchar(255) default NULL,
  `IS_READONLY` char(1) default 'N',
  `ENABLED` char(1) NOT NULL default 'Y',
  `SIZE_LIMIT` bigint(8) NOT NULL default '0',
  `ACTUAL_SIZE` bigint(8) NOT NULL default '0',
  `PATH_TEMPLATE` varchar(255) NOT NULL default '',
  `EXT_DOCSERVER_INFO` varchar(255) default NULL,
  `CHAIN_BEFORE` varchar(32) default NULL,
  `CHAIN_AFTER` varchar(32) default NULL,
  `CREATION_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `CLOSING_DATE` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`DOCSERVER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Description of the electronic media';

CREATE TABLE `doctypes` (
  `RES_TABLE` varchar(32) NOT NULL default '',
  `TYPE_ID` varchar(50) NOT NULL default '',
  `DESCRIPTION` varchar(255) NOT NULL default '',
  `DELAI_TRAITEMENT` int(2) NOT NULL default '21',
  `DELAI_RELANCE1` int(2) NOT NULL default '14',
  `DELAI_RELANCE2` int(2) NOT NULL default '1',
  `CUSTOM_T1` varchar(255) default NULL,
  `CUSTOM_T2` varchar(255) default NULL,
  `CUSTOM_T3` varchar(255) default NULL,
  `ENABLED` char(1) NOT NULL default 'Y',
  `GENERATE` char(1) NOT NULL default 'N',
  `MODEL` varchar(255) default NULL,
  PRIMARY KEY  (`TYPE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Resource types';

CREATE TABLE `ext_docserver` (
  `DOC_ID` varchar(255) NOT NULL default '',
  `PATH` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`DOC_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Sample for external document server (Ascent Capture)';

CREATE TABLE `groupbasket` (
  `GROUP_ID` varchar(32) NOT NULL default '',
  `BASKET_ID` varchar(32) NOT NULL default '',
  `SEQUENCE` int(11) NOT NULL default '0',
  `REDIRECT_BASKETLIST` text,
  `REDIRECT_GROUPLIST` text,
  `CAN_REDIRECT` char(1) NOT NULL default 'Y',
  `CAN_DELETE` char(1) NOT NULL default 'N',
  `CAN_INSERT` char(1) NOT NULL default 'N',
  `RESULT_PAGE` varchar(255) default NULL,
  PRIMARY KEY  (`GROUP_ID`,`BASKET_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Group to basket relation (for primary group only)';

CREATE TABLE `groupsecurity` (
  `GROUP_ID` varchar(32) NOT NULL default '',
  `RESGROUP_ID` varchar(32) NOT NULL default '',
  `CAN_VIEW` char(1) NOT NULL default '',
  `CAN_ADD` char(1) NOT NULL default '',
  `CAN_DELETE` char(1) NOT NULL default '',
  PRIMARY KEY  (`GROUP_ID`,`RESGROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Special security policy on resource groups';

CREATE TABLE `history` (
  `ID` bigint(8) NOT NULL auto_increment,
  `TABLE_NAME` varchar(32) default NULL,
  `RECORD_ID` varchar(255) default NULL,
  `EVENT_TYPE` varchar(32) NOT NULL default '',
  `USER_ID` varchar(50) NOT NULL default '',
  `EVENT_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `INFO` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Event history' AUTO_INCREMENT=1 ;

CREATE TABLE `listinstance` (
  `RES_TABLE` varchar(32) NOT NULL,
  `RES_ID` bigint(20) NOT NULL,
  `SEQUENCE` bigint(8) NOT NULL default '0',
  `USER_ID` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`RES_TABLE`,`RES_ID`,`SEQUENCE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `listmodel` (
  `RES_TABLE` varchar(32) NOT NULL default 'res_x',
  `ID` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,
  `SEQUENCE` bigint(8) NOT NULL default '0',
  `USER_ID` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`RES_TABLE`,`ID`,`SEQUENCE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `models` (
  `ID` bigint(20) NOT NULL auto_increment,
  `LABEL` varchar(50) character set utf8 NOT NULL,
  `CREATION_DATE` datetime NOT NULL,
  `COMMENT` varchar(255) character set utf8 default NULL,
  `CONTENT` longtext character set utf8 NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

CREATE TABLE `model_service` (
  `ID_SERVICE` varchar(30) character set utf8 NOT NULL,
  `ID_MODEL` bigint(20) NOT NULL,
  PRIMARY KEY  (`ID_SERVICE`,`ID_MODEL`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `notes` (
  `ID` bigint(8) NOT NULL auto_increment,
  `RECORD_ID` varchar(255) default NULL,
  `USER_ID` varchar(50) NOT NULL default '',
  `DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `INFO` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Event history' AUTO_INCREMENT=1 ;

CREATE TABLE `parameters` (
  `KEY1` varchar(32) NOT NULL default '',
  `KEY2` varchar(32) NOT NULL default '',
  `KEY3` varchar(32) NOT NULL default '',
  `VALUE` varchar(32) NOT NULL default '0',
  `LIB_PARAM` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`KEY1`,`KEY2`,`KEY3`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `resgroups` (
  `RESGROUP_ID` varchar(32) NOT NULL default '',
  `RESGROUP_DESC` varchar(255) NOT NULL default '',
  `CREATED_BY` varchar(255) NOT NULL default '',
  `CREATION_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`RESGROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Resource groups';

CREATE TABLE `resgroup_content` (
  `RES_TABLE` varchar(8) NOT NULL default '',
  `RES_ID` bigint(8) NOT NULL default '0',
  `RESGROUP_ID` varchar(32) NOT NULL default '',
  `SEQUENCE` int(11) NOT NULL default '0',
  PRIMARY KEY  (`RES_TABLE`,`RES_ID`,`RESGROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Resource group content (multi table)';

CREATE TABLE `res_x` (
  `RES_ID` bigint(8) NOT NULL auto_increment,
  `TITLE` varchar(255) default NULL,
  `SUBJECT` text,
  `DESCRIPTION` text,
  `PUBLISHER` varchar(255) default NULL,
  `CONTRIBUTOR` varchar(255) default NULL,
  `TYPE_ID` varchar(50) NOT NULL default '',
  `FORMAT` varchar(50) NOT NULL default '',
  `TYPIST` varchar(50) NOT NULL default '',
  `CREATION_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `AUTHOR` varchar(255) default NULL,
  `AUTHOR_NAME` text,
  `IDENTIFIER` varchar(255) default NULL,
  `SOURCE` varchar(255) default NULL,
  `DOC_LANGUAGE` varchar(50) default NULL,
  `RELATION` varchar(255) default NULL,
  `COVERAGE` varchar(255) default NULL,
  `DOC_DATE` datetime default NULL,
  `RIGHTS` varchar(20) NOT NULL default '0',
  `DOCSERVER_ID` varchar(32) NOT NULL default '',
  `ARBOX_ID` varchar(32) default NULL,
  `PATH` varchar(255) default NULL,
  `FILENAME` varchar(255) default NULL,
  `OFFSET` varchar(255) default NULL,
  `LOGICAL_ADR` varchar(255) default NULL,
  `FINGERPRINT` varchar(255) default NULL,
  `FILESIZE` bigint(20) default NULL,
  `IS_PAPER` char(1) default NULL,
  `PAGE_COUNT` int(11) default NULL,
  `SCAN_DATE` datetime default NULL,
  `SCAN_USER` varchar(50) default NULL,
  `SCAN_LOCATION` varchar(255) default NULL,
  `SCAN_WKSTATION` varchar(255) default NULL,
  `SCAN_BATCH` varchar(50) default NULL,
  `BURN_BATCH` varchar(50) default NULL,
  `SCAN_POSTMARK` varchar(50) default NULL,
  `ENVELOP_ID` bigint(20) default NULL,
  `STATUS` varchar(3) default 'NEW',
  `DESTINATION` varchar(50) default NULL,
  `DEST_USER` varchar(50) default NULL,
  `APPROVER` varchar(50) default NULL,
  `VALIDATION_DATE` datetime default NULL,
  `WORK_BATCH` bigint(8) default NULL,
  `ORIGIN` varchar(50) default NULL,
  `IS_INGOING` char(1) default NULL,
  `PRIORITY` smallint(6) default NULL,
  `CUSTOM_T1` text,
  `CUSTOM_N1` bigint(20) default NULL,
  `CUSTOM_F1` float default NULL,
  `CUSTOM_D1` datetime default NULL,
  `CUSTOM_T2` varchar(255) default NULL,
  `CUSTOM_N2` bigint(20) default NULL,
  `CUSTOM_F2` float default NULL,
  `CUSTOM_D2` datetime default NULL,
  `CUSTOM_T3` varchar(255) default NULL,
  `CUSTOM_N3` bigint(20) default NULL,
  `CUSTOM_F3` float default NULL,
  `CUSTOM_D3` datetime default NULL,
  `CUSTOM_T4` varchar(255) default NULL,
  `CUSTOM_N4` bigint(20) default NULL,
  `CUSTOM_F4` float default NULL,
  `CUSTOM_D4` datetime default NULL,
  `CUSTOM_T5` varchar(255) NOT NULL default 'false',
  `CUSTOM_N5` bigint(20) default NULL,
  `CUSTOM_F5` float default NULL,
  `CUSTOM_D5` datetime default NULL,
  `CUSTOM_T6` varchar(255) NOT NULL default 'false',
  `CUSTOM_T7` varchar(255) NOT NULL default 'false',
  `CUSTOM_T8` varchar(255) NOT NULL default 'false',
  `CUSTOM_T9` varchar(255) default NULL,
  `CUSTOM_T10` varchar(255) default NULL,
  `CUSTOM_T11` varchar(255) default NULL,
  `CUSTOM_T12` varchar(255) default NULL,
  `CUSTOM_T13` varchar(255) default NULL,
  `CUSTOM_T14` varchar(255) default NULL,
  `CUSTOM_T15` varchar(255) NOT NULL default 'false',
  `CUSTOM_T16` varchar(255) NOT NULL default 'false',
  `CUSTOM_T17` varchar(255) NOT NULL default 'false',
  `IS_FOLDER` char(1) NOT NULL default 'N',
  `FOLDER_ID` varchar(50) default NULL,
  `SOCIETE` varchar(255) default NULL,
  `FLAG_NOTIF` char(1) NOT NULL default 'N',
  `FLAG_RELANCE1` char(1) NOT NULL default 'N',
  `FLAG_RELANCE2` char(1) NOT NULL default 'N',
  PRIMARY KEY  (`RES_ID`),
  FULLTEXT KEY `search` (`TITLE`,`DESCRIPTION`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Contains all resource descriptors (can be fragmented)' AUTO_INCREMENT=1 ;

CREATE TABLE `security` (
  `GROUP_ID` varchar(32) NOT NULL default '',
  `RES_TABLE` varchar(32) NOT NULL default '',
  `WHERE_CLAUSE` text NOT NULL,
  `COMMENT` text,
  `CAN_INSERT` char(1) NOT NULL default 'N',
  `CAN_UPDATE` char(1) NOT NULL default 'N',
  PRIMARY KEY  (`GROUP_ID`,`RES_TABLE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Rights for a group to a table (SQL where clause restriction)';

CREATE TABLE `sender` (
  `ID` int(8) NOT NULL auto_increment,
  `LASTNAME` varchar(255) collate utf8_unicode_ci NOT NULL,
  `FIRSTNAME` varchar(255) collate utf8_unicode_ci default NULL,
  `SOCIETY` varchar(255) collate utf8_unicode_ci default NULL,
  `FUNCTION` varchar(255) collate utf8_unicode_ci default NULL,
  `STREET` varchar(255) collate utf8_unicode_ci NOT NULL,
  `TOWN` varchar(255) collate utf8_unicode_ci NOT NULL,
  `CP` varchar(20) collate utf8_unicode_ci NOT NULL,
  `PHONE` varchar(20) collate utf8_unicode_ci NOT NULL,
  `MAIL` varchar(255) collate utf8_unicode_ci NOT NULL,
  `TOCONTACT` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

CREATE TABLE `services` (
  `ID` varchar(30) NOT NULL default '',
  `SERVICE` varchar(50) NOT NULL default '',
  `ENABLED` char(1) NOT NULL default 'Y',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `usergroups` (
  `GROUP_ID` varchar(32) NOT NULL default '',
  `GROUP_DESC` varchar(255) default NULL,
  `ADMINISTRATOR` char(1) NOT NULL default 'N',
  `CONSULT_GROUP` char(1) NOT NULL default 'Y',
  `VIEW_RELANCE` char(1) NOT NULL default 'N',
  `VIEW_STATS` char(1) NOT NULL default 'N',
  `MODIF_RIGHTS` char(1) NOT NULL default 'N',
  `EXPORT` char(1) NOT NULL default 'N',
  `ENABLED` char(1) NOT NULL default 'Y',
  PRIMARY KEY  (`GROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='User groups';

CREATE TABLE `usergroup_content` (
  `USER_ID` varchar(32) NOT NULL default '',
  `GROUP_ID` varchar(32) NOT NULL default '',
  `PRIMARY_GROUP` char(1) NOT NULL default '',
  `ROLE` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`USER_ID`,`GROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='User to group relation';

CREATE TABLE `users` (
  `USER_ID` varchar(32) NOT NULL default '',
  `PASSWORD` varchar(255) default NULL,
  `FIRSTNAME` varchar(255) default NULL,
  `LASTNAME` varchar(255) default NULL,
  `PHONE` varchar(15) default NULL,
  `MAIL` varchar(255) default NULL,
  `DEPARTMENT` varchar(50) default '1',
  `FONCTION` varchar(255) default NULL,
  `COOKIE_KEY` varchar(255) default NULL,
  `COOKIE_DATE` datetime default '0000-00-00 00:00:00',
  `ELU` char(1) NOT NULL default 'N',
  `ENABLED` char(1) NOT NULL default 'Y',
  `STATUS` varchar(12) NOT NULL default 'OK',
  `CHANGE_PASSWORD` char(1) NOT NULL default 'Y',
  `DELAY` datetime default NULL,
  PRIMARY KEY  (`USER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Users';

CREATE TABLE `user_abs` (
  `USER_ABS` varchar(30) NOT NULL default '',
  `NEW_USER` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`USER_ABS`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `welcome_txt` (
  `ID` varchar(30) collate utf8_unicode_ci NOT NULL,
  `VALUE` longtext collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `baskets`;

CREATE TABLE `baskets` (
  `RES_TABLE` varchar(32) NOT NULL default '',
  `BASKET_ID` varchar(32) NOT NULL default '',
  `BASKET_NAME` varchar(32) character set utf8 collate utf8_unicode_ci NOT NULL,
  `BASKET_DESC` varchar(255) NOT NULL default '',
  `BASKET_CLAUSE` text NOT NULL,
  `IS_GENERIC` char(1) NOT NULL default 'N',
  `ENABLED` char(1) NOT NULL default 'Y',
  PRIMARY KEY  (`RES_TABLE`,`BASKET_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='View on documents';