/*
 * 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=MyISAM 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=MyISAM 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=MyISAM 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 ''
    , PRIMARY KEY (persongenderid)
) ENGINE=MyISAM 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");

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'
     , 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=MyISAM 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',1,'CMS','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'),
(19,'webmaster','xxx','wombat-webmaster','mailinator.com',1,'','Webmaster','C','C','1980-01-11 20:45:24','1980-08-23 20:45:24');

CREATE TABLE `wbgroup` (
  `gid` int(11) AUTO_INCREMENT,
  `groupname` varchar(255) NOT NULL,
  `grouptitle` varchar(255) NOT NULL,
  PRIMARY KEY  (`gid`)
) ENGINE=MyISAM 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");

CREATE TABLE `wbusergroup` (
  `gid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  KEY `nickname` (`uid`)
) ENGINE=MyISAM 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);


/*
 * omments 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=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='Comments for anything';

 /*
  * Ratings for anything
  */
CREATE TABLE `wbrating` (
  `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,
  `deleted` tinyint(1) NOT NULL default 0,
  KEY `namespaceidix` (`namespace`, `xid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 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=MyISAM 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=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='URLs refering to something';
