use("olms");

db.createCollection("AUTHOR");
db.createCollection("BOOKAUTHOR");
db.createCollection("BOOK");
db.createCollection("BORROWEDBOOKS");
db.createCollection("MEMBER")

db.AUTHOR.insertMany([
  {
    authorID: 1,
    firstName: "George",
    lastName: "Orwell"
  },
  {
    authorID: 2,
    firstName: "Stephen",
    lastName: "King"
  }
]);

db.BOOK.insertMany([
  {
    ISBN: "09876543321",
    title: "IT",
    publicationYear: 1986
  },
  {
    ISBN: "0123456789",
    title: "North",
    publicationYear: 2003
  }
]);

db.BOOKAUTHOR.insertMany([
  {
    authorID: 2,
    ISBN: "0123456789"
  },
  {
    authorID: 35,
    ISBN: "0192837465"
  }
]);

db.MEMBER.insertMany([
  {
    memberID: 111,
    firstName: "Bilbo",
    lastName: "Baggins",
    email: "roadgoeseveron@gmail.com",
    phoneNo: "0772390701"
  },
  {
    memberID: 2122,
    firstName: "Cameron",
    lastName: "Winter",
    email: "alanjenkins@hotmail.com",
    phoneNo: "0839876543"
  }
]);

db.BORROWEDBOOKS.insertMany([
  {
    memberID: 111,
    ISBN: "0192837465",
    dateBorrowed: new Date("2017-03-01"),
    dueDate: new Date("2017-04-02"),
    returnDate: new Date("2017-03-26"),
    fineAmount: 0
  },
  {
    memberID: 2122,
    ISBN: "0987612345",
    dateBorrowed: new Date("2026-03-01"),
    dueDate: new Date("2026-04-02"),
    returnDate: new Date("2026-03-26"),
    fineAmount: 0
  }
]);

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

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

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

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
        }
    }
]);