db.createCollection("AUTHOR");

db.createCollection("BOOKAUTHOR");

db.createCollection("BOOK");

db.createCollection("MEMBER");

db.createCollection("BORROWEDBOOKS");

// AUTHOR
db.AUTHOR.insertMany([
{
    authorID: "AU001",
    firstName: "John",
    lastName: "Smith"
},
{
    authorID: "AU002",
    firstName: "Mary",
    lastName: "Williams"
}
]);

// BOOK
db.BOOK.insertMany([
{
    ISBN: "978000001",
    title: "Database Systems",
    publicationYear: 2027
},
{
    ISBN: "978000002",
    title: "Programming Fundamentals",
    publicationYear: 2025
}
]);

// MEMBER
db.MEMBER.insertMany([
{
    memberID: "M001",
    firstName: "Thabo",
    lastName: "Mokoena",
    email: "thabo@email.com",
    phoneNo: "0821111111"
},
{
    memberID: "M002",
    firstName: "Lerato",
    lastName: "Dlamini",
    email: "lerato@email.com",
    phoneNo: "0832222222"
}
]);

// BOOKAUTHOR
db.BOOKAUTHOR.insertMany([
{
    authorID: "AU001",
    ISBN: "978000001"
},
{
    authorID: "AU002",
    ISBN: "978000002"
}
]);

// BORROWEDBOOKS
db.BORROWEDBOOKS.insertMany([
{
    memberID: "M001",
    ISBN: "978000001",
    dateBorrowed: new Date("2027-01-10"),
    dueDate: new Date("2027-01-20"),
    returnDate: new Date("2027-01-18"),
    fineAmount: 0
},
{
    memberID: "M002",
    ISBN: "978000002",
    dateBorrowed: new Date("2027-02-01"),
    dueDate: new Date("2027-02-10"),
    returnDate: new Date("2027-02-15"),
    fineAmount: 50
}
]);
db.BOOK.find({
    publicationYear: { $gt: 2026 }
});

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

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

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
    }
}
]);