// AUTHORS
db.authors.insertMany([
  { authorID: "au01", firstName: "Chinua", lastName: "Achebe" },
  { authorID: "au02", firstName: "Ngugi", lastName: "Wa Thiong'o" }
]);

// BOOKS
db.books.insertMany([
  {
    ISBN: "9780141186887",
    title: "Things Fall Apart",
    publicationYear: 1958,
    availableInStore: true,
    availableOnline: true,
    quantityInStock: 12
  },
  {
    ISBN: "9780435905484",
    title: "Petals of Blood",
    publicationYear: 2027,
    availableInStore: true,
    availableOnline: true,
    quantityInStock: 8
  },
  {
    ISBN: "9780141181424",
    title: "A Grain of Wheat",
    publicationYear: 2028,
    availableInStore: false,
    availableOnline: true,
    quantityInStock: 5
  }
]);

// BOOKAUTHOR (junction)
db.bookauthor.insertMany([
  { ISBN: "9780141186887", authorID: "au01" },
  { ISBN: "9780435905484", authorID: "au02" },
  { ISBN: "9780141181424", authorID: "au02" }
]);

// MEMBERS
db.members.insertMany([
  {
    memberID: "mem01",
    firstName: "Yunus",
    lastName: "Kopuz",
    email: "yunus@student.eduvos.ac.za",
    phoneNo: "0821234567"
  },
  {
    memberID: "mem02",
    firstName: "Paula",
    lastName: "Nkosi",
    email: "paula@student.eduvos.ac.za"
  }
]);

// BORROWEDBOOKS
db.borrowedbooks.insertMany([
  {
    memberID: "mem01",
    ISBN: "9780141186887",
    dateBorrowed: ISODate("2026-05-01"),
    dueDate: ISODate("2026-05-15"),
    returnDate: ISODate("2026-05-14"),
    fineAmount: 0
  },
  {
    memberID: "mem02",
    ISBN: "9780435905484",
    dateBorrowed: ISODate("2026-04-10"),
    dueDate: ISODate("2026-04-24"),
    returnDate: ISODate("2026-05-01"),
    fineAmount: 50
  },
  {
    memberID: "mem01",
    ISBN: "9780141181424",
    dateBorrowed: ISODate("2026-06-01"),
    dueDate: ISODate("2026-06-15"),
    returnDate: null,
    fineAmount: 0
  }
]);

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

// 2.4 Total revenue from fines
db.borrowedbooks.aggregate([
  { $group: { _id: null, totalFineRevenue: { $sum: "$fineAmount" } } }
]);

// 2.5 Delete borrowed record where member paid R50 fine
db.borrowedbooks.deleteOne({ fineAmount: 50 });

// 2.6 Members who borrowed books — full list with joins
db.borrowedbooks.aggregate([
  {
    $lookup: {
      from: "members",
      localField: "memberID",
      foreignField: "memberID",
      as: "memberInfo"
    }
  },
  { $unwind: "$memberInfo" },
  {
    $lookup: {
      from: "books",
      localField: "ISBN",
      foreignField: "ISBN",
      as: "bookInfo"
    }
  },
  { $unwind: "$bookInfo" },
  {
    $project: {
      _id: 0,
      memberID: "$memberInfo.memberID",
      firstName: "$memberInfo.firstName",
      lastName: "$memberInfo.lastName",
      title: "$bookInfo.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]);