/// QUESTION 2.1: CREATE COLLECTIONS

db.createCollection("Author");
db.createCollection("Book");
db.createCollection("Member");
db.createCollection("BookAuthor");
db.createCollection("BorrowedBooks");

/// QUESTION 2.2: INSERT RECORDS (two per collection)

// Author
db.Author.insertMany([
  { authorID: 1, firstName: "George", lastName: "Orwell" },
  { authorID: 2, firstName: "Jane", lastName: "Austen" }
]);

// Book
db.Book.insertMany([
  { ISBN: "9780451524935", title: "Advanced Database Systems", publicationYear: 2027 },
  { ISBN: "9780141439518", title: "Cloud Computing Fundamentals", publicationYear: 2028 }
]);

// Member
db.Member.insertMany([
  { memberID: 1, firstName: "Alice", lastName: "Brown", email: "alice@example.com", phoneNo: "1234567890" },
  { memberID: 2, firstName: "David", lastName: "Wilson" }
]);

// BookAuthor (bridge)
db.BookAuthor.insertMany([
  { ISBN: "9780451524935", authorID: 1 },
  { ISBN: "9780141439518", authorID: 2 }
]);

// BorrowedBooks (bridge)
db.BorrowedBooks.insertMany([
  { memberID: 1, ISBN: "9780451524935", dateBorrowed: new Date(), dueDate: new Date(Date.now() + 14*24*60*60*1000), fineAmount: 0 },
  { memberID: 2, ISBN: "9780141439518", dateBorrowed: new Date(), dueDate: new Date(Date.now() + 14*24*60*60*1000), fineAmount: 50 }
]);

/// QUESTION 2.3: Books published after 2026

db.Book.find({ publicationYear: { $gt: 2026 } });

/// QUESTION 2.4: Total fines (revenue)

db.BorrowedBooks.aggregate([
  { $group: { _id: null, totalFines: { $sum: "$fineAmount" } } }
]);

/// QUESTION 2.5: Delete record of books borrowed by member who owed R50

db.BorrowedBooks.deleteOne({ fineAmount: 50 });

/// QUESTION 2.6: List of members who borrowed books with details

db.BorrowedBooks.aggregate([
  {
    $lookup: {
      from: "Member",
      localField: "memberID",
      foreignField: "memberID",
      as: "memberInfo"
    }
  },
  { $unwind: "$memberInfo" },
  {
    $lookup: {
      from: "Book",
      localField: "ISBN",
      foreignField: "ISBN",
      as: "bookInfo"
    } 
  },
  { $unwind: "$bookInfo" },
  {
    $project: {
      memberID: 1,
      "memberInfo.firstName": 1,
      "memberInfo.lastName": 1,
      "bookInfo.title": 1,
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]);