// 2.1 Create the five (5) collections identified from the ERD
use eduvos_library;
db.createCollection("authors");
db.createCollection("books");
db.createCollection("bookauthors");     // junction collection: resolves M:N between BOOK and AUTHOR
db.createCollection("members");
db.createCollection("borrowedbooks");   // bridging collection: resolves M:N between MEMBER and BOOK
// 2.2 Insert at least two (2) records into each collection 
// AUTHORS
db.authors.insertMany([
    { authorID: 1, firstName: "Chinua", lastName: "Achebe" },
    { authorID: 2, firstName: "Trevor", lastName: "Noah" }
]);
// BOOKS
// availability: "In Store" or "Online"; quantityInStock reduces as books are borrowed
db.books.insertMany([
    {
        ISBN: "9780143026560",
        title: "Things Fall Apart",
        publicationYear: 1994,
        availability: "In Store",
        quantityInStock: 5
    },
    {
        ISBN: "9781984855205",
        title: "Born a Crime",
        publicationYear: 2027,
        availability: "Online",
        quantityInStock: 10
    }
]);
// BOOKAUTHORS (junction: links books to their author(s))
db.bookauthors.insertMany([
    { ISBN: "9780143026560", authorID: 1 },
    { ISBN: "9781984855205", authorID: 2 }
]);
// MEMBERS
// email and phoneNo are optional attributes, per the scenario
db.members.insertMany([
    {
        memberID: 1,
        firstName: "Lindiwe",
        lastName: "Dube",
        email: "lindiwe.dube@example.com",
        phoneNo: "0821234567"
    },
    {
        memberID: 2,
        firstName: "James",
        lastName: "van Wyk"
        // email and phoneNo omitted - optional fields
    }
]);
// BORROWEDBOOKS (bridging entity: memberID + ISBN + borrowing details + fine)
db.borrowedbooks.insertMany([
    {
        memberID: 1,
        ISBN: "9780143026560",
        dateBorrowed: ISODate("2026-06-01"),
        dueDate: ISODate("2026-06-15"),
        returnDate: ISODate("2026-06-20"),
        fineAmount: 50
    },
    {
        memberID: 2,
        ISBN: "9781984855205",
        dateBorrowed: ISODate("2026-06-10"),
        dueDate: ISODate("2026-06-24"),
        returnDate: null,
        fineAmount: 0
    }
]);
// 2.3 Retrieve and display all books published after 2026 
db.books.find({ publicationYear: { $gt: 2026 } });
// 2.4 Total revenue generated in fines
db.borrowedbooks.aggregate([
    {
        $group: {
            _id: null,
            totalFinesCollected: { $sum: "$fineAmount" }
        }
    }
]);
// 2.5 Erase borrowed-book records where the R50 fine has been paid 
// Assumption: a paid R50 fee means the fineAmount was 50 and is now settled,
// so the borrowing record can safely be removed to avoid redundant data.
db.borrowedbooks.deleteMany({ fineAmount: 50 });
// 2.6 Display members who have borrowed books, with book + fine details 
db.borrowedbooks.aggregate([
    {
        $lookup: {
            from: "members",
            localField: "memberID",
            foreignField: "memberID",
            as: "memberInfo"
        }
    },
    { $unwind: "$memberInfo" },
    {
        $lookup: {
            from: "books",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "bookInfo"
        }
    },
    { $unwind: "$bookInfo" },
    {
        $project: {
            _id: 0,
            memberID: "$memberInfo.memberID",
            firstName: "$memberInfo.firstName",
            lastName: "$memberInfo.lastName",
            bookTitle: "$bookInfo.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }
]);