# h-source, a web software to build a community of people that want to share their hardware information.
# Copyright (C) 2010  Antonio Gallo (h-source-copyright.txt)
# 
# This file is part of h-source
#
# h-source is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
# 
# h-source 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 General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with h-source.  If not, see <http://www.gnu.org/licenses/>.

create table regusers (
	id_user INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(80) binary NOT NULL,
	password CHAR(40) binary NOT NULL,
	last_failure INT UNSIGNED NOT NULL,
	has_confirmed INT UNSIGNED NOT NULL,
	e_mail VARCHAR(60) binary NOT NULL,
	confirmation_token CHAR(32) NOT NULL,
	creation_date timestamp default CURRENT_TIMESTAMP,
	creation_time INT UNSIGNED NOT NULL,
	temp_field CHAR(32) NOT NULL,
	deleted CHAR(4) NOT NULL default 'no',
	blocked CHAR(4) NOT NULL default 'no',
	forgot_token CHAR(32) NOT NULL,
	forgot_time INT UNSIGNED NOT NULL,  
	unique(username),
	index(username, password)
)engine=innodb;

create table reggroups (
	id_group INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30) binary NOT NULL,
	unique(name)
)engine=innodb;

create table regusers_groups (
	id_user int(11) UNSIGNED not null,
	id_group int(11) UNSIGNED not null,
	index group_indx(id_group),
	index user_indx(id_user),
	foreign key group_fky(id_group) references reggroups (id_group),
	foreign key user_fky(id_user) references regusers (id_user),
	unique (id_group,id_user)
)engine=innodb;

CREATE TABLE regsessions (
   uid CHAR(32) NOT NULL,
   token CHAR(32) NOT NULL,
   id_user INT UNSIGNED NOT NULL,
   creation_date INT UNSIGNED NOT NULL,
   user_agent CHAR(32) NOT NULL,
   INDEX(uid)
)engine=innodb;


create table regaccesses (
	id int(12) not null auto_increment primary key,
	ip char(20) not null,data char(10) not null,
	ora char(8) not null,
	username varchar(30) not null
);

create table hardware (
	id_hard INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	type varchar(50) not null,
	subtype char(15) not null,
	kernel varchar(50) not null,
	vendor varchar(50) not null,
	model varchar(100) not null,
	created_by int not null,
	updated_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	update_date datetime not null,
	compatibility char(15) not null,
	description text CHARACTER SET utf8 not null,
	distribution varchar(300) CHARACTER SET utf8 not null,
	video_card_type varchar(100) CHARACTER SET utf8 not null,
	video_card_works varchar(30) CHARACTER SET utf8 not null,
	wifi_type varchar(100) CHARACTER SET utf8 not null,
	wifi_works varchar(30) CHARACTER SET utf8 not null,
	comm_year char(15) not null,
	ask_for_del char(4) not null default 'no',
	deleted char(4) not null default 'no',
	approved char(4) not null default 'yes',
	cleared char(3) not null default 'no',
	pci_id char(10) not null,
	driver varchar(50) not null,
	interface char(15) not null default 'not-specified',
	bios char(15) not null default 'not-specified',
	webcam_type varchar(100) CHARACTER SET utf8 not null,
	webcam_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	sound_card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	bluetooth_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	fingerprint_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	ethernet-card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	sd_card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	it_tracks_users varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	can_free_systems_be_installed varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	architecture char(15) not null default 'not-specified',
	other_names text CHARACTER SET utf8 not null
)engine=innodb;

create table revisions (
	id_rev INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	type varchar(50) not null,
	subtype char(15) not null,
	kernel varchar(50) not null,
	vendor varchar(50) not null,
	model varchar(100) not null,
	created_by int not null,
	updated_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	update_date datetime not null,
	compatibility char(15) not null,
	description text CHARACTER SET utf8 not null,
	distribution varchar(300) CHARACTER SET utf8 not null,
	video_card_type varchar(100) CHARACTER SET utf8 not null,
	video_card_works varchar(30) CHARACTER SET utf8 not null,
	wifi_type varchar(100) CHARACTER SET utf8 not null,
	wifi_works varchar(30) CHARACTER SET utf8 not null,
	comm_year char(15) not null,
	pci_id char(10) not null,
	driver varchar(50) not null,
	interface char(15) not null default 'not-specified',
	bios char(15) not null default 'not-specified',
	webcam_type varchar(100) CHARACTER SET utf8 not null,
	webcam_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	sound_card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	bluetooth_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	fingerprint_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	ethernet-card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	sd_card_works varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	it_tracks_users varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	architecture char(15) not null default 'not-specified',
	other_names text CHARACTER SET utf8 not null,
	approved char(4) not null default 'yes',
	cleared char(3) not null default 'no',
	can_free_systems_be_installed varchar(30) CHARACTER SET utf8 not null default 'not-specified',
	id_hard INT UNSIGNED NOT NULL
)engine=innodb;

create table hardware_users (
	id_user int(11) UNSIGNED not null,
	id_hard int(11) UNSIGNED not null,
	index hard_indx(id_hard),
	index user_indx(id_user),
	foreign key hard_fky(id_hard) references hardware (id_hard),
	foreign key user_fky(id_user) references regusers (id_user),
	unique (id_user,id_hard)
)engine=innodb;

create table talk (
	id_talk INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	title varchar(100) CHARACTER SET utf8 not null,
	message text CHARACTER SET utf8 not null,   
	id_hard INT UNSIGNED NOT NULL,
	deleted char(4) not null default 'no'
)engine=innodb;

create table profile (
	id_prof INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	update_date datetime not null,
	real_name varchar(100)  CHARACTER SET utf8 not null,
	where_you_are varchar(100) CHARACTER SET utf8 not null,
	birth_date varchar(100)  CHARACTER SET utf8 not null,
	fav_distro varchar(100)  CHARACTER SET utf8 not null,
	projects  text CHARACTER SET utf8 not null ,
	publish_mail char(4) not null default 'no',
	description text CHARACTER SET utf8 not null,
	website varchar(100)  CHARACTER SET utf8 not null,
	send_notification char(4) not null default 'yes'
)engine=innodb;

create table issues (
	id_issue INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	update_date datetime not null,
	topic char(35) CHARACTER SET utf8 not null,
	title varchar(100) CHARACTER SET utf8 not null,
	message text CHARACTER SET utf8 not null,
	priority char(15) CHARACTER SET utf8 not null,
	status char(15) CHARACTER SET utf8 not null,
	notice text CHARACTER SET utf8 not null,
	deleted char(4) not null default 'no'
)engine=innodb;

create table deletion (
	id_del INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	object char(15) not null,
	message text CHARACTER SET utf8 not null,
	id_hard INT UNSIGNED NOT NULL,
	id_duplicate INT UNSIGNED NOT NULL
)engine=innodb;

create table params (
	id_par INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	updating char(4) not null default 'no'
)engine=innodb;

insert into params (updating) values ('no');

create table news (
	id_news INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	creation_date timestamp default CURRENT_TIMESTAMP,
	title varchar(150) not null,
	message text CHARACTER SET utf8 not null
)engine=innodb;


create table boxes (
	id_box int not null auto_increment primary key,
	creation_date timestamp default CURRENT_TIMESTAMP,
	title varchar(150) not null,
	message text CHARACTER SET utf8 not null
);

create table messages (
	id_mes int not null auto_increment primary key,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	message text CHARACTER SET utf8 not null,
	id_issue INT UNSIGNED NOT NULL,
	deleted char(4) not null default 'no',
	has_read char(4) not null default 'no'
);

create table history (
	id_history int not null auto_increment primary key,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	type char(10) not null,
	action char(15) not null,
	id INT UNSIGNED NOT NULL,
	message text CHARACTER SET utf8 not null,
	gr char(15) not null
);

insert into reggroups (name) values ('moderator');


insert into reggroups (name) values ('admin');

create table wiki (
	id_wiki INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	update_date datetime not null,
	title varchar(200) CHARACTER SET utf8 not null,
	title_clean varchar(200) CHARACTER SET utf8 not null,
	page text CHARACTER SET utf8 not null,
	deleted char(3) not null default 'no',
	blocked CHAR(3) NOT NULL default 'no',
	is_main char(3) not null default 'no'
)engine=innodb;

create table wiki_revisions (
	id_rev INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	update_date datetime not null,
	title varchar(200) CHARACTER SET utf8 not null,
	title_clean varchar(200) CHARACTER SET utf8 not null,
	page text CHARACTER SET utf8 not null,
	id_wiki INT UNSIGNED NOT NULL
)engine=innodb;

create table wiki_users (
	id_user int(11) UNSIGNED not null,
	id_wiki int(11) UNSIGNED not null,
	index wiki_indx(id_wiki),
	index user_indx(id_user),
	foreign key wiki_fky(id_wiki) references wiki (id_wiki),
	foreign key user_fky(id_user) references regusers (id_user),
	unique (id_user,id_wiki)
)engine=innodb;

create table wiki_talk (
	id_talk INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	created_by int not null,
	creation_date timestamp default CURRENT_TIMESTAMP,
	title varchar(100) CHARACTER SET utf8 not null,
	message text CHARACTER SET utf8 not null,
	id_wiki INT UNSIGNED NOT NULL,
	deleted char(4) not null default 'no'
)engine=innodb;

insert into wiki (title,title_clean,is_main) values ('Main Page','Main-Page','yes');
update wiki set update_date=creation_date where title='Main Page';