db.createCollection("AUTHOR");
db.createCollection("BOOKAUTHOR");
db.createCollection("BOOK");
db.createCollection("BORROWEDBOOKS");
db.createCollection("MEMBER");
// 1. AUTHOR
db.AUTHOR.insertMany([
  { _id: "au01", firstName: "Noni", lastName: "Jabavu" },
  { _id: "au02", firstName: "Mark", lastName: "Tshuma" }
]);

// 2. BOOKAUTHOR (Intersection)
db.BOOKAUTHOR.insertMany([
  { _id: "BA01", authorID: "au01", ISBN: "978-1-1234" },
  { _id: "BA02", authorID: "au02", ISBN: "978-2-5678" }
]);

// 3. BOOK
db.BOOK.insertMany([
  { ISBN: "978-1-1234", title: "Introduction to NoSQL", publicationYear: 2025 },
  { ISBN: "978-2-5678", title: "Advanced Cloud Architecture", publicationYear: 2027 }
]);

// 4. BORROWEDBOOKS (Intersection with strict schema matching your image)
db.BORROWEDBOOKS.insertMany([
  {
    _id: "BB01",
    memberID: "M1001",
    ISBN: "978-1-1234",
    dateBorrowed: "2026-05-01",
    dueDate: "2026-05-15",
    returnDate: "2026-05-14",
    fineAmount: 0
  },
  {
    _id: "BB02",
    memberID: "M1002",
    ISBN: "978-2-5678",
    dateBorrowed: "2026-06-01",
    dueDate: "2026-06-15",
    returnDate: "null", 
    fineAmount: 50 // R50 fee matching your questions
  }
]);

// 5. MEMBER
db.MEMBER.insertMany([
  { _id: "M1001", firstName: "Sipho", lastName: "Nkosi", email: "sipho@mail.co.za", phoneNo: "+27821112222" },
  { _id: "M1002", firstName: "Chipo", lastName: "Moyo", email: "chipo.moyo@eduvos.com" }
]);
db.BOOK.find({ publicationYear: { $gt: 2026 } });
db.BORROWEDBOOKS.aggregate([
  { $group: { _id: null, totalFineRevenue: { $sum: "$fineAmount" } } }
]);
db.BORROWEDBOOKS.deleteOne({ fineAmount: 50 });
db.BORROWEDBOOKS.aggregate([
  {
    $lookup: {
      from: "MEMBER",
      localField: "memberID",
      foreignField: "_id",
      as: "member"
    }
  },
  { $unwind: "$member" },
  {
    $lookup: {
      from: "BOOK",
      localField: "ISBN",
      foreignField: "ISBN",
      as: "book"
    }
  },
  { $unwind: "$book" },
  {
    $project: {
      _id: 0,
      memberIdentity: "$member._id",
      firstName: "$member.firstName",
      lastName: "$member.lastName",
      bookTitle: "$book.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]);