schema + aggregation

const UserDailyReportSchema = new Schema(
  {
    user: {
      type: Schema.Types.ObjectId,
      ref: 'User',
      required: true,
      index: true,
    },
    date: { type: Date, required: true, index: true },

    // Aggregated trip metrics for the day
    trips: { type: Number, default: 0 },
    miles: { type: Number, default: 0 },
    calories: { type: Number, default: 0 },
    co2Potential: { type: Number, default: 0 },
    co2Actual: { type: Number, default: 0 },
    co2Savings: { type: Number, default: 0 },
    dollarsPotential: { type: Number, default: 0 },
    dollarsActual: { type: Number, default: 0 },
    dollarsSavings: { type: Number, default: 0 },
    vehicleMilesReduced: { type: Number, default: 0 },
  },
  { collection: 'user_daily_reports' },
);

UserDailyReportSchema.index({ user: 1, date: 1 }, { unique: true });
UserDailyReportSchema.index({ date: 1, user: 1 });
UserDailyReportSchema.index({ date: 1 });

const pipeline: PipelineStage[] = [
      // 1️⃣ Filter by date range
      {
        $match: {
          date: { $gte: startDate, $lte: endDate },
          user: { $exists: true, $ne: null },
        },
      },

      // 2️⃣ Group by user
      {
        $group: {
          _id: '$user',
          totalLoggedTrips: { $sum: '$trips' },
          totalDistanceLogged: { $sum: '$miles' },
          totalCaloriesBurned: { $sum: '$calories' },
          totalCo2Potential: { $sum: '$co2Potential' },
          totalCo2Emissions: { $sum: '$co2Actual' },
          totalCo2Savings: { $sum: '$co2Savings' },
          totalDollarsPotential: { $sum: '$dollarsPotential' },
          totalVehicleMilesReduced: { $sum: '$vehicleMilesReduced' },
        },
      },
      // 4️⃣ Add total field
      {
        $addFields: {
          total: {
            $ifNull: [{ $arrayElemAt: ['$total.count', 0] }, 0],
          },
        },
      },
    ];

const result = await UserDailyReportModel.aggregate(pipeline, {
        allowDiskUse: true,
      })