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

db.author.insertMany([
    { authorID: "au01", firstName: "Anele", lastName: "Mabena" },
    { authorID: "au02", firstName: "James", lastName: "Deane" },
    { authorID: "au03", firstName: "Ntombi", lastName: "Mabaso" }
]);

db.book.insertMany([
    { ISBN: 50001, title: "Database Systems", publicationYear: 2014 },
    { ISBN: 50002, title: "Introduction to C++ Programming", publicationYear: 2018 },
    { ISBN: 50003, title: "Network Security", publicationYear: 2022 }
]);

db.member.insertMany([
    { memberID: "MEM001", firstName: "Pablo", lastName: "Gavi", email: "gavi06@gmail.com", phoneNo: "0623456789" },
    { memberID: "MEM002", firstName: "Lerato", lastName: "Mokwena", email: "lerato04@gmail.com", phoneNo: "0787654321" },
    { memberID: "MEM003", firstName: "Thabisile", lastName: "Ndlovu", email: "ndlovuthabisile@gmail.com", phoneNo: "0652820028" }
]);

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

db.borrowedBooks.insertMany([
    { memberID: "MEM001", ISBN: 50002, dateBorrowed: "2025-09-30", dueDate: "2025-10-25", returnDate: null, fineAmount: 0.0 },
    { memberID: "MEM002", ISBN: 50003, dateBorrowed: "2026-04-28", dueDate: "2026-05-07", returnDate: "2026-06-17", fineAmount: 50.0 },
    { memberID: "MEM003", ISBN: 50001, dateBorrowed: "2026-01-15", dueDate: "2026-01-22", returnDate: null, fineAmount: 0.0 }
]);

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

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

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

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