//=====================================================
// EDUVOS ONLINE LIBRARY DATABASE SYSTEM
//=====================================================


//=============
// QUESTION 2.1
//=============

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

print("========================================");
print("   Collections Created Successfully     ");
print("========================================");


//=============
// QUESTION 2.2
//=============

// Author

db.Author.insertMany([
{
    authorID: 1,
    firstName: "George",
    lastName: "Orwell"
},
{
    authorID: 2,
    firstName: "J.K.",
    lastName: "Rowling"
}
]);

// Book

db.Book.insertMany([
{
    ISBN: "9780451524935",
    title: "1984",
    publicationYear: 2027,
    available: "Store",
    quantityInStock: 10
},
{
    ISBN: "9780747532743",
    title: "Harry Potter and the Philosopher's Stone",
    publicationYear: 2025,
    available: "Online",
    quantityInStock: 20
}
]);

// BookAuthor

db.BookAuthor.insertMany([
{
    bookISBN: "9780451524935",
    authorID: 1
},
{
    bookISBN: "9780747532743",
    authorID: 2
}
]);

// Member

db.Member.insertMany([
{
    memberID: 101,
    firstName: "Kevin",
    lastName: "Clarke",
    email: "kevin@gmail.com",
    phoneNo: "0821111111"
},
{
    memberID: 102,
    firstName: "Sarah",
    lastName: "Jones",
    email: "sarah@gmail.com",
    phoneNo: "0832222222"
}
]);

// BorrowedBooks

db.BorrowedBooks.insertMany([
{
    memberID: 101,
    ISBN: "9780451524935",
    dateBorrowed: new Date("2027-02-01"),
    dueDate: new Date("2027-02-15"),
    returnDate: new Date("2027-02-20"),
    fineAmount: 50
},
{
    memberID: 102,
    ISBN: "9780747532743",
    dateBorrowed: new Date("2026-05-01"),
    dueDate: new Date("2026-05-10"),
    returnDate: new Date("2026-05-09"),
    fineAmount: 0
}
]);

print("");
print("========================================");
print("     Records Inserted Successfully      ");
print("========================================");

print("\nAUTHOR COLLECTION");
db.Author.find().forEach(printjson);

print("\nBOOK COLLECTION");
db.Book.find().forEach(printjson);

print("\nBOOKAUTHOR COLLECTION");
db.BookAuthor.find().forEach(printjson);

print("\nMEMBER COLLECTION");
db.Member.find().forEach(printjson);

print("\nBORROWEDBOOKS COLLECTION");
db.BorrowedBooks.find().forEach(printjson);


//=============
// QUESTION 2.3
//=============

print("");
print("========================================");
print("     Books Published After 2026         ");
print("========================================");

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


//=============
// QUESTION 2.4
//=============

print("");
print("========================================");
print("   Total Revenue Generated From Fines   ");
print("========================================");

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


//=============
// QUESTION 2.5
//=============

print("");
print("========================================");
print("Delete Borrowed Record Where Fine = R50 ");
print("========================================");

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

print("Remaining Borrowed Books");

db.BorrowedBooks.find().forEach(printjson);


//=============
// QUESTION 2.6
//=============

print("");
print("========================================");
print("       Members Who Borrowed Books       ");
print("========================================");

db.BorrowedBooks.aggregate([
{
    $lookup:{
        from:"Member",
        localField:"memberID",
        foreignField:"memberID",
        as:"member"
    }
},
{
    $unwind:"$member"
},
{
    $lookup:{
        from:"Book",
        localField:"ISBN",
        foreignField:"ISBN",
        as:"book"
    }
},
{
    $unwind:"$book"
},
{
    $project:{
        _id:0,
        memberID:"$member.memberID",
        firstName:"$member.firstName",
        lastName:"$member.lastName",
        title:"$book.title",
        dateBorrowed:1,
        dueDate:1,
        returnDate:1,
        fineAmount:1
    }
}
]).forEach(printjson);

print("");
print("========================================");