DELIMITER $$
CREATE PROCEDURE updateFinalGrades (
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE varSystemid CHAR(17) DEFAULT '';
	DECLARE varSubjectseqno SMALLINT DEFAULT 0;
	DECLARE varPrelimRating DECIMAL(5,2) DEFAULT 0;
	DECLARE varMidtermRating DECIMAL(5,2) DEFAULT 0;
	DECLARE varFinalRating DECIMAL(5,2) DEFAULT 0;
	DECLARE varAverageGrade DECIMAL(5,2) DEFAULT 0;
	DECLARE varFinalGrade DECIMAL(5,2) DEFAULT 0;
	DECLARE varRemarks VARCHAR(10) DEFAULT '';

	-- declare cursor for employee email
	DECLARE curFinalGrade 
		CURSOR FOR 
			SELECT systemid, subjectseqno, prelimrating, midtermrating, finalrating FROM regsubjects where finalrating > 0;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

	OPEN curFinalGrade;

	getFinalGrade: LOOP
		FETCH curFinalGrade INTO varSystemid, varSubjectseqno, varPrelimRating, varMidtermRating, varFinalRating;
		IF finished = 1 THEN 
			LEAVE getFinalGrade;
		END IF;
		
		SET varAverageGrade = ROUND((varPrelimRating * 0.3) + (varMidtermRating * 0.3) + (varFinalRating * 0.4), 2);

		-- determine equivalent and comment
		IF varAverageGrade > 100 THEN
			SET varFinalGrade = 0.00; SET varRemarks = 'INVALID';
		ELSE 
			IF varAverageGrade >= 98.5 THEN
				SET varFinalGrade = 1.00; SET varRemarks = 'PASSED';
			ELSE
				IF varAverageGrade >= 95.5 THEN 
					SET varFinalGrade = 1.25; SET varRemarks = 'PASSED';
				ELSE
					IF varAverageGrade >= 92.5 THEN 
						SET varFinalGrade = 1.50; SET varRemarks = 'PASSED';
					ELSE 
						IF varAverageGrade >= 89.5 THEN 
							SET varFinalGrade = 1.75; SET varRemarks = 'PASSED';
						ELSE
							IF varAverageGrade >= 86.5 THEN 
								SET varFinalGrade = 2.00; SET varRemarks = 'PASSED';
							ELSE
								IF varAverageGrade >= 83.5 THEN
									SET varFinalGrade = 2.25; SET varRemarks = 'PASSED';
								ELSE
									IF varAverageGrade >= 80.5 THEN 
										SET varFinalGrade = 2.50; SET varRemarks = 'PASSED';
									ELSE
										IF varAverageGrade >= 77.5 THEN 
											SET varFinalGrade = 2.75; SET varRemarks = 'PASSED';
										ELSE 
											IF varAverageGrade >= 74.5 THEN 
												SET varFinalGrade = 3.00; SET varRemarks = 'PASSED';
											ELSE
												IF varAverageGrade < 74.5 AND varAverageGrade > 0 THEN 
													SET varFinalGrade = 5.00; SET varRemarks = 'FAILED';
												ELSE 
													SET varFinalGrade = 0.00; SET varRemarks = 'INVALID';
												END IF;
											END IF;
										END IF;
									END IF;
								END IF;
							END IF;
						END IF;
					END IF;
				END IF;
			END IF;
		END IF;
		
		UPDATE regsubjects SET averagegrade = varAverageGrade, finalgrade = varFinalGrade, remarks =  varRemarks
		WHERE systemid = varSystemid AND subjectseqno = varSubjectseqno;
				
	END LOOP getFinalGrade;
	CLOSE curFinalGrade;

END$$
DELIMITER ;