db.createCollection("authors")

db.createCollection("books")

db.createCollection("members")

db.createCollection("bookAuthors")

db.createCollection("borrowedBooks")


db.authors.insertMany([
{
authorID:1,
firstName:"Mukoni",
lastName:" Mogale"
},
{
authorID:2,
firstName:"Did",
lastName:"Nthangeni"
}
])

db.books.insertMany([
{
ISBN:"9781111111111",
title:"Database Systems",
publicationYear:2027,
availability:"Online",
quantity:10
},
{
ISBN:"9782222222222",
title:"Computer Networks",
publicationYear:2028,
availability:"Store",
quantity:8
}
])

db.members.insertMany([
{
memberID:1,
firstName:"Mukoni",
lastName:"Mogale",
email:"mukoni@email.com",
phoneNo:"0812345678"
},
{
memberID:2,
firstName:"Didi",
lastName:"Nthangeni",
email:"Didi@email.com",
phoneNo:"0823456789"
}
])

db.bookAuthors.insertMany([
{
authorID:1,
ISBN:"9781111111111"
},
{
authorID:2,
ISBN:"9782222222222"
}
])

db.borrowedBooks.insertMany([
{
memberID:1,
ISBN:"9781111111111",
dateBorrowed:new Date("2027-01-10"),
dueDate:new Date("2027-01-24"),
returnDate:new Date("2027-01-20"),
fineAmount:0
},
{
memberID:2,
ISBN:"9782222222222",
dateBorrowed:new Date("2027-02-01"),
dueDate:new Date("2027-02-15"),
returnDate:new Date("2027-02-25"),
fineAmount:50
}
])

db.books.find({
publicationYear:{$gt:2026}
})
[
{
ISBN:"9781111111111",
title:"Database Systems",
publicationYear:2027,
availability:"Online",
quantity:10
},
{
ISBN:"9782222222222",
title:"Computer Networks",
publicationYear:2028,
availability:"Store",
quantity:8
}
]
db.borrowedBooks.aggregate([
{
$group:{
_id:null,
totalRevenue:{$sum:"$fineAmount"}
}
}
])
[
{
_id:null,
totalRevenue:50
}
]
db.borrowedBooks.deleteOne({
fineAmount:50
})
db.borrowedBooks.find()
db.borrowedBooks.insertOne({
    memberID:2,
    ISBN:"9782222222222",
    dateBorrowed:new Date("2027-02-01"),
    dueDate:new Date("2027-02-15"),
    returnDate:new Date("2027-02-25"),
    fineAmount:50
})
db.borrowedBooks.find()
db.borrowedBooks.aggregate([

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

{
$lookup:{
from:"books",
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
}
}

])