-- SHOW GRANTS FOR `gerd`@`localhost`;
-- CREATE DATABASE `wombat` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
-- GRANT ALL PRIVILEGES ON `wombat`.* TO `gerd`@`localhost`;
-- FLUSH PRIVILEGES;
-- USE `wombat`;

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

-- User management
CREATE TABLE `wbuser` (
       uid INT(11) NOT NULL AUTO_INCREMENT
     , nickname VARCHAR(255) NOT NULL
     , password CHAR(32)
     , 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
     , forename VARCHAR(255) NOT NULL DEFAULT ''
     , surname VARCHAR(255) NOT NULL DEFAULT ''
     , lang CHAR(5) NOT NULL DEFAULT 'C'
     , country CHAR(2) NOT NULL DEFAULT 'C'
     , 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 DEFAULT CHARSET=utf8 COMMENT='users';

CREATE TABLE `wbgroup` (
  `gid` int(11) AUTO_INCREMENT,
  `groupname` varchar(255) NOT NULL default 'maypor',
  PRIMARY KEY  (`gid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='named user group';

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

/*
CREATE TABLE `wbskin` (
  `uid` int(11) NOT NULL,
  `car` varchar(255) NOT NULL default 'maypor',
  `color` varchar(255) NOT NULL default 'black',
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='user skins';
*/

-- BLOG
CREATE TABLE `wbblog` (
  `blogid` int(10) NOT NULL auto_increment,
  `catid` int(11) NOT NULL,
  `uid` int(10) NOT NULL default '0',
  `title` varchar(200) default NULL,
  `created` datetime default NULL,
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  `body` text NOT NULL,
  `deleted` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`blogid`),
  KEY `catid` (`catid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Blog Articles';

CREATE TABLE `wbblogcategory` (
  `catid` int(11) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL,
  `brief` text NOT NULL,
  `created` datetime NOT NULL,
  `changed` datetime NOT NULL,
  `owner` varchar(250) NOT NULL default '*',
  `other` set('list','publish','edit') NOT NULL,
  `moderated` tinyint(1) NOT NULL default '0',
  `commentable` enum('nobody','user','admin','anonymous') NOT NULL,
  `uid` int(11) NOT NULL,
  `deleted` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`catid`),
  KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Blog Categories';

CREATE TABLE `wbblogcomment` (
  `blogcid` int(11) NOT NULL auto_increment,
  `blog` varchar(100) NOT NULL,
  `blogid` int(11) NOT NULL,
  `uid` int(11) default NULL,
  `new` tinyint(1) NOT NULL default '1',
  `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  (`blogcid`),
  KEY `blog` (`blog`),
  KEY `blogid` (`blogid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Blog comments';


-- VFS
CREATE TABLE `wbvfsfile` (
  `vfsfid` int(11) NOT NULL auto_increment,
  `obscure` varchar(2) NOT NULL,
  `vfsdid` int(11) NOT NULL,
  `uid` varchar(255) NOT NULL,
  `size` int(11) NOT NULL default '0',
  `md5` varchar(32) NOT NULL,
  `storage` varchar(255) NOT NULL,
  `mimemajor` varchar(20) NOT NULL,
  `mimeminor` varchar(20) NOT NULL,
  `views` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `info` BLOB,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL,
  `deleted` tinyint(1) NOT NULL default 0,
  PRIMARY KEY  (`vfsfid`),
  KEY ( `uid` )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='media file';

CREATE TABLE `wbvfsfilequota` (
  `uid` int(11) NOT NULL,
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  `sizemax` int(11) UNSIGNED NOT NULL default '104857600',
  `filemax` int(11) UNSIGNED NOT NULL default '1000',
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='media file per user quota';


CREATE TABLE `wbvfsfilequeue` (
  `vfsfid` int(11) NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`vfsfid`),
  KEY ( `created` )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='media file queue';

CREATE TABLE `wbvfsdir` (
  `vfsdid` int(11) NOT NULL auto_increment,
  `uid` varchar(255) NOT NULL,
  `path` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '' ,
  `name` varchar(255) NOT NULL default '',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL,
  PRIMARY KEY  (`vfsdid` ),
  KEY ( `path` ),
  KEY ( `uid` )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='store per user folders';


CREATE TABLE `wbvfsfileupload` (
  `vfsuid` int(11) NOT NULL auto_increment,
  `uid` varchar(255) NOT NULL,
  `size` int(11) NOT NULL default '0',
  `md5` varchar(32) NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL,
  PRIMARY KEY  (`vfsuid`),
  KEY ( `uid` )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='media file upload';
