DROP TABLE IF EXISTS user;
CREATE TABLE `anykey_dev`.`user` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(45) NOT NULL,
    password VARCHAR(32) NOT NULL,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    role_id INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY(`id`)
)
ENGINE = InnoDB;

INSERT INTO user (username, password, first_name, last_name, role_id) values ('admin', 'admin', 'Admin', 'Admin', 1);
INSERT INTO user (username, password, first_name, last_name, role_id) values ('tech', 'tech', 'Tech', 'Tech', 2);
INSERT INTO user (username, password, first_name, last_name, role_id) values ('user', 'user', 'User', 'User', 3);


DROP TABLE IF EXISTS role;
CREATE TABLE `anykey_dev`.`role` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO role (role) VALUES ('admin');
INSERT INTO role (role) VALUES ('technician');
INSERT INTO role (role) VALUES ('user');

DROP TABLE IF EXISTS category;
CREATE TABLE `anykey_dev`.`category` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(45) NOT NULL,
  `parent_cat_id` INTEGER UNSIGNED,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO category (category, parent_cat_id) values ('Hardware', NULL);
INSERT INTO category (category, parent_cat_id) values ('Software', NULL);
INSERT INTO category (category, parent_cat_id) values ('Other', NULL);
INSERT INTO category (category, parent_cat_id) values ('Monitor', 1);
INSERT INTO category (category, parent_cat_id) values ('Motherboard', 1);
INSERT INTO category (category, parent_cat_id) values ('Printer', 1);
INSERT INTO category (category, parent_cat_id) values ('Windows XP', 2);
INSERT INTO category (category, parent_cat_id) values ('Linux', 2);
INSERT INTO category (category, parent_cat_id) values ('OSX', 2);
INSERT INTO category (category, parent_cat_id) values ('Phone', 3);
INSERT INTO category (category, parent_cat_id) values ('Network', 3);

DROP TABLE IF EXISTS issue;
                CREATE TABLE `anykey_dev`.`issue` (
                  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
                  `subject` VARCHAR(45) NOT NULL,
                  `description` TEXT NOT NULL,
                  `create_date` DATETIME NOT NULL,
                  `modified_date` DATETIME NOT NULL,
                  `category_id` INTEGER UNSIGNED NOT NULL,
                  `sub_category_id` INTEGER UNSIGNED,
                  `image` VARCHAR(45),
                  `author_id` INTEGER UNSIGNED NOT NULL,
                  `image_two` VARCHAR(45),
                  PRIMARY KEY (`id`)
                )
                ENGINE = InnoDB;


INSERT INTO issue 
	(subject, 
	description, 
	create_date, 
	modified_date, 
	category_id, 
	sub_category_id, 
	image,
	author_id, 
	image_two)
VALUES
	('computer problem',
	'There is a problem with my computer',
	CURRENT_DATE,
	CURRENT_DATE,
	1,
	2,
	NULL,
	1,
	NULL);