
CREATE TABLE IF NOT EXISTS `project_type` (
  `project_type_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR( 255 ) NOT NULL,
  PRIMARY KEY ( `project_type_id` )
)
ENGINE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT IGNORE INTO `project_type` (`project_type_id` ,`name`)
  VALUES (1, 'closed source'), (2, 'open source');


CREATE TABLE IF NOT EXISTS `project_visibility` (
  `project_visibility_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR( 255 ) NOT NULL,
  PRIMARY KEY ( `project_visibility_id` )
)
ENGINE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT IGNORE INTO `project_visibility` (`project_visibility_id`, `name`)
  VALUES (1, 'hidden'), (2, 'visible');



CREATE TABLE IF NOT EXISTS `project` (
  `project_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `project_type` tinyint(3) unsigned NOT NULL,
  `project_visibility` tinyint(3) unsigned NOT NULL,
  `svn_path_doc` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `project_pretty_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `creation_time` int(11) NOT NULL,
  `creator_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`project_id`),
  KEY `name` (`name`),
  KEY `project_type` (`project_type`),
  KEY `project_visibility` (`project_visibility`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `project`
  ADD CONSTRAINT `project_ibfk_2` FOREIGN KEY (`project_visibility`) REFERENCES `project_visibility` (`project_visibility_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `project_ibfk_1` FOREIGN KEY (`project_type`) REFERENCES `project_type` (`project_type_id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE `user` (
  `user_id` bigint(20) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `pass` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `user_key` char(32) NOT NULL,
  `disabled` enum('true','false') NOT NULL default 'false',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user` (`name`),
  KEY `application_key` (`user_key`),
  KEY `email` (`email`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


INSERT INTO `user` (`user_id` ,`name` ,`pass` ,`email` ,`user_key` ,`disabled`)
VALUES ('1' , 'dummy', 'dummy_password', 'dummy@dummy.net', '', 'true');

CREATE TABLE `user_request` (
  `request_id` bigint(20) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `pass` varchar(20) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY  (`request_id`),
  UNIQUE KEY `user` (`name`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `user_information` (
  `user_id` bigint(20) unsigned NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `gender` enum('male','female') NOT NULL default 'male',
  `icon` varchar(255) NOT NULL,
  `languages` varchar(250) NOT NULL,
  `registration_date` int(10) unsigned NOT NULL,
  `last_login` int(10) unsigned NOT NULL DEFAULT '0',
  `disable_date` int(10) unsigned NOT NULL,
  `birthday` date NOT NULL default '1900-01-01',
  `timezone` varchar(10) NOT NULL,
  `signature` text NOT NULL,
  `message` text NOT NULL,
  `homepage` varchar(100) NOT NULL,
  `blog` varchar(100) NOT NULL,
  `ohloh_profile` varchar(100) NOT NULL,
  `icq` varchar(10) NOT NULL,
  `aim` varchar(50) NOT NULL,
  `jabber` varchar(50) NOT NULL,
  `yahoo` varchar(50) NOT NULL,
  `msn` varchar(50) NOT NULL,
  `skype` varchar(50) NOT NULL,
  `sip` varchar(50) NOT NULL,
  `irc` varchar(50) NOT NULL,
  `profile_visible` SMALLINT NOT NULL DEFAULT '0',
  `workitems_per_page` int(10) unsigned NOT NULL DEFAULT '16',
  `issues_per_page` int(10) unsigned NOT NULL DEFAULT '25',
  `feed_show_unread` int(10) unsigned NOT NULL DEFAULT '1',
  UNIQUE KEY `user_information` (`user_id`),
  KEY `user_information_FKIndex1` (`last_login`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `user_information` (`user_id` ,`first_name`, `last_name`)
VALUES ('1' , 'dummy', 'dummy');

CREATE TABLE `user_friendship` (
  `user_id` bigint(20) unsigned NOT NULL,
  `friend_id` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `user_friendship` (`user_id`,`friend_id`),
  KEY `user_friendship_FKIndex1` (`user_id`),
  KEY `user_friendship_FKIndex2` (`friend_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`friend_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_friendship_request` (
  `requester_id` bigint(20) unsigned NOT NULL,
  `requestee_id` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  UNIQUE KEY `user_friendship` (`requester_id`,`requestee_id`),
  KEY `user_friendship_FKIndex1` (`requester_id`),
  KEY `user_friendship_FKIndex2` (`requestee_id`),
  KEY `creation_time_index` (`creation_time`), 
  FOREIGN KEY (`requester_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`requestee_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `project_bookmark` (
  `project_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  KEY `project_bookmark_FKIndex1` (`project_id`),
  KEY `project_bookmark_FKIndex2` (`user_id`),
  FOREIGN KEY (`project_id`)
  	REFERENCES `project` (`project_id`)
  	  ON DELETE CASCADE
  	  ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `application` (
  `application_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `app_key` char(32) NOT NULL,
  `login_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY  (`application_id`),
  UNIQUE KEY `key` (`app_key`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE access_group (
  `group_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `kind` ENUM('global', 'project', 'community') NOT NULL,
  PRIMARY KEY(`group_id`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO access_group (group_id, name, kind) VALUES (1, 'Origo Administrator', 'global');
INSERT INTO access_group (group_id, name, kind) VALUES (2, 'Origo User', 'global');
INSERT INTO access_group (group_id, name, kind) VALUES (3, 'Project Administrator', 'project');
INSERT INTO access_group (group_id, name, kind) VALUES (4, 'Project Developer', 'project');
INSERT INTO access_group (group_id, name, kind) VALUES (5, 'Disabled User', 'global');
INSERT INTO access_group (group_id, name, kind) VALUES (6, 'Community Administrator', 'community');
INSERT INTO access_group (group_id, name, kind) VALUES (7, 'Community Member', 'community');
INSERT INTO access_group (group_id, name, kind) VALUES (8, 'Not Logged In', 'global');

CREATE TABLE session (
  session_id CHAR(32) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  creation_time INTEGER UNSIGNED NOT NULL,
  access_time INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(session_id),
  KEY time(creation_time, access_time),
  KEY origo_session_FKIndex1(user_id),
  KEY access_time(access_time),
  FOREIGN KEY(user_id)
    REFERENCES user(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE policy (
  policy_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  instruction VARCHAR(30) NOT NULL,
  group_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY(policy_id),
  KEY instruction(instruction),
  KEY policy_FKIndex1(group_id),
  FOREIGN KEY(group_id)
    REFERENCES access_group(group_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (1, 'website_edit_owner', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (2, 'website_edit_member', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (3, 'svn_write', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (4, 'svn_write', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (5, 'project_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (6, 'project_members_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (7, 'project_change_group', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (8, 'project_change_group', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (9, 'project_global_admin', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (10, 'is_online', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (11, 'project_list_of_user', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (12, 'user_my_name', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (14, 'user_key', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (15, 'user_key_generate', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (16, 'user_my_password', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (17, 'system_status', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (18, 'system_mail_all', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (19, 'workitem_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (20, 'project_create', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (21, 'release_add', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (22, 'user_change_email', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (23, 'user_change_wi_set', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (24, 'user_list_wi_set', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (25, 'workitem_show', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (26, 'project_show', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (27, 'add_bookmark', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (28, 'remove_bookmark', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (29, 'blog_add', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (30, 'blog_add', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (31, 'wiki_add', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (32, 'wiki_add', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (33, 'list_bookmark', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (34, 'workitem_set_read_status', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (35, 'release_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (36, 'project_change_desc', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (37, 'project_change_logo', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (38, 'release_delete', 3);
-- INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (39, 'issue_list', 3);
-- INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (40, 'issue_list', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (41, 'user_my_email', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (42, 'comment_add', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (43, 'project_request_add', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (44, 'project_request_retrieve', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (45, 'user_retrieve_information', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (46, 'user_set_information', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (47, 'user_list_reported_issues', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (48, 'user_add_friendship', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (49, 'user_list_friends', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (50, 'user_send_message', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (51, 'user_disable_account', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (52, 'issue_add', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (53, 'issue_comment', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (54, 'issue_comment_extended', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (55, 'issue_comment_extended', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (56, 'issue_comment_extended', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (57, 'issue_update', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (58, 'issue_update', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (59, 'issue_update', 4);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (60, 'release_delete', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (61, 'community_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (62, 'community_retrieve', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (63, 'community_create', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (64, 'community_change_group', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (65, 'community_change_group_admin', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (66, 'community_change_group_admin', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (67, 'community_list_members', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (68, 'community_list_projects', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (69, 'community_add_project', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (70, 'community_add_project', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (71, 'community_remove_project', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (72, 'community_remove_project', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (73, 'user_list_communities', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (74, 'project_list_communities', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (75, 'community_delete', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (76, 'community_change_desc', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (77, 'community_change_desc', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (78, 'community_list_wiki', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (79, 'community_retrieve_wiki', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (80, 'community_add_wiki', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (81, 'community_add_wiki', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (82, 'community_add_wiki', 7);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (83, 'community_edit_wiki', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (84, 'community_edit_wiki', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (85, 'community_edit_wiki', 7);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (86, 'issue_list', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (87, 'project_change_logo', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (88, 'project_change_desc', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (89, 'release_add', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (90, 'blog_add', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (91, 'wiki_add', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (92, 'community_delete_wiki', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (93, 'community_delete_wiki', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (94, 'is_community_admin', 6);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (95, 'is_community_member', 7);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (96, 'project_retrieve_statistics', 2);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (97, 'wiki_render_html', 1);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (98, 'wiki_render_html', 3);
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (99, 'wiki_render_html', 4);
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (100,'project_change_settings', '3');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (101,'project_change_settings', '1');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (102 , 'user_change_profile_visible', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (104 , 'user_retrieve_information', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (105 , 'user_list_friends', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (106 , 'project_list_of_user', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (107 , 'list_bookmark', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (108 , 'user_list_communities', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (109 , 'user_list_friends', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (118 , 'project_retrieve_settings', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (119 , 'project_retrieve_information', '8');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (120 , 'project_retrieve_settings', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (121 , 'project_retrieve_information', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (122 , 'project_change_information', '1');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (123 , 'project_change_information', '3');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (124 , 'project_remove', '1');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (125 , 'issue_add_attachment', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (126 , 'issue_retrieve_attachments', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (127 , 'issue_remove_attachment', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (128 , 'issue_add_subscription', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (129 , 'issue_remove_subscription', '2');
INSERT INTO `policy` (`policy_id` ,`instruction` ,`group_id`) VALUES (130 , 'issue_retrieve_planning_data', '2');
-- 2009-11-03: issue.delete API call
INSERT INTO `policy` (`policy_id`, `instruction`, `group_id`) VALUES (NULL , 'issue_delete', '1'), (NULL , 'issue_delete', '3');
  

CREATE TABLE user_association (
  user_id BIGINT UNSIGNED NOT NULL,
  group_id TINYINT UNSIGNED NOT NULL,
  KEY user_association_FKIndex1(user_id),
  KEY user_association_FKIndex2(group_id),
  FOREIGN KEY(user_id)
    REFERENCES user(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(group_id)
    REFERENCES access_group(group_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE user_project_association (
  project_id BIGINT UNSIGNED NOT NULL,
  group_id TINYINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  KEY user_association_FKIndex1(user_id),
  KEY user_association_FKIndex3(group_id),
  KEY user_project_association_FKIndex3(project_id),
  UNIQUE KEY project_user(project_id, user_id),
  FOREIGN KEY(user_id)
    REFERENCES user(user_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(project_id)
    REFERENCES project(project_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(group_id)
    REFERENCES access_group(group_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `release` (
  `release_id` bigint(20) unsigned NOT NULL auto_increment,
  `project_id` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `version` varchar(255) NOT NULL,
  PRIMARY KEY  (`release_id`),
  KEY `release_FKIndex1` (`project_id`),
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `release_file` (
  `file_id` bigint(20) unsigned NOT NULL auto_increment,
  `release_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `platform` varchar(255) NOT NULL,
  PRIMARY KEY  (`file_id`),
  KEY `release_file_FKIndex1` (`release_id`),
  FOREIGN KEY (`release_id`)
    REFERENCES `release` (`release_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;



CREATE TABLE `issue` (
  `issue_id` bigint(20) unsigned NOT NULL auto_increment,
  `project_id` bigint(20) unsigned NOT NULL,
  `project_issue_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `private` tinyint(1) NOT NULL,
  `last_modified` int(10) unsigned NOT NULL DEFAULT '0',
  `deadline` int(10) unsigned NOT NULL DEFAULT '0',
  `work_amount` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY  (`issue_id`),
  UNIQUE KEY `project_id` (`project_id`,`project_issue_id`),
  KEY `issue_FKIndex1` (`project_id`),
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `issue_revision` (
  `issue_revision_id` bigint(20) unsigned NOT NULL auto_increment,
  `issue_id` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  `user_id` bigint(10) unsigned NOT NULL,
  `text` text NOT NULL,
  `text_type` int(10) unsigned NOT NULL DEFAULT '2',
  PRIMARY KEY  (`issue_revision_id`),
  KEY `issue_revision_FKIndex1` (`issue_id`),
  KEY `issue_revision_FKIndex2` (`user_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`issue_id`)
    REFERENCES `issue` (`issue_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `workitem_type` (
  `type_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`type_id`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `workitem_type` (`type_id`, `name`) VALUES 
(1, 'issue'),
(2, 'release'),
(3, 'commit'),
(4, 'wikipage'),
(5, 'blog'),
(6, 'comment');



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

-- New table for comment types
CREATE TABLE IF NOT EXISTS `comment_type` (
  `type_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `type_name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

INSERT INTO `comment_type` (`type_id`, `type_name`) VALUES
  (NULL , 'undefined'),
  (NULL , 'general'),
  (NULL , 'forum');


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

CREATE TABLE IF NOT EXISTS `wi` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type_id` int(10) unsigned NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `ref` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `revision` int(10) unsigned DEFAULT NULL,
  `previous_revision` int(10) unsigned DEFAULT NULL,
  `diff` text COLLATE utf8_unicode_ci,
  `comment_text` text COLLATE utf8_unicode_ci,
  `is_new` tinyint(1) DEFAULT NULL,
  `comment_type` tinyint(3) unsigned DEFAULT NULL,
  `log` text COLLATE utf8_unicode_ci,
  `issue_revision_id` bigint(20) unsigned DEFAULT NULL,
  `release_id` bigint(10) unsigned DEFAULT NULL,
  `is_private` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type_id` (`type_id`),
  KEY `project_id` (`project_id`),
  KEY `user_id` (`user_id`),
  KEY `issue_revision_id` (`issue_revision_id`),
  KEY `release_id` (`release_id`),
  KEY `comment_type` (`comment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Constraints for table `wi`
-- RELATIONS FOR TABLE `wi`:
--   `user_id`
--       `user` -> ` user_id`
--   `type_id`
--       `workitem_type` -> `type_id`
--   `release_id`
--       `release` -> `release_id`
--   `issue_revision_id`
--       `issue_revision` -> `issue_revision_id`
--   `project_id``release`
--       `project` -> `project_id`
--
ALTER TABLE `wi`
  ADD CONSTRAINT `wi_ibfk_7` FOREIGN KEY (`comment_type`) REFERENCES `comment_type` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `wi_ibfk_2` FOREIGN KEY (`release_id`) REFERENCES `release` (`release_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `wi_ibfk_3` FOREIGN KEY (`issue_revision_id`) REFERENCES `issue_revision` (`issue_revision_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `wi_ibfk_4` FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `wi_ibfk_5` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `wi_ibfk_6` FOREIGN KEY (`type_id`) REFERENCES `workitem_type` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

CREATE TABLE `user_workitem_subscription` (
  `user_id` bigint(20) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `subscription_type` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  UNIQUE KEY `user_workitem` (`user_id`,`project_id`,`type_id`,`subscription_type`),
  KEY `user_workitem_subscription_FKIndex1` (`user_id`),
  KEY `user_workitem_subscription_FKIndex2` (`project_id`),
  KEY `user_workitem_subscription_FKIndex3` (`type_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`type_id`)
    REFERENCES `workitem_type` (`type_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_read_workitem` (
  `user_id` bigint(20) unsigned NOT NULL,
  `workitem_id` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`workitem_id`),
  KEY `user_read_workitem_FKIndex1` (`user_id`),
  KEY `user_read_workitem_FKIndex2` (`workitem_id`),
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`workitem_id`)
    REFERENCES `wi` (`id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `tag_text` (
  `tag_id` bigint(20) unsigned NOT NULL auto_increment,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY  (`tag_id`),
  UNIQUE KEY `text` (`text`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `issue_tag_association` (
  `issue_revision_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  KEY `issue_tag_association_FKIndex1` (`issue_revision_id`),
  KEY `issue_tag_association_FKIndex2` (`tag_id`),
  FOREIGN KEY (`issue_revision_id`)
    REFERENCES `issue_revision` (`issue_revision_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`tag_id`)
    REFERENCES `tag_text` (`tag_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `issue_attachment` (
  `issue_attachment_id` bigint(20) unsigned NOT NULL auto_increment,
  `issue_id` bigint(20) unsigned NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`issue_attachment_id`),
  KEY `issue_attachment_FKIndex1` (`issue_id`),
  FOREIGN KEY (`issue_id`)
    REFERENCES `issue` (`issue_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `project_request` (
  `request_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `project_type` tinyint(3) unsigned NOT NULL,
  `project_visibility` tinyint(3) unsigned NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`request_id`),
  KEY `user_id` (`user_id`),
  KEY `project_type` (`project_type`),
  KEY `project_visibility` (`project_visibility`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `project_request`
  ADD CONSTRAINT `project_request_ibfk_3` FOREIGN KEY (`project_visibility`) REFERENCES `project_visibility` (`project_visibility_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `project_request_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `project_request_ibfk_2` FOREIGN KEY (`project_type`) REFERENCES `project_type` (`project_type_id`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `project_request` (`request_id` ,`user_id` ,`name` ,`description` , `project_type`, `project_visibility`, `message`)
VALUES
('1' , '1', 'api', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('2' , '1', 'dev', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('3' , '1', 'download', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('4' , '1', 'login', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('5' , '1', 'pictures', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('6' , '1', 'static', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('7' , '1', 'svn', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('8' , '1', 'upload', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('9' , '1', 'www', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('10' , '1', 'all', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('11' , '1', 'default', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.'),
('12' , '1', 'template', 'Placeholder to prevent registration of that project name.', 1, 1, 'Placeholder to prevent registration of that project name.');

CREATE TABLE `community` (
  `community_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT NOT NULL,
  `logo` VARCHAR(50) NOT NULL,
  KEY `name` (`name`),
  PRIMARY KEY(`community_id`)
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `user_community_association` (
  `community_id` BIGINT UNSIGNED NOT NULL,
  `group_id` TINYINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  KEY user_community_association_FKIndex1(`user_id`),
  KEY user_community_association_FKIndex2(`group_id`),
  KEY user_community_association_FKIndex3(`community_id`),
  UNIQUE KEY community_user(`community_id`, `user_id`),
  FOREIGN KEY(`user_id`)
    REFERENCES user(`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(`community_id`)
    REFERENCES community(`community_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(`group_id`)
    REFERENCES access_group(`group_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `project_community_association` (
  `community_id` BIGINT UNSIGNED NOT NULL,
  `project_id` BIGINT UNSIGNED NOT NULL,
  KEY user_community_association_FKIndex1(`project_id`),
  KEY user_community_association_FKIndex2(`community_id`),
  UNIQUE KEY community_project(`community_id`, `project_id`),
  FOREIGN KEY(`project_id`)
    REFERENCES project(`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(`community_id`)
    REFERENCES community(`community_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;


CREATE TABLE `community_wiki` (
  `wiki_id` bigint(20) unsigned NOT NULL auto_increment,
  `community_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `private` enum('true','false') NOT NULL default 'false',
  PRIMARY KEY (`wiki_id`),
  UNIQUE KEY `community_title` (`community_id`,`title`),
  KEY `wiki_id_index` (`wiki_id`),
  KEY `community_id_index` (`community_id`),
  KEY `title_index` (`title`),
  FOREIGN KEY (`community_id`)
    REFERENCES `community` (`community_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `community_wiki_revision` (
  `wiki_revision_id` bigint(20) unsigned NOT NULL auto_increment,
  `wiki_id` bigint(20) unsigned NOT NULL,
  `community_wiki_revision` bigint(20) unsigned NOT NULL,
  `creation_time` int(10) unsigned NOT NULL,
  `user_id` bigint(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`wiki_revision_id`),
  KEY `wiki_id_index` (`wiki_id`),
  KEY `wiki_rev_index` (`community_wiki_revision`),
  FOREIGN KEY (`wiki_id`)
    REFERENCES `community_wiki` (`wiki_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `project_information` (
  `project_id` bigint(20) unsigned NOT NULL,
  `short_description` varchar(255) NOT NULL default '',
  `description` MEDIUMTEXT NOT NULL ,
  `programming_language` varchar(255) NOT NULL default '',
  `operating_system` varchar(255) NOT NULL default '',
  `license` varchar(255) NOT NULL default '',
  `category` varchar(255) NOT NULL default '',
  UNIQUE KEY `project_information_unique` (`project_id`),
  FOREIGN KEY (`project_id`)
    REFERENCES `project` (`project_id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
)
TYPE=InnoDB
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
