setupDatabase.sql

SET FOREIGN_KEY_CHECKS = 0;

create table if not exists o_forum (
   forum_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   f_refresname varchar(50),
   f_refresid bigint,
   primary key (forum_id)
);
create table o_forum_pseudonym (
   id bigint not null auto_increment,
   creationdate datetime not null,
   p_pseudonym varchar(255) not null,
   p_credential varchar(255) not null,
   p_salt varchar(255) not null,
   p_hashalgorithm varchar(16) not null,
   primary key (id)
);
create table if not exists o_property (
   id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   identity bigint,
   grp bigint,
   resourcetypename varchar(50),
   resourcetypeid bigint,
   category varchar(33),
   name varchar(255) not null,
   floatvalue FLOAT(65,30),
   longvalue bigint,
   stringvalue varchar(255),
   textvalue longtext,
   primary key (id)
);
create table if not exists o_bs_secgroup (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   primary key (id)
);

create table o_bs_group (
   id bigint not null,
   creationdate datetime not null,
   g_name varchar(36),
   primary key (id)
);

create table o_bs_group_member (
   id bigint not null,
   creationdate datetime not null,
   lastmodified datetime not null,
   g_role varchar(24) not null,
   g_inheritance_mode varchar(16) default 'none' not null,
   fk_group_id bigint not null,
   fk_identity_id bigint not null,
   primary key (id)
);

create table o_bs_grant (
   id bigint not null,
   creationdate datetime not null,
   g_role varchar(32) not null,
   g_permission varchar(32) not null,
   fk_group_id bigint not null,
   fk_resource_id bigint not null,
   primary key (id)
);

create table if not exists o_gp_business (
   group_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   lastusage datetime,
   groupname varchar(255),
   external_id varchar(64),
   managed_flags varchar(255),
   descr longtext,
   minparticipants integer,
   maxparticipants integer,
   waitinglist_enabled bit,
   autocloseranks_enabled bit,
   ownersintern bit not null default 0,
   participantsintern bit not null default 0,
   waitingintern bit not null default 0,
   ownerspublic bit not null default 0,
   participantspublic bit not null default 0,
   waitingpublic bit not null default 0,
   downloadmembers bit not null default 0,
   allowtoleave bit not null default 1,
   fk_resource bigint unique,
   fk_group_id bigint unique,
   primary key (group_id)
);
create table if not exists o_temporarykey (
   reglist_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   email varchar(2000) not null,
   regkey varchar(255) not null,
   ip varchar(255) not null,
   valid_until datetime,
   mailsent bit not null,
   action varchar(255) not null,
   fk_identity_id bigint,
   primary key (reglist_id)
);
create table if not exists o_bs_authentication (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   lastmodified datetime not null,
   identity_fk bigint not null,
   provider varchar(8),
   authusername varchar(255),
   credential varchar(255),
   salt varchar(255) default null,
   hashalgorithm varchar(16) default null,
   primary key (id),
   unique (provider, authusername)
);
create table if not exists o_bs_authentication_history (
   id bigint not null auto_increment,
   creationdate datetime,
   provider varchar(8),
   authusername varchar(255),
   credential varchar(255),
   salt varchar(255) default null,
   hashalgorithm varchar(16) default null,
   fk_identity bigint not null,
   primary key (id)
);
create table if not exists o_noti_pub (
   publisher_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   publishertype varchar(50) not null,
   data longtext,
   resname varchar(50),
   resid bigint,
   subident varchar(128),
   businesspath varchar(255),
   state integer,
   latestnews datetime not null,
   primary key (publisher_id)
);
create table if not exists o_qtiresultset (
   resultset_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime not null,
   creationdate datetime,
   identity_id bigint not null,
   olatresource_fk bigint not null,
   olatresourcedetail varchar(255) not null,
   assessmentid bigint not null,
   repositoryref_fk bigint not null,
   ispassed bit,
   issuspended bit default 0,
   fullyassessed bit default 0,
   score FLOAT(65,30),
   duration bigint,
   primary key (resultset_id)
);
create table if not exists o_bs_identity (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   lastlogin datetime,
   name varchar(128) not null unique,
   external_id varchar(64),
   status integer,
   deleteddate datetime,
   deletedroles varchar(1024),
   deletedby varchar(128),
   inactivationdate datetime,
   inactivationemaildate datetime,
   reactivationdate datetime,
   deletionemaildate datetime,
   primary key (id)
);
create table o_bs_relation_role (
   id bigint not null auto_increment,
   creationdate datetime not null,
   lastmodified datetime not null,
   g_role varchar(128) not null,
   g_external_id varchar(128),
   g_external_ref varchar(128),
   g_managed_flags varchar(256),
   primary key (id)
);
create table o_bs_relation_right (
   id bigint not null auto_increment,
   creationdate datetime not null,
   g_right varchar(128) not null,
   primary key (id)
);
create table o_bs_relation_role_to_right (
   id bigint not null auto_increment,
   creationdate datetime not null,
   fk_role_id bigint,
   fk_right_id bigint not null,
   primary key (id)
);
create table o_bs_identity_to_identity (
   id bigint not null auto_increment,
   creationdate datetime not null,
   g_external_id varchar(128),
   g_managed_flags varchar(256),
   fk_source_id bigint not null,
   fk_target_id bigint not null,
   fk_role_id bigint not null,
   primary key (id)
);
create table o_csp_log (
   id bigint not null auto_increment,
   creationdate datetime,
   l_blocked_uri varchar(1024),
   l_disposition varchar(32),
   l_document_uri varchar(1024),
   l_effective_directive mediumtext,
   l_original_policy mediumtext,
   l_referrer varchar(1024),
   l_script_sample mediumtext,
   l_status_code varchar(1024),
   l_violated_directive varchar(1024),
   l_source_file varchar(1024),
   l_line_number bigint,
   l_column_number bigint,
   fk_identity bigint,
   primary key (id)
);
create table if not exists o_olatresource (
   resource_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   resname varchar(50) not null,
   resid bigint not null,
   primary key (resource_id),
   unique (resname, resid)
);
create table if not exists o_bs_namedgroup (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   secgroup_id bigint not null,
   groupname varchar(16),
   primary key (id),
   unique (groupname)
);
create table if not exists o_catentry (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   name varchar(110) not null,
   description longtext,
   style varchar(16),
   externalurl varchar(255),
   fk_repoentry bigint,
   fk_ownergroup bigint unique,
   type integer not null,
   parent_id bigint,
   order_index bigint, 
   short_title varchar(255),
   add_entry_position int,
   add_category_position int,
   primary key (id)
);
create table if not exists o_note (
   note_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   owner_id bigint,
   resourcetypename varchar(50) not null,
   resourcetypeid bigint not null,
   sub_type varchar(50),
   notetitle varchar(255),
   notetext longtext,
   primary key (note_id)
);
create table if not exists o_references (
   reference_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   source_id bigint not null,
   target_id bigint not null,
   userdata varchar(64),
   primary key (reference_id)
);
create table if not exists o_user (
   user_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   language varchar(30),
   fontsize varchar(10),
   notification_interval varchar(16),
   presencemessagespublic bit,
   informsessiontimeout bit not null,
   receiverealmail varchar(16),
   u_firstname varchar(255),
   u_lastname varchar(255),
   u_email varchar(255),
   u_nickname varchar(255),
   u_birthday varchar(255),
   u_graduation varchar(255),
   u_gender varchar(255),
   u_telprivate varchar(255),
   u_telmobile varchar(255),
   u_teloffice varchar(255),
   u_smstelmobile varchar(255),
   u_skype varchar(255),
   u_msn varchar(255),
   u_xing varchar(255),
   u_linkedin text(255),
   u_icq varchar(255),
   u_homepage varchar(255),
   u_street varchar(255),
   u_extendedaddress varchar(255),
   u_pobox varchar(255),
   u_zipcode varchar(255),
   u_region varchar(255),
   u_city varchar(255),
   u_country varchar(255),
   u_countrycode varchar(255),
   u_institutionalname varchar(255),
   u_institutionaluseridentifier varchar(255),
   u_institutionalemail varchar(255),
   u_orgunit varchar(255),
   u_studysubject varchar(255),
   u_emchangekey varchar(255),
   u_emaildisabled varchar(255),
   u_typeofuser varchar(255),
   u_socialsecuritynumber varchar(255),

   u_rank varchar(255),
   u_degree varchar(255),
   u_position varchar(255),
   u_userinterests varchar(255),
   u_usersearchedinterests varchar(255),
   u_officestreet varchar(255),
   u_extendedofficeaddress varchar(255),
   u_officepobox varchar(255),
   u_officezipcode varchar(255),
   u_officecity varchar(255),
   u_officecountry varchar(255),
   u_officemobilephone varchar(255),
   u_department varchar(255),
   u_privateemail varchar(255),
   u_employeenumber text(255),
   u_organizationalunit text(255),

   u_edupersonaffiliation text(255),
   u_swissedupersonstaffcategory text(255),
   u_swissedupersonhomeorg text(255),
   u_swissedupersonstudylevel text(255),
   u_swissedupersonhomeorgtype text(255),
   u_swissedupersonstudybranch1 text(255),
   u_swissedupersonstudybranch2 text(255),
   u_swissedupersonstudybranch3 text(255),

   u_genericselectionproperty varchar(255),
   u_genericselectionproperty2 varchar(255),
   u_genericselectionproperty3 varchar(255),
   u_generictextproperty varchar(255),
   u_generictextproperty2 varchar(255),
   u_generictextproperty3 varchar(255),
   u_generictextproperty4 varchar(255),
   u_generictextproperty5 varchar(255),
   u_genericuniquetextproperty varchar(255),
   u_genericuniquetextproperty2 varchar(255),
   u_genericuniquetextproperty3 varchar(255),
   u_genericemailproperty1 varchar(255),
   u_genericcheckboxproperty varchar(255),
   u_genericcheckboxproperty2 varchar(255),
   u_genericcheckboxproperty3 varchar(255),

   fk_identity bigint,
   primary key (user_id)
);
create table if not exists o_userproperty (
   fk_user_id bigint not null,
   propname varchar(255) not null,
   propvalue varchar(255),
   primary key (fk_user_id, propname)
);
create table o_user_data_export (
   id bigint not null auto_increment,
   creationdate datetime,
   lastmodified datetime,
   u_directory varchar(255),
   u_status varchar(16),
   u_export_ids varchar(2000),
   fk_identity bigint not null,
   fk_request_by bigint,
   primary key (id)
);
create table o_user_absence_leave (
   id bigint not null auto_increment,
   creationdate datetime not null,
   lastmodified datetime not null,
   u_absent_from datetime,
   u_absent_to datetime,
   u_resname varchar(50),
   u_resid bigint,
   u_sub_ident varchar(2048),
   fk_identity bigint not null,
   primary key (id)
);

create table if not exists o_message (
   message_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   title varchar(100),
   body longtext,
   pseudonym varchar(255),
   guest bit default 0,
   parent_id bigint,
   topthread_id bigint,
   creator_id bigint,
   modifier_id bigint,
   modification_date datetime,
   forum_fk bigint,
   statuscode integer,
   numofwords integer,
   numofcharacters integer,
   primary key (message_id)
);



create table if not exists o_gp_bgtoarea_rel (
   bgtoarea_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   group_fk bigint not null,
   area_fk bigint not null,
   primary key (bgtoarea_id),
   unique (group_fk, area_fk)
);
create table if not exists o_noti_sub (
   publisher_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   fk_publisher bigint not null,
   fk_identity bigint not null,
   latestemailed datetime,
   subenabled bit default 1,
   primary key (publisher_id),
   unique (fk_publisher, fk_identity)
);
create table if not exists o_qtiresult (
   result_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime not null,
   creationdate datetime,
   itemident varchar(255) not null,
   answer longtext,
   duration bigint,
   score FLOAT(65,30),
   tstamp datetime not null,
   ip varchar(255),
   resultset_fk bigint,
   primary key (result_id)
);
create table if not exists o_bs_policy (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   oresource_id bigint not null,
   group_id bigint not null,
   permission varchar(16) not null,
   apply_from datetime default null,
   apply_to datetime default null,
   primary key (id),
   unique (oresource_id, group_id, permission)
);
create table if not exists o_gp_bgarea (
   area_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   name varchar(255) not null,
   descr longtext,
   fk_resource bigint default null,
   primary key (area_id)
);
create table if not exists o_repositoryentry (
   repositoryentry_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   softkey varchar(36) not null unique,
   external_id varchar(64),
   external_ref varchar(64),
   managed_flags varchar(255),
   displayname varchar(110) not null,
   resourcename varchar(100) not null,
   authors varchar(2048),
   mainlanguage varchar(255),
   location varchar(255),
   objectives text(32000),
   requirements text(32000),
   credits text(32000),
   expenditureofwork text(32000),
   fk_stats bigint not null unique,
   fk_lifecycle bigint,
   fk_olatresource bigint unique,
   description longtext,
   initialauthor varchar(128) not null,
   status varchar(16) default 'preparation' not null,
   allusers bit default 0 not null,
   guests bit default 0 not null,
   bookable bit default 0 not null,
   allowtoleave varchar(16),
   candownload bit not null,
   cancopy bit not null,
   canreference bit not null,
   deletiondate datetime default null,
   fk_deleted_by bigint default null,
   primary key (repositoryentry_id)
);
create table o_re_to_group (
   id bigint not null,
   creationdate datetime not null,
   r_defgroup boolean not null,
   fk_group_id bigint not null,
   fk_entry_id bigint not null,
   primary key (id)
);
create table o_re_to_tax_level (
  id bigint not null auto_increment,
  creationdate datetime not null,
  fk_entry bigint not null,
  fk_taxonomy_level bigint not null,
  primary key (id)
);
create table o_repositoryentry_cycle (
   id bigint not null,
   creationdate datetime not null,
   lastmodified datetime not null,
   r_softkey varchar(64),
   r_label varchar(255),
   r_privatecycle bit default 0,
   r_validfrom datetime,
   r_validto datetime,
   primary key (id)
);
create table o_repositoryentry_stats (
   id bigint not null,
   creationdate datetime not null,
   lastmodified datetime not null,
   r_rating decimal(65,30),
   r_num_of_ratings bigint not null default 0,
   r_num_of_comments bigint not null default 0,
   r_launchcounter bigint not null default 0,
   r_downloadcounter bigint not null default 0,
   r_lastusage datetime not null,
   primary key (id)
);
create table if not exists o_bs_membership (
   id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime,
   creationdate datetime,
   secgroup_id bigint not null,
   identity_id bigint not null,
   primary key (id),
   unique (secgroup_id, identity_id)
);

create table if not exists o_plock (
    plock_id bigint not null,
    version mediumint unsigned not null,
    creationdate datetime,
    asset varchar(255) not null unique,
    primary key (plock_id)
);

create table if not exists hibernate_unique_key (
    next_hi integer
);

create table if not exists o_lifecycle (
   id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   persistenttypename varchar(50) not null,
   persistentref bigint not null,
   action varchar(50) not null,
   lctimestamp datetime,
   uservalue longtext,
   primary key (id)
);

create table if not exists oc_lock (
    lock_id bigint not null,
    version mediumint unsigned not null,
    creationdate datetime,
    identity_fk bigint not null,
    asset varchar(120) not null unique,
    windowid varchar(32) default null,
    primary key (lock_id)
);

create table if not exists o_readmessage (
    id bigint not null,
    version mediumint unsigned not null,
    creationdate datetime,
    identity_id bigint not null,
    forum_id bigint not null,
    message_id bigint not null,
    primary key (id)
);

create table if not exists o_loggingtable (
    log_id bigint not null,
    creationdate datetime,
    sourceclass varchar(255),
    sessionid varchar(255) not null,
    user_id bigint,
    actioncrudtype varchar(1) not null,
    actionverb varchar(16) not null,
    actionobject varchar(32) not null,
    simpleduration bigint not null,
    resourceadminaction boolean not null,
    businesspath varchar(2048),
    greatgrandparentrestype varchar(32),
    greatgrandparentresid varchar(64),
    greatgrandparentresname varchar(255),
    grandparentrestype varchar(32),
    grandparentresid varchar(64),
    grandparentresname varchar(255),
    parentrestype varchar(32),
    parentresid varchar(64),
    parentresname varchar(255),
    targetrestype varchar(32),
    targetresid varchar(64),
    targetresname varchar(255),
    primary key (log_id)
);

create table if not exists o_checklist (
   checklist_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime not null,
   title varchar(255),
   description longtext,
   primary key (checklist_id)
);

create table if not exists o_checkpoint (
   checkpoint_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime not null,
   title varchar(255),
   description longtext,
   modestring varchar(64) not null,
   checklist_fk bigint,
   primary key (checkpoint_id)
);

create table if not exists o_checkpoint_results (
   checkpoint_result_id bigint not null,
   version mediumint unsigned not null,
   lastmodified datetime not null,
   result bool not null,
   checkpoint_fk bigint,
   identity_fk bigint,
   primary key (checkpoint_result_id)
);

create table if not exists o_projectbroker (
   projectbroker_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   primary key (projectbroker_id)
);

create table if not exists o_projectbroker_project (
   project_id bigint not null,
   version mediumint unsigned not null,
   creationdate datetime,
   title varchar(150),
   description longtext,
   state varchar(20),
   maxMembers integer,
   attachmentFileName varchar(100),
   mailNotificationEnabled boolean not null,
   projectgroup_fk bigint not null,
   projectbroker_fk bigint not null,
   candidategroup_fk bigint not null,
   primary key (project_id)
);

create table if not exists o_projectbroker_customfields (
   fk_project_id bigint not null,
   propname varchar(255) not null,
   propvalue varchar(255),
   primary key (fk_project_id, propname)
);

create table if not exists o_usercomment (
    comment_id bigint not null,
    version mediumint unsigned not null,
    creationdate datetime,
    resname varchar(50) not null,
    resid bigint not null,
    ressubpath varchar(2048),
    creator_id bigint not null,
    commenttext longtext,
    parent_key bigint,
    primary key (comment_id)
);
create table if not exists o_userrating (
    rating_id bigint not null,
    version mediumint unsigned not null,
    creationdate datetime,
    lastmodified datetime,
    resname varchar(50) not null,
    resid bigint not null,
    ressubpath varchar(2048),
    creator_id bigint not null,
    rating integer not null,
    primary key (rating_id)
);

create table o_co_db_entry (
   id bigint not null,
   version bigint not null,
   lastmodified datetime,
   creationdate datetime,
   courseid bigint,
   identity bigint,
   category varchar(32),
   name varchar(255) not null,
   floatvalue decimal(65,30),
   longvalue bigint,
   stringvalue varchar(255),
   textvalue mediumtext,
   primary key (id)
);

create table if not exists o_stat_lastupdated (

    lastupdated datetime not null

);
-- important: initialize with old date!
insert into o_stat_lastupdated values(date('1999-01-01'));


-- insert into o_stat_dayofweek (businesspath,resid,day,value) select businesspath,substr(businesspath,locate(':',businesspath)+1,locate(']',businesspath)-locate(':',businesspath)-1) resid,dayofweek(creationdate) day,count(*) cnt from o_loggingtable where actionverb='launch' and actionobject='node' group by businesspath,day;
create table if not exists o_stat_dayofweek (

    id bigint unsigned not null auto_increment,
    businesspath varchar(2048) not null,
    resid bigint not null,
    day int not null,
    value int not null,
    primary key (id)

);
create index statdow_resid_idx on o_stat_dayofweek (resid);


-- insert into o_stat_hourofday (businesspath,resid,hour,value) select businesspath,substr(businesspath,locate(':',businesspath)+1,locate(']',businesspath)-locate(':',businesspath)-1) resid,hour(creationdate) hour,count(*) cnt from o_loggingtable where actionverb='launch' and actionobject='node' group by businesspath,hour;
create table if not exists o_stat_hourofday (

    id bigint unsigned not null auto_increment,
    businesspath varchar(2048) not null,
    resid bigint not null,
    hour int not null,
    value int not null,
    primary key (id)

);
create index stathod_resid_idx on o_stat_hourofday (resid);


-- insert into o_stat_weekly (businesspath,resid,week,value) select businesspath,substr(businesspath,locate(':',businesspath)+1,locate(']',businesspath)-locate(':',businesspath)-1) resid,concat(year(creationdate),'-',week(creationdate)) week,count(*) cnt from o_loggingtable where actionverb='launch' and actionobject='node' group by businesspath,week;
create table if not exists o_stat_weekly (

    id bigint unsigned not null auto_increment,
    businesspath varchar(2048) not null,
    resid bigint not null,
    week varchar(7) not null,
    value int not null,
    primary key (id)

);
create index statwee_resid_idx on o_stat_weekly (resid);


-- insert into o_stat_daily (businesspath,resid,day,value) select businesspath,substr(businesspath,locate(':',businesspath)+1,locate(']',businesspath)-locate(':',businesspath)-1) resid,date(creationdate) day,count(*) cnt from o_loggingtable where actionverb='launch' and actionobject='node' group by businesspath,day;
create table if not exists o_stat_daily (

    id bigint unsigned not null auto_increment,
    businesspath varchar(2048) not null,
    resid bigint not null,
    day datetime not null,
    value int not null,
    primary key (id)

);
create index statday_resid_idx on o_stat_daily (resid);


create table if not exists o_mark (
  mark_id bigint not null,
  version mediumint unsigned not null,
  creationdate datetime,
  resname varchar(50) not null,
  resid bigint not null,
  ressubpath varchar(2048),
  businesspath varchar(2048),
  creator_id bigint not null,
  primary key (mark_id)
);

create table if not exists o_info_message (
  info_id bigint  NOT NULL,
  version mediumint NOT NULL,
  creationdate datetime,
  modificationdate datetime,
  title varchar(2048),
  message longtext,
  attachmentpath varchar(1024),
  resname varchar(50) NOT NULL,
  resid bigint NOT NULL,
  ressubpath varchar(2048),
  businesspath varchar(2048),
  fk_author_id bigint,
  fk_modifier_id bigint,
  primary key (info_id)
);


create table if not exists o_tag (
  tag_id bigint not null,
  version mediumint unsigned not null,
  creationdate datetime,
  tag varchar(128) not null,
  resname varchar(50) not null,
  resid bigint not null,
  ressubpath varchar(2048),
  businesspath varchar(2048),
  fk_author_id bigint not null,
  primary key (tag_id)
);

create table if not exists o_bs_invitation (
   id bigint not null,
   creationdate datetime,
   token varchar(64) not null,
   first_name varchar(64),
   last_name varchar(64),
   mail varchar(128),
   fk_group_id bigint,
   fk_identity_id bigint,
   primary key (id)
);

-- mail system

create table if not exists o_mail (
  mail_id bigint NOT NULL,
  meta_mail_id varchar(64),
  creationdate datetime,
    lastmodified datetime,
    resname varchar(50),
  resid bigint,
  ressubpath varchar(2048),
  businesspath varchar(2048),
  subject varchar(512),
  body longtext,
  fk_from_id bigint,
  primary key (mail_id)
);

-- mail recipient
create table if not exists o_mail_to_recipient (
  pos mediumint NOT NULL default 0,
  fk_mail_id bigint,
  fk_recipient_id bigint
);

create table if not exists o_mail_recipient (
  recipient_id bigint NOT NULL,
  recipientvisible bit,
  deleted bit,
  mailread bit,
  mailmarked bit,
  email varchar(255),
  recipientgroup varchar(255),
  creationdate datetime,
  fk_recipient_id bigint,
  primary key (recipient_id)
);

-- mail attachments
create table o_mail_attachment (
   attachment_id bigint NOT NULL,
   creationdate datetime,
   datas mediumblob,
   datas_size bigint,
   datas_name varchar(255),
   datas_checksum bigint,
   datas_path varchar(1024),
   datas_lastmodified datetime,
   mimetype varchar(255),
   fk_att_mail_id bigint,
   primary key (attachment_id)
);

-- access control
create table  if not exists o_ac_offer (
  offer_id bigint NOT NULL,
  creationdate datetime,
  lastmodified datetime,
  is_valid bit default 1,
  validfrom datetime,
  validto datetime,
  version mediumint unsigned not null,
  resourceid bigint,
  resourcetypename varchar(255),
  resourcedisplayname varchar(255),
  autobooking boolean default 0,
  confirmation_email bit default 0,
  token varchar(255),
  price_amount DECIMAL(12,4),
  price_currency_code VARCHAR(3),
  offer_desc VARCHAR(2000),
  fk_resource_id bigint,
  primary key (offer_id)
);

create table if not exists o_ac_method (
    method_id bigint NOT NULL,
    access_method varchar(32),
  version mediumint unsigned not null,
  creationdate datetime,
    lastmodified datetime,
    is_valid bit default 1,
    is_enabled bit default 1,
    validfrom datetime,
    validto datetime,
    primary key (method_id)
);

create table if not exists o_ac_offer_access (
    offer_method_id bigint NOT NULL,
  version mediumint unsigned not null,
  creationdate datetime,
    is_valid bit default 1,
    validfrom datetime,
    validto datetime,
  fk_offer_id bigint,
  fk_method_id bigint,
    primary key (offer_method_id)
);

create table o_ac_auto_advance_order (
  id bigint not null auto_increment,
  creationdate datetime not null,
  lastmodified datetime not null,
  a_identifier_key varchar(64) not null,
  a_identifier_value varchar(64) not null,
  a_status varchar(32) not null,
  a_status_modified datetime not null,
  fk_identity int8 not null,
  fk_method int8 not null,
  primary key (id)
);

-- access cart
create table if not exists o_ac_order (
    order_id bigint NOT NULL,
  version mediumint unsigned not null,
  creationdate datetime,
    lastmodified datetime,
    is_valid bit default 1,
    total_lines_amount DECIMAL(12,4),
    total_lines_currency_code VARCHAR(3),
    total_amount DECIMAL(12,4),
    total_currency_code VARCHAR(3),
    discount_amount DECIMAL(12,4),
    discount_currency_code VARCHAR(3),
    order_status VARCHAR(32) default 'NEW',
  fk_delivery_id bigint,
    primary key (order_id)
);

create table if not exists o_ac_order_part (
    order_part_id bigint NOT NULL,
  version mediumint unsigned not null,