MongoDB: mongoose高级模式：性能与灵活性

// === 基本 populate ===
const user = await User.findById(userId).populate('addresses');
// SELECT u.*, a.* FROM users u LEFT JOIN addresses a ON u._id = a.userId

// === 嵌套 populate ===
const order = await Order.findById(orderId)
  .populate('userId')
  .populate({
    path: 'items.productId',
    select: 'sku title price'
  });

// === 条件 populate ===
const orders = await Order.find()
  .populate({
    path: 'userId',
    match: { isActive: true },
    select: 'username avatar'
  });

// === 基础 User Model ===
const UserSchema = new mongoose.Schema({
  email: String,
  username: String,
  passwordHash: String
}, { discriminatorKey: 'role' });

const User = mongoose.model('User', UserSchema);

// === Customer 鉴别器 ===
const Customer = User.discriminator('Customer', new mongoose.Schema({
  loyaltyPoints: { type: Number, default: 0 },
  preferredCategories: [String]
}));

// === Admin 鉴别器 ===
const Admin = User.discriminator('Admin', new mongoose.Schema({
  permissions: [String],
  lastLoginAt: Date
}));

// === 创建不同角色 ===
const customer = await Customer.create({
  email: 'alice@example.com',
  username: 'alice',
  passwordHash: '...',
  loyaltyPoints: 100,
  preferredCategories: ['Electronics']
});

const admin = await Admin.create({
  email: 'admin@example.com',
  username: 'admin',
  passwordHash: '...',
  permissions: ['manage_products']
});

// === 查询时根据 role 区分 ===
const customers = await Customer.find();
const admins = await Admin.find();
// 所有数据在同一集合（users），通过 discriminatorKey 区分

// === 普通查询：返回 mongoose Document ===
const products = await Product.find();
// 每个 product 是 Mongoose Document（带 save() 等方法）

// === lean()：返回纯 JS 对象 ===
const products = await Product.find().lean();
// 每个 product 是 plain object，性能 ↑3-5x

// === 对比测试 ===
console.time('without lean');
const a = await Product.find({ category: 'Electronics' }).limit(100);
console.timeEnd('without lean');  // ~150ms

console.time('with lean');
const b = await Product.find({ category: 'Electronics' }).lean().limit(100);
console.timeEnd('with lean');  // ~30ms

// === mongoose 中使用聚合 ===
const stats = await Product.aggregate([
  { $match: { isActive: true } },
  { $group: { _id: '$category', count: { $sum: 1 }, avgPrice: { $avg: '$price' } } },
  { $sort: { count: -1 } }
]);

// === aggregate + populate（mongoose 6+）===
const results = await Order.aggregate([
  { $match: { status: 'paid' } },
  {
    $lookup: {
      from: 'users',
      localField: 'userId',
      foreignField: '_id',
      as: 'customer'
    }
  },
  { $unwind: '$customer' }
]);

const ProductSchema = new mongoose.Schema({
  sku: { type: String, index: true, unique: true },
  title: { type: String, text: true },  // 文本索引
  price: { type: Number, index: true },
  category: { type: String, index: true }
});

// === 复合索引 ===
ProductSchema.index({ category: 1, price: -1 });

// === 部分索引 ===
ProductSchema.index(
  { category: 1 },
  { partialFilterExpression: { isActive: true } }
);

// === TTL 索引 ===
ProductSchema.index(
  { createdAt: 1 },
  { expireAfterSeconds: 30 * 24 * 60 * 60 }
);

// === 优化 1：禁用 autoIndex（生产）===
mongoose.connect(uri, { autoIndex: false });
// 启动时手动创建索引：await Product.syncIndexes();

// === 优化 2：批量操作 ===
await Product.bulkWrite([
  { updateOne: { filter: { sku: 'A' }, update: { $inc: { stock: -1 } } } },
  { updateOne: { filter: { sku: 'B' }, update: { $inc: { stock: -1 } } } }
]);

// === 优化 3：投影减少数据传输 ===
const products = await Product.find()
  .select('sku title price')  // 只查询 3 个字段
  .lean();

// === 优化 4：使用 cursor 流式处理大数据 ===
const cursor = Product.find().cursor();
for await (const doc of cursor) {
  // 处理每个文档
}

// === 优化 5：批量插入 ===
await Product.insertMany(docs, { ordered: false });

// === 优化后的列表 API ===
app.get('/api/products', async (req, res) => {
  const { page = 1, limit = 20, category, search } = req.query;

  // 1. 构建查询
  const query = { isActive: true };
  if (category) query.category = category;
  if (search) query.$text = { $search: search };

  // 2. 并行查询（find + count）
  const [products, total] = await Promise.all([
    Product.find(query)
      .select('sku title price thumbnail rating')  // 投影
      .sort({ createdAt: -1 })
      .limit(+limit)
      .skip((page - 1) * limit)
      .lean(),  // 性能优化
    Product.countDocuments(query)
  ]);

  res.json({
    success: true,
    data: products,
    meta: { page: +page, limit: +limit, total, pages: Math.ceil(total / limit) }
  });
});

// === 场景：ShopHub 订单详情 API（3 层关联）===
const mongoose = require('mongoose');

// Schemas
const AddressSchema = new mongoose.Schema({ city: String, country: String, zipCode: String });
const UserSchema = new mongoose.Schema({
  email: String, username: String,
  addresses: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Address' }]
});
const ProductSchema = new mongoose.Schema({ sku: String, title: String, price: Number, thumbnail: String });
const OrderSchema = new mongoose.Schema({
  orderNumber: String,
  userId: { type: mongoose.Schema.Types.ObjectId, ref: 'User' },
  items: [{ productId: { type: mongoose.Schema.Types.ObjectId, ref: 'Product' }, qty: Number, price: Number }],
  status: String
}, { timestamps: true });

const Address = mongoose.model('Address', AddressSchema);
const User = mongoose.model('User', UserSchema);
const Product = mongoose.model('Product', ProductSchema);
const Order = mongoose.model('Order', OrderSchema);

// 3 层 populate + lean
const order = await Order.findById('647f1f77bcf86cd799439001')
  .populate({ path: 'userId', select: 'username email',
    populate: { path: 'addresses', select: 'city country' }
  })
  .populate({ path: 'items.productId', select: 'sku title price' })
  .lean();

console.log({
  orderNumber: order.orderNumber,
  customer: order.userId.username,
  city: order.userId.addresses[0]?.city,
  items: order.items.map(i => `${i.productId.title} x${i.qty}`)
});
// 输出：{ orderNumber: 'ORD-001', customer: 'alice', city: 'San Francisco',
//         items: ['Smartphone X x2', 'Laptop Pro x1'] }

// === 1. populate 多层关联 ===
// 订单 + 用户 + 商品（3 层嵌套）
const order = await Order.findById(orderId)
  .populate({
    path: 'userId',
    select: 'username email avatar',
    populate: { path: 'addresses', select: 'city country' }  // 用户下的地址
  })
  .populate({
    path: 'items.productId',
    select: 'sku title price thumbnail'
  })
  .lean();  // 性能优化

console.log('Order:', {
  orderNumber: order.orderNumber,
  customer: {
    username: order.userId.username,
    address: order.userId.addresses[0]?.city
  },
  items: order.items.map(i => ({
    product: i.productId.title,
    qty: i.qty,
    price: i.price
  }))
});

// === 2. discriminator 单集合多 Schema ===
// User 基类
const UserSchema = new mongoose.Schema({
  email: { type: String, required: true, unique: true },
  username: String,
  passwordHash: String,
  createdAt: { type: Date, default: Date.now }
}, { discriminatorKey: 'role' });

const User = mongoose.model('User', UserSchema);

// Customer 鉴别器（继承 User + 扩展字段）
const Customer = User.discriminator('Customer', new mongoose.Schema({
  loyaltyPoints: { type: Number, default: 0 },
  preferredCategories: [String],
  totalSpent: mongoose.Schema.Types.Decimal128
}));

// Admin 鉴别器
const Admin = User.discriminator('Admin', new mongoose.Schema({
  permissions: [String],
  lastLoginAt: Date
}));

// 创建不同角色（都存在 users 集合，通过 role 字段区分）
const customer = await Customer.create({
  email: 'alice@example.com',
  username: 'alice',
  passwordHash: '...',
  loyaltyPoints: 100,
  preferredCategories: ['Electronics']
});

const admin = await Admin.create({
  email: 'admin@example.com',
  username: 'admin',
  passwordHash: '...',
  permissions: ['manage_products', 'manage_users']
});

// 查询时根据 role 自动过滤
const customers = await Customer.find({ loyaltyPoints: { $gt: 50 } });
// 实际查询：{ role: 'Customer', loyaltyPoints: { $gt: 50 } }

// === 3. lean() 性能优化对比 ===
console.time('without lean');
const a = await Product.find({ category: 'Electronics' }).limit(100);
console.timeEnd('without lean');  // ~150ms

console.time('with lean');
const b = await Product.find({ category: 'Electronics' }).lean().limit(100);
console.timeEnd('with lean');  // ~30ms（5x 性能提升）

// === 4. Model.aggregate() 数据库层聚合 ===
const stats = await Product.aggregate([
  { $match: { isActive: true } },
  {
    $facet: {
      totalCount: [{ $count: 'count' }],
      byCategory: [
        { $group: { _id: '$category', count: { $sum: 1 }, avgPrice: { $avg: '$price' } } },
        { $sort: { count: -1 } }
      ],
      topRated: [
        { $sort: { rating: -1 } },
        { $limit: 5 },
        { $project: { sku: 1, title: 1, rating: 1 } }
      ]
    }
  }
]);

// 输出：
// {
//   totalCount: [{ count: 1250 }],
//   byCategory: [
//     { _id: 'Electronics', count: 450, avgPrice: 599 },
//     { _id: 'Books', count: 380, avgPrice: 29 },
//     ...
//   ],
//   topRated: [
//     { sku: 'PHONE-001', title: 'Smartphone X', rating: 4.9 },
//     ...
//   ]
// }

const mongoose = require('mongoose');

// === 1. 商品 Schema + 三种扩展 ===
const productSchema = new mongoose.Schema({
  sku: { type: String, required: true, unique: true },
  title: { type: String, required: true },
  category: { type: String, required: true },
  price: { type: Number, required: true, min: 0 },
  discountPercent: { type: Number, default: 0, min: 0, max: 100 },
  stock: { type: Number, default: 0, min: 0 },
  ratings: [{ userId: String, score: Number }],
  createdAt: { type: Date, default: Date.now }
}, {
  toJSON: { virtuals: true },
  toObject: { virtuals: true },
  id: false
});

// === 2. 虚拟属性（不存入数据库，动态计算）===
productSchema.virtual('finalPrice').get(function() {
  return Math.round(this.price * (1 - this.discountPercent / 100) * 100) / 100;
});

productSchema.virtual('isOnSale').get(function() {
  return this.discountPercent > 0;
});

productSchema.virtual('avgRating').get(function() {
  if (!this.ratings || this.ratings.length === 0) return null;
  const sum = this.ratings.reduce((acc, r) => acc + r.score, 0);
  return Math.round(sum / this.ratings.length * 10) / 10;
});

productSchema.virtual('stockStatus').get(function() {
  if (this.stock === 0) return 'out_of_stock';
  if (this.stock < 10) return 'low_stock';
  return 'in_stock';
});

// === 3. 实例方法（文档级操作，this 指向文档实例）===
productSchema.methods.applyDiscount = function(percent) {
  if (percent < 0 || percent > 80) throw new Error('折扣范围 0-80%');
  this.discountPercent = percent;
  return this.save();
};

productSchema.methods.reduceStock = async function(qty) {
  if (this.stock < qty) throw new Error(`库存不足：当前 ${this.stock}，需要 ${qty}`);
  this.stock -= qty;
  return this.save();
};

productSchema.methods.addRating = function(userId, score) {
  const existing = this.ratings.find(r => r.userId === userId);
  if (existing) {
    existing.score = score;
  } else {
    this.ratings.push({ userId, score });
  }
  return this.save();
};

// === 4. 静态方法（模型级操作，this 指向模型）===
productSchema.statics.findOnSale = function() {
  return this.find({ discountPercent: { $gt: 0 } }).lean();
};

productSchema.statics.findLowStock = function(threshold = 10) {
  return this.find({ stock: { $lt: threshold, $gt: 0 } }).lean();
};

productSchema.statics.getTopRated = function(limit = 10) {
  return this.aggregate([
    { $addFields: { avgScore: { $avg: '$ratings.score' } } },
    { $match: { avgScore: { $gte: 4 } } },
    { $sort: { avgScore: -1 } },
    { $limit: limit }
  ]);
};

productSchema.statics.searchByKeyword = function(keyword, page = 1, limit = 20) {
  const regex = new RegExp(keyword, 'i');
  return this.find({
    $or: [{ title: regex }, { sku: regex }, { category: regex }]
  })
    .skip((page - 1) * limit)
    .limit(limit)
    .lean();
};

const Product = mongoose.model('Product', productSchema);

// === 5. 使用示例 ===
const phone = new Product({
  sku: 'PHONE-001', title: 'Smartphone X', category: 'Electronics',
  price: 599, discountPercent: 15, stock: 50
});
await phone.save();

console.log(phone.finalPrice);      // 509.15
console.log(phone.isOnSale);        // true
console.log(phone.stockStatus);     // 'in_stock'

await phone.addRating('user1', 5);
await phone.addRating('user2', 4);
console.log(phone.avgRating);       // 4.5

await phone.reduceStock(5);
console.log(phone.stock);           // 45

const onSale = await Product.findOnSale();
const topRated = await Product.getTopRated(5);