Query Building

/**
 * Matches records where the indexed field exactly equals the given value
 * @param value - Value to match exactly
 * @returns Collection of matching records
 */
equals(value: IndexableType): Collection<T, TKey, TInsertType>;

/**
 * Case-insensitive string equality match
 * @param value - String value to match (case-insensitive)
 * @returns Collection of matching records
 */
equalsIgnoreCase(value: string): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field does not equal the given value
 * @param value - Value to exclude
 * @returns Collection of non-matching records
 */
notEqual(value: IndexableType): Collection<T, TKey, TInsertType>;

// Exact match
const alice = await db.friends.where("name").equals("Alice").toArray();

// Case-insensitive match
const users = await db.users.where("email").equalsIgnoreCase("ALICE@EXAMPLE.COM").toArray();

// Not equal
const nonAdults = await db.friends.where("age").notEqual(18).toArray();

/**
 * Matches records where the indexed field is greater than the given value
 * @param value - Minimum value (exclusive)
 * @returns Collection of matching records
 */
above(value: any): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field is greater than or equal to the given value
 * @param value - Minimum value (inclusive)
 * @returns Collection of matching records
 */
aboveOrEqual(value: any): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field is less than the given value
 * @param value - Maximum value (exclusive)
 * @returns Collection of matching records
 */
below(value: any): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field is less than or equal to the given value
 * @param value - Maximum value (inclusive)
 * @returns Collection of matching records
 */
belowOrEqual(value: any): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field is within the specified range
 * @param lower - Lower bound value
 * @param upper - Upper bound value
 * @param includeLower - Whether to include the lower bound (default: true)
 * @param includeUpper - Whether to include the upper bound (default: false)
 * @returns Collection of matching records
 */
between(
  lower: any, 
  upper: any, 
  includeLower?: boolean, 
  includeUpper?: boolean
): Collection<T, TKey, TInsertType>;

// Age comparisons
const adults = await db.friends.where("age").above(17).toArray();
const seniors = await db.friends.where("age").aboveOrEqual(65).toArray();
const minors = await db.friends.where("age").below(18).toArray();
const youngAdults = await db.friends.where("age").between(18, 30).toArray();

// Date ranges
const recentMessages = await db.messages
  .where("timestamp")
  .above(Date.now() - 24 * 60 * 60 * 1000) // Last 24 hours
  .toArray();

// String comparisons (alphabetical)
const namesAfterM = await db.friends.where("name").above("M").toArray();

// Range with custom bounds
const priceRange = await db.products
  .where("price")
  .between(10, 100, true, true) // Include both bounds
  .toArray();

/**
 * Matches records where the indexed string field starts with the given prefix
 * @param prefix - String prefix to match
 * @returns Collection of matching records
 */
startsWith(prefix: string): Collection<T, TKey, TInsertType>;

/**
 * Case-insensitive prefix matching
 * @param prefix - String prefix to match (case-insensitive)
 * @returns Collection of matching records
 */
startsWithIgnoreCase(prefix: string): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed string field starts with any of the given prefixes
 * @param prefixes - Array of string prefixes to match
 * @returns Collection of matching records
 */
startsWithAnyOf(prefixes: string[]): Collection<T, TKey, TInsertType>;
startsWithAnyOf(...prefixes: string[]): Collection<T, TKey, TInsertType>;

/**
 * Case-insensitive version of startsWithAnyOf
 * @param prefixes - Array of string prefixes to match (case-insensitive)
 * @returns Collection of matching records
 */
startsWithAnyOfIgnoreCase(prefixes: string[]): Collection<T, TKey, TInsertType>;
startsWithAnyOfIgnoreCase(...prefixes: string[]): Collection<T, TKey, TInsertType>;

// Prefix matching
const aNames = await db.friends.where("name").startsWith("A").toArray();
const phoneNumbers = await db.contacts.where("phone").startsWith("+1").toArray();

// Case-insensitive prefix
const emails = await db.users.where("email").startsWithIgnoreCase("ADMIN").toArray();

// Multiple prefixes
const vowelNames = await db.friends
  .where("name")
  .startsWithAnyOf(["A", "E", "I", "O", "U"])
  .toArray();

// Case-insensitive multiple prefixes
const commonPrefixes = await db.products
  .where("name")
  .startsWithAnyOfIgnoreCase("pro", "pre", "super")
  .toArray();

/**
 * Matches records where the indexed field equals any of the given values
 * @param keys - Array of values to match
 * @returns Collection of matching records
 */
anyOf(keys: readonly IndexableType[]): Collection<T, TKey, TInsertType>;
anyOf(...keys: IndexableType[]): Collection<T, TKey, TInsertType>;

/**
 * Case-insensitive version of anyOf for string values
 * @param keys - Array of string values to match (case-insensitive)
 * @returns Collection of matching records
 */
anyOfIgnoreCase(keys: string[]): Collection<T, TKey, TInsertType>;
anyOfIgnoreCase(...keys: string[]): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field does not equal any of the given values
 * @param keys - Array of values to exclude
 * @returns Collection of non-matching records
 */
noneOf(keys: readonly IndexableType[]): Collection<T, TKey, TInsertType>;

/**
 * Matches records where the indexed field falls within any of the given ranges
 * @param ranges - Array of range tuples [lower, upper]
 * @param options - Range inclusion options
 * @returns Collection of matching records
 */
inAnyRange(
  ranges: readonly { 0: any; 1: any }[], 
  options?: { includeLowers?: boolean; includeUppers?: boolean }
): Collection<T, TKey, TInsertType>;

// Match any of multiple values
const specificAges = await db.friends.where("age").anyOf([25, 30, 35]).toArray();
const specificIds = await db.friends.where("id").anyOf(1, 3, 5, 7).toArray();

// Case-insensitive any-of
const admins = await db.users
  .where("role")
  .anyOfIgnoreCase(["ADMIN", "admin", "Administrator"])
  .toArray();

// Exclude values
const nonTestUsers = await db.users
  .where("email")
  .noneOf(["test@example.com", "demo@example.com"])
  .toArray();

// Multiple ranges
const bizHours = await db.events
  .where("hour")
  .inAnyRange([[9, 12], [13, 17]], { includeLowers: true, includeUppers: false })
  .toArray();

// Age groups
const targetAges = await db.friends
  .where("age")
  .inAnyRange([[18, 25], [35, 45], [65, 75]])
  .toArray();

/**
 * Adds an additional filter condition using AND logic
 * @param predicate - Function that returns true for records to include
 * @returns Collection with additional filter applied
 */
and(predicate: (obj: T) => boolean): Collection<T, TKey, TInsertType>;

/**
 * Filters records using a predicate function
 * @param predicate - Function that returns true for records to include
 * @returns Collection of filtered records
 */
filter(predicate: (obj: T) => boolean): Collection<T, TKey, TInsertType>;

/**
 * Creates an OR condition with another index
 * @param indexName - Name of another index to query
 * @returns WhereClause for the alternate index
 */
or(indexName: string): WhereClause<T, TKey, TInsertType>;

/**
 * Limits the number of results returned
 * @param count - Maximum number of records to return
 * @returns Collection limited to specified count
 */
limit(count: number): Collection<T, TKey, TInsertType>;

/**
 * Skips the specified number of records
 * @param count - Number of records to skip
 * @returns Collection with records skipped
 */
offset(count: number): Collection<T, TKey, TInsertType>;

/**
 * Reverses the order of results
 * @returns Collection with reversed order
 */
reverse(): Collection<T, TKey, TInsertType>;

/**
 * Removes duplicate records
 * @returns Collection with duplicates removed
 */
distinct(): Collection<T, TKey, TInsertType>;

/**
 * Continues iteration until the predicate returns false
 * @param predicate - Function that returns false to stop iteration
 * @param includeStopEntry - Whether to include the record that failed the predicate
 * @returns Collection up to the stopping point
 */
until(predicate: (obj: T) => boolean, includeStopEntry?: boolean): Collection<T, TKey, TInsertType>;

// Combined filters
const activeYoungAdults = await db.friends
  .where("age")
  .between(18, 30)
  .and(friend => friend.status === "active")
  .toArray();

// Function-based filtering
const longNames = await db.friends
  .filter(friend => friend.name.length > 5)
  .toArray();

// Pagination
const page2 = await db.friends
  .orderBy("name")
  .offset(10)
  .limit(10)
  .toArray();

// Reverse order
const newestMessages = await db.messages
  .orderBy("timestamp")
  .reverse()
  .limit(10)
  .toArray();

// Remove duplicates
const uniqueAges = await db.friends
  .orderBy("age")
  .distinct()
  .toArray();

// Stop at condition
const friendsUntilAlice = await db.friends
  .orderBy("name")
  .until(friend => friend.name === "Alice", true)
  .toArray();

// OR conditions
const youngOrSenior = await db.friends
  .where("age")
  .below(25)
  .or("age")
  .above(65)
  .toArray();

/**
 * Executes the query and returns all matching records as an array
 * @returns Promise resolving to array of matching records
 */
toArray(): Promise<T[]>;

/**
 * Counts the number of matching records without retrieving them
 * @returns Promise resolving to the count
 */
count(): Promise<number>;

/**
 * Returns the first matching record
 * @returns Promise resolving to the first record or undefined
 */
first(): Promise<T | undefined>;

/**
 * Returns the last matching record
 * @returns Promise resolving to the last record or undefined
 */
last(): Promise<T | undefined>;

/**
 * Iterates through matching records with a callback function
 * @param callback - Function called for each record
 * @returns Promise that resolves when iteration completes
 */
each(callback: (obj: T, cursor: { key: any; primaryKey: TKey }) => void | boolean): Promise<void>;

/**
 * Returns records sorted by the specified property (non-indexed sorting)
 * @param keyPath - Property path to sort by
 * @returns Promise resolving to sorted array
 */
sortBy(keyPath: string): Promise<T[]>;

// Get all results
const adults = await db.friends.where("age").above(17).toArray();

// Count without retrieving
const adultCount = await db.friends.where("age").above(17).count();

// Get first/last
const youngest = await db.friends.orderBy("age").first();
const oldest = await db.friends.orderBy("age").last();

// Iterate with callback
await db.friends.where("age").above(17).each((friend, cursor) => {
  console.log(`${friend.name} is ${friend.age} years old`);
  if (friend.name === "Alice") {
    return false; // Stop iteration
  }
});

// Non-indexed sorting
const sortedByName = await db.friends
  .where("age")
  .above(17)
  .sortBy("name");

/**
 * Returns the index keys of matching records
 * @returns Promise resolving to array of index keys
 */
keys(): Promise<IndexableType[]>;

/**
 * Returns the primary keys of matching records
 * @returns Promise resolving to array of primary keys
 */
primaryKeys(): Promise<TKey[]>;

/**
 * Returns unique index keys of matching records
 * @returns Promise resolving to array of unique index keys
 */
uniqueKeys(): Promise<IndexableType[]>;

// Get index keys
const ageKeys = await db.friends.where("age").above(17).keys();
console.log("Ages of adults:", ageKeys);

// Get primary keys for bulk operations
const adultIds = await db.friends.where("age").above(17).primaryKeys();
await db.friends.bulkUpdate(
  adultIds.map(id => ({ key: id, changes: { category: "adult" } }))
);

// Get unique keys
const uniqueAges = await db.friends.where("age").above(0).uniqueKeys();
console.log("All unique ages:", uniqueAges);

/**
 * Updates all matching records
 * @param changes - Changes to apply (object or function)
 * @returns Promise resolving to number of modified records
 */
modify(changes: UpdateSpec<T> | ((obj: T, ctx: { value: T }) => void)): Promise<number>;

/**
 * Deletes all matching records
 * @returns Promise resolving to number of deleted records
 */
delete(): Promise<number>;

// Update matching records
const updatedCount = await db.friends
  .where("age")
  .below(18)
  .modify({ category: "minor" });
console.log(`Updated ${updatedCount} records`);

// Update with function
await db.friends
  .where("status")
  .equals("inactive")
  .modify(friend => {
    friend.lastSeen = Date.now();
    friend.status = "away";
  });

// Update with PropModification
import { add } from "dexie";
await db.friends
  .where("age")
  .equals(25)
  .modify({ age: add(1) }); // Increment age by 1

// Delete matching records
const deletedCount = await db.friends
  .where("status")
  .equals("deleted")
  .delete();
console.log(`Deleted ${deletedCount} records`);

// Conditional deletion
await db.messages
  .where("timestamp")
  .below(Date.now() - 30 * 24 * 60 * 60 * 1000) // Older than 30 days
  .delete();

// Multi-condition queries
const complexQuery = await db.products
  .where("category")
  .equals("electronics")
  .and(product => product.price > 100)
  .and(product => product.inStock)
  .filter(product => product.rating >= 4.0)
  .limit(10)
  .toArray();

// Pagination with search
const searchResults = await db.products
  .where("name")
  .startsWithIgnoreCase(searchTerm)
  .offset(pageNumber * pageSize)
  .limit(pageSize)
  .toArray();

// Range queries with sorting
const priceRangeQuery = await db.products
  .where("price")
  .between(minPrice, maxPrice)
  .reverse() // Highest price first
  .limit(20)
  .toArray();

// Multiple criteria with OR logic
const featuredProducts = await db.products
  .where("featured")
  .equals(1)
  .or("category")
  .equals("bestseller")
  .or("discount")
  .above(20)
  .toArray();