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

// 1. Insert records into MEMBER collection
db.MEMBER.insertMany([
  {
    _id: "M1001", 
    firstName: "Soobin",
    lastName: "Choi",
    email: "soobin.choi@eduvos.edu",
    phoneNo: "0821112222"
  },
  {
    _id: "M1002",
    firstName: "Terry",
    lastName: "Kang",
    email: "terry.kang@eduvos.edu" 
  }
]);

// 2. Insert records into AUTHOR collection
db.AUTHOR.insertMany([
  {
    _id: "A201", 
    firstName: "Marceline",
    lastName: "Black"
  },
  {
    _id: "A202",
    firstName: "Sarah",
    lastName: "Creme"
  }
]);

// 3. Insert records into BOOK collection
db.BOOK.insertMany([
  {
    _id: "978-0134685991", 
    title: "Introduction to Databases",
    publicationYear: 2027
  },
  {
    _id: "978-0132145678",
    title: "Advanced Cloud Architecture",
    publicationYear: 2028
  }
]);

// 4. Insert records into BOOKAUTHOR intersection collection
db.BOOKAUTHOR.insertMany([
  { _id: "BA001", ISBN: "978-0134685991", authorID: "A201" },
  { _id: "BA002", ISBN: "978-0132145678", authorID: "A202" }
]);

// 5. Insert records into BORROWEDBOOKS collection
db.BORROWEDBOOKS.insertMany([
  {
    _id: "BRec001",
    ISBN: "978-0134685991",
    memberID: "M1001",
    dateBorrowed: ISODate("2026-05-10T09:00:00Z"),
    dueDate: ISODate("2026-05-24T09:00:00Z"),
    returnDate: ISODate("2026-05-28T14:30:00Z"),
    fineAmount: 50.00 
  },
  {
    _id: "BRec002",
    ISBN: "978-0132145678",
    memberID: "M1002",
    dateBorrowed: ISODate("2026-06-01T10:00:00Z"),
    dueDate: ISODate("2026-06-15T10:00:00Z"),
    returnDate: ISODate("2026-06-14T11:00:00Z"),
    fineAmount: 0.00
  }
]);

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

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

db.BORROWEDBOOKS.deleteOne({ 
  fineAmount: 50.00 
});

db.BORROWEDBOOKS.aggregate([
  // 1. Join with MEMBER collection to get user details
  {
    $lookup: {
      from: "MEMBER",
      localField: "memberID",
      foreignField: "_id",
      as: "memberDoc"
    }
  },
  // 2. Join with BOOK collection to get book details
  {
    $lookup: {
      from: "BOOK",
      localField: "ISBN",
      foreignField: "_id",
      as: "bookDoc"
    }
  },
  // 3. Deconstruct arrays into flat documents
  { $unwind: "$memberDoc" },
  { $unwind: "$bookDoc" },
  // 4. Project fields matching the exact query request names
  {
    $project: {
      _id: 0,
      memberIdentity: "$memberID",
      firstName: "$memberDoc.firstName",
      lastName: "$memberDoc.lastName",
      titleOfBooksBorrowed: "$bookDoc.title",
      dateBorrowed: 1,
      dueDate: 1,
      returnDate: 1,
      fineAmount: 1
    }
  }
]);