Data Manipulation

/**
 * Update records in the query result set
 * @param changes - Object containing field updates
 * @param runEvent - Whether to trigger event callbacks (default: true)
 * @returns Updated QueryResult
 */
update(changes: object, runEvent?: boolean): QueryResult;

/**
 * Remove records in the query result set
 * @param runEvent - Whether to trigger event callbacks (default: true)
 * @returns QueryResult (empty after removal)
 */
remove(runEvent?: boolean): QueryResult;

// Update single field
db({ id: 1 }).update({ price: 199.99 });

// Update multiple fields
db({ category: "electronics" }).update({
  discount: 0.15,
  sale: true,
  updated: new Date()
});

// Update with event callbacks disabled
db({ status: "pending" }).update({ status: "processed" }, false);

// Remove records
db({ expired: true }).remove();

// Remove with callback events disabled
db({ temporary: true }).remove(false);

// Chain updates
db({ department: "Sales" })
  .filter({ performance: { lt: 2.0 } })
  .update({ needsTraining: true, reviewed: new Date() });

/**
 * Get all records as a plain JavaScript array
 * @returns Array of record objects
 */
get(): object[];

/**
 * Get the first record from the result set
 * @returns First record object or false if empty
 */
first(): object | false;

/**
 * Get the last record from the result set
 * @returns Last record object or false if empty
 */
last(): object | false;

/**
 * Get the count of records in the result set
 * @returns Number of records
 */
count(): number;

/**
 * Convert query results to JSON string
 * @returns JSON string representation of results
 */
stringify(): string;

// Get all matching records
const activeUsers = db({ active: true }).get();
console.log('Found', activeUsers.length, 'active users');

// Get single records (returns false if no results)
const firstUser = db().order("created").first();
const lastUser = db().order("created").last();
const newestUser = db().order("created desc").first();

// Check if results exist
if (firstUser === false) {
  console.log("No users found");
} else {
  console.log("First user:", firstUser.name);
}

// Get counts
const totalUsers = db().count();
const activeCount = db({ active: true }).count();
const inactiveCount = db({ active: false }).count();

// Export as JSON
const jsonData = db({ department: "Engineering" }).stringify();
localStorage.setItem('engineeringData', jsonData);

// Conditional operations
if (db({ role: "admin" }).count() === 0) {
  console.log("No administrators found!");
}

/**
 * Select specific columns/properties from results
 * @param columns - Column names to extract
 * @returns Array of values or arrays of values
 */
select(...columns: string[]): any[] | any[][];

/**
 * Get unique values from one or more columns
 * @param columns - Column names to extract unique values from
 * @returns Array of unique values (single column) or array of unique value arrays (multiple columns)
 */
distinct(...columns: string[]): any[] | any[][];

// Select single column
const names = db({ active: true }).select("name");
// Returns: ["Alice", "Bob", "Charlie"]

// Select multiple columns
const userInfo = db({ department: "Sales" }).select("name", "email", "salary");
// Returns: [["Alice", "alice@company.com", 75000], ["Bob", "bob@company.com", 68000]]

// Get unique values from single column
const departments = db().distinct("department");
// Returns: ["Engineering", "Sales", "Marketing", "HR"]

const locations = db({ active: true }).distinct("location");
// Returns: ["New York", "San Francisco", "Austin"]

// Get unique combinations from multiple columns
const roleDeptCombos = db().distinct("role", "department");
// Returns: [["Manager", "Sales"], ["Developer", "Engineering"], ["Manager", "Engineering"]]

// Combine with filtering
const seniorRoles = db({ experience: { gte: 5 } }).distinct("role");
const highEarners = db({ salary: { gte: 100000 } }).select("name", "salary", "department");

/**
 * Calculate sum of numeric columns (all columns are summed together)
 * @param columns - Column names to sum
 * @returns Single sum value combining all specified columns
 */
sum(...columns: string[]): number;

/**
 * Find minimum value in a column
 * @param column - Column name to analyze
 * @returns Minimum value found
 */
min(column: string): any;

/**
 * Find maximum value in a column
 * @param column - Column name to analyze
 * @returns Maximum value found
 */
max(column: string): any;

// Calculate sums (single column)
const totalSalary = db({ department: "Engineering" }).sum("salary");

// Calculate combined sum (all columns summed together)
const totalCompensation = db({ active: true }).sum("salary", "bonus", "commission");
// Returns: single number (salary + bonus + commission for all active users)

// Find min/max values
const youngestAge = db({ active: true }).min("age");
const oldestAge = db({ active: true }).max("age");
const lowestSalary = db({ department: "Sales" }).min("salary");
const highestSalary = db({ department: "Sales" }).max("salary");

// Aggregate analysis
const salesStats = {
  count: db({ department: "Sales" }).count(),
  totalSalary: db({ department: "Sales" }).sum("salary"),
  avgSalary: db({ department: "Sales" }).sum("salary") / db({ department: "Sales" }).count(),
  minSalary: db({ department: "Sales" }).min("salary"),
  maxSalary: db({ department: "Sales" }).max("salary")
};

// Multiple aggregations
const departmentSums = db().distinct("department").map(dept => ({
  department: dept,
  totalSalary: db({ department: dept }).sum("salary"),
  employeeCount: db({ department: dept }).count()
}));

/**
 * Iterate over each record in the result set
 * @param callback - Function to call for each record
 * @returns QueryResult for chaining
 */
each(callback: (record: object, index?: number) => any): QueryResult;

/**
 * Transform each record and return new array
 * @param callback - Function to transform each record
 * @returns Array of transformed values
 */
map(callback: (record: object, index?: number) => any): any[];

/**
 * Inject record values into string templates
 * @param template - String template with {property} placeholders
 * @param returnArray - Whether to return array of strings (default: false)
 * @returns Single string or array of strings
 */
supplant(template: string, returnArray?: boolean): string | string[];

// Iterate with each
db({ active: true }).each(function(user, index) {
  console.log(`${index + 1}. ${user.name} - ${user.email}`);
  
  // Return TAFFY.EXIT to break early
  if (index >= 5) return TAFFY.EXIT;
});

// Transform with map
const emailList = db({ active: true }).map(function(user) {
  return user.email.toLowerCase();
});

const userSummaries = db({ department: "Engineering" }).map(function(user, index) {
  return {
    id: user.id,
    fullName: `${user.firstName} ${user.lastName}`,
    seniority: user.experience >= 5 ? "Senior" : "Junior",
    position: index + 1
  };
});

// Template substitution
const emailTemplate = "Hello {name}, your account balance is ${balance}.";

// Single string (concatenated)
const emailBody = db({ id: 123 }).supplant(emailTemplate);
// Returns: "Hello John Smith, your account balance is $1250.50."

// Array of strings (one per record)
const emailBodies = db({ active: true }).supplant(emailTemplate, true);
// Returns: ["Hello Alice...", "Hello Bob...", "Hello Charlie..."]

// HTML generation
const tableRows = db({ department: "Sales" }).supplant(
  "<tr><td>{name}</td><td>{email}</td><td>${salary}</td></tr>",
  true
);

/**
 * Execute callback with query results after optional delay
 * @param fn - Callback function to execute
 * @param delay - Delay in milliseconds (default: 0)
 * @returns QueryResult for chaining
 */
callback(fn: (results: object[]) => void, delay?: number): QueryResult;

// Immediate callback
db({ active: true }).callback(function(results) {
  console.log('Found', results.length, 'active users');
  results.forEach(user => {
    console.log('- ' + user.name);
  });
});

// Delayed callback
db({ needsNotification: true }).callback(function(results) {
  results.forEach(user => {
    sendNotificationEmail(user.email, user.message);
  });
}, 1000); // Wait 1 second before executing

// Chain with other operations
db({ status: "pending" })
  .update({ status: "processing" })
  .callback(function(results) {
    console.log('Started processing', results.length, 'items');
  })
  .filter({ priority: "high" })
  .callback(function(results) {
    console.log('High priority items:', results.length);
  }, 500);

/**
 * Join current query results with another table using specified conditions
 * @param table - TaffyDB instance or QueryResult to join with
 * @param conditions - Join conditions (arrays, functions, or mixed)
 * @returns QueryResult containing joined records
 */
join(table: TaffyDatabase | QueryResult, ...conditions: (JoinCondition | JoinFunction)[]): QueryResult;

// Join condition types
type JoinCondition = [string, string] | [string, string, string];  // [leftField, rightField] or [leftField, operator, rightField]
type JoinFunction = (leftRow: object, rightRow: object) => boolean;

// Basic join on matching fields
const users = TAFFY([
  { id: 1, name: "Alice", deptId: 10 },
  { id: 2, name: "Bob", deptId: 20 },
  { id: 3, name: "Charlie", deptId: 10 }
]);

const departments = TAFFY([
  { id: 10, name: "Engineering", budget: 500000 },
  { id: 20, name: "Sales", budget: 300000 }
]);

// Join users with departments on matching IDs
const userDeptInfo = users().join(departments, ["deptId", "id"]);
// Results contain combined records with department info merged

// Join with custom comparison operator
const highBudgetUsers = users().join(departments, ["deptId", "===", "id"]);

// Join with custom function condition
const customJoin = users({ name: { like: "A" } }).join(departments, function(user, dept) {
  return user.deptId === dept.id && dept.budget > 400000;
});

// Multiple join conditions (all must be true)
const complexJoin = users().join(departments, 
  ["deptId", "id"],                                    // Basic field matching
  function(user, dept) {                               // Custom condition
    return user.name.length > 3;
  }
);

// Available operators in array conditions
const operatorExamples = users().join(departments,
  ["deptId", "===", "id"],    // Strict equality (default)
  ["deptId", "!==", "id"],    // Not equal
  ["deptId", "<", "id"],      // Less than
  ["deptId", ">", "id"],      // Greater than
  ["deptId", "<=", "id"],     // Less than or equal
  ["deptId", ">=", "id"],     // Greater than or equal
  ["deptId", "==", "id"],     // Loose equality
  ["deptId", "!=", "id"]      // Loose inequality
);

// When joining, conflicting field names are prefixed with "right_"
const result = users().join(departments, ["deptId", "id"]).get();
// Example result record:
// {
//   id: 1,              // From left table (users)
//   name: "Alice",      // From left table (users)
//   deptId: 10,         // From left table (users)
//   right_id: 10,       // From right table (departments) - prefixed to avoid conflict
//   right_name: "Engineering",  // From right table (departments) - prefixed
//   budget: 500000      // From right table (departments) - no conflict, no prefix
// }

// Chain joins with other operations
const filteredJoin = users({ name: { left: "A" } })
  .join(departments, ["deptId", "id"])
  .filter({ budget: { gt: 400000 } })
  .order("name")
  .get();

// Batch processing with pagination
function processAllUsers(pageSize = 100) {
  const totalUsers = db({ active: true }).count();
  const totalPages = Math.ceil(totalUsers / pageSize);
  
  for (let page = 1; page <= totalPages; page++) {
    const users = db({ active: true })
      .order("id")
      .start((page - 1) * pageSize + 1)
      .limit(pageSize)
      .get();
    
    // Process batch
    users.forEach(user => {
      processUser(user);
    });
  }
}

// Conditional updates based on aggregations
const avgSalary = db({ department: "Engineering" }).sum("salary") / 
                  db({ department: "Engineering" }).count();

db({ department: "Engineering" })
  .filter(function() { return this.salary < avgSalary * 0.8; })
  .update({ needsReview: true, reviewDate: new Date() });

// Complex reporting
const departmentReport = db().distinct("department").map(dept => {
  const deptEmployees = db({ department: dept });
  return {
    department: dept,
    totalEmployees: deptEmployees.count(),
    activeEmployees: deptEmployees.filter({ active: true }).count(),
    totalSalary: deptEmployees.sum("salary"),
    avgSalary: deptEmployees.sum("salary") / deptEmployees.count(),
    topPerformer: deptEmployees.order("performance desc").first()
  };
});