create database joblogs;
use joblogs;
create table job (
	jobid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	started BIGINT UNSIGNED NOT NULL,
	problem_started BIGINT UNSIGNED,
	caller CHAR(32) BINARY NOT NULL,
	dept CHAR(32) BINARY,
	priority SMALLINT UNSIGNED,
	username CHAR(32) BINARY NOT NULL,
	allocated_to CHAR(32) BINARY NOT NULL,
	PRIMARY KEY (jobid),
	UNIQUE jobid (jobid),
	INDEX (allocated_to)
);
create table event (
	eventid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	jobid BIGINT UNSIGNED NOT NULL,
	date BIGINT UNSIGNED NOT NULL,
	username CHAR(32) BINARY NOT NULL,
	type SMALLINT,
	status SMALLINT,
	data MEDIUMTEXT,
	PRIMARY KEY (eventid),
	UNIQUE eventid (eventid)
);
create table dept (
	dept CHAR(32) BINARY NOT NULL,
	dept_name VARCHAR(250),
	hod VARCHAR(250),
	phone CHAR(32) BINARY,
	PRIMARY KEY (dept),
	UNIQUE dept (dept)
);
create table user (
	username CHAR(32) BINARY NOT NULL,
	sectoken CHAR(32) BINARY NOT NULL,
	initials CHAR(4) BINARY,
	full_name VARCHAR(250) BINARY,
	level INT NOT NULL,
	PRIMARY KEY (username),
	UNIQUE username (username),
	INDEX (initials)
);
create table knowledge (
	jobid BIGINT UNSIGNED NOT NULL,
	name MEDIUMTEXT,
	description MEDIUMTEXT,
	data LONGBLOB
);
create table atomic (
	username CHAR(32) BINARY NOT NULL,
	time BIGINT UNSIGNED NOT NULL
);

use mysql;
INSERT INTO user VALUES(
	'localhost',
	'your_user',
	Password('your_password'),
	'Y','Y','Y','Y','Y','Y','Y',
	'Y','Y','Y','Y','Y','Y','Y'
);

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON joblogs.job TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.event TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.dept TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.user TO your_user@localhost IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON joblogs.knowledge TO csd@localhost IDENTIFIED BY 'your_password';

FLUSH PRIVILEGES;

use joblogs;
INSERT INTO user (username,sectoken,initials,full_name,level) VALUES ('admin_user','admin_password','AD','Admin User',255);
