CREATE DATABASE IF NOT EXISTS HRDB CHARACTER SET utf8 COLLATE utf8_bin;

USE HRDB;

DROP TABLE IF EXISTS Users;

DROP TABLE IF EXISTS Groups;

DROP TABLE IF EXISTS Organizations;

CREATE TABLE Users(

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

uid char(32) NOT NULL,

password char(32),

firstname varchar(32) NOT NULL default '',

lastname varchar(32) NOT NULL default '',

fullname varchar(32),

email varchar(32),

organization varchar(32),

timestamp TIMESTAMP(8)

);

CREATE TABLE Groups(

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

gid char(32) NOT NULL,

name varchar(32) NOT NULL default '',

description varchar(32),

timestamp TIMESTAMP(8)

);

CREATE TABLE Organizations(

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name varchar(32) NOT NULL default '',

description varchar(32),

timestamp TIMESTAMP(8)

);

INSERT INTO Users VALUES ("","bob","password","Bob", "Fleming","Bob Fleming","Bob.Fleming@example.com","HR",CURRENT_TIMESTAMP);

INSERT INTO Users VALUES ("","rowley","password","Rowley","Birkin","Rowley Birkin","Rowley.Birkin@example.com","SALES",CURRENT_TIMESTAMP);

INSERT INTO Users VALUES ("","louis","password","Louis", "Balfour","Louis Balfour","Louis.Balfour@example.com","SALES",CURRENT_TIMESTAMP);

INSERT INTO Users VALUES ("","john","password","John", "Smith","John Smith","John.Smith@example.com","SUPPORT",CURRENT_TIMESTAMP);

INSERT INTO Users VALUES ("","jdoe","password","John", "Doe","John Doe","John.Does@example.com","ENG",CURRENT_TIMESTAMP);

INSERT INTO Groups VALUES ("","100","admin","Admin group",CURRENT_TIMESTAMP);

INSERT INTO Groups VALUES ("","101","users","Users group",CURRENT_TIMESTAMP);

INSERT INTO Organizations VALUES ("","HR","HR organization",CURRENT_TIMESTAMP);

INSERT INTO Organizations VALUES ("","SALES","Sales organization",CURRENT_TIMESTAMP);

INSERT INTO Organizations VALUES ("","SUPPORT","Support organization",CURRENT_TIMESTAMP);

INSERT INTO Organizations VALUES ("","ENG","Engineering organization",CURRENT_TIMESTAMP);

grant all on *.* to root@'%' IDENTIFIED BY 'password';