db.createCollection("Author")

db.createCollection("Book")

db.createCollection("BookAuthor")

db.createCollection("Member")

db.createCollection("BorrowedBooks")

db.Author.insertMany([
{
    authorID: 1,
    firstName: "John",
    lastName: "Smith"
},
{
    authorID: 2,
    firstName: "Sarah",
    lastName: "Brown"
}
])

db.Book.insertMany([
{
    ISBN: "97810001",
    title: "Database Systems",
    publicationYear: 2025,
    availability: "Online",
    quantity: 12
},
{
    ISBN: "97810002",
    title: "Advanced MongoDB",
    publicationYear: 2027,
    availability: "In Store",
    quantity: 8
}
])

db.BookAuthor.insertMany([
{
    ISBN: "97810001",
    authorID: 1
},
{
    ISBN: "97810002",
    authorID: 2
}
])

db.Member.insertMany([
{
    memberID: 101,
    firstName: "Alice",
    lastName: "Johnson",
    email: "alice@gmail.com",
    phoneNo: "0821234567"
},
{
    memberID: 102,
    firstName: "David",
    lastName: "Moyo",
    email: "david@gmail.com",
    phoneNo: "0839876543"
}
])

db.BorrowedBooks.insertMany([
{
    memberID: 101,
    ISBN: "97810001",
    dateBorrowed: new Date("2026-05-01"),
    dueDate: new Date("2026-05-15"),
    returnDate: new Date("2026-05-18"),
    fineAmount: 50
},
{
    memberID: 102,
    ISBN: "97810002",
    dateBorrowed: new Date("2026-06-01"),
    dueDate: new Date("2026-06-15"),
    returnDate: new Date("2026-06-14"),
    fineAmount: 0
}
])

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

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

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

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: "$memberDetails.memberID",
        firstName: "$memberDetails.firstName",
        lastName: "$memberDetails.lastName",
        title: "$bookDetails.title",
        dateBorrowed: 1,
        dueDate: 1,
        returnDate: 1,
        fineAmount: 1
    }
}
])