SPROC

DELIMITER $$
CREATE PROCEDURE course_schedule_assignment (IN course_code CHAR(3), IN ideal_start_date DATE)
	BEGIN
		DECLARE complete BOOLEAN DEFAULT FALSE;
		DECLARE start_date DATE;
        DECLARE module_code VARCHAR(2);
        DECLARE modules CURSOR FOR
			SELECT code FROM module
            WHERE (course_code = module.course_code);
		 DECLARE CONTINUE HANDLER FOR NOT FOUND
	      SET complete = TRUE;

        IF (DAYOFWEEK(ideal_start_date) = 7) OR (DAYOFWEEK(ideal_start_date) = 1) THEN
			set start_date = DATE_ADD(ideal_start_date, INTERVAL 2 DAY);
		ELSE set start_date = ideal_start_date;
		END IF;

        IF (start_date <= DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) THEN
			SIGNAL SQLSTATE '45000'
				SET MESSAGE_TEXT = 'Invalid date. Please select a date at least 1 month in the future';
		END IF;

         IF (course_code NOT IN (SELECT code FROM course)) THEN
			SIGNAL SQLSTATE '45000'
				SET MESSAGE_TEXT = 'Invalid Course. Please select a valid course';
		END IF;

        OPEN modules;
        SET module_code ='';

        main_loop : loop
		FETCH NEXT FROM modules INTO module_code;
        IF COMPLETE THEN
				LEAVE main_loop;
		ELSE

            INSERT INTO session (code, date, room)
				VALUES (module_code, start_date ,'');
			SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);

			END IF;
            END LOOP;
        CLOSE modules;
    END$$