db.createCollection("Author")
db.createCollection("BookAuthor")
db.createCollection("Book")
db.createCollection("BorrowedBooks")
db.createCollection("Member")

db.Author.insertMany
([
    {
        authorId: 1,
        firstName: "William",
        lastName: "Shakespeare",
    },
    
    {
        authorId: 2,
        firstName: "Margaret",
        lastName: "Atwood",
    }
    ])

db.BookAuthor.insertMany
([
    {
         ISBN: "978-0143128618",
         authorId: 1,
    },
    {
        ISBN: "978-0385490818",
        authorId: 2,
    }
])

db.Book.insertMany
([
    {
        ISBN: "978-0143128618",
        title: "Othello",
        publicationYear: 2016,
    },
    {
        ISBN: "978-0385490818",
        title: "The Handmaid's Tale",
        publicationYear: 1998,
    }
])

db.Member.insertMany
([
    {
        memberId: 1,
        firstName: "Michael",
        lastName: "da Silva",
        email: "dasilvamichael822@gmail.com",
        phoneNo: "0814508453",
        
    },
    {
        memberId: 2,
        firstName: "Kyle",
        lastName: "da Silva",
        email: "kyledasilva232@gmail.com",
        phoneNo: "0834444889",
    }
])

db.BorrowedBooks.insertMany
([
    {
        memberId: 1,
        ISBN: "978-0143128618",
        dateBorrowed: new Date("2026-03-09"),
        dueDate: new Date("2026-04-09"),
        returnDate: Null,
        fineAmount: 0,
    },
    {
        memberId: 1,
        ISBN: "978-0385490818",
        dateBorrowed: new Date("2025-08-25"),
        dueDate: new Date("2025-09-25"),
        returnDate: new Date("2025-10-04"),
        fineAmount: 200,
    }
])

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

db.BorrowedBooks.aggregate
([
    {
        $group: 
        {
            revenue:
            {
                $sum: "$fineAmount"
            }
        }
    }
])

db.BorrowedBooks.aggregate
([
    {
        $lookup:
        {
            from: "Member",
            localField: "memberId",
            foreignField: "memberId",
            as: "member"
        }
    },
    {
        $unwind: "$member"
    },

    $lookup:
    {
        from: "Book",
        localField: "ISBN",
        foreignField: "ISBN",
            as: "book"
    },

    {
        $unwind: "$book"
    },

    {
        $project:
        {
            memberId: "$member.memberId",
            firstName: "$member.firstName",
            lastName: "$member.lastName",
            title: "$book.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
])