//Question 2.1
db.createCollection("AUTHOR");
db.createCollection("BOOK");
db.cteCollection("BORROWEDBOOKS");
reateCollection("BOOKAUTHOR");
db.createCollection("MEMBER");
db.crea
show collections;

//Question 2.2
db.AUTHOR.insertMany([
    { authorID: "23894", firstName:"Cressida", lastName: "Cowell"},
    { authorID: "1077326", firstName:"Joanne", lastName: "Rowling"}
]);

db.AUTHOR.find().pretty();

db.BOOK.insertMany([
    { ISBN: "978-0316737371", title: "How to Train Your Dragon", publicationYear: 2027},
    { ISBN: "978-1408855652", title: "Harry Potter and the Philosopher's Stone", publicationYear: 2026}
]);

db.BOOK.find().pretty();

db.BOOKAUTHOR.insertMany ([
    {authorID: "23894", ISBN: "978-0316737371"},
    {authorID: "1077326", ISBN: "978-1408855652"}
]);

db.BOOKAUTHOR.find().pretty();

db.MEMBER.insertMany([
    { memberID: "MEM0001", firstName: "Yashveer", lastName: "Thakur", email: "yashveerthakur@gmail.com", phoneNo: "0662244215"},
    { memberID: "MEM0002", firstName: "Lindo", lastName: "Sibeko", email: "lindosibeko@gmail.com", phoneNo: "0730215586"}
]);

db.MEMBER.find().pretty();

db.BORROWEDBOOKS.insertMany([
    { memberID: "MEM0001", ISBN: "978-0316737371", dateBorrowed: "2027-08-22", dueDate: "2027-09-22", returnDate: "2027-10-24", fineAmount: 50},
    { memberID: "MEM0002", ISBN: "978-1408855652", dateBorrowed: "2027-05-20", dueDate: "2027-06-20", returnDate: "2027-06-15", fineAmount: 0}
]);

db.BORROWEDBOOKS.find().pretty();

db.BORROWEDBOOKS.find().pretty();

//Question 2.3
db.BOOK.find({
    publicationYear: { $gt: 2026}
}).pretty()

//Question 2.4
db.BORROWEDBOOKS.aggregate([
    {
        $group: {
            _id: null,
            totalFineAmount: { $sum: "$fineAmount"}
        }
    }
]);

//Question 2.5
db.BORROWEDBOOKS.deleteOne({fineAmount: 50 });

db.BORROWEDBOOKS.find().pretty();

//Question 2.6
db.BORROWEDBOOKS.aggregate([
    {
        $lookup: {
            from: "MEMBER",
            localField: "memberID",
            foreignField: "memberID",
            as: "member"
        }
    },
    {
        $lookup: {
            from: "BOOK",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "book"
            
        }
    },

    {
        $unwind: "$member"
    },

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