-- # ***** BEGIN LICENSE BLOCK *****
-- #
-- #    This file is a part of CodingTeam. See <http://www.codingteam.net>.
-- #    Copyright (C) 2007-2008 CodingTeam (See AUTHORS and THANKS for details)
-- #
-- #    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/>.
-- #
-- # ***** END LICENSE BLOCK *****
 
--
-- 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;
 
--
-- 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 ;
 
--
-- 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,
  `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',
  `versions` int(11) NOT NULL default '0',
  `popularity` int(11) NOT NULL default '0',
  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,
  UNIQUE KEY `admin` (`projectid`,`userid`,`role`)
) 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,
  `authorid` int(11) NOT NULL,
  `version` 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,
  `authorid` int(11) 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`),
  UNIQUE KEY `file` (`file`),
  UNIQUE KEY `paste` (`paste`)
) 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,
  `authorid` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `version` 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
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
--
-- 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',
  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_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,
  `nbseen` int(11) NOT NULL DEFAULT '0',
  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_todo
--
 
CREATE TABLE IF NOT EXISTS `projects_todo` (
  `id` int(11) NOT NULL auto_increment,
  `projectid` int(11) NOT NULL,
  `version` varchar(15) collate utf8_bin NOT NULL,
  `status` enum('done','todo','working') COLLATE utf8_bin NOT NULL,
  `task` 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`projectid`,`version`,`status`)
) 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(40) 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') collate utf8_bin NOT NULL,
  `keyid` varchar(42) collate utf8_bin NOT NULL,
  `lang` varchar(2) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uninc0` (`nickname`),
  UNIQUE KEY `uninc1` (`keyid`),
  UNIQUE KEY `uninc2` (`email`(255))
) 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_todo`
  ADD CONSTRAINT `constr_projects_projectid6` FOREIGN KEY (projectid) REFERENCES projects(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_jabber`
  ADD CONSTRAINT `constr_projects_projectid9` FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE;