db.createCollection("author")
db.createCollection("book")
db.createCollection("bookauthor")
db.createCollection("member")
db.createCollection("borrowedbooks")

db.author.insertMany([
    { authorID: "A001", firstName: "Trevor", lastName: "Noah" }'
    { authorID: "A002", firstName: "Nadine", lastName: "Gordimer" }
])

db.book.insertMany([
    { ISBN: "9780399588174", title: "Born a Crime", publicationYear: 2016, availability: "In Store", quantityInStock: 5 },
    { ISBN: "9780670874225", title: "The Conservationist", publicationYear: 2027, availability: "Online", quantityInStock: 3 }
])

db.bookauthor.insertMany([
    { authorID: "A001", ISBN: "9780399588174"},
    { authorID: "AOO2", ISBN: "9780670874225"}
])

db.member.insertMany([
  { memberID: "M001", firstName: "Sipho", lastName: "Nkosi", email: "sipho@example.com", phoneNo: "0821234567" },
  { memberID: "M002", firstName: "Amanda", lastName: "van Wyk", email: null, phoneNo: null }
])

db.borrowedbooks.insertMany([
  { memberID: "M001", ISBN: "9780399588174", dateBorrowed: new Date("2026-05-01"), dueDate: new Date("2026-05-15"), returnDate: new Date("2026-05-20"), fineAmount: 50 },
  { memberID: "M002", ISBN: "9780670874225", dateBorrowed: new Date("2026-06-01"), dueDate: new Date("2026-06-15"), returnDate: null, fineAmount: 0 }
])
db.book.find({ publicationYear: { $gt: 2026 }})

db.borroedbooks.aggregate([
    { $group: { _id: null, totalFines: { $sum: "$fineAmount" }}}
])
db.borrowedbooks.deleteMany({ fineAmount: 50 })

db.borrowedbooks.aggregate([
  { $lookup: { from: "member", localField: "memberID", foreignField: "memberID", as: "memberInfo" } },
  { $unwind: "$memberInfo" },
  { $lookup: { from: "book", localField: "ISBN", foreignField: "ISBN", as: "bookInfo" } },
  { $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
  } }
])