use EduvosLibraryDB

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

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

db.Book.insertMany([
{
    ISBN: "978001",
    title: "Database Systems",
    publicationYear: 2024
},
{
    ISBN: "978002",
    title: "MongoDB Essentials",
    publicationYear: 2027
}
])

db.Member.insertMany([
{
    memberID: 1001,
    firstName: "Alice",
    lastName: "Johnson",
    email: "alice@email.com",
    phoneNo: "0821234567"
},
{
    memberID: 1002,
    firstName: "Michael",
    lastName: "Green",
    email: "michael@email.com",
    phoneNo: "0837654321"
}
])

db.BorrowedBooks.insertMany([
{
    memberID: 1001,
    ISBN: "978001",
    dateBorrowed: new Date("2025-08-01"),
    dueDate: new Date("2025-08-15"),
    returnDate: null,
    fineAmount: 50
},
{
    memberID: 1002,
    ISBN: "978002",
    dateBorrowed: new Date("2025-08-05"),
    dueDate: new Date("2025-08-19"),
    returnDate: null,
    fineAmount: 0
}
])

db.Author.find()
db.Book.find()
db.BookAuthor.find()
db.Member.find()
db.BorrowedBooks.find()

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

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

{
    memberID: 1001,
    ISBN: "978001",
    dateBorrowed: new Date("2025-08-01"),
    dueDate: new Date("2025-08-15"),
    returnDate: null,
    fineAmount: 50
}

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

db.BorrowedBooks.aggregate([
{
    $lookup: {
        from: "Member",
        localField: "memberID",
        foreignField: "memberID",
        as: "member"
    }
},
{
    $lookup: {
        from: "Book",
        localField: "ISBN",
        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
    }
}
])