SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

DROP TABLE IF EXISTS `api`;
CREATE TABLE IF NOT EXISTS `api` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(16) NOT NULL,
  `url` varchar(128) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `name` (`name`),
  KEY `type` (`url`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

INSERT INTO `api` VALUES (1, 'mobile', 'http://mobile');
INSERT INTO `api` VALUES (2, 'jabber', 'http://jabber');

DROP TABLE IF EXISTS `favorites`;
CREATE TABLE IF NOT EXISTS `favorites` (
  `ID` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `note_id` int(11) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `user_id` (`user_id`,`note_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `invitations`;
CREATE TABLE IF NOT EXISTS `invitations` (
  `ID` int(11) NOT NULL auto_increment,
  `token` varchar(8) NOT NULL,
  `email` varchar(64) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `token` (`token`,`user_id`),
  KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `keys`;
CREATE TABLE IF NOT EXISTS `keys` (
  `ID` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `token` char(12) NOT NULL,
  `type` enum('activation','email','password','drop') NOT NULL,
  `email` varchar(64) default NULL,
  `timestamp` int(11) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `token` (`token`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `notes`;
CREATE TABLE IF NOT EXISTS `notes` (
  `ID` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `type` enum('public','personal','private') NOT NULL default 'public',
  `to` varchar(16) default NULL,
  `twitter` int(11) NOT NULL,
  `note` text NOT NULL,
  `attached_file` text,
  `from` varchar(10) NOT NULL,
  `replying` int(11) default NULL,
  `timestamp` int(10) NOT NULL,
  `ip` varchar(16) NOT NULL,
  `hash` varchar(128) NOT NULL,
  `serial` text NOT NULL,
  `reply_user` int(255) NOT NULL,
  `read` tinyint(1) default NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `hash` (`hash`),
  KEY `user_id` (`user_id`,`type`,`to`,`twitter`),
  KEY `to` (`to`),
  KEY `type` (`type`),
  KEY `twitter` (`twitter`),
  KEY `from` (`from`),
  KEY `replying` (`replying`),
  KEY `timestamp` (`timestamp`),
  KEY `ID` (`ID`,`type`),
  KEY `type_2` (`type`,`timestamp`),
  KEY `type_3` (`type`,`user_id`),
  KEY `reply_user` (`reply_user`),
  KEY `read` (`read`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `post2id`;
CREATE TABLE IF NOT EXISTS `post2id` (
  `ID` int(11) NOT NULL auto_increment,
  `from` int(11) NOT NULL,
  `to` int(11) default NULL,
  `note_id` int(11) NOT NULL,
  `timestamp` int(255) NOT NULL,
  `reply_user` int(255) NOT NULL,
  `private_profile` int(255) NOT NULL,
  `type` enum('public','personal','twitter','twitter_reply') NOT NULL default 'public',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `from_2` (`from`,`to`,`note_id`),
  KEY `to` (`to`,`note_id`),
  KEY `timestamp` (`timestamp`),
  KEY `note_id` (`note_id`),
  KEY `to_2` (`to`,`timestamp`),
  KEY `reply_user` (`reply_user`),
  KEY `private_profile` (`private_profile`),
  KEY `to_3` (`to`,`timestamp`,`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `relationships`;
CREATE TABLE IF NOT EXISTS `relationships` (
  `ID` int(11) NOT NULL auto_increment,
  `creator` int(11) NOT NULL,
  `who` int(11) NOT NULL,
  `both` int(1) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `creator` (`creator`,`who`),
  KEY `who` (`who`),
  KEY `both` (`both`,`creator`),
  KEY `who_2` (`who`,`both`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `sessions`;
CREATE TABLE IF NOT EXISTS `sessions` (
  `ID` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `time` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `type` varchar(50) NOT NULL default 'normal',
  PRIMARY KEY  (`ID`),
  KEY `user_id` (`user_id`,`time`),
  KEY `hash` (`hash`),
  KEY `time` (`time`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `settings`;
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL auto_increment,
  `category` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

INSERT INTO `settings` VALUES (1, 'db_version', '0');

DROP TABLE IF EXISTS `tags_c`;
CREATE TABLE IF NOT EXISTS `tags_c` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `timestamp` int(11) NOT NULL,
  `founder` varchar(255) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

DROP TABLE IF EXISTS `tags_n`;
CREATE TABLE IF NOT EXISTS `tags_n` (
  `id` int(11) NOT NULL auto_increment,
  `note_id` int(11) NOT NULL,
  `tag` varchar(255) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `poster` int(11) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

DROP TABLE IF EXISTS `twitter`;
CREATE TABLE IF NOT EXISTS `twitter` (
  `ID` int(255) NOT NULL auto_increment,
  `hash` varchar(255) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `note` text NOT NULL,
  `serial` text NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `hash` (`hash`),
  KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `ID` int(11) NOT NULL auto_increment,
  `username` varchar(16) NOT NULL,
  `password` varchar(255) NOT NULL,
  `api` varchar(16) NOT NULL,
  `salt` char(5) NOT NULL,
  `avatar` varchar(32) default NULL,
  `language` varchar(255) default NULL,
  `theme` varchar(255) NOT NULL,
  `background` varchar(4) default NULL,
  `background_style` enum('normal','repeat','centered','fixed') default 'normal',
  `private` int(1) NOT NULL,
  `realname` varchar(64) default NULL,
  `email` varchar(64) NOT NULL,
  `jabber` varchar(64) default NULL,
  `url` varchar(64) default NULL,
  `location` varchar(64) default NULL,
  `bio` varchar(140) default NULL,
  `invitations` int(11) default NULL,
  `twitter_username` varchar(255) default NULL,
  `twitter_password` varchar(255) default NULL,
  `status` enum('ok','nc','banned') NOT NULL default 'ok',
  `since` int(10) NOT NULL,
  `last_seen` int(10) NOT NULL,
  `last_follow` int(10) NOT NULL,
  `ip` varchar(16) NOT NULL,
  `last_twitter_update` int(255) NOT NULL,
  `show_twits` int(1) NOT NULL,
  `show_followers` int(1) NOT NULL default '1',
  `jabber_notifications` int(1) NOT NULL default '1',
  `notification_level` int(1) NOT NULL default '2',
  `ignored` text NOT NULL,
  `twitter_key` varchar(500) NOT NULL,
  `last_note` int(10) NOT NULL,
  `shorter_service` varchar(255) NOT NULL default 'a:3:{s:7:"service";s:7:"default";s:4:"data";s:0:"";s:7:"preview";b:0;}',
  PRIMARY KEY  (`ID`),
  KEY `username` (`username`),
  KEY `password` (`password`),
  KEY `api` (`api`),
  KEY `realname` (`realname`),
  KEY `email` (`email`),
  KEY `url` (`url`),
  KEY `location` (`location`),
  KEY `bio` (`bio`),
  KEY `language` (`language`),
  KEY `theme` (`theme`),
  KEY `twitter_username` (`twitter_username`),
  KEY `twitter_password` (`twitter_password`),
  KEY `since` (`since`),
  KEY `salt` (`salt`),
  KEY `avatar` (`avatar`),
  KEY `background` (`background`),
  KEY `background_style` (`background_style`),
  KEY `jabber` (`jabber`),
  KEY `private` (`private`),
  KEY `last_change` (`last_follow`),
  KEY `last_note` (`last_note`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;