use('EduvosLibrary');

// Insert member data
db.member.insertOne({
    memberID: "MEM001",
    firstName: "Lerato",
    lastName: "Khumalo",
    email: "lerato@gmail.com",
    phoneNo: "0821234567"
});

db.member.insertOne({
    memberID: "MEM002",
    firstName: "Tumelo",
    lastName: "Nkosi",
    email: "tumelo@gmail.com",
    phoneNo: "0837654321"
});

// Insert book data
db.book.insertOne({
    ISBN: "978-001",
    title: "Database Systems",
    publicationYear: 2022
});

db.book.insertOne({
    ISBN: "978-002",
    title: "Introduction to Programming",
    publicationYear: 2027
});

// Insert borrowedbooks data
db.borrowedbooks.insertOne({
    memberID: "MEM001",
    ISBN: "978-001",
    dateBorrowed: "2024-01-15",
    dueDate: "2024-02-15",
    returnDate: "2024-02-10",
    fineAmount: 0
});

db.borrowedbooks.insertOne({
    memberID: "MEM002",
    ISBN: "978-002",
    dateBorrowed: "2024-03-01",
    dueDate: "2024-03-15",
    returnDate: null,
    fineAmount: 50
});

// Query - join borrowedbooks with member and book
print('Members who borrowed books:');
db.borrowedbooks.aggregate([
    {
        $lookup: {
            from: "member",
            localField: "memberID",
            foreignField: "memberID",
            as: "memberDetails"
        }
    },
    {
        $unwind: "$memberDetails"
    },
    {
        $lookup: {
            from: "book",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "bookDetails"
        }
    },
    {
        $unwind: "$bookDetails"
    },
    {
        $project: {
            _id: 0,
            memberID: "$memberDetails.memberID",
            firstName: "$memberDetails.firstName",
            lastName: "$memberDetails.lastName",
            bookTitle: "$bookDetails.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
]).forEach(record => print(JSON.stringify(record)));