-- SQL: data split across 3 tables, need JOIN to reconstruct
SELECT o.id, u.name, p.title, oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;

// MongoDB: one read, all data together
db.orders.findOne({ _id: orderId });
// Returns: { _id, user: { name, email }, items: [{ title, qty, price }] }

{
  _id:        ObjectId("507f1f77bcf86cd799439011"), // 12-byte unique ID
  createdAt:  new Date("2026-01-15"),          // ISODate — NOT a string
  price:      Decimal128("19.99"),             // Precise decimal (no float errors)
  views:      NumberLong(9999999999),           // 64-bit integer
  data:       BinData(0, "base64string"),       // Binary blob (images, files)
  pattern:   RegExp(/^mongo/i),               // Stored regex
  isActive:  true                               // Boolean (same as JSON)
}

{
  _id: ObjectId("507f1f77bcf86cd799439011"),   // auto-generated unique key
  name: "MongoDB Mastery",                       // String
  price: Decimal128("49.99"),                    // Precise decimal
  inStock: true,                                 // Boolean
  tags: ["mongodb", "nosql", "database"],        // Array
  publishedAt: new Date("2026-01-01"),           // BSON Date
  author: {                                       // Embedded subdocument
    name: "Yogesh Tiwari",
    email: "yogesh@example.com"
  },
  reviews: [                                      // Array of subdocuments
    { user: "alice", rating: 5, comment: "Great!" },
    { user: "bob",   rating: 4, comment: "Good"   }
  ],
  metadata: null                                 // null value
}

// Create a capped collection — max 1000 docs or 5MB
db.createCollection("auditLog", {
  capped: true,
  size: 5242880,   // 5MB in bytes
  max:  1000        // optional: max document count
});

const { ObjectId } = require('mongodb');

const id = new ObjectId();

console.log(id.toString());      // "507f1f77bcf86cd799439011"
console.log(id.getTimestamp()); // 2026-01-01T00:00:00.000Z — embedded creation time!

// IMPORTANT: always query with ObjectId, not a string
db.collection('users').findOne({ _id: new ObjectId("507f1f77bcf86cd799439011") });
// NOT: { _id: "507f1f77bcf86cd799439011" } ← this won't match!

// All 3 can coexist in the same collection — MongoDB won't complain
db.users.insertMany([
  { name: "Alice", email: "alice@example.com", age: 30 },
  { name: "Bob",   phone: "+91-9999"                      },  // no email or age
  { fullName: "Charlie D", age: "twenty-five"           }   // age as string!
]);

db.createCollection("users", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["name", "email"],
      properties: {
        name:  { bsonType: "string",  description: "must be a string" },
        email: { bsonType: "string",  pattern: "^.+@.+$"           },
        age:   { bsonType: "int",     minimum: 0, maximum: 150        }
      }
    }
  },
  validationAction: "error"  // "warn" just logs, "error" rejects invalid inserts
});

// ── EMBEDDING: address inside user ──
{
  _id: ObjectId("..."),
  name: "Alice",
  address: {                    // embedded — always fetched with user
    street: "123 Main St",
    city: "Chandigarh",
    zip: "160001"
  }
}

// ── REFERENCING: author as a separate document ──
// posts collection
{
  _id: ObjectId("post1"),
  title: "MongoDB Guide",
  authorId: ObjectId("user1")  // reference — lookup separately
}
// users collection
{ _id: ObjectId("user1"), name: "Alice", email: "alice@example.com" }

// Insert a single document
db.users.insertOne({
  name: "Alice",
  email: "alice@example.com",
  age: 30,
  createdAt: new Date()
});
// Returns: { acknowledged: true, insertedId: ObjectId("...") }

// Insert multiple documents in one round-trip
db.users.insertMany([
  { name: "Bob",     email: "bob@example.com",     age: 25 },
  { name: "Charlie", email: "charlie@example.com", age: 35 }
], { ordered: false }); // ordered:false = continue on partial error

// Find one document matching filter
db.users.findOne({ email: "alice@example.com" });

// Find all documents matching filter
db.users.find({ age: { $gte: 25 } })
         .sort({ name: 1 })   // 1 = ascending, -1 = descending
         .limit(10)
         .skip(0);

// Count matching documents
db.users.countDocuments({ age: { $gte: 25 } });

// Update one matching document
db.users.updateOne(
  { email: "alice@example.com" },      // filter
  { $set: { age: 31, updatedAt: new Date() } } // update
);

// Update all matching documents
db.users.updateMany(
  { age: { $lt: 18 } },
  { $set: { isMinor: true } }
);

// Delete one document
db.users.deleteOne({ email: "bob@example.com" });

// Delete all matching documents
db.users.deleteMany({ isActive: false });

// ⚠ deleteMany({}) deletes EVERY document in the collection!

const cursor = db.collection('users').find({ age: { $gte: 18 } });

// Pattern 1: toArray() — loads all into memory (fine for small sets)
const users = await cursor.toArray();

// Pattern 2: async iterator — streams one at a time (large result sets)
for await (const user of cursor) {
  processUser(user); // handle each document without loading all into memory
}

// Always close the cursor when done if not exhausted
await cursor.close();

// Original document:
// { _id: 1, name: "Alice", email: "alice@x.com", age: 30, role: "admin" }

// updateOne — ONLY changes age, everything else untouched
db.users.updateOne(
  { _id: 1 },
  { $set: { age: 31 } }
);
// Result: { _id: 1, name: "Alice", email: "alice@x.com", age: 31, role: "admin" }

// replaceOne — ENTIRE document is replaced (only _id survives)
db.users.replaceOne(
  { _id: 1 },
  { name: "Alice", age: 31 }  // no $set — this IS the new document
);
// Result: { _id: 1, name: "Alice", age: 31 }  ← email and role are GONE!

// $set — add or overwrite specific fields
db.products.updateOne({ _id: 1 }, {
  $set: { price: 29.99, updatedAt: new Date() }
});

// $unset — remove a field entirely (value is irrelevant, use "")
db.products.updateOne({ _id: 1 }, {
  $unset: { legacyField: "" }
});

// $inc — atomically increment (or decrement with negative value)
db.products.updateOne({ _id: 1 }, {
  $inc: { stock: -1, soldCount: 1 }  // decrement stock, increment sales
});

// $rename — rename a field
db.users.updateMany({}, {
  $rename: { fullname: "name" }
});

// $push — append an element to an array (allows duplicates)
db.posts.updateOne({ _id: 1 }, {
  $push: { tags: "mongodb" }
});

// $push with $each — append multiple elements at once
db.posts.updateOne({ _id: 1 }, {
  $push: { tags: { $each: ["nosql", "database"] } }
});

// $addToSet — append only if not already present (no duplicates)
db.posts.updateOne({ _id: 1 }, {
  $addToSet: { tags: "mongodb" }  // ignored if "mongodb" already exists
});

// $pull — remove all elements matching a condition
db.posts.updateOne({ _id: 1 }, {
  $pull: { tags: "nosql" }
});

// $pop — remove first (-1) or last (1) element of an array
db.posts.updateOne({ _id: 1 }, {
  $pop: { tags: -1 }  // remove first element
});

// $eq — explicit (same as shorthand)
db.users.find({ age: { $eq: 30 } });
db.users.find({ age: 30 }); // shorthand — same result

// $gt / $lte — range query (users aged 18–65)
db.users.find({ age: { $gte: 18, $lte: 65 } });

// $in — match multiple values (like SQL IN)
db.users.find({ status: { $in: ["active", "pending"] } });

// $nin — exclude values
db.users.find({ role: { $nin: ["admin", "superuser"] } });

// $ne — not equal
db.orders.find({ status: { $ne: "cancelled" } });

// Combined — products priced between $10 and $50, not out of stock
db.products.find({
  price:  { $gte: 10, $lte: 50 },
  status: { $ne: "out_of_stock" }
});

// $and — implicit (comma-separated conditions default to AND)
db.users.find({ age: { $gte: 18 }, isActive: true }); // implicit $and

// $and — explicit (required when same field appears multiple times)
db.products.find({
  $and: [
    { price: { $gte: 10 } },
    { price: { $lte: 50 } }
  ]
});
// Shorthand: { price: { $gte: 10, $lte: 50 } } (same result)

// $or — match users who are admins OR have premium subscription
db.users.find({
  $or: [
    { role: "admin" },
    { subscription: "premium" }
  ]
});

// $not — users whose age is NOT less than 18
db.users.find({ age: { $not: { $lt: 18 } } });
// Note: $not wraps an operator expression, not a plain value

// $nor — exclude both banned and suspended users
db.users.find({
  $nor: [
    { status: "banned" },
    { status: "suspended" }
  ]
});

// Sample document: { tags: ["mongodb", "nosql", "database"], scores: [85, 92, 78] }

// Match documents where array CONTAINS a value
db.posts.find({ tags: "mongodb" }); // finds docs where tags array includes "mongodb"

// Match documents where array equals EXACTLY this array (same order)
db.posts.find({ tags: ["mongodb", "nosql"] }); // exact match, order matters

// $all — array must contain ALL of these values (any order)
db.posts.find({ tags: { $all: ["mongodb", "nosql"] } });

// $size — array has exactly N elements
db.posts.find({ tags: { $size: 3 } });

// $elemMatch — at least one element matches ALL conditions
db.students.find({
  scores: { $elemMatch: { $gte: 90, $lt: 100 } }
});

// Sample document: { address: { city: "Chandigarh", zip: "160001" } }

// Dot notation to query nested fields
db.users.find({ "address.city": "Chandigarh" });
db.users.find({ "address.zip": { $in: ["160001", "160017"] } });

// Array of subdocuments — $elemMatch for subdoc conditions
// Sample: { orders: [{ product: "Book", qty: 2 }, { product: "Pen", qty: 5 }] }
db.users.find({
  orders: {
    $elemMatch: { product: "Book", qty: { $gte: 2 } }
  }
});

// Update a specific array element by index using dot notation
db.users.updateOne(
  { _id: 1 },
  { $set: { "address.city": "Delhi" } }
);

// INCLUSION mode (1) — only return specified fields + _id
db.users.find({}, { name: 1, email: 1 });
// Returns: { _id: ..., name: "Alice", email: "alice@x.com" }

// Suppress _id explicitly (the only field you can mix include + exclude)
db.users.find({}, { name: 1, email: 1, _id: 0 });
// Returns: { name: "Alice", email: "alice@x.com" }

// EXCLUSION mode (0) — return everything EXCEPT specified fields
db.users.find({}, { password: 0, __v: 0 });
// Returns all fields except password and __v

// Nested field projection with dot notation
db.users.find({}, { "address.city": 1, name: 1 });

// Array element projection — return only first matching element
db.posts.find(
  { tags: "mongodb" },
  { "tags.$": 1 }  // $ projects only the first matching array element
);

// Upsert: update if exists, insert if not
db.pageViews.updateOne(
  { url: "/home" },                          // filter
  { $inc: { views: 1 }, $set: { lastSeen: new Date() } }, // update
  { upsert: true }                             // option
);
// First time: inserts { url: "/home", views: 1, lastSeen: Date }
// Every subsequent call: increments views by 1

// $setOnInsert — set fields ONLY on insert, not on update
db.users.updateOne(
  { email: "alice@example.com" },
  {
    $set: { lastLogin: new Date() },
    $setOnInsert: { createdAt: new Date(), role: "user" }
  },
  { upsert: true }
);

// Returns the document BEFORE the update (default)
const before = await db.collection('orders').findOneAndUpdate(
  { status: "pending", assignedTo: null },   // filter
  { $set: { assignedTo: "worker-1", status: "processing" } },
  { sort: { createdAt: 1 } }                 // pick oldest first
);

// Returns the document AFTER the update
const after = await db.collection('users').findOneAndUpdate(
  { email: "alice@example.com" },
  { $inc: { loginCount: 1 } },
  { returnDocument: "after", upsert: true }  // returnDocument: "after" returns updated doc
);

async function claimNextJob(workerId) {
  return db.collection('jobs').findOneAndUpdate(
    { status: "queued" },
    { $set: { status: "running", workerId, startedAt: new Date() } },
    { sort: { priority: -1, createdAt: 1 }, returnDocument: "after" }
  );
  // If null returned — no jobs available. Otherwise the claimed job.
}

db.orders.aggregate([
  // Stage 1: filter — uses index on (status, createdAt)
  { $match: { status: "completed", createdAt: { $gte: new Date("2024-01-01") } } },
  // Stage 2: group by customer, sum revenue
  { $group: { _id: "$customerId", total: { $sum: "$amount" }, count: { $sum: 1 } } },
  // Stage 3: sort by total descending
  { $sort: { total: -1 } },
  // Stage 4: top 5 customers only
  { $limit: 5 }
]);

// Simple equality
{ $match: { status: "active" } }

// Range filter — uses compound index on (status, createdAt)
{ $match: {
  status: "completed",
  createdAt: { $gte: new Date("2024-01-01"), $lt: new Date("2025-01-01") }
} }

// $expr — compare two fields within the same document
{ $match: { $expr: { $gt: ["$revenue", "$cost"] } } }

// Array contains element (uses multikey index)
{ $match: { tags: "featured" } }

// $text search (requires text index)
{ $match: { $text: { $search: "mongodb aggregation" } } }

// Two readable stages — optimizer merges into one:
[{ $match: { status: "active"  } },
 { $match: { age:    { $gte: 18 } } }]

// Executed as:
[{ $match: { status: "active", age: { $gte: 18 } } }]

{ $group: {
  _id: "<grouping expression>",  // null = single group (grand total)
  field1: { <accumulator>: "<expression>" },
  field2: { <accumulator>: "<expression>" }
} }

// Example: revenue per product category
{ $group: {
  _id:          "$category",
  totalRevenue: { $sum: "$price" },
  avgPrice:     { $avg: "$price" },
  orderCount:   { $sum: 1 },       // count docs in group
  maxPrice:     { $max: "$price" },
  products:     { $push: "$name" }  // collect into array
} }

// First group: daily totals per user
// Second group: monthly total from daily buckets
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
    _id: {
      userId: "$userId",
      day: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } }
    },
    dailyTotal: { $sum: "$amount" }
  }},
  { $group: {
    _id:          "$_id.userId",
    monthlyTotal: { $sum: "$dailyTotal" },
    activeDays:   { $sum: 1 }
  }},
  { $sort: { monthlyTotal: -1 } }
]);

// Include specific fields (_id included by default)
{ $project: { name: 1, email: 1, createdAt: 1 } }

// Exclude _id (common pattern)
{ $project: { _id: 0, name: 1, email: 1 } }

// Compute a new field — fullName from first + last
{ $project: {
  fullName:  { $concat: ["$firstName", " ", "$lastName"] },
  isAdult:   { $gte: ["$age", 18] },
  discounted:{ $multiply: ["$price", 0.9] }
} }

// Rename a field
{ $project: { userId: "$_id", _id: 0 } }

// Conditional field — $cond (ternary)
{ $project: {
  tier: { $cond: { if: { $gt: ["$spend", 1000] }, then: "gold", else: "standard" } }
} }

// Join orders → users on orders.userId = users._id
db.orders.aggregate([
  { $lookup: {
    from:         "users",   // target collection
    localField:   "userId", // field in orders
    foreignField: "_id",    // field in users
    as:           "user"    // output array field
  }},
  // Flatten single-element array → object
  { $unwind: { path: "$user", preserveNullAndEmptyArrays: true } }
]);
// Result: { _id, amount, user: { name, email, ... } }

db.orders.aggregate([
  { $lookup: {
    from: "products",
    let:  { pid: "$productId", minQty: "$quantity" }, // expose local fields
    pipeline: [
      { $match: { $expr: { $and: [
          { $eq:  ["$_id",    "$$pid"]    },
          { $gte: ["$stock", "$$minQty"] }  // only in-stock
      ]}}},
      { $project: { name: 1, price: 1, _id: 0 } }
    ],
    as: "product"
  }}
]);

// Input: { _id: 1, items: ["book", "pen", "notebook"] }

{ $unwind: "$items" }

// Output — 3 documents:
// { _id: 1, items: "book"     }
// { _id: 1, items: "pen"      }
// { _id: 1, items: "notebook" }

// Full options form
{ $unwind: {
  path:                       "$items",
  includeArrayIndex:          "itemIndex",  // adds 0-based position field
  preserveNullAndEmptyArrays: true          // keep doc if array is [] or null
} }

db.products.aggregate([
  { $unwind: "$tags" },                               // one doc per tag
  { $group:  { _id: "$tags", count: { $sum: 1 } } },  // count per tag
  { $sort:  { count: -1 } },
  { $limit: 10 }
]);

const [result] = await db.collection('products').aggregate([
  // Filter once — same set flows into ALL facets
  { $match: { category: "electronics", inStock: true } },

  { $facet: {
    // Paginated product list
    products: [
      { $sort:    { price: 1 } },
      { $skip:    0 },
      { $limit:   20 },
      { $project: { name: 1, price: 1, thumbnail: 1 } }
    ],
    // Total count for pagination controls
    totalCount: [
      { $count: "total" }
    ],
    // Brand breakdown for sidebar filter
    byBrand: [
      { $group: { _id: "$brand", count: { $sum: 1 } } },
      { $sort:  { count: -1 } }
    ],
    // Price histogram for range slider
    priceRanges: [
      { $bucket: {
        groupBy:    "$price",
        boundaries: [0, 50, 100, 250, 500, 1000],
        default:    "1000+",
        output:     { count: { $sum: 1 } }
      }}
    ]
  }}
]).toArray();

// result.products    → 20 products for page 1
// result.totalCount  → [{ total: 342 }]
// result.byBrand     → [{ _id: "Apple", count: 45 }, ...]
// result.priceRanges → [{ _id: 0, count: 12 }, { _id: 50, count: 38 }, ...]

db.orders.explain("executionStats").aggregate([
  { $match: { status: "completed", createdAt: { $gte: new Date("2024-01-01") } } },
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } }
]);

// Key things to look for in output:
// winningPlan.inputStage.stage → "IXSCAN" (good) or "COLLSCAN" (bad)
// executionStats.nReturned vs totalDocsExamined
//   → ratio near 1.0 = efficient; large gap = missing index

const page     = 3;
const pageSize = 20;

db.collection('products').aggregate([
  { $match:  { category: "books" } },
  { $sort:   { createdAt: -1 } },
  { $skip:   (page - 1) * pageSize },  // skip first (page-1)*pageSize docs
  { $limit:  pageSize }
]);

const [result] = await db.collection('products').aggregate([
  { $match: { category: "books" } },
  { $sort:  { createdAt: -1 } },
  { $facet: {
    data:  [{ $skip: 40 }, { $limit: 20 }],  // page 3
    total: [{ $count: "count" }]
  }}
]).toArray();

const total      = result.total[0]?.count ?? 0;
const totalPages  = Math.ceil(total / 20);
const data        = result.data;

// Use the last doc's indexed fields as the cursor
db.collection('products').aggregate([
  { $match: {
    category: "books",
    $or: [
      { createdAt: { $lt: lastDate } },
      { createdAt: lastDate, _id: { $lt: lastId } }  // tie-break
    ]
  }},
  { $sort:  { createdAt: -1, _id: -1 } },
  { $limit: 20 }
]);

{
  _id:        ObjectId("..."),
  status:     "completed",
  createdAt:  new Date("2024-03-15"),
  items: [
    { productId: "p-1", name: "Laptop", category: "Electronics", qty: 1, price: 999 },
    { productId: "p-2", name: "Mouse",  category: "Electronics", qty: 2, price: 29  }
  ]
}

const report = await db.collection('orders').aggregate([

  // 1. Filter — uses index on (status, createdAt)
  { $match: {
    status:    "completed",
    createdAt: { $gte: new Date("2024-01-01"), $lt: new Date("2025-01-01") }
  }},

  // 2. Extract month from createdAt
  { $addFields: {
    month: { $dateToString: { format: "%Y-%m", date: "$createdAt" } }
  }},

  // 3. Unwind items — one doc per line item
  { $unwind: "$items" },

  // 4. Compute line-item revenue (qty × price)
  { $addFields: {
    "items.revenue": { $multiply: ["$items.qty", "$items.price"] }
  }},

  // 5. Group by month + category
  { $group: {
    _id:          { month: "$month", category: "$items.category" },
    totalRevenue: { $sum:      "$items.revenue" },
    orderIds:     { $addToSet: "$_id" },       // unique orders
    products:     { $push: {
      name: "$items.name", id: "$items.productId", revenue: "$items.revenue"
    }}
  }},

  // 6. Derive counts and averages from grouped data
  { $addFields: {
    orderCount:   { $size: "$orderIds" },
    avgOrderValue: { $divide: ["$totalRevenue", { $size: "$orderIds" }] }
  }},

  // 7. Sort products array and take top 3
  { $addFields: {
    top3Products: {
      $slice: [
        { $sortArray: { input: "$products", sortBy: { revenue: -1 } } },
        3
      ]
    }
  }},

  // 8. Shape the final output
  { $project: {
    _id:           0,
    month:         "$_id.month",
    category:      "$_id.category",
    totalRevenue:  { $round: ["$totalRevenue", 2] },
    orderCount:    1,
    avgOrderValue: 1,
    top3Products:  1
  }},

  // 9. Final sort — chronological, highest revenue first within month
  { $sort: { month: 1, totalRevenue: -1 } }

], { allowDiskUse: true }).toArray();

// Create — rolling background build in production
db.users.createIndex({ email: 1 }, { unique: true, name: "email_unique" })

// List all indexes on a collection
db.users.getIndexes()

// Get index sizes
db.users.stats().indexSizes

// Drop a specific index
db.users.dropIndex("email_unique")

// Hide an index (test impact before dropping — MongoDB 4.4+)
db.runCommand({ collMod: "users", index: { name: "email_unique", hidden: true } })

// Index: { status: 1, customerId: 1, createdAt: -1 }
db.orders.createIndex({ status: 1, customerId: 1, createdAt: -1 })

// ✅ Uses index — matches prefix (status)
db.orders.find({ status: "completed" })

// ✅ Uses index — matches prefix (status, customerId)
db.orders.find({ status: "completed", customerId: "c-1" })

// ✅ Uses index — full prefix match
db.orders.find({ status: "completed", customerId: "c-1" }).sort({ createdAt: -1 })

// ❌ Does NOT use index — skips status (first field)
db.orders.find({ customerId: "c-1" })

// Query: { status: "active", createdAt: { $gte: lastWeek } }, sort: { score: -1 }

// ❌ Wrong order — range before sort
db.users.createIndex({ status: 1, createdAt: 1, score: -1 })

// ✅ ESR order — equality(status), sort(score), range(createdAt)
db.users.createIndex({ status: 1, score: -1, createdAt: 1 })
// Result: index provides sort order (no in-memory sort) and covers range

// Index contains: status, customerId, amount
db.orders.createIndex({ status: 1, customerId: 1, amount: 1 })

// ✅ COVERED — filter and projection only use indexed fields
//            _id must be excluded (it's not in this index)
db.orders.find(
  { status: "completed", customerId: "c-1" },  // filter
  { amount: 1, _id: 0 }                        // projection — exclude _id!
)

// ❌ NOT covered — requests a field not in the index
db.orders.find(
  { status: "completed" },
  { amount: 1, createdAt: 1, _id: 0 }  // createdAt not in index → fetch doc
)

db.orders.find(
  { status: "completed", customerId: "c-1" },
  { amount: 1, _id: 0 }
).explain("executionStats")

// Look for in executionStats:
// totalDocsExamined: 0   ← key indicator — no documents fetched
// totalKeysExamined: N   ← only index keys scanned
// stage: "PROJECTION_COVERED"  ← confirms covered execution

// If totalDocsExamined > 0 → NOT covered → check if projection
// requests any field outside the index (including _id by default)

const exp = db.orders.find({ status: "completed" }).explain("executionStats")

// ── queryPlanner section ──
exp.queryPlanner.winningPlan.stage
// "COLLSCAN" → no index used (bad for large collections)
// "IXSCAN"   → index scan (good)
// "FETCH"    → fetched docs from collection after IXSCAN
// "PROJECTION_COVERED" → served entirely from index (best)
// "SORT"     → in-memory sort (potential performance issue)

exp.queryPlanner.winningPlan.inputStage.indexName
// Which index was selected

// ── executionStats section ──
exp.executionStats.nReturned          // docs returned to caller
exp.executionStats.totalDocsExamined   // docs fetched from collection
exp.executionStats.totalKeysExamined   // index entries scanned
exp.executionStats.executionTimeMillis // total execution time

// Efficiency ratios to check:
// nReturned / totalDocsExamined → close to 1.0 = efficient
// nReturned / totalKeysExamined → close to 1.0 = good index selectivity
// totalDocsExamined = 0         → covered query (best possible)

// Delete sessions 24 hours after createdAt
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 86400 }  // 24 × 60 × 60
)

// Delete at a specific time — set expiresAt to the exact expiry date
// expireAfterSeconds: 0 means "expire at the date stored in the field"
db.tokens.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 0 }
)
// Document: { token: "abc", expiresAt: new Date("2024-12-31") }
// → deleted after Dec 31 2024

// Change TTL on existing index (no need to drop/recreate)
db.runCommand({
  collMod: "sessions",
  index: { keyPattern: { createdAt: 1 }, expireAfterSeconds: 3600 }
})

// Create index on tags field
db.products.createIndex({ tags: 1 })
// If any document has tags: ["a", "b"], this becomes a multikey index

// Documents:
// { _id: 1, tags: ["mongodb", "nosql"] }
// { _id: 2, tags: ["mongodb", "atlas"] }

// Index entries created:
// "atlas"   → doc 2
// "mongodb" → doc 1, doc 2
// "nosql"   → doc 1

// These queries all use the multikey index:
db.products.find({ tags: "mongodb" })              // ✅ element match
db.products.find({ tags: { $in: ["atlas", "nosql"] } })  // ✅ $in
db.products.find({ tags: { $all: ["mongodb", "nosql"] } }) // ✅ $all

// Create text index on title and body fields
db.articles.createIndex({ title: "text", body: "text" })

// Weight fields — title matches count 3× more than body matches
db.articles.createIndex(
  { title: "text", body: "text" },
  { weights: { title: 3, body: 1 } }
)

// Basic search
db.articles.find({ $text: { $search: "mongodb aggregation" } })

// Search with relevance score sorted
db.articles.find(
  { $text: { $search: "mongodb aggregation" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })

// Exact phrase (wrap in quotes)
db.articles.find({ $text: { $search: '"aggregation pipeline"' } })

// Exclude a word
db.articles.find({ $text: { $search: "mongodb -mapreduce" } })

// See how many times each index has been used since last restart
db.orders.aggregate([{ $indexStats: {} }])
// Output: { name, key, accesses: { ops, since } }
// ops: 0 → index never used → candidate for removal

// Check index sizes (are they fitting in RAM?)
db.orders.stats({ indexDetails: true }).indexSizes

// Hide index before dropping (MongoDB 4.4+ — safer than direct drop)
// Hidden indexes are maintained but not used by the planner
// Monitor for 1–2 weeks, then drop if no performance regression
db.runCommand({ collMod: "orders", index: { name: "old_index", hidden: true } })

// Enable the query profiler to find slow queries (level 2 = all queries)
db.setProfilingLevel(1, { slowms: 100 })  // log queries > 100ms
db.system.profile.find().sort({ millis: -1 }).limit(5)

// ✅ Good embed: line items always read with the order
{
  _id: ObjectId("..."),
  customerId: "u-123",
  status: "completed",
  createdAt: new Date(),
  shippingAddress: {          // ✅ embed — always read with order
    street: "123 Main St",
    city: "Austin", zip: "78701"
  },
  items: [                     // ✅ embed — bounded, read together
    { productId: "p-1", name: "Laptop", qty: 1, price: 999 },
    { productId: "p-2", name: "Mouse",  qty: 2, price: 29  }
  ]
  // customerId is a REFERENCE — user profile read separately
}

// users collection
{ _id: "u-123", name: "Alice", email: "alice@example.com" }

// orders collection — references user by ID
{ _id: ObjectId("..."), customerId: "u-123", amount: 1057 }

// ✅ Why reference works here:
//  - User profile updated independently of orders
//  - One user can have thousands of orders (unbounded)
//  - Order list page and user profile page read separately

// Person → phone numbers (always < 5, read together)
{
  _id: "u-1",
  name: "Alice",
  phones: [
    { type: "mobile", number: "555-0101" },
    { type: "work",   number: "555-0102" }
  ]
}
// ✅ Single read, atomic update, no join needed
// ❌ Cannot query phones independently

// Publisher → books (dozens of books per publisher)
// publishers collection
{ _id: "pub-1", name: "O'Reilly", bookIds: ["b-1", "b-2", "b-3"] }

// books collection
{ _id: "b-1", title: "MongoDB: The Definitive Guide", publisherId: "pub-1" }

// ✅ Books queried independently; publisher list clean
// ❌ Need $lookup or two round-trips to get full book data
// ❌ bookIds array grows — risky if publisher has thousands of books

// Host → log events (millions of events per host)
// hosts collection
{ _id: "host-1", hostname: "web-01", ip: "10.0.0.1" }

// logs collection — each log references its host
{ _id: ObjectId("..."), hostId: "host-1", level: "error", msg: "...", ts: new Date() }

// ✅ Scales to unlimited events per host
// ✅ Each log queryable and indexable independently
// ❌ Joining host info requires $lookup

// Products ↔ Tags — query "all products with tag X" most often
// Store tag IDs on the product; tags collection stays clean

// products collection
{ _id: "p-1", name: "Laptop", tagIds: ["tag-nosql", "tag-hardware"] }

// tags collection
{ _id: "tag-nosql", label: "NoSQL", colour: "green" }

// ✅ "Products with tag X" → db.products.find({ tagIds: "tag-nosql" })
// ✅ Only one document to update on enrol
// ❌ "All tags for product X" needs $lookup or two queries

// Orders ↔ Products — embed just the fields you display
{
  _id: ObjectId("..."),
  items: [
    {
      productId: "p-1",         // reference (for lookups)
      name: "Laptop",           // snapshot of name at purchase time
      price: 999               // snapshot of price at purchase time
    }
  ]
}
// ✅ Order page renders without any join
// ✅ Price is preserved as it was at purchase — correct
// ❌ Product name change doesn't update old orders — by design

// 1 document per sensor reading — 1M readings/day = 1M tiny docs
{ sensorId: "s-1", ts: new Date(), temp: 22.3, humidity: 45 }
{ sensorId: "s-1", ts: new Date(), temp: 22.4, humidity: 46 }
// Problem: huge index overhead, slow hourly aggregations

// One bucket = one hour of readings for one sensor
{
  _id: "s-1_2024-03-15T14",     // sensorId + hour as natural key
  sensorId: "s-1",
  hour: new Date("2024-03-15T14:00:00Z"),
  count: 60,                    // number of readings in this bucket
  sum: { temp: 1342, humidity: 2730 },  // running totals for avg
  min: { temp: 21.8, humidity: 44   },
  max: { temp: 23.1, humidity: 48   },
  readings: [                   // raw data — up to 60 per bucket
    { ts: new Date("2024-03-15T14:00:00Z"), temp: 22.3, humidity: 45 },
    { ts: new Date("2024-03-15T14:01:00Z"), temp: 22.4, humidity: 46 }
  ]
}

// Insert a new reading — $push + update running totals atomically
db.sensors.updateOne(
  { _id: "s-1_2024-03-15T14", count: { $lt: 60 } },
  {
    $push: { readings: { ts: new Date(), temp: 22.5, humidity: 47 } },
    $inc:  { count: 1, "sum.temp": 22.5, "sum.humidity": 47 },
    $min:  { "min.temp": 22.5 },
    $max:  { "max.temp": 22.5 }
  },
  { upsert: true }
)

// ❌ Pure reference — order list page needs JOIN to show customer name
{
  _id: ObjectId("..."),
  customerId: "u-123",      // reference only
  amount: 1057
}

// ✅ Extended reference — order list page renders without any join
{
  _id: ObjectId("..."),
  customer: {
    _id:   "u-123",          // keep the reference
    name:  "Alice Smith",    // snapshot — displayed on every order
    email: "alice@example.com" // snapshot — used for notifications
    // NOT: address, preferences, etc. — only what THIS doc needs
  },
  amount: 1057,
  status: "completed"
}

// When customer's name changes — update only recent/open orders
// Historical orders can keep the old name (which may be intentional)

// When a customer changes their name, update recent open orders
await db.collection('orders').updateMany(
  {
    "customer._id": "u-123",
    status: { $in: ["pending", "processing"] }  // only open orders
  },
  { $set: { "customer.name": "Alice Johnson" } }
)
// Completed orders intentionally keep the old name — historical record

async function getReviews(bookId, page = 1) {
  const book = await db.collection('books').findOne({ _id: bookId });

  if (!book.hasOverflow) {
    return book.reviews;  // fast path — embedded data, no extra query
  }

  // Slow path — fetch from overflow collection with pagination
  return db.collection('book_reviews_overflow')
    .find({ bookId })
    .skip((page - 1) * 20)
    .limit(20)
    .toArray();
}

db.createCollection("users", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["name", "email", "createdAt"],
      properties: {
        name: {
          bsonType: "string",
          minLength: 2,
          maxLength: 100,
          description: "Name is required and must be 2–100 chars"
        },
        email: {
          bsonType: "string",
          pattern: "^[^@]+@[^@]+\\.[^@]+$"
        },
        age: {
          bsonType: "int",
          minimum: 0,
          maximum: 130
        },
        role: {
          bsonType: "string",
          enum: ["admin", "editor", "viewer"]
        },
        address: {
          bsonType: "object",
          required: ["city", "zip"],
          properties: {
            city: { bsonType: "string" },
            zip:  { bsonType: "string", pattern: "^[0-9]{5}$" }
          }
        }
      }
    }
  },
  validationLevel:  "strict",  // "strict" (all writes) or "moderate" (existing docs exempt)
  validationAction: "error"   // "error" (reject) or "warn" (log, allow)
})

// All three field changes are atomic — no partial state visible
db.orders.updateOne(
  { _id: orderId },
  {
    $set: { status: "shipped", shippedAt: new Date() },
    $inc: { version: 1 }
  }
)
// A concurrent reader sees either the old state OR the new state
// — never status="shipped" with the old shippedAt

// w:majority + j:true — strongest durability guarantee
// Write survives majority failover AND primary crash before journal flush
await db.collection('payments').insertOne(
  { amount: 500, currency: "USD" },
  { writeConcern: { w: "majority", j: true, wtimeout: 5000 } }
)

// High-throughput event logging — speed over safety
await db.collection('events').insertOne(
  { type: "page_view", url: "/home" },
  { writeConcern: { w: 0 } }
)

// Set default write concern at client level (recommended)
const client = new MongoClient(uri, {
  writeConcern: { w: "majority", j: true }
})

// Default — fast, may read un-committed data
db.collection('products').findOne({ _id: id })

// Majority — only read data that won't be rolled back
db.collection('payments').findOne(
  { _id: id },
  { readConcern: { level: "majority" } }
)

// Linearizable — strongest; single-document reads only
// Waits for majority acknowledgment before returning
db.collection('inventory').findOne(
  { _id: productId },
  { readConcern: { level: "linearizable" }, maxTimeMS: 5000 }
)
// Use when: "is there still stock?" before a purchase commitment

async function transferFunds(fromId, toId, amount) {
  const session = client.startSession();

  try {
    await session.withTransaction(async () => {
      const accounts = db.collection('accounts');

      // Step 1: Debit source account — check balance atomically
      const from = await accounts.findOneAndUpdate(
        { _id: fromId, balance: { $gte: amount } },  // only if sufficient funds
        { $inc: { balance: -amount } },
        { session, returnDocument: "after" }
      );
      if (!from) throw new Error("Insufficient funds");

      // Step 2: Credit destination account
      await accounts.updateOne(
        { _id: toId },
        { $inc: { balance: amount } },
        { session }
      );

      // Step 3: Write audit record
      await db.collection('transfers').insertOne(
        { fromId, toId, amount, ts: new Date() },
        { session }
      );
    }, {
      readConcern:  { level: "snapshot"  },
      writeConcern: { w: "majority" },
      maxCommitTimeMS: 5000
    });
    // withTransaction automatically retries transient errors
    // (TransientTransactionError) and commit errors (UnknownTransactionCommitResult)

  } finally {
    await session.endSession();
  }
}

// ❌ Race condition — two clients can both read 5, both write 6
const product = await findOne({ _id: id });
await updateOne({ _id: id }, { $set: { stock: product.stock - 1 } });

// ✅ Atomic — database decrements in one operation, no race
await db.collection('products').updateOne(
  { _id: id, stock: { $gt: 0 } },  // guard: only decrement if in stock
  { $inc: { stock: -1 } }
)
// If matchedCount === 0 → out of stock

// Document has a __v (version) field
// { _id: "p-1", stock: 10, price: 99, __v: 5 }

async function updateProductPrice(productId, newPrice) {
  while (true) {
    // Step 1: Read current document including version
    const product = await db.collection('products').findOne({ _id: productId });
    if (!product) throw new Error("Not found");

    // Step 2: Business logic with the current state
    const newDoc = { ...product, price: newPrice };

    // Step 3: Write — only succeeds if __v hasn't changed
    const result = await db.collection('products').updateOne(
      { _id: productId, __v: product.__v },  // version guard
      { $set: { price: newPrice }, $inc: { __v: 1 } }  // increment version
    );

    if (result.matchedCount === 1) return;  // success

    // matchedCount === 0 → someone else updated — retry
    // In practice: add retry limit + exponential backoff
  }
}

const changeStream = db.collection('orders').watch(
  // Optional pipeline to filter events
  [{ $match: { operationType: { $in: ["insert", "update"] } } }],
  { fullDocument: "updateLookup" }  // include full doc on updates
);

for await (const change of changeStream) {
  console.log(change.operationType);   // "insert" | "update" | "delete" | "replace"
  console.log(change.fullDocument);   // the new document state
  console.log(change.updateDescription);// { updatedFields, removedFields }
  console.log(change._id);             // resume token — store this
}

// Resumable stream — restart from a saved token after disconnect
const resumableStream = db.collection('orders').watch([], {
  resumeAfter: savedResumeToken  // pick up exactly where you left off
});

// Application writes order + outbox event in one transaction
await session.withTransaction(async () => {
  await db.collection('orders').insertOne({ ...orderData }, { session });
  await db.collection('outbox').insertOne(
    { event: "ORDER_PLACED", payload: orderData, published: false },
    { session }
  );
});

// Separate publisher process watches outbox and publishes to Kafka
const stream = db.collection('outbox').watch([
  { $match: { operationType: "insert" } }
]);
for await (const evt of stream) {
  await kafka.publish(evt.fullDocument.event, evt.fullDocument.payload);
  await db.collection('outbox').updateOne({ _id: evt.fullDocument._id }, { $set: { published: true } });
}

// ── Technique 1: Natural idempotent key with upsert ──
// Payment identified by idempotencyKey — safe to retry
await db.collection('payments').updateOne(
  { idempotencyKey: req.headers['idempotency-key'] },
  { $setOnInsert: {
    amount: req.body.amount,
    customerId: req.body.customerId,
    createdAt: new Date()
  }},
  { upsert: true }
)
// $setOnInsert only runs if a new doc is created
// If idempotencyKey already exists → matched, nothing changes

// ── Technique 2: Conditional update — only update if state allows ──
// State machine guard prevents double-processing
const result = await db.collection('orders').updateOne(
  { _id: orderId, status: "pending" },   // guard: only from pending
  { $set: { status: "processing", processedAt: new Date() } }
)
// Second call: status is already "processing" → filter doesn't match
// matchedCount === 0 is NOT an error — it's already processed

// ── Technique 3: $addToSet for deduplication ──
// Tag can only be added once regardless of how many times called
await db.collection('products').updateOne(
  { _id: productId },
  { $addToSet: { tags: "featured" } }  // idempotent — duplicate safe
)

async function processPayment({ orderId, customerId, amount, idempotencyKey }) {
  const session = client.startSession();

  try {
    let payment;

    await session.withTransaction(async () => {

      // ── Step 1: Idempotency check — claim the key inside the transaction ──
      const keyInsert = await db.collection('idempotency_keys').updateOne(
        { key: idempotencyKey },
        { $setOnInsert: { key: idempotencyKey, createdAt: new Date() } },
        { upsert: true, session }
      );

      if (keyInsert.upsertedCount === 0) {
        // Key already exists — fetch and return existing payment
        payment = await db.collection('payments').findOne(
          { idempotencyKey }, { session }
        );
        return;  // abort the rest — already processed
      }

      // ── Step 2: Debit wallet — atomic check-and-debit ──
      const wallet = await db.collection('wallets').findOneAndUpdate(
        { customerId, balance: { $gte: amount } },
        { $inc: { balance: -amount } },
        { session, returnDocument: "after" }
      );
      if (!wallet) throw Object.assign(new Error("Insufficient funds"), { code: "INSUFFICIENT_FUNDS" });

      // ── Step 3: Create payment record ──
      payment = {
        _id: new ObjectId(), orderId, customerId, amount,
        status: "completed", idempotencyKey, createdAt: new Date()
      };
      await db.collection('payments').insertOne(payment, { session });

      // ── Step 4: Write to outbox (change stream will publish to Kafka) ──
      await db.collection('outbox').insertOne({
        event: "ORDER_PAID", orderId, amount, paymentId: payment._id,
        createdAt: new Date()
      }, { session });

    }, { readConcern: { level: "snapshot" }, writeConcern: { w: "majority", j: true } });

    return payment;

  } finally {
    await session.endSession();
  }
}

// ── Raw MongoDB Driver ──
const { MongoClient } = require('mongodb');
const db = client.db('myapp');

// No validation — any shape goes in
await db.collection('users').insertOne({
  name: 999,       // number instead of string — driver won't care
  email: "invalid"  // not an email — driver won't care
});

// ── Mongoose ──
const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  name:  { type: String,  required: true },
  email: { type: String,  required: true, match: /^.+@.+\..+$/ }
});
const User = mongoose.model('User', userSchema);

await new User({ name: 999, email: "invalid" }).save();
// Throws ValidationError: name must be a string, email is invalid

const { Schema, model } = require('mongoose');

const userSchema = new Schema(
  {
    name: {
      type: String,
      required: [true, 'Name is required'],  // custom error message
      trim: true,                               // auto-trim whitespace
      minlength: 2,
      maxlength: 100
    },
    email: {
      type: String,
      required: true,
      unique: true,                            // creates a unique index
      lowercase: true,                         // auto-lowercase before save
      match: [/^.+@.+\..+$/, 'Invalid email']
    },
    age: {
      type: Number,
      min: 0,
      max: 120
    },
    role: {
      type: String,
      enum: ['user', 'admin', 'moderator'],  // only these values allowed
      default: 'user'
    },
    isActive: { type: Boolean, default: true },
    tags: [String],                              // shorthand for array of strings
    address: {                                  // nested subdocument
      city:    String,
      country: { type: String, default: 'India' }
    },
    profileId: { type: Schema.Types.ObjectId, ref: 'Profile' } // reference
  },
  {
    timestamps: true,   // auto adds createdAt and updatedAt
    versionKey: false  // removes the __v field
  }
);

const userSchema = new Schema({ name: String, email: String });

// Model name 'User' → maps to collection 'users' (auto-pluralised, lowercased)
const User = mongoose.model('User', userSchema);

// ── CREATE ──
const user = new User({ name: 'Alice', email: 'alice@x.com' });
await user.save();

// Shorthand (create = new + save)
await User.create({ name: 'Bob', email: 'bob@x.com' });

// ── READ ──
const users    = await User.find({ isActive: true });
const oneUser  = await User.findById(id);
const byEmail  = await User.findOne({ email: 'alice@x.com' });

// ── UPDATE ──
await User.findByIdAndUpdate(id, { $set: { name: 'Alice B' } }, { new: true });

// ── DELETE ──
await User.findByIdAndDelete(id);

const productSchema = new Schema({
  name:     { type: String,  required: true, minlength: 3, maxlength: 200 },
  price:    { type: Number,  required: true, min: 0 },
  category: { type: String,  enum: ['electronics', 'books', 'clothing'] },
  sku:      { type: String,  unique: true, match: /^[A-Z]{3}-\d{4}$/ }
});

const userSchema = new Schema({
  phone: {
    type: String,
    validate: {
      validator: function(v) {
        return /^\+?[\d\s\-]{10,15}$/.test(v);
      },
      message: props => `${props.value} is not a valid phone number`
    }
  },
  password: {
    type: String,
    validate: {
      validator: async function(v) {      // async validator
        return v.length >= 8;
      },
      message: 'Password must be at least 8 characters'
    }
  }
});

// Trigger validators on update operations too
await User.findByIdAndUpdate(id, update, { runValidators: true, new: true });

const bcrypt = require('bcryptjs');

// Hash password BEFORE saving — classic use case
userSchema.pre('save', async function(next) {
  if (!this.isModified('password')) return next(); // only hash if changed
  this.password = await bcrypt.hash(this.password, 12);
  next();
});

// Normalise email before saving
userSchema.pre('save', function(next) {
  this.email = this.email.toLowerCase().trim();
  next();
});

// Pre-find: automatically exclude deleted documents (soft delete)
userSchema.pre(/^find/, function(next) {  // regex matches find, findOne, findById…
  this.where({ isDeleted: { $ne: true } });
  next();
});

// Post-save: send welcome email after user created
userSchema.post('save', async function(doc) {
  if (doc.isNew) {
    await sendWelcomeEmail(doc.email);
  }
});

// Post-find: log slow queries
userSchema.post('find', function(result) {
  console.log(`Found ${result.length} users`);
});

// Post hook with error handling
userSchema.post('save', function(error, doc, next) {
  if (error.name === 'MongoServerError' && error.code === 11000) {
    next(new Error('Email already exists'));  // duplicate key → friendly error
  } else {
    next(error);
  }
});

const userSchema = new Schema({
  firstName: String,
  lastName:  String,
  birthYear: Number
}, { toJSON: { virtuals: true }, toObject: { virtuals: true } });
// ↑ Must enable for virtuals to appear in JSON output

// Virtual getter — computed from other fields
userSchema.virtual('fullName').get(function() {
  return `${this.firstName} ${this.lastName}`;
});

// Virtual setter — split fullName back into parts
userSchema.virtual('fullName').set(function(v) {
  const [first, ...rest] = v.split(' ');
  this.firstName = first;
  this.lastName  = rest.join(' ');
});

// Age virtual — calculated from birthYear
userSchema.virtual('age').get(function() {
  return new Date().getFullYear() - this.birthYear;
});

// Usage
const user = await User.findById(id);
console.log(user.fullName); // "Alice Smith" — not stored in DB
console.log(user.age);      // 30 — computed each time

// Schemas with references
const postSchema = new Schema({
  title:   String,
  author:  { type: Schema.Types.ObjectId, ref: 'User' },   // single ref
  tags:    [{ type: Schema.Types.ObjectId, ref: 'Tag' }]   // array of refs
});

// Basic populate
const post = await Post
  .findById(id)
  .populate('author');             // replaces ObjectId with full User doc

// Populate with field selection (only return name and email)
const post = await Post
  .findById(id)
  .populate({ path: 'author', select: 'name email -_id' });

// Populate multiple fields
const post = await Post
  .findById(id)
  .populate('author')
  .populate('tags');

// Deep / nested populate (author's company)
const post = await Post
  .findById(id)
  .populate({ path: 'author', populate: { path: 'company' } });