Query Building

interface Query<ResultType, DocType> {
  /**
   * Add where conditions to query
   * @param path - Field path or condition object
   * @param val - Value for equality match
   * @returns this query
   */
  where(path: string | object, val?: any): this;
  
  /**
   * Specify equality condition
   * @param value - Value to match
   * @returns this query
   */
  equals(value: any): this;
  
  /**
   * Greater than condition
   * @param value - Comparison value
   * @returns this query
   */
  gt(value: any): this;
  
  /**
   * Greater than or equal condition
   * @param value - Comparison value
   * @returns this query
   */
  gte(value: any): this;
  
  /**
   * Less than condition
   * @param value - Comparison value
   * @returns this query
   */
  lt(value: any): this;
  
  /**
   * Less than or equal condition
   * @param value - Comparison value
   * @returns this query
   */
  lte(value: any): this;
  
  /**
   * In array condition
   * @param value - Array of values to match
   * @returns this query
   */
  in(value: any[]): this;
  
  /**
   * Not in array condition
   * @param value - Array of values to exclude
   * @returns this query
   */
  nin(value: any[]): this;
  
  /**
   * Field exists condition
   * @param val - True to check existence, false for non-existence
   * @returns this query
   */
  exists(val?: boolean): this;
  
  /**
   * Regular expression match
   * @param val - Regular expression or string pattern
   * @returns this query
   */
  regex(val: RegExp | string): this;
  
  /**
   * Array size condition
   * @param val - Required array length
   * @returns this query
   */
  size(val: number): this;
  
  /**
   * All array elements match
   * @param val - Array of values that must all be present
   * @returns this query
   */
  all(val: any[]): this;
  
  /**
   * Element match condition for arrays
   * @param val - Condition object for array elements
   * @returns this query
   */
  elemMatch(val: any): this;
  
  /**
   * Logical OR conditions
   * @param conditions - Array of condition objects
   * @returns this query
   */
  or(conditions: any[]): this;
  
  /**
   * Logical AND conditions
   * @param conditions - Array of condition objects
   * @returns this query
   */
  and(conditions: any[]): this;
  
  /**
   * Logical NOR conditions
   * @param conditions - Array of condition objects
   * @returns this query
   */
  nor(conditions: any[]): this;
  
  /**
   * Return lean objects instead of Mongoose documents
   * @param val - Enable/disable lean mode
   * @returns this query
   */
  lean<T = any>(val?: boolean): Query<T>;
  
  /**
   * Set cursor batch size for streaming
   * @param val - Batch size
   * @returns this query
   */
  batchSize(val: number): this;
  
  /**
   * Allow disk usage for large operations
   * @param v - Allow disk usage
   * @returns this query
   */
  allowDiskUse(v: boolean): this;
  
  /**
   * Add comment to query for profiling
   * @param val - Comment string
   * @returns this query
   */
  comment(val: string): this;
  
  /**
   * Return cursor for streaming results
   * @param options - Cursor options
   * @returns Query cursor
   */
  cursor(options?: any): QueryCursor<DocType>;
  
  /**
   * Set query session for transactions
   * @param session - MongoDB session
   * @returns this query
   */
  session(session: ClientSession): this;
  
  /**
   * Transform query results before returning
   * @param fn - Transform function
   * @returns this query
   */
  transform(fn: Function): this;
  
  /**
   * Set maximum time for query execution
   * @param ms - Maximum time in milliseconds
   * @returns this query
   */
  maxTimeMS(ms: number): this;
  
  /**
   * Get query options object
   * @returns Current query options
   */
  getOptions(): QueryOptions;
  
  /**
   * Check if query has projection
   * @returns True if query has field selection
   */
  selected(): boolean;
  
  /**
   * Promise then handler
   * @param resolve - Success handler
   * @param reject - Error handler
   * @returns Promise
   */
  then(resolve?: Function, reject?: Function): Promise<ResultType>;
  
  /**
   * Promise catch handler
   * @param reject - Error handler
   * @returns Promise
   */
  catch(reject?: (reason: any) => any): Promise<ResultType>;
}

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

// Basic conditions
const adults = await User.find().where('age').gte(18);
const youngAdults = await User.find().where('age').gte(18).lte(30);

// Multiple conditions
const activeUsers = await User.find()
  .where('status').equals('active')
  .where('lastLogin').gte(new Date(Date.now() - 30 * 24 * 60 * 60 * 1000));

// Array conditions
const usersWithTags = await User.find()
  .where('tags').in(['premium', 'verified'])
  .where('skills').size(3)
  .where('certifications').exists(true);

// Text search
const searchResults = await User.find()
  .where('name').regex(/john/i)
  .where('bio').regex('developer');

// Complex conditions
const complexQuery = await User.find()
  .or([
    { age: { $lt: 25 } },
    { status: 'premium' }
  ])
  .and([
    { active: true },
    { verified: true }
  ]);

interface Query<ResultType, DocType> {
  /**
   * Select fields to include or exclude
   * @param fields - Field selection string or object
   * @returns this query
   */
  select(fields: string | object): this;
  
  /**
   * Exclude specific fields
   * @param fields - Fields to exclude
   * @returns this query
   */
  deselect(fields: string): this;
  
  /**
   * Return distinct values for a field
   * @param field - Field to get distinct values for
   * @returns Query for distinct values
   */
  distinct(field: string): Query<any[], DocType>;
}

// Select specific fields
const users = await User.find().select('name email age');
const users = await User.find().select({ name: 1, email: 1, age: 1 });

// Exclude fields
const users = await User.find().select('-password -secret');
const users = await User.find().select({ password: 0, secret: 0 });

// Mixed selection (include some, exclude others)
const users = await User.find().select('name email -_id');

// Get distinct values
const ages = await User.find().distinct('age');
const statuses = await User.find({ active: true }).distinct('status');

interface Query<ResultType, DocType> {
  /**
   * Sort query results
   * @param sort - Sort specification
   * @returns this query
   */
  sort(sort: string | object): this;
  
  /**
   * Limit number of results
   * @param val - Maximum number of documents to return
   * @returns this query
   */
  limit(val: number): this;
  
  /**
   * Skip number of results
   * @param val - Number of documents to skip
   * @returns this query
   */
  skip(val: number): this;
  
  /**
   * Set query slice for arrays
   * @param path - Array field path
   * @param val - Number of elements or [skip, limit] array
   * @returns this query  
   */
  slice(path: string, val: number | [number, number]): this;
}

// Sort by single field
const users = await User.find().sort('name'); // Ascending
const users = await User.find().sort('-createdAt'); // Descending

// Sort by multiple fields
const users = await User.find().sort({ age: -1, name: 1 });
const users = await User.find().sort('age -name');

// Pagination
const page = 2;
const limit = 10;
const users = await User.find()
  .sort('-createdAt')
  .skip((page - 1) * limit)
  .limit(limit);

// Array slicing
const posts = await Post.find()
  .select('title comments')
  .slice('comments', 5) // First 5 comments
  .slice('tags', [2, 5]); // Skip 2, take 5 tags

interface Query<ResultType, DocType> {
  /**
   * Populate referenced fields
   * @param path - Path to populate or populate options
   * @returns this query
   */
  populate(path: string | PopulateOptions | PopulateOptions[]): this;
  
  /**
   * Populate virtual fields
   * @param path - Virtual path to populate
   * @returns this query
   */
  populateVirtuals(path: string): this;
}

interface PopulateOptions {
  /** Path to populate */
  path: string;
  
  /** Fields to select from populated documents */
  select?: string | object;
  
  /** Model to populate from */
  model?: string | Model<any>;
  
  /** Additional query conditions for populated documents */
  match?: any;
  
  /** Sort populated documents */
  sort?: any;
  
  /** Limit populated documents */
  limit?: number;
  
  /** Skip populated documents */
  skip?: number;
  
  /** Populate nested fields */
  populate?: PopulateOptions | PopulateOptions[];
  
  /** Transform populated results */
  transform?: Function;
  
  /** Preserve null and undefined values */
  strictPopulate?: boolean;
  
  /** Count instead of populating documents */
  count?: boolean;
}

// Basic population
const posts = await Post.find().populate('author');

// Population with field selection
const posts = await Post.find().populate('author', 'name email');

// Population with conditions
const posts = await Post.find().populate({
  path: 'comments',
  match: { approved: true },
  select: 'text author createdAt',
  sort: { createdAt: -1 },
  limit: 10
});

// Multiple populations
const posts = await Post.find().populate([
  { path: 'author', select: 'name' },
  { path: 'category', select: 'name slug' },
  { 
    path: 'comments',
    populate: { path: 'author', select: 'name avatar' }
  }
]);

// Nested population
const users = await User.find().populate({
  path: 'posts',
  populate: {
    path: 'comments',
    populate: {
      path: 'author',
      select: 'name'
    }
  }
});

// Virtual population
userSchema.virtual('posts', {
  ref: 'Post',
  localField: '_id',
  foreignField: 'author'
});

const users = await User.find().populate('posts');

interface Query<ResultType, DocType> {
  /**
   * Execute the query
   * @returns Promise resolving to query results
   */
  exec(): Promise<ResultType>;
  
  /**
   * Execute query and return a cursor
   * @param options - Cursor options
   * @returns Query cursor for streaming results
   */
  cursor(options?: CursorOptions): QueryCursor<DocType>;
  
  /**
   * Execute query and return readable stream
   * @param options - Stream options
   * @returns Readable stream of documents
   */
  stream(options?: any): NodeJS.ReadableStream;
  
  /**
   * Get query execution plan
   * @param verbosity - Explanation verbosity level
   * @returns Promise resolving to execution plan
   */
  explain(verbosity?: string): Promise<any>;
  
  /**
   * Return lean documents (plain objects)
   * @param val - Enable/disable lean mode
   * @returns this query
   */
  lean(val?: boolean | object): this;
  
  /**
   * Set read preference
   * @param pref - Read preference
   * @param tags - Read preference tags
   * @returns this query
   */
  read(pref: string, tags?: any[]): this;
  
  /**
   * Set query comment for profiling
   * @param val - Comment string
   * @returns this query
   */
  comment(val: string): this;
  
  /**
   * Force index usage
   * @param val - Index specification
   * @returns this query
   */
  hint(val: object): this;
  
  /**
   * Set maximum execution time
   * @param ms - Maximum time in milliseconds
   * @returns this query
   */
  maxTimeMS(ms: number): this;
}

interface CursorOptions {
  /** Transform function for each document */
  transform?: Function;
  
  /** Batch size for cursor */
  batchSize?: number;
  
  /** Use lean mode */
  lean?: boolean;
}

// Basic execution
const users = await User.find({ active: true }).exec();

// Lean queries (return plain objects)
const users = await User.find().lean();
const fastUsers = await User.find().lean({ virtuals: true });

// Cursor for large datasets
const cursor = User.find().cursor();
for (let doc = await cursor.next(); doc != null; doc = await cursor.next()) {
  console.log(doc.name);
}

// Streaming
const stream = User.find().stream();
stream.on('data', (doc) => {
  console.log('Received:', doc.name);
});

// Query optimization
const users = await User.find()
  .hint({ email: 1 }) // Force index usage
  .maxTimeMS(5000) // 5 second timeout
  .comment('User lookup query')
  .read('secondary'); // Read from secondary

// Execution plan
const plan = await User.find({ age: { $gte: 18 } }).explain('executionStats');
console.log('Query execution plan:', plan);

interface Query<ResultType, DocType> {
  /**
   * Use database session for transactions
   * @param session - MongoDB session
   * @returns this query
   */
  session(session: ClientSession): this;
  
  /**
   * Set collation for string comparisons
   * @param collation - Collation specification
   * @returns this query
   */
  collation(collation: CollationOptions): this;
  
  /**
   * Set batch size for cursor operations
   * @param val - Batch size
   * @returns this query
   */
  batchSize(val: number): this;
  
  /**
   * Allow disk usage for large operations
   * @param val - Enable disk usage
   * @returns this query
   */
  allowDiskUse(val: boolean): this;
  
  /**
   * Set maximum number of documents to scan
   * @param val - Maximum scan count
   * @returns this query
   */
  maxscan(val: number): this;
  
  /**
   * Enable/disable query timeout
   * @param val - Enable timeout
   * @returns this query
   */
  timeout(val: boolean): this;
  
  /**
   * Cast query conditions using schema
   * @param model - Model to use for casting
   * @param obj - Object to cast (optional)
   * @returns Casted conditions
   */
  cast(model?: Model<DocType>, obj?: any): any;
}

interface CollationOptions {
  locale: string;
  caseLevel?: boolean;
  caseFirst?: string;
  strength?: number;
  numericOrdering?: boolean;
  alternate?: string;
  maxVariable?: string;
  backwards?: boolean;
}

// Transaction session
const session = await mongoose.startSession();
session.startTransaction();

try {
  const users = await User.find({ status: 'pending' })
    .session(session);
  
  await User.updateMany(
    { _id: { $in: users.map(u => u._id) } },
    { status: 'approved' }
  ).session(session);
  
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

// Collation for case-insensitive sorting
const users = await User.find()
  .sort({ name: 1 })
  .collation({ locale: 'en', strength: 2 });

// Performance tuning
const results = await User.find({ status: 'active' })
  .batchSize(100)
  .maxTimeMS(10000)
  .allowDiskUse(true)
  .maxscan(1000);

interface QueryCursor<T> extends NodeJS.ReadableStream {
  /** Get next document from cursor */
  next(): Promise<T | null>;
  
  /** Close the cursor */
  close(): Promise<void>;
  
  /** Check if cursor is closed */
  closed: boolean;
  
  /** Transform each document */
  map<U>(fn: (doc: T) => U): QueryCursor<U>;
  
  /** Add cursor event listeners */
  on(event: 'data' | 'error' | 'end', listener: Function): this;
}

type ProjectionType<T> = 
  | string 
  | { [K in keyof T]?: 1 | 0 | boolean } 
  | { [key: string]: 1 | 0 | boolean };

interface QueryOptions {
  /** Use lean mode */
  lean?: boolean;
  
  /** Population options */
  populate?: string | PopulateOptions | PopulateOptions[];
  
  /** Field selection */
  select?: string | object;
  
  /** Sort specification */
  sort?: string | object;
  
  /** Limit results */
  limit?: number;
  
  /** Skip results */
  skip?: number;
  
  /** Maximum time for query execution */
  maxTimeMS?: number;
  
  /** Query comment */
  comment?: string;
  
  /** Force index usage */
  hint?: object;
  
  /** Read preference */
  read?: any;
  
  /** Database session */
  session?: ClientSession;
  
  /** Collation options */
  collation?: CollationOptions;
  
  /** Batch size */
  batchSize?: number;
  
  /** Allow disk usage */
  allowDiskUse?: boolean;
}