db.createCollection("author")
db.createCollection("book")
db.createCollection("bookauthor")
db.createCollection("member")
db.createCollection("borrowedbooks")

db.author.insertMany([
    { authorID: 1, firstName: "Chimamanda", lastName: "Adichie" },
    { authorID: 2, firstName: "George", lastName: "Orwell" }
])

db.book.insertMany([
    { ISBN: "9780143127550", title: "Americanah", publicationYear: 2013, availability: "in store", quantityInStock: 5 },
    { ISBN: "9780451524935", title: "1984", publicationYear: 2027, availability: "online", quantityInStock: 3 }
])

db.bookauthor.insertMany([
    { ISBN: "9780143127550", authorID: 1 },
    { ISBN: "9780451524935", authorID: 2 }
])

db.member.insertMany([
    { memberID: 1, firstName: "Thabo", lastName: "Mokoena", email: "thabo@gmail.com", phoneNo: "0821234567" },
    { memberID: 2, firstName: "Lerato", lastName: "Dlamini", email: "lerato@gmail.com", phoneNo: "0837654321" }
])

db.borrowedbooks.insertMany([
    { memberID: 1, ISBN: "9780143127550", dateBorrowed: new Date("2026-06-01"), dueDate: new Date("2026-06-15"), returnDate: new Date("2026-06-14"), fineAmount: 0 },
    { memberID: 2, ISBN: "9780451524935", dateBorrowed: new Date("2026-06-10"), dueDate: new Date("2026-06-24"), returnDate: null, fineAmount: 50 }
])

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

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

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: {
            _id: 0,
            memberID: "$memberInfo.memberID",
            firstName: "$memberInfo.firstName",
            lastName: "$memberInfo.lastName",
            title: "$bookInfo.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
])

db.borrowedbooks.deleteMany({ fineAmount: 50 })