-- DDL & Inserts for Worker and Project Example chapter 5

ALTER TABLE IF EXISTS `Worker` DROP FOREIGN KEY IF EXISTS `Worker_deptName_fk`;
ALTER TABLE IF EXISTS `Dept` DROP FOREIGN KEY IF EXISTS `Dept_mgrId_fk`;
ALTER TABLE IF EXISTS `Project` DROP FOREIGN KEY IF EXISTS `Project_projMgrId_fk`;
ALTER TABLE IF EXISTS `Assign` DROP FOREIGN KEY IF EXISTS `Assign_projNo_fk`; 
ALTER TABLE IF EXISTS `Assign` DROP FOREIGN KEY IF EXISTS `Assign_empId_fk`;

DROP VIEW IF EXISTS MYPROJECT;
DROP TABLE IF EXISTS Dept;
DROP TABLE IF EXISTS Worker;
DROP TABLE IF EXISTS Project;
DROP TABLE IF EXISTS Assign;
DROP TRIGGER IF EXISTS `InsertProjLog`;

CREATE TABLE Worker (
empId INT NOT NULL AUTO_INCREMENT,
lastName VARCHAR(20) NOT NULL,
firstName VARCHAR(15) NOT NULL, 
deptName VARCHAR(15), 
birthDate DATE, 
hireDate DATE, 
salary DECIMAL(8,2),
PRIMARY KEY (empId)
);

CREATE TABLE Dept(
deptName VARCHAR(15),
mgrId INT(6),
CONSTRAINT Dept_deptName_pk PRIMARY KEY (deptName),
CONSTRAINT Dept_mgrId_fk FOREIGN KEY (mgrId) REFERENCES Worker(empId) ON DELETE SET NULL
);

ALTER TABLE Worker ADD CONSTRAINT Worker_deptName_fk FOREIGN KEY (deptName) REFERENCES Dept(deptName) ON DELETE SET NULL;

CREATE TABLE Project (
projNo INT NOT NULL AUTO_INCREMENT, 
projName VARCHAR(20), 
projMgrId INT(6),
budget DECIMAL (8,2), 
startDate DATE, 
expectedDurationWeeks INT(4),
CONSTRAINT Project_projNo_pk PRIMARY KEY (projNo),
CONSTRAINT Project_projMgrId_fk FOREIGN KEY (projMgrId) REFERENCES Worker(empId) ON DELETE SET NULL
);

	
CREATE TABLE Assign (
projNo INT(6), 
empId INT(6), 
hoursAssigned INT(3), 
rating INT(1),
CONSTRAINT Assign_projNo_empId_pk PRIMARY KEY (projNo, empId),
CONSTRAINT Assign_projNo_fk FOREIGN KEY (projNo) REFERENCES Project(projNo) ON DELETE CASCADE,
CONSTRAINT Assign_empId_fk FOREIGN KEY (empId) REFERENCES Worker(empId) ON DELETE CASCADE
);


INSERT INTO Dept VALUES ('Accounting',null);
INSERT INTO Dept VALUES ('Research',null);

-- Need to change date formats from old version of sql
INSERT INTO Worker VALUES(101,'Smith','Tom', 'Accounting', '1970-02-20', '1993-06-06',50000);
INSERT INTO Worker VALUES(103,'Jones','Mary','Accounting', '1975-06-15', '2005-09-20',48000);
INSERT INTO Worker VALUES(105,'Burns','Jane','Accounting', '1980-09-21', '1990-06-12',39000);
INSERT INTO Worker VALUES(110,'Burns','Michael', 'Research', '1977-05-04', '2010-09-10',70000);
INSERT INTO Worker VALUES(115,'Chin','Amanda', 'Research', '1980-09-22', '2014-06-19',60000);

UPDATE Dept SET mgrId = 101 WHERE deptName = 'Accounting';
UPDATE Dept SET mgrId = 110 WHERE deptName = 'Research';	

INSERT INTO Project VALUES (1001, 'Jupiter', 101, 300000, '2015-02-01', 50);
INSERT INTO Project VALUES (1005, 'Saturn', 101, 400000,  '2014-06-01', 35);
INSERT INTO Project VALUES (1019, 'Mercury', 110, 350000, '2014-02-15', 40);
INSERT INTO Project VALUES (1025, 'Neptune', 110, 600000, '2015-02-01', 45);
INSERT INTO Project VALUES (1030, 'Pluto', 110, 380000, '2014-09-15', 50);

INSERT INTO Assign VALUES(1001, 101, 30,null);
INSERT INTO Assign VALUES(1001, 103, 20,5);
INSERT INTO Assign VALUES(1005, 103, 20,null);
INSERT INTO Assign VALUES(1001, 105, 30,null);
INSERT INTO Assign VALUES(1001, 115, 20,4);
INSERT INTO Assign VALUES(1019, 110, 20,5);
INSERT INTO Assign VALUES(1019, 115, 10,4);
INSERT INTO Assign VALUES(1025, 110, 10,null);
INSERT INTO Assign VALUES(1030, 110, 10,null);

CREATE TRIGGER `InsertProjLog` AFTER INSERT ON `Project` FOR EACH ROW INSERT INTO Logs VALUES(NULL, NEW.projNo, 'Inserted', NOW());

-- Figure 5.3