// 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 (Including a third member who will owe the 50 fine)
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 (Including a record with fineAmount: 50.00 to test deletion)
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 
  }
]);

// --- QUERY 2.5 EXECUTION ---

print("--- BORROWED BOOKS BEFORE DELETION ---");
db.BORROWEDBOOKS.find().forEach(printjson);

// Execute the delete operation
db.BORROWEDBOOKS.deleteOne({ fineAmount: 50 });

print("--- BORROWED BOOKS AFTER DELETION ---");
db.BORROWEDBOOKS.find().forEach(printjson);