db.createCollection("AUTHOR");
db.createCollection("BOOKAUTHOR");
db.createCollection("BOOK");
db.createCollection("MEMBER");
db.createCollection("BORROWEDBOOKS");

db.AUTHOR.insertMany([
  { authorID: 1, firstName: "John", lastName: "Doe" },
  { authorID: 2, firstName: "Jane", lastName: "Smith" }
]);

db.BOOK.insertMany([
  { ISBN: "978-1", title: "Advanced Databases", publicationYear: 2027 },
  { ISBN: "978-2", title: "Introduction to NoSQL", publicationYear: 2025 }
]);

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

db.MEMBER.insertMany([
  { memberID: 101, firstName: "Alice", lastName: "Johnson", email: "alice@mail.com", phoneNo: "0123456789" },
  { memberID: 102, firstName: "Bob", lastName: "Brown", email: "bob@mail.com", phoneNo: "0987654321" }
]);

db.BORROWEDBOOKS.insertMany([
  { memberID: 101, ISBN: "978-1", dateBorrowed: "2026-01-10", dueDate: "2026-01-24", returnDate: "2026-01-26", fineAmount: 50 },
  { memberID: 102, ISBN: "978-2", dateBorrowed: "2026-02-15", dueDate: "2026-03-01", returnDate: "2026-03-01", fineAmount: 0 }
]);

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

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

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
    }
  }
]);

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