// ===============================================
// Question 2.1. 
// Create the five collections found from the ERD:
// Author, Book, Member, BookAuthor, and BorrowedBooks 
// ===============================================

// Create Author Collection
db.createCollection("Author")

// Create Book Collection
db.createCollection("Book")

// Create Member Collection
db.createCollection("Member")

// Create BookAuthor bridging Collection
db.createCollection("BookAuthor")

// Create BorrowedBooks Collection
db.createCollection("BorrowedBooks")

// ===================================================== 
// Question 2.2.
// Insert at least two records to each collection 
// Sample data stored according to ERD attributes 
// =====================================================

// Insert records into Author Collection
db.Author.insertMany([
{
    authorID: 1,
    firstName: "John",
    lastName: "Smith"
},
{
    authorID: 2,
    firstName: "Sarah",
    lastName: "Johnson"
}   
])

// Insert records to Book collection 
// Includes ISBN, title, year of publication,
// availability status, and stock quantity

db.Book.insertMany([
{
    ISBN: "9781920496859",
    title: "Database Systems",
    publicationYear: 2024,
    availability: "Online",
    quantityInStock: 10
},
{
    ISBN: "978453098772",
    title: "MongoDB Fundamentals",
    publicationYear: 2027,
    availability: "Store",
    quantityInStock: 5
}   
])


// Insert records to Member collection 
// Email and phone number are optional attributes

db.Member.insertMany([
{
    memberID: 101,
    firstName: "James",
    lastName: "Brown",
    email: "james83@gmail.com",
    phoneNo: "0824567893"
},
{
    memberID: 102,
    firstName: "Linda",
    lastName: "Taylor",
    email: "linda@gmail.com",
    phoneNo: "0832970340"
}   
])

// Insert records to BookAuthor collection 
// This collection illustrates the link 
// between books and authors

db.BookAuthor.insertMany([
{
    ISBN: "9781920496859",
    authorID: 1
},
{
    ISBN: "978453098772",
    authorID: 2
}   
])

// Add records to the BorrowedBooks Collection 
// It stores the borrowing transaction details 
// along with the dates and any potential fines

db.BorrowedBooks.insertMany([
{
    memberID: 101,
    ISBN: "9781920496859",
    dateBorrowed: new Date("2026-01-05"),
    dueDate: new Date("2026-01-20"),
    returnDate: new Date("2026-01-18"),
    fineAmount: 0
},
{
    memberID: 102,
    ISBN: "978453098772",
    dateBorrowed: new Date("2026-02-01"),
    dueDate: new Date("2026-02-15"),
    returnDate: new Date("2026-02-20"),
    fineAmount: 50
}   
])

// ===================================================== 
// Question 2.3.
// Get and display all books published after 2026
// =====================================================



// $gt means greater than 
// This query returns books where the publicationYear 
// is greater than 2026

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

// ===================================================== 
// Question 2.4.
// Compute the total revenue from fines paid by members 
// who borrowed books 
// ======================================================

// The aggregate function is used to calculate totals 
// $sum takes all fineAmount values and adds them together

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

// ===================================================== 
// Question 2.5.
// Delete the borrowing record of the member 
// Who owed R50 after payment was done 
// =====================================================

// removeOne deletes the particular borrowing record
// where the member had a fine amount of R50

db.BorrowedBooks.deleteOne(
{
    memberID: 102,
    fineAmount: 50
}
)

// ===================================================== 
// Question 2.6 
// Show members that have borrowed books
// Show: 
// - Member ID 
// - First name 
// - Last name 
// - Book title 
// - Date borrowed
// - Due date 
// - Return date 
// - Fine amount
// =====================================================

// $lookup is for collection join 
// Like JOIN in relational databases

db.BorrowedBooks.aggregate([

// Join BorrowedBooks with Member Collection
{
    $lookup:
    {
        from: "Member",
        localField: "memberID",
        foreignField: "memberID",
        as: "memberDetails"
    }
},

// Join BorrowedBooks with Book Collection
{
    $lookup:
    {
        from: "Book",
        localField: "ISBN",
        foreignField: "ISBN",
        as: "bookDetails"
    }
},

// Convert arrays created by lookup into objects
{
    $unwind: "$memberDetails"
},
{
    $unwind: "$bookDetails"   
},
// Select only required information for display
{
    $project:
    {
        _id: 0,
        memberID: "$memberDetails.memberID",
        firstName: "$memberDetails.firstName",
        lastName: "$memberDetails.lastName",
        bookTitle: "$bookDetails.title",
        dateBorrowed: 1,
        dueDate: 1,
        returnDate: 1,
        fineAmount: 1
    }
}
])