// Clean up existing collections
db.AUTHOR.drop();
db.BOOK.drop();
db.BOOKAUTHOR.drop();
db.MEMBER.drop();
db.BORROWEDBOOKS.drop();

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

// Insert into AUTHOR
db.AUTHOR.insertMany([
  { authorID: 1, firstName: "George", lastName: "Orwell" },
  { authorID: 2, firstName: "Jane", lastName: "Austen" },
  { authorID: 3, firstName: "Arthur", lastName: "Clarke" }
]);

// Insert into BOOK
db.BOOK.insertMany([
  { ISBN: "978-0451524935", title: "1984", publicationYear: 1949, availableType: "in store", quantityInStock: 5 },
  { ISBN: "978-0141439518", title: "Pride and Prejudice", publicationYear: 1813, availableType: "online", quantityInStock: 10 },
  { ISBN: "978-0593150000", title: "The Future of Space Exploration", publicationYear: 2027, availableType: "online", quantityInStock: 3 }
]);

// Insert into BOOKAUTHOR
db.BOOKAUTHOR.insertMany([
  { ISBN: "978-0451524935", authorID: 1 },
  { ISBN: "978-0141439518", authorID: 2 },
  { ISBN: "978-0593150000", authorID: 3 }
]);

// Insert into MEMBER
db.MEMBER.insertMany([
  { memberID: 101, firstName: "John", lastName: "Doe", email: "john.doe@email.com", phoneNo: "+27821234567" },
  { memberID: 102, firstName: "Sarah", lastName: "Connor", email: "sarah.c@email.com", phoneNo: "+27837654321" },
  { memberID: 103, firstName: "James", lastName: "Smith", email: "james.s@email.com", phoneNo: "+27845551234" }
]);

// Insert into BORROWEDBOOKS
db.BORROWEDBOOKS.insertMany([
  { 
    ISBN: "978-0451524935", 
    memberID: 101, 
    dateBorrowed: new Date("2026-06-01"), 
    dueDate: new Date("2026-06-15"), 
    returnDate: null, 
    fineAmount: 0.00 
  },
  { 
    ISBN: "978-0141439518", 
    memberID: 102, 
    dateBorrowed: new Date("2026-05-10"), 
    dueDate: new Date("2026-05-24"), 
    returnDate: new Date("2026-05-25"), 
    fineAmount: 15.50 
  },
  { 
    ISBN: "978-0593150000", 
    memberID: 103, 
    dateBorrowed: new Date("2026-04-01"), 
    dueDate: new Date("2026-04-15"), 
    returnDate: new Date("2026-04-20"), 
    fineAmount: 50.00 
  }
]);

// Run the deletion from 2.5 to keep the data clean
db.BORROWEDBOOKS.deleteOne({ fineAmount: 50 });

// --- QUERY 2.6 EXECUTION ---
print("--- DETAILED BORROWING LIST ---");
db.BORROWEDBOOKS.aggregate([
  {
    $lookup: {
      from: "MEMBER",
      localField: "memberID",
      foreignField: "memberID",
      as: "memberDetails"
    }
  },
  {
    $lookup: {
      from: "BOOK",
      localField: "ISBN",
      foreignField: "ISBN",
      as: "bookDetails"
    }
  },
  { $unwind: "$memberDetails" },
  { $unwind: "$bookDetails" },
  {
    $project: {
      _id: 0,
      memberID: 1,
      firstName: "$memberDetails.firstName",
      lastName: "$memberDetails.lastName",
      bookTitle: "$bookDetails.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]).forEach(printjson);