db.createCollection("AUTHOR");
db.createCollection("BOOK");
db.createCollection("MEMBER");
db.createCollection("BOOKAUTHOR");
db.createCollection("BORROWEDBOOKS");
db.AUTHOR.insertMany([
    { authorID: "A01", firstName: "Adewale", lastName: "Obaro" },
    { authorID: "A02", firstName: "Mia", lastName: "Adrian" }
]);

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

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.BOOKAUTHOR.insertMany([
    { ISBN: "50001", authorID: "A01" },
    { ISBN: "50002", authorID: "A02" }
]);

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

db.BOOK.find({ publicationYear: { $gt: 2026 } });
db.BORROWEDBOOKS.aggregate([
    {
        $group: {
            _id: null,
            totalRevenue: { $sum: "$fineAmount" }
        }
    }
]);
db.BORROWEDBOOKS.deleteMany({ fineAmount: 50 });
db.MEMBER.aggregate([
    {
        $lookup: {
            from: "BORROWEDBOOKS",
            localField: "memberID",
            foreignField: "memberID",
            as: "borrowed_info"
        }
    },
    { $unwind: "$borrowed_info" },
    {
        $lookup: {
            from: "BOOK",
            localField: "borrowed_info.ISBN",
            foreignField: "ISBN",
            as: "book_info"
        }
    },
    { $unwind: "$book_info" },
    {
        $project: {
            _id: 0,
            memberID: 1,
            firstName: 1,
            lastName: 1,
            title: "$book_info.title",
            dateBorrowed: "$borrowed_info.dateBorrowed",
            dueDate: "$borrowed_info.dueDate",
            returnDate: "$borrowed_info.returnDate",
            fineAmount: "$borrowed_info.fineAmount"
        }
    }
]);