use EduvosLibraryDB

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

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


db.Book.insertMany([
{
    ISBN: "978000001",
    title: "Database Systems",
    publicationYear: 2027
},
{
    ISBN: "978000002",
    title: "Introduction to MongoDB",
    publicationYear: 2025
}
])

db.Member.insertMany([
{
    memberID: 1,
    firstName: "Alice",
    lastName: "Johnson",
    email: "alice@email.com",
    phoneNo: "0812345678"
},
{
    memberID: 2,
    firstName: "Peter",
    lastName: "Williams",
    email: "peter@email.com",
    phoneNo: "0823456789"
}
])

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


db.BorrowedBooks.insertMany([
{
    memberID: 1,
    ISBN: "978000001",
    dateBorrowed: new Date("2027-02-01"),
    dueDate: new Date("2027-02-15"),
    returnDate: new Date("2027-02-16"),
    fineAmount: 50
},
{
    memberID: 2,
    ISBN: "978000002",
    dateBorrowed: new Date("2027-03-01"),
    dueDate: new Date("2027-03-15"),
    returnDate: new Date("2027-03-14"),
    fineAmount: 0
}
])

print("Question 2.3")

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

print("Question 2.4")

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

print("Question 2.5")

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

db.BorrowedBooks.find()