// 2.1

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

// 2.2

db.AUTHOR.insertMany([
  { authorID: "AUTH001", firstName: "J.K.", lastName: "Rowling" },
  { authorID: "AUTH002", firstName: "George", lastName: "Orwell" }
]);

db.BOOK.insertMany([
  { ISBN: "ISBN001", title: "Harry Potter and the Philosopher's Stone", publicationYear: 1997 },
  { ISBN: "ISBN002", title: "1984", publicationYear: 1949 },
  { ISBN: "ISBN003", title: "The Future of AI", publicationYear: 2027 },
  { ISBN: "ISBN004", title: "Quantum Computing for Beginners", publicationYear: 2028 }
]);

db.BOOKAUTHOR.insertMany([
  { ISBN: "ISBN001", authorID: "AUTH001" },
  { ISBN: "ISBN002", authorID: "AUTH002" },
  { ISBN: "ISBN003", authorID: "AUTH001" },
  { ISBN: "ISBN004", authorID: "AUTH002" }
]);

db.MEMBER.insertMany([
  { memberID: "MEM001", firstName: "John", lastName: "Doe", email: "john.doe@example.com", phoneNo: "0123456789" },
  { memberID: "MEM002", firstName: "Jane", lastName: "Smith", email: "jane.smith@example.com" }
]);

db.BORROWEDBOOKS.insertMany([
  { ISBN: "ISBN001", memberID: "MEM001", dateBorrowed: ISODate("2026-01-15"), dueDate: ISODate("2026-02-15"), returnDate: ISODate("2026-02-20"), fineAmount: 50 },
  { ISBN: "ISBN002", memberID: "MEM002", dateBorrowed: ISODate("2026-02-01"), dueDate: ISODate("2026-03-01"), returnDate: ISODate("2026-02-28"), fineAmount: 0 },
  { ISBN: "ISBN003", memberID: "MEM001", dateBorrowed: ISODate("2026-03-10"), dueDate: ISODate("2026-04-10"), returnDate: null, fineAmount: null }
]);

// 2.3

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

// 2.4

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

// 2.5

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

// 2.6

db.BORROWEDBOOKS.aggregate([
  {
    $lookup: {
      from: "MEMBER",
      localField: "memberID",
      foreignField: "memberID",
      as: "member"
    }
  },
  {
    $lookup: {
      from: "BOOK",
      localField: "ISBN",
      foreignField: "ISBN",
      as: "book"
    }
  },
  { $unwind: "$member" },
  { $unwind: "$book" },
  {
    $project: {
      _id: 0,
      memberID: "$member.memberID",
      firstName: "$member.firstName",
      lastName: "$member.lastName",
      title: "$book.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]);