//Question 2.1
//creating all collections

db.createCollection("Author")
db.createCollection("Book")
db.createCollection("BookAuthor")
db.createCollection("Member")
db.createCollection("BorrowedCheck")

//Question 2.2
//creating the sample data of all collections

db.Author.insertMany([
    {AuthorID: 1, FirstName: "Jordan", LastName: "Clarkson"}
    {AuthorID: 2, FirstName: "Micheal", LastName: "Phillips"}
    {AuthorID: 1, FirstName: "Anita", LastName: "Brown"}
]);

//sample data for books
db.Book.insertMany([
    {ISBN: "7418479189523", Title: "Over the gardenWall", PublicationYear: 2016}
    {ISBN: "5318479129523", Title: "Bibliomania", PublicationYear: 2020}
    {ISBN: "9288434569527", Title: "Parasite", PublicationYear: 2024}
    {ISBN: "3567479122653", Title: "Blood on the Tracks", PublicationYear: 2018}
    {ISBN: "22418434651523", Title: "Homunculus", PublicationYear: 2022}
]);

//adding sample data for member collections
db.member.insertMany([
    {MemberID:1, FirstName: "Jacob" LastName: "Yves", email: "JacobYves2@gmail.com" PhoneNum : "0674433588"}
    {MemberID:2, FirstName: "Tony" LastName: "Raquel", email: "TonyRaquel33@gmail.com" PhoneNum : "0823334588"}
    {MemberID:3, FirstName: "Quinton" LastName: "Miller", email: "QuintonMiller2005@gmail.com" PhoneNum : "0924563838"}
]);

// borrowed books (bridging members and books)

db.BorrowedBooks.insertMany([
    {
        MemberID:1, ISBN: "5318479129523",
        DateBorrowed: new Date("2026-02-11"), DueDate: new Date ("2026-02-17"),
        ReturnDate: new Date("2026-02-20"), FineAmount: 100
    },
    {
        MemberID:2, ISBN: "9288434569527",
        DateBorrowed: new Date("2026-05-16"), DueDate: new Date ("2026-05-22"),
    },
    {
        MemberID:3, ISBN: "22418434651523",
        DateBorrowed: new Date("2026-06-07"), DueDate: new Date ("2026-06-11"),
        ReturnDate: new Date("2026-06-20"), FineAmount: 80
    },
    {
        MemberID:1, ISBN: "7418479189523",
        DateBorrowed: new Date("2026-04-22"), DueDate: new Date ("2026-04-30")
    }

]);

//Question 2.3
    db.Book.find({ PublicationYear: {$gt: 2026} });

//Question 2.4 
// total revenue generated from fines 

db.BorrowedBooks.aggregate([
    {$group: { _id: null, TotalFines: {$sum: "$FineAmount"} } }
]);

//Question 2.5
//erase the record for the member who owed R100 fine now that its paid

db.BorrowedBooks.DeleteOne({FineAmount: 100});

//Question 2.6