//Deliverable 2
//2.1
db.createCollection("Author")
db.createCollection("Book")
db.createCollection("Member")
db.createCollection("BookAuthor")
db.createCollection("BorrowedBooks")
//2.2
db.Author.insertMany([
    {
        authorID: 1,
        firstName: " Norman", 
        lastname: "Nise"
    }, 
    {
        authorID: 2,
        firstName: "Steven", 
        lastname: "Morris"
    }
])
db.Book.insertMany([
    {
        ISBN: "9780357673034",
        title: "Database Systems: Design, Implementation, & Management",
        publicationYear: "2016",
        available: true,
        quantity: 8
    },
    {
        ISBN: "9781119474227",
        title: "Control Systems Engineering",
        publicationYear: "2020",
        available: true,
        quantity: 5
    }
])
db.Member.insertMany([
    {
        memberID: 101,
        firstName: "Meira",
        lastName: "Stollard",
        email: "meira@gmail.com",
        phoneNo: "0662115477"
    },
    {
        memberID: 102,
        firstName: "Lior",
        lastName: "Marks",
        email: "lior@gmail.com",
        phoneNo: "0670317197"
    }
])
db.BookAuthor.insertMany([
    {
        ISBN: "9780357673034", 
        authorID: 2
    },
    {
        ISBN: "9781119474227", 
        authorID: 1
    }
])
db.BorrowedBooks.insertMany([
    {
        memberID: 101,
        ISBN: "9781119474227",
        dateBorrowed: new Date("2026-01-03"),
        dueDate: new Date("2026-02-03"),
        returnDate: new Date("2027-02-20"),
        fineAmount: 50
    },
    {
        memberID: 102,
        ISBN: "9780357673034",
        dateBorrowed: new Date("2026-02-08"),
        dueDate: new Date("2026-03-08"),
        returnDate: new Date("2027-03-01"),
        fineAmount: 0
    }
])
//2.3
db.Book.find({
    publicationYear: {$gt: 2026}
})
//2.4
db.BorrowedBooks.aggregate([
{
    $group: {
        _id: null,
        totalRevenue: {
            $sum: "$fineAmount"
        }
    }
}
])
//2.5
db.BorrowedBooks.remove()
//2.6
db.BorrowedBooks.aggregate([

{
    $lookup: { //(MongoDB, n.d.)
        from: "Member",
        localField: "memberID",
        foreignField: "memberID",
        as: "member"
    }
},

{
    $unwind: "$member" //(MongoDB, n.d.)
},

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

{
    $unwind: "$book"
},

{
    $project: { //(MongoDB, n.d.)
        _id: 0,
        memberID: "$member.memberID",
        firstName: "$member.firstName",
        lastName: "$member.lastName",
        title: "$book.title",
        dateBorrowed: 1,
        dueDate: 1,
        returnDate: 1,
        fineAmount: 1
    }
}

])