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

db.AUTHOR.insertMany([
  {
    authorID: 1,
    firstName: "Roald",
    lastName: "Dahl"
  },
  {
    authorID: 2,
    firstName: "Qiniso",
    lastName: "Madonsela"
  }
]);

db.BOOK.insertMany([
  {
    ISBN: "978-0-620-00002-8",
    title: "Introduction to digital libraries",
    publicationYear: 2025
  },
  {
    ISBN: "978-0-0620-00002-8",
    title: "Advanced Database Systems",
    publicationYear: 2027
  }
]);

db.MEMBER.insertMany([
  {
    memberID: 1,
    firstName: "John",
    lastName: "Stones",
    email: "stonesjohn@vossie.net",
    phoneNo: "072345678"
  },
  {
    memberID: 2,
    firstName: "Thandi",
    lastName: "Dlamini",
    email: "dlaminithandi@vossie.net",
    phoneNo: "0845678912"
  }
]);

db.BOOKAUTHOR.insertMany([
  {
    authorID: 1,
    ISBN: "987-0-620-00001-1"
  },
  {
    authorID: 2,
    ISBN: "978-0-620-00002-8"
  }
]);

db.BORROWEDBOOKS.insertMany([
  {
    borrowedID: 1,
    memberID: 1,
    ISBN: "978-0-620-00001-1",
    dateBorrowed: ISODate("2026-05-01T00:00:00Z"),
    dueDate: ISODate("2026-05-15T00:00:00Z"),
    returnedDate: ISODate("2026-05-14T00:00:00Z"),
    fineAmount: 0
  },
  {
    borrowedID: 2,
    memberID: 2,
    ISBN: "978-0-620-00002-8",
    dateBorrowed: ISODate("2026-05-10T00:00:00Z"),
    dueDate: ISODate("2026-05-24T00:00:00Z"),
    returnedDate: ISODate("2026-05-30T00:00:00Z"),
    fineAmount: 50
  }
]);

// 2.3 – books published after 2026
db.BOOK.find({
  publicationYear: { $gt: 2026 }
});

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

db.BORROWEDBOOKS.deleteMany({
  memberID: 2,
  fineAmount: 50
});

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