db.createCollection("AUTHOR");
db.createCollection("BOOKAUTHOR");
db.createCollection("BOOK");
db.createCollection("BORROWEDBOOKS");
db.createCollection("MEMBER");

// Insert into AUTHOR
db.AUTHOR.insertMany([
    { authorID: "A01", firstName: "George", lastName: "Orwell" },
    { authorID: "A02", firstName: "Jane", lastName: "Austen" }
]);

// Insert into BOOK 
// (Note: Included one book published after 2026 to support Question 2.3)
db.BOOK.insertMany([
    { ISBN: "978-1-0001", title: "1984", publicationYear: 1949 },
    { ISBN: "978-2-0002", title: "Future Tech Innovations", publicationYear: 2027 }
]);

// Insert into BOOKAUTHOR (Bridge Collection)
db.BOOKAUTHOR.insertMany([
    { authorID: "A01", ISBN: "978-1-0001" },
    { authorID: "A02", ISBN: "978-2-0002" }
]);

// Insert into MEMBER
db.MEMBER.insertMany([
    { memberID: "M01", firstName: "Alice", lastName: "Smith", email: "alice@eduvos.com", phoneNo: "0821234567" },
    { memberID: "M02", firstName: "Bob", lastName: "Jones", email: "bob@eduvos.com", phoneNo: "0837654321" }
]);

// Insert into BORROWEDBOOKS (Bridge Collection)
// (Note: Included one record with a fine of 50 to support Question 2.5)
db.BORROWEDBOOKS.insertMany([
    { memberID: "M01", ISBN: "978-1-0001", dateBorrowed: new Date("2026-05-01"), dueDate: new Date("2026-05-15"), returnDate: new Date("2026-05-20"), fineAmount: 50 },
    { memberID: "M02", ISBN: "978-2-0002", dateBorrowed: new Date("2026-06-10"), dueDate: new Date("2026-06-24"), returnDate: null, fineAmount: 0 }
]);

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


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

db.MEMBER.aggregate([
    {
        // Join with BORROWEDBOOKS to see who borrowed what
        $lookup: {
            from: "BORROWEDBOOKS",
            localField: "memberID",
            foreignField: "memberID",
            as: "borrowing_info"
        }
    },
    // Deconstruct the array to output a document for each borrowed book
    { $unwind: "$borrowing_info" },
    {
        // Join with BOOK to get the book titles based on ISBN
        $lookup: {
            from: "BOOK",
            localField: "borrowing_info.ISBN",
            foreignField: "ISBN",
            as: "book_info"
        }
    },
    { $unwind: "$book_info" },
    {
        // Select and format the specific fields requested
        $project: {
            _id: 0,
            memberID: 1,
            firstName: 1,
            lastName: 1,
            title: "$book_info.title",
            dateBorrowed: "$borrowing_info.dateBorrowed",
            dueDate: "$borrowing_info.dueDate",
            returnDate: "$borrowing_info.returnDate",
            fineAmount: "$borrowing_info.fineAmount"
        }
    }
]);