db.Member.insertMany([
    {
        memberId: "MEM001",
        firstName: "Duma",
        lastName: "Baloyi",
        email: "duma@email.com",
        phone: "0821234567"
    },

     {
        memberId: "MEM002",
        firstName: "Lerato",
        lastName: "Tebogo",
        email: "lerato@email.com",
        phone: "0832345678"
    },
    {
        memberId: "MEM003",
        firstName: "Ndlovu",
        lastName: "Lesedi",
        email: "ndlovu@email.com",
        phone: "0843456789"
            }
]);

db.Author.insertMany([
    {
        authorId: "au01",
        firstName: "John",
        lastName: "Doe"
    },
    {
        authorId: "au02",
        firstName: "Mia",
        lastName: "Adrian"
    },
    {
       authorId: "au03",
        firstName: "Robert",
        lastName: "Martin"
    }
]);

db.Book.insertMany([
    {
        isbn: "50001",
        title: "Database Systems",
        publicationYear: 2024,
        authorId: "au01",
        available: true,
        quantityInStock: 5
    },
    {
       isbn: "50002",
        title: "Introduction to Python Programming",
        publicationYear: 2023,
        authorId: "au02",
        available: true,
        quantityInStock: 3
    },
    {
        isbn: "50003",
        title: "Technical Programming",
        publicationYear: 2022,
        authorId: "au03",
        available: true,
        quantityInStock: 4
    },
    {
        isbn: "50004",
        title: "Advanced Database Concepts",
        publicationYear: 2025,
        authorId: "au01",
        available: true,
        quantityInStock: 2
    }
]);

db.BorrowedBook.insertMany([
    {
        borrowId: "BB001",
        memberId: "MEM001",
        isbn: "50002",
        borrowedDate: new Date("2024-09-15"),
        dueDate: new Date("2024-10-25"),
        returnDate: null,
        fineAmount: 0
    },
     {
        borrowId: "BB002",
        memberId: "MEM002",
        isbn: "50003",
        borrowedDate: new Date("2024-04-15"),
        dueDate: new Date("2024-06-25"),
        returnDate: new Date("2024-09-25"),
        fineAmount: 50
    },
     {
        borrowId: "BB003",
        memberId: "MEM003",
        isbn: "50001",
        borrowedDate: new Date("2024-09-15"),
        dueDate: new Date("2024-11-25"),
        returnDate: null,
        fineAmount: 0
    }
]);

db.Fine.insertMany([
    {
        fineId: "F001",
        borrowId: "BB002",
        amount: 50,
        paid: true,
        paidDate: new Date("2024-09-26")
    }
]);

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

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

db.BorrowedBook.deleteOne({ memberId: "MEM002", fineAmount: 50 });

db.BorrowedBook.aggregate([
    {
        $lookup: {
            from: "Member",
            localField: "memberId",
            foreignField: "memberId",
            as: "memberInfo"
        }
    },
    {
        $unwind: "$memberInfo"
    },
     {
        $lookup: {
            from: "Book",
            localField: "isbn",
            foreignField: "isbn",
            as: "bookInfo"
        }
    },
    {
        $unwind: "$bookInfo"
    },
    {
        $project: {
            memberId: 1,
            firstName: "$memberInfo.firstName",
            lastName: "$memberInfo.lastName",
            bookTitle: "$bookInfo.title",
            borrowedDate: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
]);