-- create views
DROP VIEW IF EXISTS TotalPoints;
CREATE VIEW TotalPoints AS SELECT * FROM Rawscores WHERE SSN = 0001;
DROP VIEW IF EXISTS Weights;
CREATE VIEW Weights AS SELECT * FROM Rawscores WHERE SSN = 0002;
DROP VIEW IF EXISTS WtdPts;
CREATE VIEW WtdPts AS
SELECT 1/t.HW1 * w.HW1 AS HW1, 1/t.HW2a * w.HW2a AS HW2a, 1/t.HW2b * w.HW2b AS HW2b, 1/t.Midterm * w.Midterm AS Midterm, 1/t.HW3 * w.HW3 AS HW3, 1/t.FExam * w.FExam AS FExam
FROM TotalPoints t, Weights w;
DROP VIEW IF EXISTS CumAvgs;
CREATE VIEW CumAvgs AS 
SELECT r.SSN, FORMAT((r.HW1 * w.HW1 + r.HW2a * w.HW2a + r.HW2b * w.HW2b + r.Midterm * w.Midterm + r.HW3 * w.HW3 + r.FExam * w.FExam), 3) * 100 AS CumAvg
FROM Rawscores r, WtdPts w, TotalPoints t
WHERE r.SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights);

DELIMITER |

-- Help with checking SSN
DROP PROCEDURE IF EXISTS CheckSSN |
CREATE PROCEDURE CheckSSN(pSSN INT)
BEGIN
	DECLARE tmp INT;
	DECLARE cur CURSOR FOR SELECT SSN FROM Rawscores WHERE SSN = pSSN AND SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights);
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid SSN." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;
END |

DROP PROCEDURE IF EXISTS CheckPassword |
CREATE PROCEDURE CheckPassword(password VARCHAR(20))
BEGIN
	DECLARE tmp VARCHAR(20);
	DECLARE cur CURSOR FOR SELECT * FROM Passwords WHERE CurPasswords = password;
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid password." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;
END |

-- a
DROP PROCEDURE IF EXISTS ShowRawScores |
CREATE PROCEDURE ShowRawScores(pSSN INT)
BEGIN
	DECLARE tmp INT;
	DECLARE cur CURSOR FOR SELECT SSN FROM Rawscores WHERE SSN = pSSN AND SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights);
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid SSN." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;

	SELECT *
	FROM Rawscores
	WHERE SSN = pSSN;
END |

-- b
DROP PROCEDURE IF EXISTS ShowPercentages |
CREATE PROCEDURE ShowPercentages(pSSN INT)
BEGIN
	DECLARE pLName VARCHAR(20);
	DECLARE pFName VARCHAR(20);
	DECLARE pSection INT;
	DECLARE pHW1 FLOAT;
	DECLARE pHW2a FLOAT;
	DECLARE pHW2b FLOAT;
	DECLARE pMidterm FLOAT;
	DECLARE pHW3 FLOAT;
	DECLARE pFExam FLOAT;
	DECLARE cumAvg FLOAT;

--	CALL CheckSSN(pSSN);
	DECLARE tmp INT;
	DECLARE cur CURSOR FOR SELECT SSN FROM Rawscores WHERE SSN = pSSN AND SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights);
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid SSN." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;

	SELECT LName, FName, Section, HW1, HW2a, HW2b, Midterm, HW3, FExam
	INTO pLName, pFName, pSection, pHW1, pHW2a, pHW2b, pMidterm, pHW3, pFExam
	FROM Rawscores
	WHERE SSN = pSSN;

	SELECT pSSN AS SSN, pLName AS LName, pFName AS PFName, pSection AS Section, FORMAT(pHW1/t.HW1 * 100, 2) AS 'HW1 %', FORMAT(pHW2a/t.HW2a * 100, 2) AS 'HW2a %', FORMAT(pHW2b/t.HW2b * 100, 2) AS 'HW2b %', FORMAT(pMidterm/t.Midterm * 100, 2) AS 'Midterm %', FORMAT(pHW3/t.HW3 * 100, 2) AS 'HW3 %', FORMAT(pFExam/t.FExam * 100, 2) AS 'FExam %'
	FROM TotalPoints t;

	SELECT (pHW1 * w.HW1 + pHW2a * w.HW2a + pHW2b * w.HW2b + pMidterm * w.Midterm + pHW3 * w.HW3 + pFExam * w.FExam) * 100
	INTO cumAvg
	FROM WtdPts w;

	SET @wAvgString = CONCAT('The cumulative course average for ', pFName, ' ', pLName, ' is ', FORMAT(cumAvg, 3));

	SELECT @wAvgString AS CumAvg;
END |

-- c
DROP PROCEDURE IF EXISTS AllRawScores |
CREATE PROCEDURE AllRawScores(password VARCHAR(20))
BEGIN
--	CALL CheckPassword(password);
	DECLARE tmp VARCHAR(20);
	DECLARE cur CURSOR FOR SELECT * FROM Passwords WHERE CurPasswords = password;
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid password." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;
	
	SELECT *
	FROM Rawscores r
	WHERE r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights)
	ORDER BY r.Section ASC, LName ASC, FName ASC;
END |

-- d
DROP PROCEDURE IF EXISTS AllPercentages |
CREATE PROCEDURE AllPercentages(password VARCHAR(20))
BEGIN
--	CALL CheckPasswords(password);
	DECLARE tmp VARCHAR(20);
	DECLARE cur CURSOR FOR SELECT * FROM Passwords WHERE CurPasswords = password;
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid password." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;

	SELECT r.SSN, r.LName, r.FName, r.Section, FORMAT(r.HW1/t.HW1 * 100, 2) AS 'HW1 %', FORMAT(r.HW2a/t.HW2a * 100, 2) AS 'HW2a %', FORMAT(r.HW2b/t.HW2b * 100, 2) AS 'HW2b %', FORMAT(r.Midterm/t.Midterm * 100, 2) AS 'Midterm %', FORMAT(r.HW3/t.HW3 * 100, 2) AS 'HW3 %', FORMAT(r.FExam/t.FExam * 100, 2) AS 'FExam %', FORMAT((r.HW1 * w.HW1 + r.HW2a * w.HW2a + r.HW2b * w.HW2b + r.Midterm * w.Midterm + r.HW3 * w.HW3 + r.FExam * w.FExam), 3) * 100 AS CumAvg
	FROM Rawscores r, WtdPts w, TotalPoints t
	WHERE r.SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights)
	ORDER BY Section, CumAvg;
END |

-- e
DROP PROCEDURE IF EXISTS Stats |
CREATE PROCEDURE Stats(password VARCHAR(20))
BEGIN
	DECLARE tmp VARCHAR(20);
	DECLARE cur CURSOR FOR SELECT * FROM Passwords WHERE CurPasswords = password;
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid password." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;

	SELECT r.SSN, r.LName, r.FName, r.Section, FORMAT(r.HW1/t.HW1 * 100, 2) AS 'HW1 %', FORMAT(r.HW2a/t.HW2a * 100, 2) AS 'HW2a %', FORMAT(r.HW2b/t.HW2b * 100, 2) AS 'HW2b %', FORMAT(r.Midterm/t.Midterm * 100, 2) AS 'Midterm %', FORMAT(r.HW3/t.HW3 * 100, 2) AS 'HW3 %', FORMAT(r.FExam/t.FExam * 100, 2) AS 'FExam %', FORMAT((r.HW1 * w.HW1 + r.HW2a * w.HW2a + r.HW2b * w.HW2b + r.Midterm * w.Midterm + r.HW3 * w.HW3 + r.FExam * w.FExam), 3) * 100 AS CumAvg
	FROM Rawscores r, WtdPts w, TotalPoints t
	WHERE r.Section = 315 AND r.SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights)
	ORDER BY r.Section, CumAvg;

	SELECT 'Mean' AS Statistic, FORMAT(avg(HW1), 3) AS HW1, FORMAT(avg(HW2a), 3) AS HW2a, FORMAT(avg(HW2b), 3) AS HW2b, FORMAT(avg(Midterm), 3) AS Midterm, FORMAT(avg(HW3), 3) AS HW3, FORMAT(avg(FExam), 3) AS FExam, FORMAT(avg(c.CumAvg), 3) AS CumAvg
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 315 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT 'Min' AS Statistic, min(r.HW1) AS HW1, min(r.HW2a) AS HW2a, min(r.HW2b) AS HW2b, min(r.Midterm) AS Midterm, min(r.HW3) AS HW3, min(r.FExam) AS FExam, min(c.CumAvg) AS CumAvg
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 315 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT 'Max' AS Statistic, max(r.HW1) AS HW1, max(r.HW2a) AS HW2a, max(r.HW2b) AS HW2b, max(r.Midterm) AS Midterm, max(r.HW3) AS HW3, max(r.FExam) AS FExam, max(c.CumAvg) AS CumAvgs
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 315 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT 'Std Dev' AS Statistic, FORMAT(stddev(r.HW1), 3) AS HW1, FORMAT(stddev(r.HW2a), 3) AS HW2a, FORMAT(stddev(r.HW2b), 3) AS HW2b, FORMAT(stddev(r.Midterm), 3) AS Midterm, FORMAT(stddev(r.HW3), 3) AS HW3, FORMAT(stddev(r.FExam), 3) AS FExam, FORMAT(stddev(c.CumAvg), 3) AS CumAvg
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 315 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT r.SSN, r.LName, r.FName, r.Section, FORMAT(r.HW1/t.HW1 * 100, 2) AS 'HW1 %', FORMAT(r.HW2a/t.HW2a * 100, 2) AS 'HW2a %', FORMAT(r.HW2b/t.HW2b * 100, 2) AS 'HW2b %', FORMAT(r.Midterm/t.Midterm * 100, 2) AS 'Midterm %', FORMAT(r.HW3/t.HW3 * 100, 2) AS 'HW3 %', FORMAT(r.FExam/t.FExam * 100, 2) AS 'FExam %', FORMAT((r.HW1 * w.HW1 + r.HW2a * w.HW2a + r.HW2b * w.HW2b + r.Midterm * w.Midterm + r.HW3 * w.HW3 + r.FExam * w.FExam), 3) * 100 AS CumAvg
	FROM Rawscores r, WtdPts w, TotalPoints t
	WHERE r.Section = 415 AND r.SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights)
	ORDER BY r.Section, CumAvg;

	SELECT 'Mean' AS Statistic, FORMAT(avg(HW1), 3) AS HW1, FORMAT(avg(HW2a), 3) AS HW2a, FORMAT(avg(HW2b), 3) AS HW2b, FORMAT(avg(Midterm), 3) AS Midterm, FORMAT(avg(HW3), 3) AS HW3, FORMAT(avg(FExam), 3) AS FExam, FORMAT(avg(c.CumAvg), 3) AS CumAvg
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 415 AND r.SSN = c.SSN AND
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT 'Min' AS Statistic, min(r.HW1) AS HW1, min(r.HW2a) AS HW2a, min(r.HW2b) AS HW2b, min(r.Midterm) AS Midterm, min(r.HW3) AS HW3, min(r.FExam) AS FExam, min(c.CumAvg) AS CumAvg
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 415 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT 'Max' AS Statistic, max(r.HW1) AS HW1, max(r.HW2a) AS HW2a, max(r.HW2b) AS HW2b, max(r.Midterm) AS Midterm, max(r.HW3) AS HW3, max(r.FExam) AS FExam, max(c.CumAvg) AS CumAvgs
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 415 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);

	SELECT 'Std Dev' AS Statistic, FORMAT(stddev(r.HW1), 3) AS HW1, FORMAT(stddev(r.HW2a), 3) AS HW2a, FORMAT(stddev(r.HW2b), 3) AS HW2b, FORMAT(stddev(r.Midterm), 3) AS Midterm, FORMAT(stddev(r.HW3), 3) AS HW3, FORMAT(stddev(r.FExam), 3) AS FExam, FORMAT(stddev(c.CumAvg), 3) AS CumAvg
	FROM Rawscores r, CumAvgs c
	WHERE r.Section = 415 AND r.SSN = c.SSN AND 
	r.SSN NOT IN (SELECT SSN
						FROM TotalPoints
						UNION
						SELECT SSN
						FROM Weights);
END |

-- f
DROP PROCEDURE IF EXISTS ChangeScores |
CREATE PROCEDURE ChangeScores(password VARCHAR(20), pSSN INT, AssignmentName VARCHAR(20), NewScore FLOAT)
BEGIN
	DECLARE tmp VARCHAR(20);
	DECLARE cur CURSOR FOR SELECT * FROM Passwords WHERE CurPasswords = password;
	DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid password." AS ErrorMessage;
	OPEN cur;
	FETCH cur INTO tmp;
	CLOSE cur;

	BEGIN
		DECLARE tmp_1 VARCHAR(20);
		DECLARE cur_1 CURSOR FOR SELECT SSN FROM Rawscores r WHERE SSN = pSSN AND SSN NOT IN (SELECT SSN FROM TotalPoints UNION SELECT SSN FROM Weights);
		DECLARE EXIT HANDLER FOR NOT FOUND SELECT "Not a valid SSN." AS ErrorMessage;
		OPEN cur_1;
		FETCH cur_1 INTO tmp_1;
		CLOSE cur_1;

		CASE AssignmentName
		WHEN 'HW1' THEN
			SELECT 'Old Values:' AS '';
			SELECT * FROM Rawscores WHERE SSN = pSSN;
			SELECT 'New Values:' AS '';
			UPDATE Rawscores r SET r.HW1=NewScore WHERE r.SSN = pSSN;
			SELECT * FROM Rawscores WHERE SSN = pSSN;
		WHEN 'HW2a' THEN
			SELECT 'Old Values:' AS '';
			SELECT * FROM Rawscores WHERE SSN = pSSN;
			SELECT 'New Values:' AS '';
			UPDATE Rawscores r SET r.HW2a=NewScore WHERE r.SSN = pSSN;
			SELECT * FROM Rawscores WHERE SSN = pSSN;
		WHEN 'HW2b' THEN
			SELECT 'Old Values:' AS '';
			SELECT * FROM Rawscores WHERE SSN = pSSN;
			SELECT 'New Values:' AS '';
			UPDATE Rawscores r SET r.HW2b=NewScore WHERE r.SSN = pSSN;
			SELECT * FROM Rawscores WHERE SSN = pSSN;
		WHEN 'Midterm' THEN
			SELECT 'Old Values:' AS '';
			SELECT * FROM Rawscores WHERE SSN = pSSN;
			SELECT 'New Values:' AS '';
			UPDATE Rawscores r SET r.Midterm=NewScore WHERE r.SSN = pSSN;
			SELECT * FROM Rawscores WHERE SSN = pSSN;
		WHEN 'HW3' THEN
			SELECT 'Old Values:' AS '';
			SELECT * FROM Rawscores WHERE SSN = pSSN;
			SELECT 'New Values:' AS '';
			UPDATE Rawscores r SET r.HW3=NewScore WHERE r.SSN = pSSN;
			SELECT * FROM Rawscores WHERE SSN = pSSN;
		WHEN 'FExam' THEN
			SELECT 'Old Values:' AS '';
			SELECT * FROM Rawscores WHERE SSN = pSSN;
			SELECT 'New Values:' AS '';
			UPDATE Rawscores r SET r.FExam=NewScore WHERE r.SSN = pSSN;
			SELECT * FROM Rawscores WHERE SSN = pSSN;
		ELSE
			SELECT 'Not a valid AssignmentName' AS ErrorMessage;
		END CASE;
	END;
END |

DELIMITER ;