// Reset collections to ensure schema consistency
db.book.drop(); db.member.drop(); db.borrowedbooks.drop();

// 1. Insert book data
db.book.insertMany([
  { "bookID": 101, "title": "Harry Potter and the Philosopher's Stone" },
  { "bookID": 102, "title": "A Game of Thrones" }
]);

// 2. Insert member data
db.member.insertMany([
  { "memberID": 501, "firstName": "Nuzayh", "lastName": "Varachia" },
  { "memberID": 502, "firstName": "John", "lastName": "Doe" }
]);

// 3. Insert borrowing logs (with a dueDate included)
db.borrowedbooks.insertMany([
  { "borrowID": 901, "memberID": 501, "bookID": 101, "borrowDate": "2026-06-01", "dueDate": "2026-06-15", "returnDate": "2026-06-14", "fine": 0 },
  { "borrowID": 902, "memberID": 502, "bookID": 102, "borrowDate": "2026-06-10", "dueDate": "2026-06-24", "returnDate": "2026-06-28", "fine": 15.00 }
]);

// 4. Run the required join query
db.borrowedbooks.aggregate([
  { $lookup: { from: "member", localField: "memberID", foreignField: "memberID", as: "mem" } },
  { $unwind: "$mem" },
  { $lookup: { from: "book", localField: "bookID", foreignField: "bookID", as: "bk" } },
  { $unwind: "$bk" },
  {
    $project: {
      _id: 0,
      "Member Identity": "$memberID",
      "First Name": "$mem.firstName",
      "Last Name": "$mem.lastName",
      "Title of Book Borrowed": "$bk.title",
      "Date Borrowed": "$borrowDate",
      "Due Date": "$dueDate",
      "Return Date": "$returnDate",
      "Fine Amount": "$fine"
    }
  }
]);