db.createCollection('book');
db.createCollection('borrowedBooks');
db.createCollection('author');
db.createCollection('bookAuthor');
db.createCollection('member');

db.author.insertMany(
    [ { authorID: "Auth01", firstName: "Gege", lastName: "Akutami" },
    { authorID: "Auth02", firstName: "Tatsuki", lastName: "Fujimoto" }
    ]
);

db.book.insertMany (
    [ {ISBN: "978-0-439-02348-1", title: "Jujutsu Kaisen", publicationYear: 2018 },
       {ISBN: "0-439-02348-3", title: "Chainsaw Man", publicationYear: 2026 } 
    ]
);

db.bookAuthor.insertMany (
[
    {ISBN: "978-0-439-02348-1", authorID: "Auth01"},
    {ISBN: "0-439-02348-3", authorID: "Auth02"}
]
);

db.member.insertMany (
    [
        {memberID: "OLM01", firstName: "Lucy", lastName: "Heartfilia", email: "lucyheartfilia@hotmail.com", phoneNo: "0698541256"},
        {memberID: "OLM02", firstName: "Natsu", lastName: "Dragneel", email: "natsudragneel@ymail.com", phoneNo: "0845793217"}
    ]
);

db.borrowedBooks.insertMany (
[
    {memberID: "OLM01", ISBN: "978-0-439-02348-1", dateborrowed:new Date("2026-06-07"), dueDate: new Date ("2026-06-25"), returnDate: new Date ("2026-06-23"), fineAmount: 0}, 
    {memberID: "OLM02", ISBN: "0-439-02348-3", dateborrowed:new Date("2026-06-15"), dueDate: new Date ("2026-07-03"), returnDate: new Date ("2026-07-05"), fineAmount: 50 }
]
);

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

db.borrowedBooks.aggregate(
    [
        {
            $match: {fineAmount: {$gt: 0}}
        },
        {
            $group: {
                _id: null,
                totalFines: {$sum: "$fineAmount"}
            }
        }
    ]
);

db.borrowedBooks.deleteMany(
    {fineAmount: 50 }
);

db.borrowedBooks.aggregate(
    [
        {
            $lookup: {
                from: "member",
                localField: "memberID",
                foreignField: "memberID",
                as: "memberInformation"
            }
        },
        {$unwind: "$memberInformation"},
        {$lookup: {
            from: "book",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "bookInformation"
        }
            
        },
        {$unwind: "$bookInformation"},
        {
            $project: {
                _id: 0,
                memberID:"memberInformation.memberID",
                firstName:"memberInformation.firstName",
                lastName:"memberInformation.lastName",
                title:"$bookInformation.title",
                dateBorrowed: 1,
                dueDate: 1,
                returnDate: 1,
                fineAmount: 1
            }
        }
    ]
);