create database utilities;
use utilities;

CREATE USER 'employee'@'localhost' IDENTIFIED BY 'svte7!vw'; 
GRANT ALL PRIVILEGES ON utilities.* TO 'employee'@'localhost'; 
ALTER  USER 'employee'@'localhost' IDENTIFIED WITH mysql_native_password BY 'svte7!vw';

CREATE USER 'employee'@'192.168.200.65' IDENTIFIED BY 'svte7!vw'; GRANT ALL PRIVILEGES ON utilities.* TO 'employee'@'192.168.200.65'; 
FLUSH PRIVILEGES;

--UPDATE mysql.user SET Password=PASSWORD('svte7!vw') WHERE User='employee' AND Host='localhost';


CREATE TABLE users (
	userid          int(11)     NOT NULL AUTO_INCREMENT,
	username        varchar(50) NOT NULL,
	hashed_password varchar(60) NOT NULL,
	fname           varchar(20),
	lname           varchar(20),
	PRIMARY KEY (userid)
)	ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--admin tc#egp2017!

INSERT INTO users ( username, hashed_password, fname, lname ) VALUES                                                         
('admin',  '$2y$10$YWIyMDc3YWJkNzg0OGJiO.XajNA7hN8/YuihmMh19vDHTmBGR3qH.', '',       'admin'), 
('edpol',  '$2y$10$OWRlOTg5YTQ1ZmVlYjcwNOYsomm9WySPmuxOMBMj.PxTZXw812zXm', 'Edward' ,'Pol'     ),
('selma',  '$2y$10$ODQ5NzI0NGQyMjI3MzI2NePMkUNmYIKL0CIPxZ5tfkk9IAul3svgq', 'Selma'  ,'Joyce'   ),
('cecelia','$2y$10$ZmU4ZGFhYjJhNzBiZWFmMOfGgUin5eXTzmFD7lHXJ3TmAmmTO2QRG', 'Cecelia','Mitchell'),
('denise', '$2y$10$N2Q1Y2U0M2U2ZmIyNzExM.8Q8QZV4H0Lo8dzOVoG1/sjxXhkaGokm', 'Denise' ,'Elliott' ),
('janel',  '$2y$10$ZWIyZDUzZjQyMDBlODRhZOJyNVGfvflyEawT8t33zTTuhwE3Epawq' ,'Janel',  'Johnson' );


CREATE TABLE util (
    utilid  int(11)      not null   auto_increment,
    name    varchar(50)  not null,
    url     varchar(50)  not null,
    ip      varchar(15)  not null,
    notes    longtext,
primary key (utilid),
key(name)
);

insert into util values
(1, 'admin',      'utilities.loc',  '192.168.200.?',  'Create/Delete/Modify Accounts'),
(2, 'timeclock',  'timeclock.loc',  '192.168.200.?',  'Employees timecard.  Access to accounting for reports'),
(3, 'commission', 'commission.loc', '192.168.200.65', 'Commission reports for Customer service'),
(4, 'mom_orders', 'mom.loc',        '192.168.200.18', 'Generate order file to load in MOM');

CREATE TABLE acl (
	aclid   int(11)      NOT NULL AUTO_INCREMENT,
	userid  int(11)      NOT NULL,
    utilid  int(11)      not null,
primary key (aclid),
key(userid)
);

insert into acl (userid, utilid) values (1,1), (1,2), (1,3), (1,4);	-- Ed
insert into acl (userid, utilid) values               (2,3)       ;	-- Selma
insert into acl (userid, utilid) values        (3,2), (3,3)       ;	-- Cecelia
insert into acl (userid, utilid) values (4,1),        (4,3), (4,4);	-- Denise
insert into acl (userid, utilid) values        (5,2)              ; -- Janel
