use EduvosOlMS

db.createCollection("members")
db.createCollection("books")
db.createCollection("authors")
db.createCollection("bookAuthors")
db.createCollection("borrowings")

db.members.insertMany([
    {
        memberID: "MB001",
        firstName: "Ben",
        lastName: "Smith",
        email: "BenSmith26@gamil.com",
        phoneNumber: "0843019228"
    },
    {
          memberID: "MB002",
        firstName: "Jeff",
        lastName: "Mzolo",
        email: "MzoloJeff@gamil.com",
        phoneNumber: "0823456789"
    }
])
db.authors.insertMany([
    {
        authorID: "AH002",
        firstName: "Thabo",
        lastName: "Sosibo"    
    },
    {
       authorID: "AH003",
        firstName: "John",
        lastName: "Kendricks"   
    }
])

db.books.insertMany([
    {
        ISBN: "4560000000002",
        title: "The blue house",
        publicationYear: "2026",
        availability: "Online",
        quantityInStock: "23"
    },
    {
        ISBN: "5920000000008",
        title: "Life of Pie",
        publicationYear: "2006",
        availability: "In Store",
        quantityInStock: "45" 
    }
])

db.bookAuthors.insertMany([
    {
        bookAuthorID: "BA002",
        ISBN: "4560000000002",
        authorID: "AH002"
    },
    {
        bookAuthorID: "BA003",
        ISBN: "5920000000008",
        authorID: "AH003"
    }
])

db.borrowings.insertMany([
    {
        borrowingID: "BR002",
        memberID: "MB001",
        ISBN: "4560000000002",
        dateBorrowed: ISODate("2026-03-14"),
        dueDate: ISODate("2026-05-14"),
        returnDate: ISODate("2026-05-18"),
        fineAmount: 20
    },
    {
        borrowingID: "BR003",
        memberID: "MB002",
        ISBN: "5920000000008",
        dateBorrowed: ISODate("2026-06-21"),
        dueDate: ISODate("2026-08-21"),
        returnDate: null
        fineAmount: 0
    }
])

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

db.borrowings.aggregate([
    {
        $group:{
            _id: nul,
            totalFineRevenue: { $sum: "$fineAmount"}
        }
    }
])


db.borrowings.deleteOne(
    {fineAmount: 20 }
)

db.borrowings.aggregate([
    {
        $lookup: {
            from: "members",
            localField:"memberID",
            foreignField: "memberID",
            as:"memberDetails"
        }
    },
    {
        $unwind: "$memberDetails"
    },
    {
        $lookup:{
            from: "books",
            localField:"ISBN",
            foreignField: "ISBN",
            as:"bookDetails"
        }
    },
    {
        $unwind: "$bookDetails"
    },
    {
        $project:{
            _id: 0,
            memberID: 1,
            firstName: "$memberDetails.firstName",
            lastName: "$memberDetails.lastName",
            title: "$bookDetails.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
])