db.createCollection("members");
db.createCollection("authors");
db.createCollection("books");
db.createCollection("borrowed_books");
db.createCollection("book_authors");

db.members.insertMany([
  { memberID: "m01", firstName: "Thabo", lastName: "Nkosi", email: "thabo.nkosi@email.com", phone: "0712345678" },
  { memberID: "m02", firstName: "Lerato", lastName: "Dube", email: "lerato.dube@email.com", phone: "0723456789" }
]);

db.authors.insertMany([
  { authorID: "au01", firstName: "Chinua", lastName: "Achebe" },
  { authorID: "au02", firstName: "Trevor", lastName: "Noah" }
]);

db.books.insertMany([
  { ISBN: "9780385474542", title: "Born a Crime", pubYear: 2027, format: "Online", quantityInStock: 12 },
  { ISBN: "9780385667833", title: "Things Fall Apart", pubYear: 1958, format: "In Store", quantityInStock: 5 }
]);

db.book_authors.insertMany([
  { ISBN: "9780385474542", authorID: "au02" },
  { ISBN: "9780385667833", authorID: "au01" }
]);

db.borrowed_books.insertMany([
  { memberID: "m01", ISBN: "9780385474542", dateBorrowed: new Date("2026-05-01"), dueDate: new Date("2026-05-15"), returnDate: new Date("2026-05-20"), fineAmount: 50 },
  { memberID: "m02", ISBN: "9780385667833", dateBorrowed: new Date("2026-06-01"), dueDate: new Date("2026-06-15"), returnDate: null, fineAmount: 0 }
]);

db.books.find({ pubYear: { $gt: 2026 } });

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

db.borrowed_books.deleteOne({ fineAmount: 50 });

db.borrowed_books.deleteOne({ memberID: "m01", fineAmount: 50 });

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