--  This file is a part of CodingTeam. Take a look at <http://codingteam.org>.
--  Copyright © 2007-2010 Erwan Briand <erwan@codingteam.net>
-- 
--  This program is free software: you can redistribute it and/or modify it
--  under the terms of the GNU Affero General Public License as published by
--  the Free Software Foundation, version 3 only.
-- 
--  This program is distributed in the hope that it will be useful, but
--  WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
--  or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public
--  License for more details.
-- 
--  You should have received a copy of the GNU Affero General Public License
--  along with this program. If not, see <http://www.gnu.org/licenses/>.

--
-- config
--

CREATE TABLE IF NOT EXISTS `config` (
  `group` varchar(42) collate utf8_bin NOT NULL,
  `field` varchar(42) collate utf8_bin NOT NULL,
  `value` tinytext collate utf8_bin NOT NULL,
  `text` tinytext collate utf8_bin NOT NULL,
  UNIQUE KEY `key` (`group`,`field`,`value`(255),`text`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- jabber_fortunes
--

CREATE TABLE IF NOT EXISTS `jabber_fortunes` (
  `id` int(11) NOT NULL auto_increment,
  `vote` int(11) NOT NULL default '0',
  `text` text collate utf8_bin NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- messages
--

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL auto_increment,
  `sender_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `subject` tinytext NOT NULL,
  `message` text NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `read_sender` tinyint(1) NOT NULL,
  `read_recipient` tinyint(1) NOT NULL,
  PRIMARY KEY ( `id` )
) ENGINE = InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- messages_answers
--

CREATE TABLE IF NOT EXISTS `messages_answers` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `message` text NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `read_sender` tinyint(1) NOT NULL,
  `read_recipient` tinyint(1) NOT NULL,
  PRIMARY KEY ( `id` )
) ENGINE = InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- notepad
--

CREATE TABLE IF NOT EXISTS `notepad` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(11) NOT NULL,
  `title` tinytext collate utf8_bin NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `language` tinytext collate utf8_bin NOT NULL,
  `source` text collate utf8_bin NOT NULL,
  `time` enum('d','m','f') collate utf8_bin NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- notifications
--

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL auto_increment,
  `jabberid` tinytext collate utf8_bin NOT NULL,
  `subject` tinytext collate utf8_bin NOT NULL,
  `notification` text collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects
--

CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL auto_increment,
  `name` tinytext collate utf8_bin NOT NULL,
  `dbname` varchar(30) collate utf8_bin NOT NULL,
  `website` tinytext collate utf8_bin NOT NULL,
  `logo` tinytext collate utf8_bin NOT NULL,
  `tags` tinytext collate utf8_bin NOT NULL,
  `platforms` tinytext collate utf8_bin NOT NULL,
  `languages` tinytext collate utf8_bin NOT NULL,
  `translations` tinytext collate utf8_bin NOT NULL,
  `category` tinytext collate utf8_bin NOT NULL,
  `license` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `oneline` tinytext collate utf8_bin NOT NULL,
  `date` datetime NOT NULL,
  `userid` int(11) NOT NULL,
  `is_valid` tinyint(1) NOT NULL,
  `is_private` tinyint(1) NOT NULL,
  `mail_paypal` tinytext collate utf8_bin NOT NULL,
  `cur_paypal` enum('USD','EUR') collate utf8_bin NOT NULL,
  `downloads` int(11) NOT NULL default '0',
  `popularity` int(11) NOT NULL default '0',
  `scm` enum('mercurial','subversion') collate utf8_bin NOT NULL,
  `features` tinytext NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `dbname` (`dbname`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_admins
--

CREATE TABLE IF NOT EXISTS `projects_admins` (
  `projectid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `role` enum('projectleader','graphicdesigner','developer','translater','packager','contributer') collate utf8_bin NOT NULL,
  `powers` tinytext collate utf8_bin NOT NULL,
  UNIQUE KEY `admin` (`projectid`,`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- projects_bugs
--

CREATE TABLE IF NOT EXISTS `projects_bugs` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `name` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `version` varchar(15) collate utf8_bin NOT NULL,
  `milestone` varchar(15) collate utf8_bin NOT NULL,
  `priority` enum('blocker','critical','major','normal','minor','trivial') collate utf8_bin NOT NULL,
  `type` enum('bugreport','featurerequest') collate utf8_bin NOT NULL,
  `status` enum('unconfirmed','confirmed','working','needinformations','needcontributors','needtests','resolved','rejected') collate utf8_bin NOT NULL,
  `assignedid` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_bugs_answers
--

CREATE TABLE IF NOT EXISTS `projects_bugs_answers` (
  `id` int(11) NOT NULL auto_increment,
  `bugid` int(11) NOT NULL,
  `name` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_bugs_attachements
--

 CREATE TABLE IF NOT EXISTS `projects_bugs_attachements` (
  `id` int(11) NOT NULL auto_increment,
  `bugid` int(11) NOT NULL,
  `type` enum('file','paste') collate utf8_bin NOT NULL,
  `file` varchar(255) collate utf8_bin NOT NULL,
  `paste` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `authorid` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_bugs_log
--

CREATE TABLE IF NOT EXISTS `projects_bugs_log` (
  `bugid` int(11) NOT NULL,
  `projectid` int(11) NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `datetime` datetime NOT NULL,
  `version` varchar(15) collate utf8_bin NOT NULL,
  `milestone` varchar(15) collate utf8_bin NOT NULL,
  `priority` enum('!nochange!','blocker','critical','major','normal','minor','trivial') collate utf8_bin NOT NULL,
  `type` enum('!nochange!','bugreport','featurerequest') collate utf8_bin NOT NULL,
  `status` enum('!nochange!','unconfirmed','confirmed','working','needinformations','needcontributors','needtests','resolved','rejected') collate utf8_bin NOT NULL,
  `assignedid` int(11) NOT NULL default '0',
  `attachement` int(11) NOT NULL default '0',
  `answer` int(11) NOT NULL default '0'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- projects_commits
--

CREATE TABLE IF NOT EXISTS `projects_commits` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `prev_rev` int(11) NOT NULL,
  `new_rev` int(11) NOT NULL,
  `log` text collate utf8_bin NOT NULL,
  `author` tinytext collate utf8_bin NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_doc
--

CREATE TABLE IF NOT EXISTS `projects_doc` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `link` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `log` tinytext collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `is_public` tinyint(1) NOT NULL,
  `datetime` datetime NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_downloads
--

CREATE TABLE IF NOT EXISTS `projects_downloads` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `name` tinytext collate utf8_bin NOT NULL,
  `text` tinytext collate utf8_bin NOT NULL,
  `version` varchar(15) collate utf8_bin NOT NULL,
  `file` tinytext collate utf8_bin NOT NULL,
  `downloads` int(11) NOT NULL default '0',
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_forum
--

CREATE TABLE IF NOT EXISTS `projects_forum` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `category` enum('help','developement','userscomments','global') collate utf8_bin NOT NULL,
  `title` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `answers` int(11) NOT NULL,
  `last_answer_id` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `is_open` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_forum_answers
--

CREATE TABLE IF NOT EXISTS `projects_forum_answers` (
  `id` int(11) NOT NULL auto_increment,
  `postid` int(11) NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `unregistered_nick` tinytext collate utf8_bin NOT NULL,
  `registered_id` int(11) NOT NULL,
  `author_ip` varchar(15) collate utf8_bin NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_i18n
--

CREATE TABLE IF NOT EXISTS `projects_i18n` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `baselang` varchar(5) collate utf8_bin NOT NULL,
  `references` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `is_translated` tinytext collate utf8_bin NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_i18n_proposals
--

CREATE TABLE IF NOT EXISTS `projects_i18n_proposals` (
  `id` int(11) NOT NULL auto_increment,
  `text_id` int(11) NOT NULL,
  `authorid` int(11) NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `type` enum('translation','reformulation','correction') collate utf8_bin NOT NULL,
  `lang` varchar(5) collate utf8_bin NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_jabber
--

CREATE TABLE IF NOT EXISTS `projects_jabber` (
  `projectid` int(11) NOT NULL,
  `jid` varchar(255) collate utf8_bin NOT NULL,
  `desc` tinytext collate utf8_bin NOT NULL,
  `nbuser` int(11) NOT NULL,
  UNIQUE KEY `projectid` (`projectid`,`jid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- projects_news
--

CREATE TABLE IF NOT EXISTS `projects_news` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `link` tinytext collate utf8_bin NOT NULL,
  `title` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `authorid` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_screenshots
--

CREATE TABLE IF NOT EXISTS `projects_screenshots` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `name` tinytext collate utf8_bin NOT NULL,
  `version` varchar(15) collate utf8_bin NOT NULL,
  `image` tinytext collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- projects_versions
--

CREATE TABLE IF NOT EXISTS `projects_versions` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `version` varchar(15) COLLATE utf8_bin NOT NULL,
  `status` enum('planned','development','testing','alpha','beta','stable','abandonned') COLLATE utf8_bin NOT NULL,
  `posid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`projectid`,`version`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

--
-- texts
--

CREATE TABLE IF NOT EXISTS `texts` (
  `page` varchar(42) collate utf8_bin NOT NULL,
  `title` tinytext collate utf8_bin NOT NULL,
  `text` text collate utf8_bin NOT NULL,
  `allow_nl2br` tinyint(1) NOT NULL,
  `date` datetime NOT NULL,
  `author` tinytext collate utf8_bin NOT NULL,
  UNIQUE KEY `page` (`page`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 
-- users
-- 

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `nickname` varchar(30) collate utf8_bin NOT NULL,
  `password` varchar(32) collate utf8_bin NOT NULL,
  `apache_password` varchar(37) collate utf8_bin NOT NULL,
  `surname` tinytext collate utf8_bin NOT NULL,
  `name` tinytext collate utf8_bin NOT NULL,
  `date` datetime NOT NULL,
  `email` varchar(255) collate utf8_bin NOT NULL,
  `jid` tinytext collate utf8_bin NOT NULL,
  `url` tinytext collate utf8_bin NOT NULL,
  `avatar` tinytext collate utf8_bin NOT NULL,
  `level` enum('administrator','member','banned','deleted') collate utf8_bin NOT NULL,
  `keyid` varchar(42) collate utf8_bin NOT NULL,
  `lang` varchar(2) collate utf8_bin NOT NULL,
  `unread_messages` int(11) NOT NULL,
  `notifs` enum('mail','xmpp','no') collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uninc0` (`nickname`),
  UNIQUE KEY `uninc1` (`keyid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- -----------------------------------------------
--

ALTER TABLE `projects_admins`
  ADD CONSTRAINT `constr_projects_projectid` FOREIGN KEY(projectid) REFERENCES projects(id) ON DELETE CASCADE,
  ADD CONSTRAINT `constr_users_userid` FOREIGN KEY(userid) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE `projects_versions`
  ADD CONSTRAINT `constr_projects_projectid1` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_downloads`
  ADD CONSTRAINT `constr_projects_projectid2` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_screenshots`
  ADD CONSTRAINT `constr_projects_projectid3` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_news`
  ADD CONSTRAINT `constr_projects_projectid4` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_bugs`
  ADD CONSTRAINT `constr_projects_projectid5` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_bugs_log`
  ADD CONSTRAINT `constr_projects_bugs_bugid` FOREIGN KEY (bugid) REFERENCES projects_bugs(id) ON DELETE CASCADE;

ALTER TABLE `projects_bugs_answers`
  ADD CONSTRAINT `constr_projects_bugs_bugid1` FOREIGN KEY (bugid) REFERENCES projects_bugs(id) ON DELETE CASCADE;

ALTER TABLE `projects_bugs_attachements`
  ADD CONSTRAINT `constr_projects_bugs_bugid2` FOREIGN KEY (bugid) REFERENCES projects_bugs(id) ON DELETE CASCADE;

ALTER TABLE `projects_doc`
  ADD CONSTRAINT `constr_projects_projectid7` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_forum`
  ADD CONSTRAINT `constr_projects_projectid8` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_forum_answers`
  ADD CONSTRAINT `constr_projects_forum_postid` FOREIGN KEY (postid) REFERENCES projects_forum(id) ON DELETE CASCADE;

ALTER TABLE `projects_i18n`
  ADD CONSTRAINT `constr_projects_projectid11` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_i18n_proposals`
  ADD CONSTRAINT `constr_projects_i18n_textid` FOREIGN KEY (text_id) REFERENCES projects_i18n(id) ON DELETE CASCADE;

ALTER TABLE `projects_jabber`
  ADD CONSTRAINT `constr_projects_projectid9` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE `projects_commits`
  ADD CONSTRAINT `constr_projects_projectid10` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;