//Q2.1

db.createCollection("AUTHOR");

db.createCollection("BOOK");

db.createCollection("BOOKAUTHOR");

db.createCollection("MEMBER");

db.createCollection("BORROWEDBOOKS");

//Q2.2

//Author
db.AUTHOR.insertMany([
{
    authorID: 1,
    firstName: "Mason",
    lastName: "Mccree"
},
{
    authorID: 2,
    firstName: "Sojourn",
    lastName: "Khazi"
}
]);

//Book
db.BOOK.insertMany([
{
    ISBN: "811200001",
    title: "Database Systems",
    publicationYear: 2027,
    availability: "Online",
    quantityInStock: 8
},
{
    ISBN: "811200002",
    title: "Oracle and MongoDB Essentials",
    publicationYear: 2025,
    availability: "Store",
    quantityInStock: 5
}
]);

//Book Author
db.BOOKAUTHOR.insertMany([
{
    authorID: 1,
    ISBN: "811200001"
},
{
    authorID: 2,
    ISBN: "811200002"
}
]);

//Member
db.MEMBER.insertMany([
{
    memberID: 101,
    firstName: "Mason",
    lastName: "Mccree",
    email: "mccree@email.com",
    phoneNo: "0621230667"
},
{
    memberID: 102,
    firstName: "Sojourn",
    lastName: "Khazi",
    email: "khazisoj@email.com",
    phoneNo: "0819476593"
}
]);

//Borrowed Books
db.BORROWEDBOOKS.insertMany([
{
    memberID: 101,
    ISBN: "811200001",
    dateBorrowed: new Date("2027-01-10"),
    dueDate: new Date("2027-01-20"),
    returnDate: new Date("2027-01-25"),
    fineAmount: 50
},
{
    memberID: 102,
    ISBN: "811200002",
    dateBorrowed: new Date("2027-02-05"),
    dueDate: new Date("2027-02-15"),
    returnDate: new Date("2027-02-14"),
    fineAmount: 0
}
]);

//Q2.3

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

//Q2.4

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

//Q2.5

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

//Q2.6

db.BORROWEDBOOKS.aggregate([
{
    $lookup:
    {
        from: "MEMBER",
        localField: "memberID",
        foreignField: "memberID",
        as: "member"
    }
},
{
    $unwind: "$member"
},
{
    $lookup:
    {
        from: "BOOK",
        localField: "ISBN",
        foreignField: "ISBN",
        as: "book"
    }
},
{
    $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
    }
}
]);