use EduvosLibrary

db.createCollection("members")
db.createCollection("books")
db.createCollection("authors")
db.createCollection("borrowedBooks")
db.createCollection("fines")

db.members.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"
    },
    {
        memberID: "MEM003",
        firstName: "Ndlovu",
        lastName: "Lesedi",
        email: "lesedi@gmail.com",
        phoneNo: "0652820028"
    }
]);

db.authors.insertMany([
    {
        authorID: "auo1",
        firstName: "Adewale",
        lastName: "Obaro"
    },
    {
        authorID: "auo2",
        firstName: "Mia",
        lastName: "Adrian"
    },
    {
        authorID: "auo3",
        firstName: "Ntombi",
        lastName: "Lebo"
    }
]);

db.books.insertMany([
    {
        ISBN: 50001,
        title: "Database Systems",
        publicationYear: 2022,
        authorID: "auo1",
        quantityInStock: 10,
        availability: "Online"
    },
    {
        ISBN: 50002,
        title: "Introduction to Python Programming",
        publicationYear: 2026,
        authorID: "auo2",
        quantityInStock: 15,
        availability: "Store"
    },
    {
        ISBN: 50003,
        title: "Technical Programming",
        publicationYear: 2025,
        authorID: "auo3",
        quantityInStock: 8,
        availability: "Online"
    }
]);

db.borrowedBooks.insertMany([
    {
        borrowedID: "BB001",
        memberID: "MEM001",
        ISBN: 50002,
        dateBorrowed: new Date("2024-09-15"),
        dueDate: new Date("2024-10-25"),
        returnDate: null,
        status: "Borrowed"
    },
    {
        borrowedID: "BB002",
        memberID: "MEM002",
        ISBN: 50003,
        dateBorrowed: new Date("2024-04-15"),
        dueDate: new Date("2024-06-25"),
        returnDate: new Date("2024-09-25"),
        status: "Returned"
    },
    {
        borrowedID: "BB003",
        memberID: "MEM003",
        ISBN: 50001,
        dateBorrowed: new Date("2024-09-15"),
        dueDate: new Date("2024-11-25"),
        returnDate: null,
        status: "Borrowed"
    }
]);

db.fines.insertMany([
    {
        fineID: "FIN001",
        memberID: "MEM002",
        borrowedID: "BB002",
        fineAmount: 50.0,
        reason: "Late return",
        dateIssued: new Date("2024-09-25"),
        status: "Paid"
    },
    {
        fineID: "FIN002",
        memberID: "MEM001",
        borrowedID: "BB001",
        fineAmount: 0.0,
        reason: "Returned on time",
        dateIssued: new Date("2024-10-25"),
        status: "Paid"
    },
    {
        fineID: "FIN003",
        memberID: "MEM003",
        borrowedID: "BB003",
        fineAmount: 0.0,
        reason: "Not yet due",
        dateIssued: null,
        status: "Pending"
    }
]);


db.books.find(
    { publicationYear: { $gt: 2026 } },
    { ISBN: 1, title: 1, publicationYear: 1, authorID: 1, _id: 0 }
).pretty();



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


db.books.find(
    { publicationYear: { $lt: 2026 } }
).pretty();


db.books.find(
    { publicationYear: 2026 }
).pretty();


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


db.fines.aggregate([
    { $group: {
        _id: "$status",
        totalAmount: { $sum: "$fineAmount" },
        count: { $sum: 1 }
    }}
]);


db.fines.aggregate([
    { $match: { status: "Paid" } },
    { $group: {
        _id: null,
        totalRevenue: { $sum: "$fineAmount" }
    }}
]);


db.fines.updateOne(
    { memberID: "MEM002", fineAmount: 50.0 },
    { $set: { status: "Paid", datePaid: new Date() } }
);


db.borrowedBooks.deleteOne(
    { memberID: "MEM002", ISBN: 50003 }
);


db.borrowedBooks.updateOne(
    { memberID: "MEM002", ISBN: 50003 },
    { 
        $set: { 
            returnDate: new Date(),
            status: "Returned"
        } 
    }
);

// Query to display members who borrowed books with full details
db.borrowedBooks.aggregate([
    {
        $lookup: {
            from: "members",
            localField: "memberID",
            foreignField: "memberID",
            as: "memberDetails"
        }
    },
    {
        $unwind: "$memberDetails"
    },
    {
        $lookup: {
            from: "books",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "bookDetails"
        }
    },
    {
        $unwind: "$bookDetails"
    },
    {
        $lookup: {
            from: "fines",
            localField: "borrowedID",
            foreignField: "borrowedID",
            as: "fineDetails"
        }
    },
    {
        $project: {
            memberID: "$memberDetails.memberID",
            firstName: "$memberDetails.firstName",
            lastName: "$memberDetails.lastName",
            bookTitle: "$bookDetails.title",
            dateBorrowed: "$dateBorrowed",
            dueDate: "$dueDate",
            returnDate: "$returnDate",
            fineAmount: { 
                $ifNull: [
                    { $arrayElemAt: ["$fineDetails.fineAmount", 0] },
                    0.0
                ]
            },
            status: 1
        }
    }
]).pretty();

db.borrowedBooks.aggregate([
    {
        $lookup: {
            from: "members",
            localField: "memberID",
            foreignField: "memberID",
            as: "memberDetails"
        }
    },
    {
        $unwind: "$memberDetails"
    },
    {
        $lookup: {
            from: "books",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "bookDetails"
        }
    },
    {
        $unwind: "$bookDetails"
    },
    {
        $project: {
            "Member Identity": "$memberDetails.memberID",
            "First Name": "$memberDetails.firstName",
            "Last Name": "$memberDetails.lastName",
            "Book Title": "$bookDetails.title",
            "Date Borrowed": "$dateBorrowed",
            "Due Date": "$dueDate",
            "Return Date": "$returnDate"
        }
    }
]).pretty();