db.createCollection("MEMBER");
db.createCollection("AUTHOR");
db.createCollection("BOOK");
db.createCollection("BOOKAUTHOR");
db.createCollection("BORROWEDBOOKS");

db.AUTHOR.insertMany([
    { authorID: "au01", firstName: "Adewale", lastName: "Obaro" },
    { authorID: "au02", firstName: "Mia", lastName: "Adrian" }
]);

db.BOOK.insertMany([
    { ISBN: 50001, title: "Database Systems", publicationYear: 2022 },
    { ISBN: 50002, title: "Introduction to Python Programming", publicationYear: 2027 }
]);

db.BOOKAUTHOR.insertMany([
    { authorID: "au01", ISBN: 50001 },
    { authorID: "au02", ISBN: 50002 }
]);

db.MEMBER.insertMany([
    { memberID: "MEM001", firstName: "Duma", lastName: "Baloyi", email: "duma@gmail.com", phoneNo: "0623456789" },
    { memberID: "MEM002", firstName: "Lerato", lastName: "Tebogo", email: "lerato@yahoo.com", phoneNo: "0787654321" }
]);

db.BORROWEDBOOKS.insertMany([
    { memberID: "MEM001", ISBN: 50001, dateBorrowed: "2024-09-15", dueDate: "2024-10-25", returnDate: null, fineAmount: 10.0 },
    { memberID: "MEM002", ISBN: 50002, dateBorrowed: "2024-04-15", dueDate: "2024-06-25", returnDate: "2024-09-25", fineAmount: 50.0 }
]);

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

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

db.BORROWEDBOOKS.deleteMany({ fineAmount: 50.0 });

db.BORROWEDBOOKS.aggregate([
    {
        $lookup: {
            from: "MEMBER",
            localField: "memberID",
            foreignField: "memberID",
            as: "member_details"
        }
    },
    {
        $lookup: {
            from: "BOOK",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "book_details"
        }
    },
    { $unwind: "$member_details" },
    { $unwind: "$book_details" },
    {
        $project: {
            _id: 0,
            memberID: "$member_details.memberID",
            firstName: "$member_details.firstName",
            lastName: "$member_details.lastName",
            title: "$book_details.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
]);