// 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" }
]);

// 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 
  }
]);

// --- QUERY 2.4 EXECUTION ---
print("--- TOTAL REVENUE GENERATED FROM FINES ---");
db.BORROWEDBOOKS.aggregate([
  {
    $group: {
      _id: null,
      totalFines: { $sum: "$fineAmount" }
    }
  }
]).forEach(printjson);