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

use toyu

// [1] Show the information of all departments with six or more staffs in JSON format in the following manner.
db["department"].find(
	{ numStaff: {$gte: 6}},
	{ deptCode: true, "department name": "$deptName", "number of staff": "$numStaff", _id: false }
)


// [2] Redo [1] and output in regular string in the following manner in the final result.
department = db["department"].find(
	{ numStaff: {$gte: 6}},
	{ _id: false }
).toArray()
department.forEach((x,i) => {
	console.log("[" + (i + 1) + "] " + x["deptName"] + " (" + x["deptName"] + "): in the college " + x["schoolCode"] + " with " +
		x["numStaff"] + " staff.")
})

// [3] Show the schoolcode of all schools with their number and names of departments in JSON in the following manner.

db["department"].aggregate(
	[	
		{ $group: { "_id": "$schoolCode", "count": {$count: {}},
					"departments": {$push: "$deptName"}}},
		{ $project: { "school code": "$_id" , "number of departments": "$count",  
			"departments": 1,
			"_id": 0 }}
	]
)

// Mongo projects according to the order of field insertion so departments may output first. To assure school code comes first. 

db["department"].aggregate(
	[	
		{ $group: { "_id": "$schoolCode", "count": {$count: {}},
					"depts": {$push: "$deptName"}}},
		{ $project: {"scode": "$_id", "numDept": "$count", "depts":1  }},		
		{ $project: { "school code": "$_id" , "number of departments": "$numDept",  
			"departments": "$depts",
			"_id": 0 }}
	]
)


// [4] Show department information in the JSON string format in the following manner. List only those departments in the school "HSH" or "CSE" and with a number of staff of 5 or more.
db["department"].find(
	{  schoolCode: { $in: ["HSH", "CSE"] },
	   numStaff: { $gte: 5 }},
	{	"department": {$concat: ["$deptName", " (", "$deptCode", ")"]},
	    "numStaff": 1, "schoolCode": 1, "_id": 0  }	
)

// [5] Repeat [3], but show the final result in regular string the following manner.
deptResult = db["department"].aggregate(
	[	
		{ $group: { "_id": "$schoolCode", "count": {$count: {}},
					"departments": {$push: "$deptName"}}},
		{ $project: { "schoolCode": "$_id" , "numDept": "$count",  
			"departments": 1,
			"_id": 0 }}
	]
).toArray()
var departments = {};
var numDept = {};
deptResult.forEach((x,i) => {
	departments[x["schoolCode"]] = x["departments"].join(', ');
	numDept[x["schoolCode"]] = x["numDept"]
})
	
schoolResult = db["school"].find(
	{  schoolCode: { $in: ["HSH", "CSE"] }},
	{ "_id": 0}
).toArray()

schoolResult.forEach((x,i) => console.log('School ' + x["schoolCode"] + ' (' + 
	x["schoolName"] + ") has " + (numDept[x["schoolCode"]] || 0) + " departments: " +
	(departments[x["schoolCode"]] || '')))
    
// Alternatively:

db.school.aggregate([
{$lookup:
    {
      from: "department",
      let: {joinValue: '$schoolCode'},
      pipeline: [
            { $match:
                  { $expr:
                        {$and: [
                                { $eq: [ "$schoolCode",  "$$joinValue" ] },
                                { $gte: ["numStaff", 5 ]}
                               ]
                         }
                  }
            }, 
            { $group: { "_id": "$schoolCode", "count": {$count: {}},
                        "departments": {$push: "$deptName"}}},
            { $project: { "schoolCode": "$_id" , "numDept": "$count",  
                "departments": 1,
                "_id": 0 }}            
            ],
        as: "dept"     }},
  { $match: { schoolCode: { $in: ["HSH", "CSE"] }}}, 
  { $project: { "schoolCode": 1, "schoolName": 1, "dept": 1,"_id": 0}} 
]).toArray().forEach(
    (x,i) => console.log('School ' + x["schoolCode"] + ' (' + 
	x["schoolName"] + ") has " + (x["dept"][0]["numDept"] || 0) + " departments: " +
	(x["dept"][0]["departments"].join(', ') || '')))