//2.1
db.createCollection("member");

db.createCollection("author");

db.createCollection("book");

db.createCollection("borrowedbook");

db.createCollection("fine");

// 2.2 Insert records  collection
db.member.insertMany([
    {
        member_id: "MEM001",
        first_name: "CHEESE",
        last_name: "Baloyi",
        email: "CHEESE.baloyi@EDU.com",
        phone: "0821111111"
    },
    {
        member_id: "MEM002",
        first_name: "Tebogo",
        last_name: "Lerato",
        email: "tebogo.lerato@EDU.com",
        phone: "0822222222"
    }
]);

// Insert records into 'author' collection
db.author.insertMany([
    {
        author_id: "AUTH001",
        first_name: "John",
        last_name: "Sue"
    },
    {
        author_id: "AUTH002",
        first_name: "Tony",
        last_name: "Smith"
    }
]);

// Insert records into 'book' collection
db.book.insertMany([
    {
        isbn: "50001",
        title: "Database Systems",
        publication_year: 2025,
        quantity_in_stock: 15,
        available_format: "Both",
        author_id: "AUTH001"
    },
    {
        isbn: "50002",
        title: "Introduction to Python Programming",
        publication_year: 2024,
        quantity_in_stock: 10,
        available_format: "Online",
        author_id: "AUTH002"
    },
    
]);

// Insert records into 'borrowedbook' collection (intersection entity)
db.borrowedbook.insertMany([
    {
        borrowing_id: "BR001",
        member_id: "MEM001",
        isbn: "50002",
        borrowed_date: new Date("2026-02-15"),
        due_date: new Date("2026-08-25"),
        return_date: null,
        fine_amount: 0
    },
    {
        borrowing_id: "BR002",
        member_id: "MEM002",
        isbn: "50003",
        borrowed_date: new Date("2025-04-15"),
        due_date: new Date("2026-01-25"),
        return_date: new Date("2026-06-25"),
        fine_amount: 50.0
    },
    {
        borrowing_id: "BR003",
        member_id: "MEM003",
        isbn: "50001",
        borrowed_date: new Date("2024-09-15"),
        due_date: new Date("2025-01-25"),
        return_date: null,
        fine_amount: 0
    }
]);

// Insert records into 'fine' collection
db.fine.insertMany([
    {
        fine_id: "F001",
        borrowing_id: "BR002",
        amount: 50.0,
        payment_status: "Paid"
    },
    {
        fine_id: "F002",
        borrowing_id: "BR001",
        amount: 0,
        payment_status: "Not Applicable"
    }
]);

//2.3
db.book.find({ publication_year: { $gt: 2026 } });

//2.4
db.borrowedbook.aggregate([
    {
        $group: {
            _id: null,
            total_fine_revenue: { $sum: "$fine_amount" }
        }
    }
]);

//2.5

// Delete the borrowing record for the member who paid the R50 fine
db.borrowedbook.deleteOne({ borrowing_id: "BR002" });

// Also delete the associated fine record
db.fine.deleteOne({ borrowing_id: "BR002" });

//2.6
db.borrowedbook.aggregate([
    {
        $lookup: {
            from: "member",
            localField: "member_id",
            foreignField: "member_id",
            as: "member_details"
        }
    },
    {
        $unwind: "$member_details"
    },
    {
        $lookup: {
            from: "book",
            localField: "isbn",
            foreignField: "isbn",
            as: "book_details"
        }
    },
    {
        $unwind: "$book_details"
    },
    {
        $project: {
            _id: 0,
            "member_id": 1,
            "first_name": "$member_details.first_name",
            "last_name": "$member_details.last_name",
            "book_title": "$book_details.title",
            "borrowed_date": 1,
            "due_date": 1,
            "return_date": 1,
            "fine_amount": 1
        }
    }
]);