// Create AUTHOR
db.createCollection("AUTHOR");

// Create BOOK 
db.createCollection("BOOK");

// Create MEMBER 
db.createCollection("MEMBER");

// Create BOOKAUTHOR (BRIDGING VALUE)
db.createCollection("BOOKAUTHOR");

// Create BORROWEDBOOKS (BRIDGING VALUE)
db.createCollection("BORROWEDBOOKS");

// POPULATE into AUTHOR
db.AUTHOR.insertMany([
    { authorID: 1, firstName: "Neil", lastName: "Armstrong" },
    { authorID: 2, firstName: "Cupcake", lastName: "Kahoot" }
]);

// POPULATE into BOOK
db.BOOK.insertMany([
    { ISBN: "978-001", title: "American Gods", publicationYear: 2001 },
    { ISBN: "978-002", title: "Future Tech Innovations", publicationYear: 2028 }
]);

// POPULATE into MEMBER 
db.MEMBER.insertMany([
    { memberID: 101, firstName: "Cameron", lastName: "Pillay", email: "CameronPillay@100%.com", phoneNo: "0812345678" },
    { memberID: 102, firstName: "Sumit", lastName: "Januk" } 
]);

// POPULATE into BOOKAUTHOR
db.BOOKAUTHOR.insertMany([
    { authorID: 1, ISBN: "978-001" },
    { authorID: 2, ISBN: "978-002" }
]);

// POPULATE into BORROWEDBOOKS
db.BORROWEDBOOKS.insertMany([
    { 
        memberID: 101, 
        ISBN: "978-001", 
        dateBorrowed: new Date("2026-05-01"), 
        dueDate: new Date("2026-05-15"), 
        returnDate: new Date("2026-05-20"), 
        fineAmount: 50 
    },
    { 
        memberID: 102, 
        ISBN: "978-002", 
        dateBorrowed: new Date("2026-06-01"), 
        dueDate: new Date("2026-06-15"), 
        returnDate: null, 
        fineAmount: 0 
    }
]);

db.BOOK.find({ 
    publicationYear: { $gt: 2026 } //checks for after 2026
});

db.BORROWEDBOOKS.aggregate([ //checks borrowed books
    { 
        $group: { 
            _id: null, 
            totalFineRevenue: { $sum: "$fineAmount" } //total
        } 
    }
])

db.BORROWEDBOOKS.deleteOne({ //deletes single value
    fineAmount: 50 
});

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