// 2.1) CREATE FIVE COLLECTIONS
db.createCollection("Members");
db.createCollection("Authors");
db.createCollection("Books");
db.createCollection("BorrowedBooks");
db.createCollection("BookAuthors");

// 2.2) INSERT SAMPLE DATA
db.Members.insertMany([
{
    memberID: "M001",
    firstName: "Thabo",
    lastName: "Nkosi",
    email: "thabo.nkosi@eduvos.com",
    phoneNo: "0712345678"
},
{
    memberID: "M002",
    firstName: "Lerato",
    lastName: "Mokoena",
    email: "lerato.mokoena@eduvos.com",
    phoneNo: "0723456789"
},
{
    memberID: "M003",
    firstName: "Sipho",
    lastName: "Zulu"
}
]);

db.Authors.insertMany([
{
    authorID: "A001",
    firstName: "Chimamanda",
    lastName: "Adichie"
},
{
    authorID: "A002",
    firstName: "J.K.",
    lastName: "Rowling"
},
{
    authorID: "A003",
    firstName: "Yuval",
    lastName: "Harari"
}
]);

db.Books.insertMany([
{
    ISBN: "9780141030144",
    title: "Half of a Yellow Sun",
    publicationYear: 2006,
    available: "store",
    quantityInStock: 5
},
{
    ISBN: "9780747558198",
    title: "Harry Potter and the Philosopher's Stone",
    publicationYear: 1997,
    available: "online",
    quantityInStock: 10
},
{
    ISBN: "9781784706775",
    title: "The Future of AI",
    publicationYear: 2027,
    available: "store",
    quantityInStock: 4
},
{
    ISBN: "9780995203045",
    title: "Modern Robotics",
    publicationYear: 2028,
    available: "online",
    quantityInStock: 8
}
]);

db.BookAuthors.insertMany([
{
    bookAuthorID: "BA001",
    ISBN: "9780141030144",
    authorID: "A001"
},
{
    bookAuthorID: "BA002",
    ISBN: "9780747558198",
    authorID: "A002"
},
{
    bookAuthorID: "BA003",
    ISBN: "9781784706775",
    authorID: "A003"
},
{
    bookAuthorID: "BA004",
    ISBN: "9780995203045",
    authorID: "A003"
}
]);

db.BorrowedBooks.insertMany([
{
    borrowID: "B001",
    memberID: "M001",
    ISBN: "9780141030144",
    dateBorrowed: new Date("2026-05-01"),
    dueDate: new Date("2026-05-15"),
    returnDate: new Date("2026-05-20"),
    fineAmount: 50
},
{
    borrowID: "B002",
    memberID: "M002",
    ISBN: "9780747558198",
    dateBorrowed: new Date("2026-06-01"),
    dueDate: new Date("2026-06-15"),
    returnDate: new Date("2026-06-10"),
    fineAmount: 0
},
{
    borrowID: "B003",
    memberID: "M001",
    ISBN: "9781784706775",
    dateBorrowed: new Date("2026-06-10"),
    dueDate: new Date("2026-06-24"),
    returnDate: null,
    fineAmount: null
}
]);

// 2.3) DISPLAY BOOKS PUBLISHED AFTER 2026
db.Books.find({
    publicationYear: { $gt: 2026 }
});

// 2.4) TOTAL REVENUE GENERATED FROM FINES
db.BorrowedBooks.aggregate([
{
    $group:
    {
        _id: null,
        totalRevenue: {
            $sum: {
                $ifNull: ["$fineAmount", 0]
            }
        }
    }
}
]);

// 2.5) DELETE BORROW RECORD OF MEMBER WHO PAID R50 FINE
db.BorrowedBooks.deleteOne({
    fineAmount: 50
});

// 2.6) DISPLAY MEMBERS WHO BORROWED BOOKS
db.BorrowedBooks.aggregate([

{
    $lookup: {
        from: "Members",
        localField: "memberID",
        foreignField: "memberID",
        as: "member"
    }
},

{
    $unwind: "$member"
},

{
    $lookup: {
        from: "Books",
        localField: "ISBN",
        foreignField: "ISBN",
        as: "book"
    }
},

{
    $unwind: "$book"
},

{
    $project: {
        _id: 0,
        memberID: "$member.memberID",
        firstName: "$member.firstName",
        lastName: "$member.lastName",
        bookTitle: "$book.title",
        dateBorrowed: 1,
        dueDate: 1,
        returnDate: 1,
        fineAmount: 1
    }
}
]);