# h-admin, a web software to build a community of people that want to share their hardware information.
# Copyright (C) 2010  Antonio Gallo (h-admin-copyright.txt)
#
# This file is part of h-admin
#
# h-admin 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-admin 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-admin.  If not, see <http://www.gnu.org/licenses/>.

create table adminusers (
   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,
   unique(username),
   index(username, password)
)engine=innodb;

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

create table adminusers_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 admingroups (id_group),
	foreign key user_fky(id_user) references adminusers (id_user),
	unique (id_group,id_user)
)engine=innodb;


insert into adminusers (username,password) values ('admin',sha1('admin'));


CREATE TABLE adminsessions (
   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 accesses (
	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
);