db.getCollection("debitors").aggregate(

	// Pipeline
	[
		// Stage 1
		{
			$lookup: // Equality Match
			{
			    from: "projects",
			    localField: "fundings.project_id",
			    foreignField: "_id",
			    as: "projects"
			}

			// Uncorrelated Subqueries
			// (supported as of MongoDB 3.6)
			// {
			//    from: "<collection to join>",
			//    let: { <var_1>: <expression>, …, <var_n>: <expression> },
			//    pipeline: [ <pipeline to execute on the collection to join> ],
			//    as: "<output array field>"
			// }
		},

		// Stage 2
		{
			$project: {
			    "_id":1,
			    "name":1,
			    "description":1,
			    "fundings":1,
				"projects":{
				    $filter: {
				        input:"$projects",
				        as:"pro",
				        cond:{$not:{$eq:["$$pro.projectType", "MANAGEMENT_PROJECT"]}}
				    }
				}
			}
		},

		// Stage 3
		{
			$addFields: {
			   "fundedAmount":{$sum:"$fundings.amount"}
			}
		},

		// Stage 4
		{
			$group: {
				"_id":null,
				"max_funding":{$max:"$fundedAmount"},
				"debitors": {
				    $addToSet:{
				        "_id":"$_id",
				        "name":"$name",
				        "fundedAmount":"$fundedAmount",
				        "fundedProjects":{$size:"$fundings"},
				        "projects":"$projects"
				    }
				}
			}
		},

		// Stage 5
		{
			$project: {
			    "debitors":{
			        $filter:{
			        	input:"$debitors",
			        	as: "deb",
			        	cond:{$eq:["$max_funding", "$$deb.fundedAmount"]}
			    	}
			    }
			}
		},

		// Stage 6
		{
			$unwind: {
				path:"$debitors"
			}
		},

		// Stage 7
		{
			$project: {
			    "_id":"$debitors._id",
			    "name":"$debitors.name",
			    "fundedAmount":"$debitors.fundedAmount",
			    "fundedProjects":{$size:"$debitors.projects"},
			    "projects":"$debitors.projects.title"
			}
		},

		// Stage 8
		{
			$group: {
				"_id":null,
				"max_projects":{$max:"$fundedProjects"},
				"debitors":
				{
				    $addToSet:{
				        "_id":"$_id",
				        "name":"$name",
				        "fundedAmount":"$fundedAmount",
				        "fundedProjects":"$fundedProjects",
				        "projects":"$projects",

				    }
				}
			}
		},

		// Stage 9
		{
			$project: {
			    "debitors":{
			    	$filter:
			    	{
			    	    input:"$debitors",
			    	    as:"deb",
			    	    cond:{$eq:["$$deb.fundedProjects", "$max_projects"]}
			    	}
			    }
			}
		},

		// Stage 10
		{
			$unwind: {
			    path : "$debitors"
			}
		},

		// Stage 11
		{
			$project: {
			    "_id":"$debitors._id",
			    "name":"$debitors.name",
			    "fundedAmount":"$debitors.fundedAmount",
			    "fundedProjects":"$debitors.fundedProjects",
			    "projects":"$debitors.projects"
			}
		},

		// Stage 12
		{
			$sort: {
				"name":-1
			}
		},

		// Stage 13
		{
			$out: "DebitorReport"
		},

	]

	// Created with Studio 3T, the IDE for MongoDB - https://studio3t.com/

);