db.createCollection("AUTHOR");
db.createCollection("BOOK");
db.createCollection("BOOKAUTHOR");
db.createCollection("MEMBER");
db.createCollection("BORROWEDBOOKS");
 
db.AUTHOR.insertMany([
  { authorID: 1, firstName: "John", lastName: "Doe" },
  { authorID: 2, firstName: "Jane", lastName: "Doe" }
]);

db.BOOK.insertMany([
  { ISBN: "9781234567890", title: "Database Systems", publicationYear: 2024},
  { ISBN: "9789876543210", title: "Math Fundamentals for Robotics", publicationYear: 2028}
]);

db.BOOKAUTHOR.insertMany([
  { ISBN: "9781234567890", authorID: 1 },
  { ISBN: "9789876543210", authorID: 2 }
]);

db.MEMBER.insertMany([
  { memberID: 101, firstName: "James", lastName: "Doe", email: "james@email.com", phoneNo: "0821112222" },
  { memberID: 102, firstName: "Jill", lastName: "Doe", email: "jill@email.com" }
]);

db.BORROWEDBOOKS.insertMany([
  { 
    memberID: 101, 
    ISBN: "9781234567890", 
    dateBorrowed: ISODate("2026-06-01T08:00:00Z"), 
    dueDate: ISODate("2026-06-15T17:00:00Z"), 
    returnDate: ISODate("2026-06-14T12:00:00Z"), 
    fineAmount: 50.00 
  },
  { 
    memberID: 102, 
    ISBN: "9789876543210", 
    dateBorrowed: ISODate("2026-06-10T09:30:00Z"), 
    dueDate: ISODate("2026-06-24T17:00:00Z"), 
    returnDate: null,
    fineAmount: 15.50
  }
]);

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: "memberID",
      as: "memberDetails"
    }
  },
  { $unwind: "$memberDetails" },
  {
    $lookup: {
      from: "BOOK",
      localField: "ISBN",
      foreignField: "ISBN",
      as: "bookDetails"
    }
  },
  { $unwind: "$bookDetails" },
  {
    $project: {
      _id: 0,
      memberID: "$memberDetails.memberID",
      firstName: "$memberDetails.firstName",
      lastName: "$memberDetails.lastName",
      bookTitle: "$bookDetails.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]);