Project 2

1.
Customers Collection:
{
"_id": ObjectId,
"first_name": String,
"last_name": String,
"email": String,
"password": String,
"address": String,
"phone_number": String,
"type": String (enum ["normal", "premium"]),
"created_at": Date,
"updated_at": Date
}

Products Collection:
{
"_id": ObjectId,
"name": String,
"description": String,
"price": Number,
"categories": [ObjectId],
"brands": [ObjectId],
"segments": [ObjectId],
"tags": [ObjectId],
"created_at": Date,
"updated_at": Date
}

Categories Collection:
{
"_id": ObjectId,
"name": String,
"parent_category": ObjectId,
"created_at": Date,
"updated_at": Date
}

Brands Collection:
{
"_id": ObjectId,
"name": String,
"created_at": Date,
"updated_at": Date
}

Segments Collection:
{
"_id": ObjectId,
"name": String,
"created_at": Date,
"updated_at": Date
}

Tags Collection:
{
"_id": ObjectId,
"name": String,
"created_at": Date,
"updated_at": Date
}

Cart Collection:
{
"_id": ObjectId,
"customer_id": ObjectId,
"products": [
{
"product_id": ObjectId,
"quantity": Number
}
],
"created_at": Date,
"updated_at": Date
}

Orders Collection:
{
"_id": ObjectId,
"customer_id": ObjectId,
"cart_id": ObjectId,
"shipping_address": String,
"billing_address": String,
"status": String (enum ["booked", "shipped", "delivered", "cancelled"]),
"created_at": Date,
"updated_at": Date
}

Sellers Collection:
{
"_id": ObjectId,
"first_name": String,
"last_name": String,
"email": String,
"password": String,
"address": String,
"phone_number": String,
"created_at": Date,
"updated_at": Date
}

1.2

// Customers Collection

db.createCollection("Customers")
db.Customers.insertMany([
{
first_name: "John",
last_name: "Doe",
email: "[email protected]",
password: "password",
address: "123 Main St, Anytown USA 12345",
phone_number: "123-456-7890",
type: "normal",
created_at: new Date(),
updated_at: new Date()
},
{
first_name: "Jane",
last_name: "Doe",
email: "[email protected]",
password: "password",
address: "456 Main St, Anytown USA 12345",
phone_number: "098-765-4321",
type: "premium",
created_at: new Date(),
updated_at: new Date()
}
])

// Products Collection

db.createCollection("Products")
db.Products.insertMany([
{
name: "Product 1",
description: "This is a sample product",
price: 9.99,
categories: [ObjectId("5f9c93e44ea8f00e4040a7c5")],
brands: [ObjectId("5f9c93f74ea8f00e4040a7c6")],
segments: [ObjectId("5f9c93f74ea8f00e4040a7c7")],
tags: [ObjectId("5f9c93f74ea8f00e4040a7c8")],
created_at: new Date(),
updated_at: new Date()
},
{
name: "Product 2",
description: "This is another sample product",
price: 19.99,
categories: [ObjectId("5f9c93e44ea8f00e4040a7c5")],
brands: [ObjectId("5f9c93f74ea8f00e4040a7c6")],
segments: [ObjectId("5f9c93f74ea8f00e4040a7c7")],
tags: [ObjectId("5f9c93f74ea8f00e4040a7c8")],
created_at: new Date(),
updated_at: new Date()
}
])

// Categories Collection

db.createCollection("Categories")
db.Categories.insertMany([
{
name: "Category 1",
parent_category: null,
created_at: new Date(),
updated_at: new Date()
},
{
name: "Category 1.1",
parent_category: ObjectId("5f9c93e44ea8f00e4040a7c5"),
created_at: new Date(),
updated_at: new Date()
}
])

// Brands Collection

db.createCollection("Brands")
db.Brands.insertMany([
{
name: "Apple",
created_at: new Date(),
updated_at: new Date()
},
{
name: "Samsung",
created_at: new Date(),
updated_at: new Date()
}
])

// Creating Segments Collection

db.createCollection("segments")
db.segments.insertMany([
{
name: "High End",
created_at: new Date(),
updated_at: new Date()
},
{
name: "Mid Range",
created_at: new Date(),
updated_at: new Date()
}
])

//Simple Queries to perform on ecommerce db

1.
db.Orders.aggregate([
{
$group: {
_id: null,
average_order_value: { $avg: "$total_amount" }
}
}
])

2.
db.Orders.aggregate([
{
$lookup: {
from: "Customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_info"
}
},
{
$match: {
"customer_info.created_at": {
$gte: new Date(new Date() - 2 * 30 * 24 * 60 * 60 * 1000)
}
}
},
{
$group: {
_id: "$customer_id",
total_amount: { $sum: "$total_amount" }
}
},
{ $sort: { total_amount: -1 } },
{ $limit: 10 }
])

3.
db.Orders.aggregate([
{
$match: {
created_at: {
$gte: new Date(new Date().getFullYear(), new Date().getMonth(), 1),
$lt: new Date(new Date().getFullYear(), new Date().getMonth() + 1, 1)
}
}
},
{
$group: {
_id: null,
total_amount: { $sum: "$total_amount" }
}
}
])

4.
db.Customers.aggregate([
{
$group: {
_id: "$address.city",
count: { $sum: 1 }
}
}
])

5.
db.Orders.aggregate([
{
$lookup: {
from: "Products",
localField: "product_id",
foreignField: "_id",
as: "product_info"
}
},
{
$lookup: {
from: "Customers",
localField: "customer_id",
foreignField: "_id",
as: "customer_info"
}
},
{
$group: {
_id: "$product_info.seller_id",
total_sold: { $sum: "$quantity" },
city: { $first: "$customer_info.address.city" }
}
},
{
$lookup: {
from: "Sellers",
localField: "_id",
foreignField: "_id",
as: "seller_info"
}
}
])