//
// DB, Fall 2024; HW #8
//

use toyu

// [1] Show the information of all students with a faculty advisor in the following manner.
db["student"].find(
	{ advisor: {$ne: null}},
	{ stuId: 1, fname: 1, lname: 1,
	  major: 1, minor: 1, advisor: 1, _id: 0 }
)

// [2] Redo [1] and output in regular string in the following manner in the final result.
db["student"].find(
	{ advisor: {$ne: null}},
	{ stuId: 1, fname: 1, lname: 1,
	  major: 1, minor: 1, advisor: 1, _id: 0 }
).toArray().forEach((x,i) => {
	console.log("[" + (i + 1) + "] " + x["fname"] + " " + x["lname"] + 
		" (" + x["stuId"] + ") -> major: " + x["major"] + 
		 "; minor: " + x["minor"] +
		 "; advisor: " + x["advisor"] +	".") 
})

// [3] Show the names of students majoring in CSCI, CINF or ITEC in the following manners.
db.student.find(
	{  "major": {"$in": ["CSCI", "CINF", "ITEC"] }},
	{  "student": { $concat: ["$fname", " ", "$lname"] },
	   "major": 1,
	   "advisor facId": "$advisor",
	   "_id": 0	   }	
)

// [4] List the information of all students as a textual report in the following manner. Note that the result appears in the order of major, with undeclared major (null in the field major) comes first. Note also the use of "none" for advisor.

// Prepar the faculty object: faculty[facId] = facultyName
facResult = db.faculty.find( {},
	{	"facId": 1,
		"faculty": { $concat: ["$fname", " ", "$lname"] },
	    "_id": 0	   }			
).toArray()
var faculty = {};
facResult.forEach((x,i) => {
	faculty[x["facId"]] = x["faculty"];
})

stuResult = db.student.find(
	{},
	{  "stuId": 1,
	   "student": { $concat: ["$fname", " ", "$lname"] },
	   "major": 1,
	   "advisor": 1,
	   "_id": 0
	}	
).sort( {"major": 1, "student": 1} ).toArray()

stuResult.forEach((x,i) => console.log(x["student"] + ' (' + 
	x["stuId"] + ") => major: " + (x["major"] || 'undeclared') + "; advisor: " +
	(faculty[x["advisor"]] || "none") + "."))


//  [5] Show the student id and the number of classes enrolled with a pasing grade: A, A-, B+, B, B- or C+ in the following manner.
//
db.enroll.aggregate(
	[	
		{ $match: { "grade": {"$in": ['A', 'A-', 'B+', 'B', 'B-', 'C+']}}},
		{ $group: { "_id": "$stuId", "count": {$sum:1}} },
		{ $project: { "student_id": "$_id" , "number of passed courses": "$count",  "_id": 0}}
	]
)