// DELIVERABLE 2: DATABASE MANAGEMENT WITH MONGODB
// Eduvos Online Library Database System
// QUESTIONS 2.1 - 2.5 FINAL CODE

db = db.getSiblingDB("EduvosOnlineLibraryDB");

// QUESTION 2.1
// Create the five collections from the ERD

if (!db.getCollectionNames().includes("authors")) {
    db.createCollection("authors");
}

if (!db.getCollectionNames().includes("books")) {
    db.createCollection("books");
}

if (!db.getCollectionNames().includes("members")) {
    db.createCollection("members");
}

if (!db.getCollectionNames().includes("bookAuthors")) {
    db.createCollection("bookAuthors");
}

if (!db.getCollectionNames().includes("borrowedBooks")) {
    db.createCollection("borrowedBooks");
}


// Clear existing records to avoid duplicates when running the code again

db.authors.deleteMany({});
db.books.deleteMany({});
db.members.deleteMany({});
db.bookAuthors.deleteMany({});
db.borrowedBooks.deleteMany({});


// QUESTION 2.2
// Insert at least two records into each collection

db.authors.insertMany([
    {
        authorID: 1,
        firstName: "Nomsa",
        lastName: "Dlamini"
    },
    {
        authorID: 2,
        firstName: "Thabo",
        lastName: "Mokoena"
    }
]);

db.books.insertMany([
    {
        ISBN: "978-0-620-12345-1",
        title: "Advanced Database Systems",
        publicationYear: 2027,
        quantityInStock: 5
    },
    {
        ISBN: "978-0-620-67890-2",
        title: "Cloud Computing for Beginners",
        publicationYear: 2028,
        quantityInStock: 4
    }
]);

db.members.insertMany([
    {
        memberID: 1,
        firstName: "Sipho",
        lastName: "Nkosi",
        email: "sipho.nkosi@email.com",
        phoneNo: "0712345678"
    },
    {
        memberID: 2,
        firstName: "Lerato",
        lastName: "Mabena",
        email: "lerato.mabena@email.com",
        phoneNo: "0723456789"
    }
]);

db.bookAuthors.insertMany([
    {
        ISBN: "978-0-620-12345-1",
        authorID: 1
    },
    {
        ISBN: "978-0-620-67890-2",
        authorID: 2
    }
]);

db.borrowedBooks.insertMany([
    {
        borrowID: 1,
        dateBorrowed: new Date("2026-07-01"),
        dueDate: new Date("2026-07-14"),
        returnDate: null,
        fineAmount: 0,
        ISBN: "978-0-620-12345-1",
        memberID: 1
    },
    {
        borrowID: 2,
        dateBorrowed: new Date("2026-07-03"),
        dueDate: new Date("2026-07-17"),
        returnDate: new Date("2026-07-22"),
        fineAmount: 50,
        ISBN: "978-0-620-67890-2",
        memberID: 2
    }
]);


// QUESTION 2.3
// Retrieve all books published after 2026

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


// QUESTION 2.4
// Calculate the total revenue generated from fines

db.borrowedBooks.aggregate([
    {
        $group: {
            _id: null,
            totalFineRevenue: { $sum: "$fineAmount" }
        }
    }
]);


// QUESTION 2.5
// Delete the borrowed book record where the member owed a R50 fine

db.borrowedBooks.deleteOne({
    fineAmount: 50
});