/* ============================================================
   DELIVERABLE 2: Eduvos Online Library Database System (MongoDB)
   ============================================================
   Note: the same sample data set is reused in Deliverable 3
   (Neo4j) so the two solutions stay consistent with each other.
   ============================================================ */
 
use eduvosLibrary;
 
 
/* ------------------------------------------------------------
   2.1 CREATE THE FIVE (5) COLLECTIONS FROM THE ERD          (5 Marks)
   ------------------------------------------------------------
   author        - independent entity
   book          - independent entity
   bookauthor    - bridging entity (author <-> book, many-to-many)
   member        - independent entity
   borrowedbooks - bridging entity (member <-> book, many-to-many)
   ------------------------------------------------------------ */
 
db.createCollection("author");
db.createCollection("book");
db.createCollection("bookauthor");
db.createCollection("member");
db.createCollection("borrowedbooks");
 
 
/* ------------------------------------------------------------
   2.2 INSERT AT LEAST TWO (2) RECORDS INTO EACH COLLECTION  (15 Marks)
   ------------------------------------------------------------ */
 
db.author.insertMany([
    { authorID: "au01", firstName: "Adewale", lastName: "Obaro" },
    { authorID: "au02", firstName: "Mia",     lastName: "Adrian" },
    { authorID: "au03", firstName: "Ntombi",  lastName: "Lebo" }
]);
 
db.book.insertMany([
    { ISBN: 50001, title: "Database Systems",                publicationYear: 2022, availability: "in store", quantityInStock: 5 },
    { ISBN: 50002, title: "Introduction to Python Programming", publicationYear: 2026, availability: "online",   quantityInStock: 3 },
    { ISBN: 50003, title: "Technical Programming",            publicationYear: 2025, availability: "in store", quantityInStock: 4 },
    { ISBN: 50004, title: "Advanced Data Structures",         publicationYear: 2027, availability: "online",   quantityInStock: 2 }
]);
 
db.bookauthor.insertMany([
    { ISBN: 50001, authorID: "au01" },
    { ISBN: 50002, authorID: "au02" },
    { ISBN: 50003, authorID: "au03" },
    { ISBN: 50004, authorID: "au01" }
]);
 
db.member.insertMany([
    { memberID: "MEM001", firstName: "Duma",   lastName: "Baloyi", email: "duma@gmail.com",   phoneNo: "0623456789" },
    { memberID: "MEM002", firstName: "Lerato", lastName: "Tebogo", email: "lerato@yahoo.com",  phoneNo: "0787654321" },
    { memberID: "MEM003", firstName: "Ndlovu", lastName: "Lesedi", email: "lesedi@gmail.com",  phoneNo: "0652820028" }
]);
 
db.borrowedbooks.insertMany([
    { memberID: "MEM001", ISBN: 50002, dateBorrowed: ISODate("2024-09-15"), dueDate: ISODate("2024-10-25"), returnDate: null,                     fineAmount: 0.0 },
    { memberID: "MEM002", ISBN: 50003, dateBorrowed: ISODate("2024-04-15"), dueDate: ISODate("2024-06-25"), returnDate: ISODate("2024-09-25"),    fineAmount: 50.0 },
    { memberID: "MEM003", ISBN: 50001, dateBorrowed: ISODate("2024-09-15"), dueDate: ISODate("2024-11-25"), returnDate: null,                     fineAmount: 0.0 }
]);
 
 
/* ------------------------------------------------------------
   2.3 BOOKS PUBLISHED AFTER 2026                             (2 Marks)
   ------------------------------------------------------------ */
 
db.book.find(
    { publicationYear: { $gt: 2026 } }
);
// With the sample data above, this returns ISBN 50004 - "Advanced Data
// Structures" (publicationYear 2027).
 
 
/* ------------------------------------------------------------
   2.4 TOTAL REVENUE GENERATED IN FINES                       (3 Marks)
   ------------------------------------------------------------ */
 
db.borrowedbooks.aggregate([
    {
        $group: {
            _id: null,
            totalFinesRevenue: { $sum: "$fineAmount" }
        }
    }
]);
// With the sample data above, totalFinesRevenue = 50.0
 
 
/* ------------------------------------------------------------
   2.5 ERASE BORROWED RECORD ONCE THE R50 FINE IS PAID        (2 Marks)
   ------------------------------------------------------------ */
 
db.borrowedbooks.deleteOne(
    { memberID: "MEM002", ISBN: 50003, fineAmount: 50.0 }
);
 
 
/* ------------------------------------------------------------
   2.6 LIST OF MEMBERS WHO HAVE BORROWED BOOKS                (8 Marks)
   ------------------------------------------------------------
   Uses $lookup to join member -> borrowedbooks -> book,
   returning member identity, name, book title, and the
   borrowing/return/fine details.
   ------------------------------------------------------------ */
 
db.member.aggregate([
    {
        $lookup: {
            from: "borrowedbooks",
            localField: "memberID",
            foreignField: "memberID",
            as: "borrowed"
        }
    },
    { $unwind: "$borrowed" },
    {
        $lookup: {
            from: "book",
            localField: "borrowed.ISBN",
            foreignField: "ISBN",
            as: "bookInfo"
        }
    },
    { $unwind: "$bookInfo" },
    {
        $project: {
            _id: 0,
            memberID: 1,
            firstName: 1,
            lastName: 1,
            bookTitle: "$bookInfo.title",
            dateBorrowed: "$borrowed.dateBorrowed",
            dueDate: "$borrowed.dueDate",
            returnDate: "$borrowed.returnDate",
            fineAmount: "$borrowed.fineAmount"
        }
    }
]);
 
/* End of Deliverable 2 */