db.createCollection("author");

db.createCollection("book");

db.createCollection("member");

db.createCollection("borrowedbooks");

db.createCollection("bookauthor");

// AUTHOR
db.author.insertMany([
{
    authorID: 1,
    firstName: "Ofentse",
    lastName: "Dephane"
},
{
    authorID: 2,
    firstName: "Kevin",
    lastName: "Mowell"
}
]);

// BOOK
db.book.insertMany([
{
    ISBN: "1001",
    title: "The World Of Science",
    publicationYear: 2025,
    availability: "Store",
    quantityInStock: 5
},
{
    ISBN: "1002",
    title: "Pride Is The Devil",
    publicationYear: 2028,
    availability: "Online",
    quantityInStock: 10
}
]);

// MEMBER
db.member.insertMany([
{
    memberID: 101,
    firstName: "Themba",
    lastName: "Zwide",
    email: "zwidethemba@gmail.com",
    phoneNo: "0724300041"
},
{
    memberID: 102,
    firstName: "Khuseleko",
    lastName: "Kgosing",
    email: "khuseleko@gmail.com",
    phoneNo: "0657889450"
}
]);

// BORROWEDBOOKS
db.borrowedbooks.insertMany([
{
    memberID: 101,
    ISBN: "1001",
    dateBorrowed: new Date("2026-06-22"),
    dueDate: new Date("2026-07-01"),
    returnDate: new Date("2026-07-20"),
    fineAmount: 50
},
{
    memberID: 102,
    ISBN: "1002",
    dateBorrowed: new Date("2027-07-02"),
    dueDate: new Date("2027-07-15"),
    returnDate: null,
    fineAmount: 0
}
]);

// BOOKAUTHOR
db.bookauthor.insertMany([
{
    ISBN: "1001",
    authorID: 1
},
{
    ISBN: "1002",
    authorID: 2
}
]);


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

db.borrowedbooks.aggregate([
{
    $group: {
        _id: null,
        totalRevenue: { $sum: "$fineAmount" }
    }
}
]);

db.borrowedbooks.find();

db.borrowedbooks.deleteOne({
    fineAmount: 50
});


db.borrowedbooks.find();