/**
 * initializes the student, course, and schedule tables and adds
 * a trigger and stored procedure
 * @version 080305
 * @author Pete Nordquist
 */

-- drop tables

-- create tables
CREATE TABLE student (
	ID INT PRIMARY KEY,
	NAME VARCHAR(50)
	) engine=innodb;
CREATE TABLE course (
	CRN INT PRIMARY KEY,
	NAME VARCHAR(50)
	) engine=innodb;
CREATE TABLE schedule (
	ID INT,
	CRN INT, 
	constraint schedulePK primary key(ID,CRN),
	constraint studentFK foreign key(ID) references student(ID)
		on delete cascade on update cascade,
	constraint courseFK foreign key(CRN) references course(CRN)
		on delete cascade on update cascade
	) engine=innodb;


-- Create a few courses
INSERT INTO course VALUES (1, 'cs257');
INSERT INTO course VALUES (2, 'cs360');
INSERT INTO course VALUES (3, 'cs310');
INSERT INTO course VALUES (4, 'cs450');
INSERT INTO course VALUES (5, 'pe180');


-- Create Tables and Triggers for preserving deleted courses
-- Drop tables

-- Create tables
CREATE TABLE deletedStudent (
	ID INT PRIMARY KEY,
	NAME VARCHAR(50)
	) engine=innodb;
CREATE TABLE deletedCourse (
	CRN INT PRIMARY KEY,
	NAME VARCHAR(50)
	) engine=innodb;
/*
Can't use foreign keys on deletedSchedule, because if delete a course,
do not want to enter the students who had registered for that course into
the deleted students table.
*/
CREATE TABLE deletedSchedule (
	ID INT,
	CRN INT, 
	constraint dschedulePK primary key(ID,CRN)
	) engine=innodb;

-- drop Triggers 
DROP TRIGGER IF EXISTS tStudent;
DROP TRIGGER IF EXISTS tCourse;

delimiter // 
-- Create triggers
/* Cascaded deletes do not do a 'delete' operation on the cascaded table
So must manually do the insert into the deletedSchedule table
in the trigger of the parent table.
*/
-- trigger on deletes from the student table.
-- we want to know what courses the student was in when they were deleted
CREATE TRIGGER tStudent BEFORE DELETE ON student FOR EACH ROW
	BEGIN
		INSERT INTO deletedStudent 
			SELECT * FROM student WHERE ID = old.id;
		INSERT INTO deletedSchedule
			SELECT * FROM schedule WHERE ID = old.id;
			
	END;
//
-- trigger on deletes from the course table.
-- we want to know what students were registered when the course was deleted
CREATE TRIGGER tCourse BEFORE DELETE ON course FOR EACH ROW
	BEGIN
		INSERT INTO deletedCourse 
			SELECT * FROM course WHERE CRN = old.crn;
		INSERT INTO deletedSchedule
			SELECT * FROM schedule WHERE CRN = old.crn;
	END;
//

/*
Do not need a trigger on schedule.
The deletedSchedule table's purpose is only to find courses for deleted
students and students for deleted courses
*/

delimiter ; 