CREATE TABLE IF NOT EXISTS `jid` (
	`jid` VARCHAR(128) NOT NULL,
	`added_at` DATETIME NOT NULL,
	PRIMARY KEY (`jid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `jid_status` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`jid` VARCHAR(128) NOT NULL,
	`resource` VARCHAR(128) NOT NULL,
	`status` VARCHAR(128) NOT NULL,
	`added_at` DATETIME NOT NULL,
	PRIMARY KEY (`id`),
	KEY `added_at` (`added_at`),
	KEY `jid_date` (`jid`, `added_at`, `resource`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `jid_status_message` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`jid` VARCHAR(128) NOT NULL,
	`resource` VARCHAR(128) NOT NULL,
	`message` TEXT NOT NULL,
	`added_at` DATETIME NOT NULL,
	PRIMARY KEY (`id`),
	KEY `added_at` (`added_at`),
	KEY `jid_date` (`jid`, `added_at`, `resource`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `jid_status_map` (
	`status_id` INT NOT NULL,
	`status_message_id` INT NOT NULL,
	`added_at` DATETIME NOT NULL,
	UNIQUE `s_sm` (`status_id`, `status_message_id`),
	KEY `sm_s` (`status_message_id`, `status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `jid_commands` (
	`jid` VARCHAR(128) NOT NULL,
	`updated` DATETIME NOT NULL,
	`command` VARCHAR(128) NOT NULL,
	`count` INT NOT NULL,
	KEY `jid` (`jid`),
	KEY `com_count` (`command`, `count`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `blog` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`jid` VARCHAR(128) NOT NULL,
	`posted` DATETIME NOT NULL,
	`subject` VARCHAR(255) NOT NULL,
	`body` TEXT NOT NULL,
	PRIMARY KEY (`id`),
	KEY `jid` (`jid`),
	FULLTEXT KEY `subject_body` (`subject`, `body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `blog_tag` (
	`blog_id` INT NOT NULL,
	`tag` VARCHAR(32) NOT NULL,
	PRIMARY KEY (`blog_id`, `tag`),
	KEY `tag` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;