use EduvosLibraryDB

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

db.AUTHOR.insertMany([
    {authorID: 1,
    firstName: "Jenny",
    lastName: "Louis"},
    {authorID: 2,
    firstName: "Henry",
    lastName: "Stanley"},
])

db.BOOK.insertMany([
    {ISBN: "36904785",
    title: "Years of Trees",
    publicationYear: 2026},
    {ISBN: "64782028",
    title: "Healthy Relationships",
    publicationYear: 2028}
])

db.MEMBER.insertMany([
    {memberID: 60,
    firstName: "Stefan",
    lastName: "Thompson",
    email: "stefanthompson@vossie.net",
    phoneNo: "0835926714"},
    {memberID: 61,
    firstName: "Bruno",
    lastName: "Scott",
    email: "brunoscott@vossie.net",
    phoneNo: "0728743610"}
])

db.BOOKAUTHOR.insertMany([
    {ISBN: "36904785",
    authorID: 1},
    {ISBN: "64782028",
    authorID: 2}
])

db.BORROWEDBOOKS.insertMany([
    {memberID: 60,
    ISBN: "36904785",
    dateBorrowed: new Date("2026-04-02"),
    dueDate: new Date("2026-04-16"),
    returnDate: new Date("2026-04-21"),
    fineAmount: 50},
    {memberID: 61,
    ISBN: "64782028",
    dateBorrowed: new Date("2026-06-06"),
    dueDate: new Date("2026-06-17"),
    returnDate: new Date("2026-06-21"),
    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: "member"
}},
                            {
                                $unwind: "$member"
                            }, { $unwind: "$book"},
                            { $lookup: {
                                from: "BOOK",
                                localField: "ISBN",
                                foreignField: "ISBN",
                                as: "book"
                            }}, { $project:  {
                                _id: 0,
                                title: "$book.title",
                                memberID: "$member.memberID",
                                firstName: "$member.firstName",
                                lastName: "$member.lastName",
                                dateBorrowed: 1,
                                dueDate: 1,
                                returnDate: 1,
                                fineAmount: 1
                            }
                                
                            }])