CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INT REFERENCES authors(id),
  title VARCHAR(200),
  body TEXT
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  label VARCHAR(50)
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id),
  tag_id INT REFERENCES tags(id)
);

-- Reading one post = a 4-table join
SELECT p.title, a.name, t.label
FROM posts p
JOIN authors a ON a.id = p.author_id
LEFT JOIN post_tags pt ON pt.post_id = p.id
LEFT JOIN tags t ON t.id = pt.tag_id
WHERE p.id = 1;

db.posts.insertOne({
  title: "Understanding Indexes",
  body: "Indexes are B-trees that...",
  author: { name: "Ada Lovelace", authorId: ObjectId("...") },
  tags: ["mongodb", "performance", "indexes"],
  createdAt: new Date()
})

// Reading the whole post — no joins
db.posts.findOne({ _id: ObjectId("...") })

// Embedded — line items live inside the order
db.orders.insertOne({
  orderNumber: 1001,
  items: [
    { sku: "KB-01", name: "Keyboard", qty: 1, price: 79.99 },
    { sku: "MS-02", name: "Mouse", qty: 2, price: 24.99 }
  ],
  total: 129.97
})

// Referenced — orders point at a shared customer document
db.orders.insertOne({
  orderNumber: 1002,
  customerId: ObjectId("665f1a2b3c4d5e6f7a8b9c0d"),
  total: 59.99
})

// Resolve the reference with $lookup (MongoDB's LEFT OUTER JOIN)
db.orders.aggregate([
  { $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customer"
  } }
])

const session = db.getMongo().startSession()
session.startTransaction()
try {
  const accounts = session.getDatabase("bank").accounts
  accounts.updateOne({ _id: "alice" }, { $inc: { balance: -100 } })
  accounts.updateOne({ _id: "bob" },   { $inc: { balance: 100 } })
  session.commitTransaction()
} catch (e) {
  session.abortTransaction()
  throw e
} finally {
  session.endSession()
}

// SQL: SELECT * FROM users WHERE age >= 18 ORDER BY name LIMIT 10;
db.users.find({ age: { $gte: 18 } }).sort({ name: 1 }).limit(10)

// SQL: SELECT name, email FROM users WHERE status = 'active';
db.users.find({ status: "active" }, { name: 1, email: 1, _id: 0 })

// SQL: SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;
db.users.aggregate([
  { $group: { _id: "$city", count: { $sum: 1 } } },
  { $match: { count: { $gt: 5 } } }
])

// SQL: UPDATE users SET status = 'inactive' WHERE lastLogin < '2025-01-01';
db.users.updateMany(
  { lastLogin: { $lt: new Date("2025-01-01") } },
  { $set: { status: "inactive" } }
)

// SQL: DELETE FROM sessions WHERE expires < NOW();
db.sessions.deleteMany({ expires: { $lt: new Date() } })