// AUTHOR Collection
db.AUTHOR.insertOne({
    authorID: "AU01",
    firstName: "John",
    lastName: "Smith"
});

// BOOK Collection
db.BOOK.insertOne({
    ISBN: "9781234567890",
    title: "Database Systems",
    publicationYear: 2027
});

// MEMBER Collection
db.MEMBER.insertOne({
    memberID: "MEM01",
    firstName: "Michael",
    lastName: "Brown",
    email: "michael@email.com",
    phoneNo: "0821234567"
});

// BOOKAUTHOR Collection
db.BOOKAUTHOR.insertOne({
    authorID: "AU01",
    ISBN: "9781234567890"
});

// BORROWEDBOOKS Collection
db.BORROWEDBOOKS.insertOne({
    memberID: "MEM01",
    ISBN: "9781234567890",
    dateBorrowed: new Date("2026-05-01"),
    dueDate: new Date("2026-05-15"),
    returnDate: null,
    fineAmount: 0
});

// =========================
// AUTHOR COLLECTION
// =========================

db.AUTHOR.insertMany([
{
    authorID: "AU01",
    firstName: "John",
    lastName: "Smith"
},
{
    authorID: "AU02",
    firstName: "Sarah",
    lastName: "Johnson"
}
]);

// =========================
// BOOK COLLECTION
// =========================

db.BOOK.insertMany([
{
    ISBN: "9781234567890",
    title: "Database Systems",
    publicationYear: 2027
},
{
    ISBN: "9780987654321",
    title: "Programming Basics",
    publicationYear: 2025
}
]);

// =========================
// MEMBER COLLECTION
// =========================

db.MEMBER.insertMany([
{
    memberID: "MEM01",
    firstName: "Michael",
    lastName: "Brown",
    email: "michael@email.com",
    phoneNo: "0821234567"
},
{
    memberID: "MEM02",
    firstName: "Lisa",
    lastName: "Green",
    email: "lisa@email.com",
    phoneNo: "0837654321"
}
]);

// =========================
// BOOKAUTHOR COLLECTION
// =========================

db.BOOKAUTHOR.insertMany([
{
    authorID: "AU01",
    ISBN: "9781234567890"
},
{
    authorID: "AU02",
    ISBN: "9780987654321"
}
]);

// =========================
// BORROWEDBOOKS COLLECTION
// =========================

db.BORROWEDBOOKS.insertMany([
{
    memberID: "MEM01",
    ISBN: "9781234567890",
    dateBorrowed: new Date("2026-05-01"),
    dueDate: new Date("2026-05-15"),
    returnDate: null,
    fineAmount: 0
},
{
    memberID: "MEM02",
    ISBN: "9780987654321",
    dateBorrowed: new Date("2026-05-05"),
    dueDate: new Date("2026-05-20"),
    returnDate: new Date("2026-05-25"),
    fineAmount: 50
}
]);

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

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

db.BORROWEDBOOKS.deleteOne({
    memberID: "MEM02",
    fineAmount: 50
});

db.BORROWEDBOOKS.aggregate([
{
    $lookup: {
        from: "MEMBER",
        localField: "memberID",
        foreignField: "memberID",
        as: "member"
    }
},
{
    $lookup: {
        from: "BOOK",
        localField: "ISBN",
        foreignField: "ISBN",
        as: "book"
    }
},
{
    $unwind: "$member"
},
{
    $unwind: "$book"
},
{
    $project: {
        _id: 0,
        firstName: "$member.firstName",
        lastName: "$member.lastName",
        title: "$book.title",
        dateBorrowed: 1,
        dueDate: 1
    }
}
]);