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

db.createCollection("Book");
db.Book.insertMany([
{
    ISBN: "97810001",
    title: "Networking Fundamentals",
    publicationYear: 2027,
    available: true,
    quantity: 15
},
{
    ISBN: "97810002",
    title: "Database Systems",
    publicationYear: 2025,
    available: true,
    quantity: 10
}
]);

db.createCollection("BookAuthor");
db.BookAuthor.insertMany([
{
    authorID: 1,
    ISBN: "97810001"
},
{
    authorID: 2,
    ISBN: "97810002"
}
]);

db.createCollection("Member");
db.Member.insertMany([
{
    memberID: 101,
    firstName: "Michael",
    lastName: "Brown",
    email: "michael@email.com",
    phoneNo: "0812345678"
},
{
    memberID: 102,
    firstName: "Emily",
    lastName: "Davis",
    email: "emily@email.com",
    phoneNo: "0823456789"
}
]);

db.createCollection("BorrowedBooks");
db.BorrowedBooks.insertMany([
{
    memberID: 101,
    ISBN: "97810001",
    dateBorrowed: new Date("2027-01-10"),
    dueDate: new Date("2027-01-24"),
    returnDate: new Date("2027-01-20"),
    fineAmount: 0
},
{
    memberID: 102,
    ISBN: "97810002",
    dateBorrowed: new Date("2027-01-05"),
    dueDate: new Date("2027-01-19"),
    returnDate: new Date("2027-01-28"),
    fineAmount: 50
}
]);
show collections
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"
    }
},

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

{
    $unwind: "$member"
},

{
    $unwind: "$book"
},

{
    $project: {
        _id: 0,
        memberID: "$member.memberID",
        firstName: "$member.firstName",
        lastName: "$member.lastName",
        title: "$book.title",
        dateBorrowed: 1,
        dueDate: 1,
        returnDate: 1,
        fineAmount: 1
    }
}

]);