// 2.1
db.createCollection("author");
db.createCollection("member");
db.createCollection("book");
db.createCollection("bookAuthor");
db.createCollection("borrowedBooks");

// 2.2
db.Author.insertMany([
	{ authorID: 1, firstName: "Simphiwe", lastName: "Wick" },
	{ authorID: 2, firstName: "Qhawe", lastName: "Sibisi" }
]);

db.Member.insertMany([
	{ memberID: 50, firstName: "Cole", lastName: "Palmer", email: "Cole@gmail.com", phoneNumber: "0761234567" },
	{ memberID: 51, firstName: "Joao", lastName: "Pedro", email: "Pedro@gmail.com", phoneNumber: "0821234567" },
]);

db.Book.insertMany([
	{ ISBN: "1234567890", title: "Python", publicationYear: 2026 },
	{ ISBN: "2467892345", title: "Cybersecurity", publicationYear: 2026 }
]);

db.BookAuthor.insertMany([
	{ ISBN: "1234567890", authorID: 1},
	{ ISBN: "2467892345", authorID: 2}
]);

db.BorrowedBooks.insertMany([
{ memberID: 50, ISBN: "1234567890", dateBorrowed: ISODate("2026-06-14"), dueDate: ISODate("2026-06-30"), returnDate: null, fineAmount: 50 } 
{ memberID: 51, ISBN: "2467892345", dateBorrowed: ISODate("2026-06-15"), dueDate: ISODate("2026-06-30"), returnDate: ISODate("2026-06-29"), fineAmount: 0 },

// 2.3
db.Book.find({ publicationYear: { $gt: 2026 } });

// 2.4
db.BorrowedBooks.aggregate([
	{ $group: { _id: null, totalFines: { $sum: "$fineAmount"} } }
]);

// 2.5
db.borrowedBooks.deleteOne(
{
	fineAmount: 50
});

// 2.6
db.borrowereBooks.aggregate([
{
	$lookup:
	{
		from: "members",
		localField: "memberID",
		foreignField: "memberID",
		as: "memberInfo"
	}
},
{
	$lookup:
	{
		from: "books",
		localField: "ISBN",
		foreignField: "ISBN",
		as: "bookInfo"
	}
},
{
	$unwind: "$member"
},
{
	$unwind: "$bookInfo"
},
{
	$project:
	{
		_id: 0,
		memberID: "$memberInfo.memberID",
		firstName: "$memberInfo.firstName",
		lastName: "$memberInfo.lastName",
		title: "$bookInfo.title",
		dateBorrowed: 1,
		dueDate: 1,
		returnDate: 1,
		fineAmount: 1
	}
}
]);