use eduvos_library
 
db.createCollection("bookauthor")
db.createCollection("book")
db.createCollection("borrowedbooks")
db.createCollection("memeber")
db.createCollection("author")
db.authors.insertMany([
  { _id: "A001", firstName: "Angel", lastName: "Mathivha" },
  { _id: "A002", firstName: "Aisha", lastName: "Homela" }
])
db.books.insertMany([
  { _id: "9780399588174", title: "Born a Crime", publicationYear: 2016,
    authorID: "A001", availability: "In Store", quantityInStock: 5 },
  { _id: "9780307455925", title: "Americanah", publicationYear: 2013,
    authorID: "A002", availability: "Online", quantityInStock: 4 },
  { _id: "9781234567897", title: "Digital Futures", publicationYear: 2027,
    authorID: "A001", availability: "Online", quantityInStock: 10 },
  { _id: "9789876543210", title: "The Township Economy", publicationYear: 2028,
    authorID: "A002", availability: "In Store", quantityInStock: 7 }
])
db.members.insertMany([
  { _id: "M001", firstName: "Angel", lastName: "Mabitsela",
    email: "angel@eduvos.com", phone: "0821234567" },
  { _id: "M002", firstName: "Nhlanhla", lastName: "Chauke",
    email: "chauke@eduvos.com", phone: "0837654321" },
  { _id: "M003", firstName: "Christina", lastName: "Mpande",
    phone: "0791122334" }
])
db.borrow.insertMany([
  { memberID: "M001", isbn: "9780399588174",
    dateBorrowed: ISODate("2026-05-01"), dueDate: ISODate("2026-05-15"),
    returnDate: ISODate("2026-05-20"), fineAmount: 50 },
  { memberID: "M002", isbn: "9780307455925",
    dateBorrowed: ISODate("2026-06-01"), dueDate: ISODate("2026-06-15"),
    returnDate: ISODate("2026-06-14"), fineAmount: 0 },
  { memberID: "M001", isbn: "9781234567897",
    dateBorrowed: ISODate("2026-06-10"), dueDate: ISODate("2026-06-24"),
    returnDate: null, fineAmount: 0 },
  { memberID: "M003", isbn: "9789876543210",
    dateBorrowed: ISODate("2026-05-20"), dueDate: ISODate("2026-06-03"),
    returnDate: ISODate("2026-06-10"), fineAmount: 70 }
])
db.payments.insertMany([
  { _id: "P001", memberID: "M002", isbn: "9780307455925",
    transactionType: "Rent", amount: 25, device: "Tablet",
    paymentDate: ISODate("2026-06-01") },
  { _id: "P002", memberID: "M003", isbn: "9789876543210",
    transactionType: "Purchase", amount: 150, device: "Laptop",
    paymentDate: ISODate("2026-05-20") }
])
db.books.find({ publicationYear: { $gt: 2026 } })
db.borrow.aggregate([
  { $group: { _id: null, totalFinesCollected: { $sum: "$fineAmount" } } }
])
db.borrow.deleteOne({
  memberID: "M001",
  isbn: "9780399588174",
  fineAmount: 50
})
db.borrow.aggregate([
  {
    $lookup: {
      from: "members",
      localField: "memberID",
      foreignField: "_id",
      as: "memberInfo"
    }
  },
  { $unwind: "$memberInfo" },
  {
    $lookup: {
      from: "books",
      localField: "isbn",
      foreignField: "_id",
      as: "bookInfo"
    }
  },
  { $unwind: "$bookInfo" },
  {
    $project: {
      _id: 0,
      memberID: 1,
      firstName: "$memberInfo.firstName",
      lastName: "$memberInfo.lastName",
      title: "$bookInfo.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
])