CREATE TABLE wbmandator (
    mandatorid int(11) unsigned NOT NULL AUTO_INCREMENT,
    customerid int(11) unsigned NOT NULL DEFAULT 0, /* connection to ValueAce */
    customer varchar(255) NOT NULL DEFAULT '',
    tradename varchar(64) NOT NULL DEFAULT '',
    enabled tinyint(1) NOT NULL DEFAULT '1',
    visible tinyint(1) NOT NULL DEFAULT '1',
    usermax int(11) unsigned NOT NULL DEFAULT 0,
    blurb text NOT NULL DEFAULT '',
    useowncheckout4cart tinyint(1) NOT NULL DEFAULT 0,
    useowncheckout4participant tinyint(1) NOT NULL DEFAULT 0,
    useowninvoice4cart tinyint(1) NOT NULL DEFAULT 0,
    useowninvoice4participant tinyint(1) NOT NULL DEFAULT 0,
    useownpayment tinyint(1) NOT NULL DEFAULT 0,
    newsletter tinyint(1) NOT NULL DEFAULT 0,
    created datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    changed datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    deleted tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (mandatorid),
    KEY (customerid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 COMMENT='Mandators Ids';

/*
 * CAPTCHA Requests
 */
CREATE TABLE `wbcaptcharequest` (
    captcharequestid int(11) unsigned NOT NULL AUTO_INCREMENT
    , namespace VARCHAR(200) NOT NULL DEFAULT ''
    , authenticated tinyint(1) NOT NULL DEFAULT 0
    , created DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
    , PRIMARY KEY (captcharequestid)
    , KEY nsa (namespace, authenticated)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='Count CAPTCHA requests';

/*
 * Event Queue
 */
CREATE TABLE `wbeventqueue` (
    eid INT(11) NOT NULL AUTO_INCREMENT
     , namespace VARCHAR(256) NOT NULL DEFAULT ''
     , action VARCHAR(256) NOT NULL DEFAULT ''
     , name VARCHAR(200) NOT NULL DEFAULT ''
     , message VARCHAR(255) NOT NULL DEFAULT ''
     , data BLOB NOT NULL DEFAULT ''
     , created DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , failed TINYINT(1) NOT NULL DEFAULT 0
     , PRIMARY KEY eidix (eid)
     , KEY namespaceidx (namespace)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='event queue for application level events';

 /*
  * general purpose obscure codes
  */
CREATE TABLE `wbobscurecode` (
     namespace VARCHAR(200) NOT NULL
     , xid VARCHAR(125) NOT NULL
     , code VARCHAR(255) NOT NULL DEFAULT ''
     , created DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , KEY namespaceidx (namespace, xid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='multipurpose obscure codes';

/*
 * Multi purpose counter
 */
CREATE TABLE `wbcounter` (
     namespace CHAR(32) NOT NULL DEFAULT ""
     , counter INT(11) UNSIGNED NOT NULL DEFAULT 0
     , changed DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , PRIMARY KEY namespace (namespace)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='multi purpose counters';


/*
 * User System
 */
CREATE TABLE wbpersongender (
    persongenderid INT(11) UNSIGNED NOT NULL AUTO_INCREMENT
    , gender VARCHAR(255) NOT NULL DEFAULT ''
    , salutation VARCHAR(255) NOT NULL DEFAULT ''
    , created datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
    , changed datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
    , deleted tinyint(1) NOT NULL DEFAULT 0
    , PRIMARY KEY (persongenderid)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='gender definitions';

INSERT INTO wbpersongender (persongenderid, gender, salutation) VALUES
(1, "neutral", ""),
(2, "rather not say", ""),
(3, "female", "Mrs"),
(4, "male", "Mr"),
(5, "other", "");
UPDATE wbpersongender SET created = now(), changed = now();

CREATE TABLE wbuser (
       uid INT(11) UNSIGNED NOT NULL AUTO_INCREMENT
     , nickname VARCHAR(64) NOT NULL
     , password CHAR(128) NOT NULL DEFAULT ""
     , passwordalgo CHAR(32) NOT NULL DEFAULT ""
     , passwordsalt CHAR(32) NOT NULL DEFAULT ""
     , passworditeration INT(11) UNSIGNED NOT NULL DEFAULT 100000
     , vfsfid INT(11) UNSIGNED NOT NULL DEFAULT 0
     , emaillocal VARCHAR(200) NOT NULL
     , emaildomain VARCHAR(125) NOT NULL
     , approved TINYINT NOT NULL DEFAULT 0
     , enabled TINYINT NOT NULL DEFAULT 0
     , deleted TINYINT NOT NULL DEFAULT 0
     , persongenderid INT(11) UNSIGNED NOT NULL DEFAULT 0
     , title VARCHAR(255) NOT NULL DEFAULT ''
     , forename VARCHAR(255) NOT NULL DEFAULT ''
     , surname VARCHAR(255) NOT NULL DEFAULT ''
     , address text NOT NULL DEFAULT ''
     , zip varchar(64) NOT NULL DEFAULT ''
     , city varchar(64) NOT NULL DEFAULT ''
     , lang CHAR(5) NOT NULL DEFAULT 'C'
     , country CHAR(2) NOT NULL DEFAULT 'C'
     , stateid int(11) unsigned NOT NULL DEFAULT 0
     , brief text NOT NULL DEFAULT ''
     , blurb text NOT NULL DEFAULT ''
     , created DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , changed DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , lastlogin DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , PRIMARY KEY (uid)
     , KEY email (emaillocal, emaildomain)
     , KEY (nickname)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='users';

INSERT INTO `wbuser` (uid, nickname, password, emaillocal, emaildomain, approved, forename, surname, lang, country, created, changed) VALUES
(1,'webmaster','xxx','webmaster','example.com',0,'','Webmaster','C','C','1970-07-15 00:09:24','2009-07-15 00:09:24'),
(2,'gerd','xxx','gerd','schaufelberger.de',1,'gERD','Schaufelberger','C','C','1975-08-23 20:45:24','1975-08-23 20:45:24'),
(3,'demo','xxx','wombat-demo-3','mailinator.com',1,'Demo','Wombat Demo User','C','C','1980-01-11 20:45:24','1980-08-23 20:45:24'),
(4,'wombat-its','xxx','wombat-its-4','mailinator.com',0,'Wombat','Incident Ticket System','C','C','1980-01-11 20:45:24','1980-08-23 20:45:24'),
(19,'webmaster-cms','xxx','wombat-webmaster','example.com',0,'CMS','Webmaster','C','C','1980-01-11 20:45:24','1980-08-23 20:45:24'),
(20,'webmaster-vfs','xxx','wombat-vfs-webmaster','example.com',0,'','VFS Webmaster','C','C','1980-01-11 20:45:24','1980-08-23 20:45:24');

CREATE TABLE wbmandatoruser (
    uid INT(11) UNSIGNED NOT NULL default 0
    , mandatorid INT(11) UNSIGNED NOT NULL default 0
    , KEY (uid)
    , KEY (mandatorid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Users of mandator';

CREATE TABLE `wbgroup` (
  `gid` int(11) AUTO_INCREMENT,
  `groupname` varchar(255) NOT NULL,
  `grouptitle` varchar(255) NOT NULL,
  PRIMARY KEY  (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='named user groups';

INSERT INTO wbgroup (gid,groupname, grouptitle) VALUES
(11, "admin", "Adminiatrator"),
(12, "blogeditor", "Blog Author"),
(13, "vfs", "Media File Manager"),
(14, "vfsgallery", "Media File Gallery Maintainer"),
(15, "guestbookeditor", "Guestbook Manager"),
(16, "contenteditor", "Author"),
(17, "nls-translator", "Translator"),
(18, "staff", "Employee"),
(19, "accounting", "Accounting"),
(20, "manager", "Manager"),
(21, "its-staff", "Incident Ticket Staff"),
(22, "its-master", "Incident Ticket Master"),
(24, "its-user", "Incident Ticket Staff"),
(25, "blogadmin", "Blog Adminr"),
(26, "customer", "Kunde"),
(27, "tax", "Tax Accounting"),
(29, "newsletter", "Newsletter"),
(30, "mandator", "Mandator");

CREATE TABLE `wbusergroup` (
  mandatorid INT(11) UNSIGNED NOT NULL default 0,
  `gid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  KEY `nickname` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='relation of users in group';

INSERT INTO wbusergroup (uid,gid) VALUES
(1, 11),
(1, 12),
(1, 13),
(1, 14),
(1, 15),
(1, 16),
(1, 17),
(1, 18),
(1, 19),
(1, 20);


/*
 * Comments for anything
 */
CREATE TABLE `wbcomment` (
  `cmtid` int(11) NOT NULL auto_increment,
  `namespace` varchar(32) NOT NULL,
  `xid` int(11) NOT NULL,
  `uid` int(11) default NULL,
  `xuid` int(11) default NULL default 0, /* origin user id */
  `approved` tinyint(1) NOT NULL default 0,
  `created` datetime NOT NULL,
  `changed` datetime default NULL,
  `commentbody` blob NOT NULL,
  `deleted` tinyint(1) NOT NULL default 0,
  PRIMARY KEY  (`cmtid`),
  KEY `namespaceidix` (`namespace`, `xid`),
  KEY `uid` (`uid`),
  KEY `xuid` (`xuid`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='Comments for anything';

 /*
  * Ratings for anything
  */
CREATE TABLE `wbrating` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `namespace` varchar(32) NOT NULL,
  `xid` int(11) NOT NULL,
  `uid` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  `value` int(11) NOT NULL DEFAULT 0,
  `blurb` text NOT NULL DEFAULT '',
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  KEY `namespaceidix` (`namespace`,`xid`),
  KEY `uid` (`uid`),
  KEY `mandatorid` (`mandatorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Ratings for anything';


 /*
  * URLs
  */
CREATE TABLE `wburl` (
  `urlid` int(10) NOT NULL AUTO_INCREMENT,
  `protocol` varchar(10) NOT NULL  DEFAULT "",
  `host` varchar(55) NOT NULL DEFAULT "",
  `path` varchar(200) NOT NULL DEFAULT "",
  `title` varchar(255) NOT NULL DEFAULT "",
  `vfsfid` int(11) DEFAULT NULL DEFAULT 0,
  `description` text NOT NULL DEFAULT "",
  `headlines` text NOT NULL DEFAULT "",
  `populated` datetime NULL ,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`urlid`),
  KEY `hostpathidx` (`host`,`path`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='List of URLs';

CREATE TABLE `wbreference` (
    refid INT(11) NOT NULL AUTO_INCREMENT
     , urlid INT(11) NOT NULL DEFAULT 0
     , path VARCHAR(255) NOT NULL DEFAULT ''
     , xnamespace VARCHAR(50) NOT NULL DEFAULT ''
     , xtag VARCHAR(50) NOT NULL DEFAULT ''
     , xid INT(11) NOT NULL DEFAULT 0
     , created DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00'
     , PRIMARY KEY idxrefid (refid)
     , KEY idxref (xnamespace, xtag, xid)
     , KEY idxurl (urlid)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='URLs refering to something';
