/*
1:
	Skrifið stored procedure: StudentListJSon() sem notar cursor til að breyta vensluðum gögnum í JSon string.
	JSon-formuð gögnin eru listi af objectum.
	OBS: StudentListJSon skilar texta sem þið hafið formað.

	Niðurstöðurnar ættu að líta einhvern vegin svona út:

	[
		  {"first_name": "Guðrún", "last_name": "Ólafsdóttir", "date_of_birth": "1999-03-31"},
		  {"first_name": "Andri Freyr", "last_name": "Kjartansson", "date_of_birth": "2000-11-01"},
		  {"first_name": "Tinna Líf", "last_name": "Björnsson", "date_of_birth": "1998-08-14"},
		  {"first_name": "Magni Þór", "last_name": "Sigurðsson", "date_of_birth": "2000-05-27"},
		  {"first_name": "Rheza Már", "last_name": "Hamid-Davíðs", "date_of_birth": "2001-09-17"},
		  {"first_name": "Hadría Gná", "last_name": "Schmidt", "date_of_birth": "1999-07-29"},
		  {"first_name": "Jasmín Rós", "last_name": "Stefánsdóttir", "date_of_birth": "1996-02-29"}
	]
*/
delimiter $$
drop procedure if exists StudentListJSon $$

create procedure StudentListJSon()
	begin
    declare fnafn varchar(255);
    declare lnafn varchar(255);
    declare dateob date;

    declare json_string text;
    declare done int default false;

	DECLARE cursor1 CURSOR FOR SELECT firstName, lastName, dob FROM Students;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

    set json_string = "[";

    select json_string;

    OPEN cursor1;
    read_loop:loop
		FETCH cursor1 INTO fnafn, lnafn, dateob;

        if done then leave read_loop;
        end if;

        set json_string = concat(json_string, '{"first_name": ','"', fnafn,'", "last_name": ', '"', lnafn, '", "date_of_birth": ', '"', dateob, '"},');


    # FETCH cursor1 INTO fnafn, lnafn, dateob;
	end loop;

	CLOSE cursor1;

    set json_string = trim(trailing ',' from json_string);
    set json_string = concat(json_string, "]");

    select json_string;

    end $$

delimiter ;

call StudentListJSon();

/*
	2:
	Skrifið nú SingleStudentJSon()þannig að nemandinn innihaldi nú lista af þeim áföngum sem hann hefur tekið.
	Śé nemandinn enn við nám þá koma þeir áfangar líka með.
	ATH: setjið nemandann sem object.
	Líkleg niðurstaða:

	{
		"student_id": "1",
		"first_name": "Guðrún",
		"last_name": "Ólafsdóttir",
		"date_of_birth": "1999-03-31",
		"courses" :[
		  {"course_number": "STÆ103","course_credits": "5","status": "pass"},
		  {"course_number": "EÐL103","course_credits": "5","status": "pass"},
		  {"course_number": "STÆ203","course_credits": "5","status": "pass"},
		  {"course_number": "EÐL203","course_credits": "5","status": "pass"},
		  {"course_number": "STÆ303","course_credits": "5","status": "pass"},
		  {"course_number": "GSF2A3U","course_credits": "5","status": "pass"},
		  {"course_number": "FOR3G3U","course_credits": "5","status": "pass"},
		  {"course_number": "GSF2B3U","course_credits": "5","status": "pass"},
		  {"course_number": "GSF3B3U","course_credits": "5","status": "fail"},
		  {"course_number": "FOR3D3U","course_credits": "5","status": "fail"}
		]
	}
*/
delimiter $$
drop procedure if exists SingleStudentJson $$
create procedure SingleStudentJSon(
stuID int
)
begin

declare student_id int;
declare fname varchar(32);
declare lname varchar(32);
declare dateob date;

declare c_num varchar(32);
declare c_cred int;
declare stat varchar(10);

declare json_string text;

declare done int default false;

declare courseCursor cursor
for select Courses.courseNumber, Courses.courseCredits,
case
	when Registration.passed = 1 then 'pass'
    else 'fail'
end
from Courses
inner join TrackCourses on Courses.courseNumber = TrackCourses.courseNumber
inner join Registration on TrackCourses.courseNumber = Registration.courseNumber
and Registration.studentID = stuID;

declare continue handler for not found set done = true;

select studentID, firstName, lastName, dob
into student_id,fname,lname,dateob
from Students where studentID = stuID;

set json_string = "{";

set json_string = concat('"student_id": ','"',student_id,'", ',
'"first_name": ','"',fname,'", ',
'"last_name": ','"',lname,'", '
'"data_of_birth": ','"',dateob,'", ',
'"courses": [');

open courseCursor;

read_loop: loop
	fetch courseCursor into c_num, c_cred, stat;
    if done then leave read_loop;
    end if;

    set json_string = concat(json_string, '{"course_numer": ', '"',c_num,'", ',
    '"course_credits": ', '"',c_cred,'" ,',
    '"course_status": ', '"',stat,'"},');
end loop;
close courseCursor;
set json_string = trim(trailing ',' from json_string);
set json_string = concat(json_string, ']}');

select json_string;

end $$
delimiter ;

call SingleStudentJSon(1);

/*
	3:
	Skrifið stored procedure: SemesterInfoJSon() sem birtir uplýsingar um ákveðið semester.
	Semestrið inniheldur lista af nemendum sem eru /hafa verið á þessu semestri.
	Og að sjálfsögðu eru gögnin á JSon formi!

	Gæti litið út einhvern veginn svona(hérna var semesterID 8 notað á original gögnin:
	[
		{"student_id": "1", "first_name": "Guðrún", "last_name": "Ólafsdóttir", "courses_taken": "2"},
		{"student_id": "2", "first_name": "Andri Freyr", "last_name": "Kjartansson", "courses_taken": "1"},
		{"student_id": "5", "first_name": "Rheza Már", "last_name": "Hamid-Davíðs", "courses_taken": "2"},
		{"student_id": "6", "first_name": "Hadríra Gná", "last_name": "Schmidt", "courses_taken": "2"}
	]
*/

delimiter $$
drop procedure if exists SemesterInfoJSon $$
create procedure SemesterInfoJSon(
id int
)
begin
	declare stu_id int;
    declare stu_fname varchar(32);
    declare stu_lname varchar(32);
    declare stu_course int;

	declare json_string text;

	declare done int default false;


	# declare courseCursor cursor
    # for select distinct Students.studentID, Students.firstName, Students.lastName, count(Registration.courseNumber)
    # from Semesters
    # inner join Registration on Semesters.semesterID = Registration.semesterID
    # inner join Students on Registration.studentID = Students.studentID
    # where Semesters.semesterID = id;

	declare courseCursor cursor
    for select distinct Students.studentID, Students.firstName, Students.lastName, count(Registration.courseNumber)
    from Students
    inner join Registration on Students.studentID = Registration.studentID
    and Registration.semesterID = id
    group by Students.studentID;

    declare continue handler for not found set done = true;

    open courseCursor;

    set json_string = "[";
    read_loop: loop

	fetch courseCursor into stu_id, stu_fname, stu_lname, stu_course;

    if done then leave read_loop;
    end if;

    set json_string = concat(json_string, '{"student_id": ', '"',stu_id,'", ',
    '"first_name": ', '"',stu_fname,'" ,',
    '"last_name": ', '"',stu_lname,'",',
    '"course_taken": ', '"',stu_course,'"},');
	end loop;
    close courseCursor;
	set json_string = trim(trailing ',' from json_string);
	set json_string = concat(json_string, ']');

	select json_string;

end $$
delimiter ;

select * from Students;

call SemesterInfoJSon(2);

-- ACHTUNG:  2 og 3 nota líka cursor!