db.createCollection("Author");
db.createCollection("Book");
db.createCollection("BookAuthor");
db.createCollection("Member");
db.createCollection("BorrowedBooks");

db.Author.insertMany([
  {
    authorID:  1,
    firstName: "Chinua",
    lastName:  "Achebe"
  },
  {
    authorID:  2,
    firstName: "Ngugi",
    lastName:  "wa Thiong'o"
  },
  {
    authorID:  3,
    firstName: "Nadine",
    lastName:  "Gordimer"
  }
]);
db.Book.insertMany([
  {
    ISBN:            "9780385474542",
    title:           "Things Fall Apart",
    publicationYear: 1958,
    availability:    "online",
    quantityInStock: 10
  },
  {
    ISBN:            "9780143039761",
    title:           "A Grain of Wheat",
    publicationYear: 1967,
    availability:    "in-store",
    quantityInStock: 5
  },
  {
    ISBN:            "9780140281408",
    title:           "July's People",
    publicationYear: 2028,
    availability:    "online",
    quantityInStock: 8
  },
  {
    ISBN:            "9780374528379",
    title:           "The Conservationist",
    publicationYear: 2027,
    availability:    "in-store",
    quantityInStock: 3
  }
]);
db.BookAuthor.insertMany([
  { ISBN: "9780385474542", authorID: 1 },
  { ISBN: "9780143039761", authorID: 2 },
  { ISBN: "9780140281408", authorID: 3 },
  { ISBN: "9780374528379", authorID: 3 }
]);
db.Member.insertMany([
  {
    memberID:  101,
    firstName: "Thabo",
    lastName:  "Mokoena",
    email:     "thabo.mokoena@eduvos.ac.za",
    phoneNo:   "0712345678"
  },
  {
    memberID:  102,
    firstName: "Aisha",
    lastName:  "Patel",
    email:     "aisha.patel@eduvos.ac.za",
    phoneNo:   null
  },
  {
    memberID:  103,
    firstName: "Sipho",
    lastName:  "Dlamini",
    email:     null,
    phoneNo:   "0834567890"
  }
]);
db.BorrowedBooks.insertMany([
  {
    memberID:     101,
    ISBN:         "9780385474542",
    dateBorrowed: new Date("2026-05-01"),
    dueDate:      new Date("2026-05-15"),
    returnDate:   new Date("2026-05-20"),
    fineAmount:   50
  },
  {
    memberID:     102,
    ISBN:         "9780143039761",
    dateBorrowed: new Date("2026-06-01"),
    dueDate:      new Date("2026-06-15"),
    returnDate:   new Date("2026-06-18"),
    fineAmount:   30
  },
  {
    memberID:     103,
    ISBN:         "9780140281408",
    dateBorrowed: new Date("2026-06-10"),
    dueDate:      new Date("2026-06-24"),
    returnDate:   null,
    fineAmount:   null
  },
  {
    memberID:     101,
    ISBN:         "9780374528379",
    dateBorrowed: new Date("2026-06-15"),
    dueDate:      new Date("2026-06-29"),
    returnDate:   null,
    fineAmount:   null
  }
]);

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

db.BorrowedBooks.aggregate([
  {
    $match: { fineAmount: { $ne: null } }
  },
  {
    $group: {
      _id:              null,
      totalFineRevenue: { $sum: "$fineAmount" }
    }
  },
  {
    $project: {
      _id:              0,
      totalFineRevenue: 1
    }
  }
]);
db.BorrowedBooks.deleteOne(
  {
    memberID:   101,
    fineAmount: 50
  }
);
// Verify deletion
db.BorrowedBooks.find({ memberID: 101 });

db.BorrowedBooks.aggregate([
  // Step 1: Join with Member collection on memberID
  {
    $lookup: {
      from:         "Member",
      localField:   "memberID",
      foreignField: "memberID",
      as:           "memberDetails"
    }
  },
  // Step 2: Flatten the memberDetails array
  { $unwind: "$memberDetails" },

  // Step 3: Join with Book collection on ISBN
  {
    $lookup: {
      from:         "Book",
      localField:   "ISBN",
      foreignField: "ISBN",
      as:           "bookDetails"
    }
  },

  // Step 4: Flatten the bookDetails array
  { $unwind: "$bookDetails" },

  // Step 5: Project only required fields
  {
    $project: {
      _id:          0,
      memberID:     "$memberDetails.memberID",
      firstName:    "$memberDetails.firstName",
      lastName:     "$memberDetails.lastName",
      title:        "$bookDetails.title",
      dateBorrowed: 1,
      dueDate:      1,
      returnDate:   1,
      fineAmount:   1
    }
  }
]);