use EduvosLibary

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

//Question 2.2
// Insert records into Member collection
db.Member.insertMany([
    {
        memberID: "M001",
        firstName: "Angela",
        lastName: "Smith",
        email: "angela.smith@eduvos.com",
        phoneNo: "0821234567"
    },
    {
        memberID: "M002",
        firstName: "Jane",
        lastName: "Dlamini",
        email: "jane.dlamini@eduvos.com",
        phoneNo: "0839876543"
    }
])

// Insert records into Book collection
db.Book.insertMany([
    {
        ISBN: "978-0-13-468599-1",
        title: "The Mark",
        publicationYear: 2025,
        quantityInStock: 10,
        availability: "online"
    },
    {
        ISBN: "978-0-12-345678-9",
        title: "Hamlet",
        publicationYear: 2024,
        quantityInStock: 5,
        availability: "store"
    }
])

// Insert records into Author collection
db.Author.insertMany([
    {
        authorID: "A001",
        firstName: "Robert",
        lastName: "Johnson"
    },
    {
        authorID: "A002",
        firstName: "William",
        lastName: "Shakespeare"
    }
])

// Insert records into BookAuthor collection
db.BookAuthor.insertMany([
    {
        bookISBN: "978-0-13-468599-1",
        authorID: "A001"
    },
    {
        bookISBN: "978-0-12-345678-9",
        authorID: "A002"
    }
])

// Insert records into BorrowedBooks collection
db.BorrowedBooks.insertMany([
    {
        memberID: "M001",
        ISBN: "978-0-13-468599-1",
        dateBorrowed: ISODate("2026-06-15"),
        dueDate: ISODate("2026-07-15"),
        returnDate: ISODate("2026-07-20"),
        fineAmount: 50.00
    },
    {
        memberID: "M002",
        ISBN: "978-0-12-345678-9",
        dateBorrowed: ISODate("2026-06-20"),
        dueDate: ISODate("2026-07-20"),
        returnDate: null,
        fineAmount: 0.00
    }
])

//Question 2.3
db.Book.find({ publicationYear: { $gt: 2026 } }).pretty()

//Question 2.4
db.BorrowedBooks.aggregate([
    {
        $group: {
            _id: null,
            totalRevenue: { $sum: "$fineAmount" }
        }
    }
])

//Question 2.5
// Delete the borrowed book record where fineAmount is 50 (assuming the member with R50 fine paid)
db.BorrowedBooks.deleteMany({ fineAmount: 50 })

// Delete a specific member's record
db.BorrowedBooks.deleteOne({ memberID: "M001", fineAmount: 50 })

//Question 2.6
db.BorrowedBooks.aggregate([
    {
        $lookup: {
            from: "Member",
            localField: "memberID",
            foreignField: "memberID",
            as: "memberDetails"
        }
    },
    {
        $unwind: "$memberDetails"
    },
    {
        $lookup: {
            from: "Book",
            localField: "ISBN",
            foreignField: "ISBN",
            as: "bookDetails"
        }
    },
    {
        $unwind: "$bookDetails"
    },
    {
        $project: {
            "memberIdentity": "$memberDetails.memberID",
            "firstName": "$memberDetails.firstName",
            "lastName": "$memberDetails.lastName",
            "bookTitle": "$bookDetails.title",
            "dateBorrowed": 1,
            "dueDate": 1,
            "returnDate": 1,
            "fineAmount": 1,
            "_id": 0
        }
    }
])