#Question 1

db.createCollection("AUTHOR")
db.createCollection("BOOKAUTHOR")
db.createCollection("BOOK")
db.createCollection("BORROWEDBOOKS")
db.createCollection("MEMBER")

#Question 2

db.AUTHORinsertMany([
{
    authorID: "A001",
    firstName: "George",
    lastName: "Martin"
},
{
    authorID: "A002",
    firstName: "Paulo",
    LastName: "Coelho"
}
])

db.BOOK.insertMany([
{
    ISBN: "978001",
    title: "The Future of Technology",
    publicationYear: 2027
},
{
    ISBN: "978002",
    title: "Digital Learning",
    publicationYear: 2025
}
])

#Question 3

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

#Question 4

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

#Question 5

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

#Question 6

db.BORROWEDBOOKS.aggregate([
{
    $lookup: {
        from: "MEMBER",
        localField: "memberID",
        foreignField: "memberID",
        as: "member"
}
},
{
    $lookup: {
        from: "BOOK",
        localField: "IBN",
        foreignField: "ISBN",
        as: "book"
    }
},
{
    $project: {
        _id: 0,
        memberID: 1,
        firstName: { $arrayElemAt: ["$member.firstName", 0] },
        lastName: { $arrayElemAt: ["$member.lastName", 0] },
        title: { $arrayElemAt: ["$book.title", 0] },
        dateBorrowed: 1,
        dueDate: 1,
        returnDate: 1,
        fineAmount: 1
    }
}
])