Advanced Querying

/**
 * Creates a new resultset for chainable operations
 * @param collection - Source collection
 * @param options - Resultset options
 */
constructor Resultset(collection: Collection, options?: object);

// Create resultset from collection
const users = db.getCollection('users');
const resultset = new users.constructor.Resultset(users);

// Or create via collection.chain()
const resultset = users.chain();

/**
 * Apply find query to filter documents
 * @param query - MongoDB-style query object
 * @param firstOnly - If true, stop after first match
 * @returns Resultset for chaining
 */
find(query: object, firstOnly?: boolean): Resultset;

/**
 * Apply OR query across multiple expressions
 * @param expressionArray - Array of query expressions
 * @returns Resultset for chaining
 */
findOr(expressionArray: object[]): Resultset;

/**
 * Apply AND query across multiple expressions
 * @param expressionArray - Array of query expressions
 * @returns Resultset for chaining
 */
findAnd(expressionArray: object[]): Resultset;

/**
 * Filter using custom function
 * @param fun - Filter function returning boolean
 * @returns Resultset for chaining
 */
where(fun: (obj: object) => boolean): Resultset;

// Basic find operations
const activeUsers = users.chain()
  .find({ active: true })
  .data();

// OR query
const result = users.chain()
  .findOr([
    { department: 'Engineering' },
    { department: 'Design' },
    { age: { $gt: 50 } }
  ])
  .data();

// AND query
const result = users.chain()
  .findAnd([
    { active: true },
    { age: { $gte: 25 } },
    { department: { $ne: 'Intern' } }
  ])
  .data();

// Custom filter function
const result = users.chain()
  .where(obj => obj.email.includes('@company.com'))
  .data();

/**
 * Sort using custom comparison function
 * @param comparefun - Function returning -1, 0, or 1 for comparison
 * @returns Resultset for chaining
 */
sort(comparefun: (a: object, b: object) => number): Resultset;

/**
 * Simple sort by property name
 * @param propname - Property name to sort by
 * @param options - Sort options (desc: boolean)
 * @returns Resultset for chaining
 */
simplesort(propname: string, options?: { desc?: boolean }): Resultset;

/**
 * Sort by multiple properties with individual sort directions
 * @param properties - Array of [property, isDescending] pairs
 * @returns Resultset for chaining
 */
compoundsort(properties: [string, boolean][]): Resultset;

// Simple ascending sort
const result = users.chain()
  .find({ active: true })
  .simplesort('name')
  .data();

// Simple descending sort
const result = users.chain()
  .find({ active: true })
  .simplesort('age', { desc: true })
  .data();

// Compound sort
const result = users.chain()
  .find({ active: true })
  .compoundsort([
    ['department', false],  // ascending
    ['age', true]          // descending
  ])
  .data();

// Custom sort function
const result = users.chain()
  .find({ active: true })
  .sort((a, b) => {
    // Sort by name length, then alphabetically
    if (a.name.length !== b.name.length) {
      return a.name.length - b.name.length;
    }
    return a.name.localeCompare(b.name);
  })
  .data();

/**
 * Limit number of results returned
 * @param qty - Maximum number of results
 * @returns Resultset for chaining
 */
limit(qty: number): Resultset;

/**
 * Skip specified number of results
 * @param pos - Number of results to skip
 * @returns Resultset for chaining
 */
offset(pos: number): Resultset;

// Get first 10 users
const firstPage = users.chain()
  .find({ active: true })
  .limit(10)
  .data();

// Get users 11-20 (second page)
const secondPage = users.chain()
  .find({ active: true })
  .offset(10)
  .limit(10)
  .data();

// Get top 5 highest paid employees
const topEarners = users.chain()
  .find({ active: true })
  .simplesort('salary', { desc: true })
  .limit(5)
  .data();

/**
 * Execute the query chain and return results
 * @param options - Data retrieval options
 * @returns Array of documents matching the query chain
 */
data(options?: { removeMeta?: boolean, forceClones?: boolean }): object[];

/**
 * Get count of documents in resultset without retrieving data
 * @returns Number of documents that would be returned
 */
count(): number;

// Get results with metadata
const results = users.chain()
  .find({ active: true })
  .simplesort('name')
  .data();

// Get results without metadata
const cleanResults = users.chain()
  .find({ active: true })
  .data({ removeMeta: true });

// Just get the count
const activeCount = users.chain()
  .find({ active: true })
  .count();

/**
 * Apply a transform to the resultset
 * @param transform - Transform name or function
 * @param parameters - Transform parameters
 * @returns Resultset for chaining
 */
transform(transform: string | Function, parameters?: object): Resultset;

/**
 * Update documents in the resultset
 * @param updateFunction - Function to update each document
 * @returns Resultset for chaining
 */
update(updateFunction: (obj: object) => void): object[];

/**
 * Remove documents in the resultset from the collection
 * @returns Array of removed documents
 */
remove(): object[];

/**
 * Copy the resultset
 * @returns New resultset with same filters
 */
copy(): Resultset;

/**
 * Reset resultset to include all documents
 * @returns Resultset for chaining
 */
reset(): Resultset;

/**
 * Create a branch (copy) of the resultset
 * @returns New resultset with same filters
 */
branch(): Resultset;

/**
 * Serialize resultset to JSON
 * @returns JSON representation of the resultset
 */
toJSON(): string;

// Apply transform
const transformed = users.chain()
  .find({ active: true })
  .transform('extractName')  // Assuming transform exists
  .data();

// Update documents in resultset
const updated = users.chain()
  .find({ department: 'Engineering' })
  .update(obj => {
    obj.lastReviewDate = new Date();
    obj.needsReview = false;
  });

// Remove documents matching criteria
const removed = users.chain()
  .find({ active: false, lastLogin: { $lt: new Date('2020-01-01') } })
  .remove();

// Copy resultset for different operations
const baseQuery = users.chain().find({ active: true });
const engineers = baseQuery.copy().find({ department: 'Engineering' }).data();
const designers = baseQuery.copy().find({ department: 'Design' }).data();

/**
 * Perform map-reduce operation on resultset
 * @param mapFunction - Map function to apply to each document
 * @param reduceFunction - Reduce function to combine results
 * @returns Reduced result
 */
mapReduce(mapFunction: (obj: object) => any, reduceFunction: (array: any[]) => any): any;

/**
 * Perform equi-join with external data
 * @param joinData - External data to join with
 * @param leftJoinKey - Property from resultset documents
 * @param rightJoinKey - Property from external data
 * @param mapFun - Optional function to transform join results
 * @param dataOptions - Additional options
 * @returns Array of joined results
 */
eqJoin(joinData: any[], leftJoinKey: string, rightJoinKey: string, mapFun?: Function, dataOptions?: object): object[];

/**
 * Apply map operation to transform each document
 * @param mapFun - Map function to apply
 * @param dataOptions - Additional options
 * @returns Array of transformed results
 */
map(mapFun: (obj: object) => any, dataOptions?: object): any[];

// Map-reduce to calculate department statistics
const deptStats = users.chain()
  .find({ active: true })
  .mapReduce(
    obj => ({ [obj.department]: { count: 1, totalSalary: obj.salary } }),
    results => {
      const stats = {};
      results.forEach(result => {
        Object.keys(result).forEach(dept => {
          if (!stats[dept]) {
            stats[dept] = { count: 0, totalSalary: 0 };
          }
          stats[dept].count += result[dept].count;
          stats[dept].totalSalary += result[dept].totalSalary;
        });
      });
      // Calculate averages
      Object.keys(stats).forEach(dept => {
        stats[dept].avgSalary = stats[dept].totalSalary / stats[dept].count;
      });
      return stats;
    }
  );

// Join with external orders data
const orders = [
  { customerId: 1, total: 150.00, date: '2023-01-15' },
  { customerId: 2, total: 75.50, date: '2023-01-16' }
];

const usersWithOrders = users.chain()
  .find({ active: true })
  .eqJoin(orders, 'id', 'customerId', (left, right) => ({
    user: left,
    orderTotal: right.total,
    orderDate: right.date
  }));

// Map to extract specific fields
const userSummaries = users.chain()
  .find({ active: true })
  .map(obj => ({
    name: obj.name,
    email: obj.email,
    department: obj.department
  }));

// Comparison operators
$eq: any;           // Strict equality
$aeq: any;          // Abstract equality (==)
$ne: any;           // Not equal
$gt: any;           // Greater than
$gte: any;          // Greater than or equal
$lt: any;           // Less than
$lte: any;          // Less than or equal

// Array operators
$in: any[];         // Value in array
$nin: any[];        // Value not in array
$contains: any;     // Array contains value
$containsAny: any[]; // Array contains any of values
$containsNone: any[]; // Array contains none of values

// Object operators
$exists: boolean;   // Property exists
$type: string;      // Type checking ('string', 'number', etc.)
$size: number;      // Array/object size
$regex: RegExp;     // Regular expression match

// Logical operators
$and: object[];     // Logical AND
$or: object[];      // Logical OR
$not: object;       // Logical NOT

// Numeric operators
$between: [any, any]; // Between two values
$finite: boolean;   // Is finite number

// Advanced operators
$dteq: any;           // Date/abstract equality
$jgt: any;            // JavaScript greater than (lightweight)
$jgte: any;           // JavaScript greater than or equal
$jlt: any;            // JavaScript less than (lightweight)
$jlte: any;           // JavaScript less than or equal
$jbetween: [any, any]; // JavaScript between (lightweight)
$inSet: Set<any>;     // Value in Set object
$keyin: object;       // Property key exists in object
$nkeyin: object;      // Property key does not exist in object
$definedin: object;   // Property is defined in object (not undefined)
$undefinedin: object; // Property is undefined in object
$containsString: string; // String contains substring
$elemMatch: object;   // Array element matches query

// Custom operators
$where: Function;     // Custom function filter

// Comparison operators
const adults = users.chain().find({ age: { $gte: 18 } }).data();
const active = users.chain().find({ status: { $ne: 'inactive' } }).data();

// Array operators
const managers = users.chain().find({ 
  roles: { $contains: 'manager' } 
}).data();

const techRoles = users.chain().find({
  skills: { $containsAny: ['JavaScript', 'Python', 'Java'] }
}).data();

// Object operators
const hasPhone = users.chain().find({ 
  phone: { $exists: true } 
}).data();

const numbers = users.chain().find({ 
  employeeId: { $type: 'number' } 
}).data();

// Logical operators
const seniorEngineers = users.chain().find({
  $and: [
    { department: 'Engineering' },
    { experience: { $gte: 5 } },
    { level: { $in: ['Senior', 'Lead', 'Principal'] } }
  ]
}).data();

// Custom function filter
const recentHires = users.chain().find({
  $where: function(obj) {
    const hireDate = new Date(obj.hireDate);
    const sixMonthsAgo = new Date();
    sixMonthsAgo.setMonth(sixMonthsAgo.getMonth() - 6);
    return hireDate > sixMonthsAgo;
  }
}).data();

// Advanced operators
const advancedUsers = users.chain().find({
  // Date equality
  createdAt: { $dteq: new Date('2023-01-01') },
  
  // Lightweight JavaScript comparisons
  score: { $jgt: 85 },
  
  // Between range (lightweight)
  age: { $jbetween: [25, 65] },
  
  // Set membership
  department: { $inSet: new Set(['Engineering', 'Design']) },
  
  // Object property checks
  userId: { $keyin: userMap },
  preferences: { $definedin: { theme: 'dark' } },
  
  // String operations
  email: { $containsString: '@company.com' },
  
  // Array element matching
  orders: {
    $elemMatch: {
      status: 'completed',
      total: { $gt: 100 }
    }
  }
}).data();

// Complex nested queries
const complexResults = users.chain()
  .find({
    $and: [
      { status: 'active' },
      { 
        $or: [
          { department: { $inSet: new Set(['Engineering', 'Design']) } },
          { skills: { $containsAny: ['leadership', 'management'] } }
        ]
      },
      {
        projects: {
          $elemMatch: {
            status: 'completed',
            budget: { $jbetween: [10000, 100000] },
            deadline: { $dteq: new Date('2023-12-31') }
          }
        }
      }
    ]
  })
  .data();

// String and object operations
const textSearchResults = users.chain()
  .find({
    // String contains
    biography: { $containsString: 'engineer' },
    
    // Property existence
    permissions: { $definedin: { admin: true } },
    
    // Key membership  
    userId: { $keyin: { 1: true, 2: true, 3: true } },
    
    // Undefined check
    deletedAt: { $undefinedin: {} }
  })
  .data();

// Logical operator aliases
$or(expressionArray: object[]): Resultset;  // Alias for findOr
$and(expressionArray: object[]): Resultset; // Alias for findAnd

// Use MongoDB-style operator aliases
const result1 = users.chain()
  .$or([
    { department: 'Engineering' },
    { department: 'Design' }
  ])
  .data();

const result2 = users.chain()
  .$and([
    { active: true },
    { age: { $gte: 25 } }
  ])
  .data();

interface Resultset {
  /** Reference to source collection */
  collection: Collection;
  /** Array of filtered row indices */
  filteredrows: number[];
  /** Filter initialization flag */
  filterInitialized: boolean;
}