//Creations of the 5 collections from the ERD
db.createCollection("Author");
db.createCollection("Book");
db.createCollection("Member");
db.createCollection("BookAuthor");
db.createCollection("BorrowedBooks");

/*Question 2.2 - Inserting Data*/

//Authours
db.Author.insertMany([
    {
        authorID: 1,
        firstName: "Jane",
        lastName: "Doe"
    },
    {
        authorID: 2,
        firstName: "Cherry",
        lastName: "Berry"
    }
]);
//Books
db.Book.insertMany([
    {
        ISBN: "97801234",
        title: "Avatar",
        publicationYear: 2027
    },
    {
        ISBN: "97812345",
        title: "One Piece",
        publicationYear: 1999
    }
]);
//BookAuthors
db.BookAuthor.insertMany([
    {
        authorID: 1,
        ISBN: "97801234"
    },
    {
        authorID: 2,
        ISBN: "97812345"
    }
]);
//BorrowedBooks
db.BorrowedBooks.insertMany([
    {
        memberID: 101,
        ISBN: "97801234",
        dateBorrowed: "2026-03-01",
        dueDate: "2026-03-15",
        returnDate: "2026-03-14",
        fineAmount: 0
    },
    {
        memberID: 102,
        ISBN: "97812345",
        dateBorrowed: "2026-04-01",
        dueDate: "2026-04-15",
        returnDate: "2026-04-20", //late for fineAmount
        fineAmount: 50
    }
]);
//Members
db.Member.insertMany([
    {
        memberID: 101,
        firstName: "Jake",
        lastName: "lowel",
        email: "jakeL@example.com",
        phoneNo: "+27 812345678"
    },
    {
        memberID: 102,
        firstName: "David",
        lastName: "Goliath",
        email: "DavidG@example.com",
        phoneNo: "+27 823456789"
    }
]);

/*Question 2.3 -retrieve and display books published past 2026*/
//Publication year is found in the Book collection, therfore find there
db.Book.find(
    {
        publicationYear:{ $gt: 2026 }//$gt greater than comparison, checking publicationYear>2026, outputs if true
    }            
);

/*Question 2.4 - total fines amount calculation*/
//fines are found within BorrowedBooks collection

//use aggregate to calculation
db.BorrowedBooks.aggregate([
    {
        //group all the documents into one
        $group: 
        {
            _id: null, //group records into one group where data totalRevenue will contain the sum
            totalRevenue: { $sum: "$fineAmount" } //sum operation adding everything found in the fineAmount address
        }
    }
]);

/*Question 2.5 -delete the record who's fine amount is R50*/
// Delete all the records who's fineAmount was 50
db.BorrowedBooks.deleteMany(
    {
        //specifies the document with a fine amount of 50
        fineAmount: 50
    }
);

/* Question 2.6 - Display members who have borrowed books*/
db.BorrowedBooks.aggregate([
    // Join BorrowedBooks with the Member collection
    {
        $lookup: //join function
        {
            from: "Member", // Tell where to join
            // match the localfield of memberID in borroweBooks with the matching Member field
            localField: "memberID",
            foreignField: "memberID",
            as: "member" // Store the matching Member documents in a new field called member
        }
    },

    // change the member array into a single document
    {
        $unwind: "$member"
    },

    // Join BorrowedBooks with the Book collection
    {
        $lookup: 
        {
            from: "Book",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "book"
        }
    },

    // change the book array into a single document
    {
        $unwind: "$book"
    },

    // Display
    {
        $project: 
        {
            _id: 0, // exclude the default id field
            //display everything
            firstName: "$member.firstName",
            lastName: "$member.lastName",
            bookTitle: "$book.title",
            dateBorrowed: 1,
            dueDate: 1,
            returnDate: 1,
            fineAmount: 1
        }
    }

]);