DROP TABLE IF EXISTS `pboard`.`pb_board_boards`;
CREATE TABLE  `pboard`.`pb_board_boards` (
  `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'board id',
  `name` varchar(64) NOT NULL default '' COMMENT 'board name',
  `description` varchar(255) NOT NULL default '' COMMENT 'board description',
  `position` int(10) unsigned NOT NULL default '0' COMMENT 'board is displayed at that position',
  `parentId` bigint(20) unsigned NOT NULL default '0' COMMENT 'the id of the parent board if any',
  `containsMessages` tinyint(1) NOT NULL default '0',
  `roleRequired` int(10) unsigned NOT NULL default '0' COMMENT 'restricted access, 0 = everybody',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='board boards';

DROP TABLE IF EXISTS `pboard`.`pb_board_messages`;
CREATE TABLE  `pboard`.`pb_board_messages` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `originatorId` bigint(20) unsigned NOT NULL default '0' COMMENT 'id of originator of the message',
  `editorId` bigint(20) unsigned NOT NULL default '0' COMMENT 'id of last editor of the message',
  `threadId` bigint(20) unsigned NOT NULL default '0' COMMENT 'message belongs to this thread',
  `parentMsgId` bigint(20) unsigned default NULL COMMENT 'message may has a parent message',
  `messageRaw` text NOT NULL COMMENT 'raw message data',
  `messageCompiled` text COMMENT 'compiled message to display',
  `created` timestamp NOT NULL default '0000-00-00 00:00:00',
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'last time the message was modified',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `FULLTEXT_MESSAGERAW` (`messageRaw`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='board messages';

DROP TABLE IF EXISTS `pboard`.`pb_board_settings`;
CREATE TABLE  `pboard`.`pb_board_settings` (
  `name` varchar(32) NOT NULL default '',
  `value` varchar(512) NOT NULL default '',
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='board settings';

DROP TABLE IF EXISTS `pboard`.`pb_board_threads`;
CREATE TABLE  `pboard`.`pb_board_threads` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `title` varchar(128) NOT NULL default '',
  `subTitle` varchar(128) NOT NULL default '',
  `status` set('fixed','deleted','closed','open','virtual') NOT NULL default 'virtual',
  `boardId` bigint(20) unsigned NOT NULL default '0',
  `readCount` int(10) unsigned NOT NULL default '0',
  `messageCount` bigint(20) unsigned NOT NULL default '0',
  `lastAction` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='board threads';

DROP TABLE IF EXISTS `pboard`.`pb_role`;
CREATE TABLE  `pboard`.`pb_role` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rolename` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='role';

DROP TABLE IF EXISTS `pboard`.`pb_user`;
CREATE TABLE  `pboard`.`pb_user` (
  `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'user id',
  `username` varchar(64) NOT NULL default '' COMMENT 'name of the user',
  `password` varchar(512) NOT NULL default '' COMMENT 'password of the user',
  `oldId` int(10) unsigned default '0' COMMENT 'user id in old board',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `USERNAME_INDEX` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user';

DROP TABLE IF EXISTS `pboard`.`pb_user_profile`;
CREATE TABLE  `pboard`.`pb_user_profile` (
  `userId` bigint(20) unsigned NOT NULL default '0',
  `realname` varchar(30) NOT NULL default '',
  `email` varchar(64) NOT NULL default '',
  `homepage` varchar(64) default NULL,
  `location` varchar(30) default NULL,
  `signatureCompiled` text,
  `signatureRaw` text,
  `imageUrl` varchar(100) default NULL,
  `sex` tinyint(1) default NULL,
  `interests` varchar(200) default NULL,
  `comment` varchar(100) default NULL,
  `birthday` date default NULL,
  `aolId` varchar(20) default NULL,
  `icqId` varchar(20) default NULL,
  `msnId` varchar(30) default NULL,
  `yahooId` varchar(20) default NULL,
  `avatar` varchar(100) default NULL,
  PRIMARY KEY  (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user profile';

DROP TABLE IF EXISTS `pboard`.`pb_user_role`;
CREATE TABLE  `pboard`.`pb_user_role` (
  `userId` bigint(20) unsigned NOT NULL default '0',
  `roleId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userId`,`roleId`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COMMENT='user role';

DROP TABLE IF EXISTS `pboard`.`pb_user_session`;
CREATE TABLE  `pboard`.`pb_user_session` (
  `id` char(136) NOT NULL default '',
  `session_data` text NOT NULL,
  `expires` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COMMENT='user sessions';

DROP TABLE IF EXISTS `pboard`.`pb_user_settings`;
CREATE TABLE  `pboard`.`pb_user_settings` (
  `userId` bigint(20) unsigned NOT NULL default '0',
  `lastLogon` datetime NOT NULL default '0000-00-00 00:00:00',
  `lastAction` datetime default NULL,
  `languageCode` char(3) NOT NULL default 'de',
  `showImages` tinyint(1) NOT NULL default '1',
  `cssStylesheet` varchar(40) default NULL,
  `postCount` int(10) unsigned NOT NULL default '0',
  `registerDate` datetime default NULL,
  `showEmail` tinyint(1) NOT NULL default '0',
  `isApproved` tinyint(1) NOT NULL default '0',
  `approveId` char(36) NOT NULL default '',
  PRIMARY KEY  (`userId`),
  KEY `lastaction` (`lastAction`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user settings';