Querying and Filtering

/**
 * Query the database with filter conditions
 * @param filter - Filter object, function, or record ID
 * @returns QueryResult for further operations
 */
(filter?: object | function | string): QueryResult;

// Get all records
const allUsers = db();

// Filter by simple equality
const activeUsers = db({ active: true });

// Filter by multiple conditions (AND logic)
const youngActiveUsers = db({ active: true, age: { lt: 30 } });

// Filter by record ID
const specificUser = db("T000001R000002");

// Filter with custom function
const customFilter = db(function() {
  return this.name.startsWith('A') && this.age > 25;
});

// Available operators in filter objects
interface FilterOperators {
  is?: any;              // Exact equality (===)
  eq?: any;              // Loose equality (==)
  like?: string;         // Contains substring (case-sensitive)
  likenocase?: string;   // Contains substring (case-insensitive)
  left?: string;         // Starts with string
  leftnocase?: string;   // Starts with string (case-insensitive)
  right?: string;        // Ends with string
  rightnocase?: string;  // Ends with string (case-insensitive)
  lt?: number;           // Less than
  lte?: number;          // Less than or equal
  gt?: number;           // Greater than
  gte?: number;          // Greater than or equal
  ne?: any;              // Not equal (!=)
  regex?: RegExp;        // Regular expression match
  has?: any;             // Contains value (for arrays/objects)
  hasall?: any[];        // Contains all values
  isnocase?: string;     // Case-insensitive equality
  contains?: any;        // Array contains value
}

// Exact equality
const user1 = db({ id: { is: 1 } });

// String matching
const nameContains = db({ name: { like: "John" } });
const nameStartsWith = db({ name: { left: "Dr." } });
const nameEndsWith = db({ name: { right: "Jr." } });

// Case-insensitive matching
const caseInsensitive = db({ email: { likenocase: "GMAIL" } });

// Numeric comparisons
const adults = db({ age: { gte: 18 } });
const seniors = db({ age: { gt: 65 } });
const youngAdults = db({ age: { gte: 18, lt: 30 } });

// Regular expressions
const emailPattern = db({ email: { regex: /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/ } });

// Array operations
const hasSkill = db({ skills: { contains: "JavaScript" } });
const hasAllSkills = db({ skills: { hasall: ["JavaScript", "React"] } });

// Negation (prefix with !)
const notActive = db({ active: { '!is': true } });
const notContains = db({ name: { '!like': "test" } });

// Logical OR using arrays
database([condition1, condition2, condition3]): QueryResult;

// Custom filter functions
database(function(): boolean): QueryResult;

// OR logic with arrays
const seniorOrVIP = db([
  { age: { gte: 65 } },
  { vip: true }
]);

// Complex OR conditions
const multipleRoles = db([
  { role: "admin" },
  { role: "manager" },
  { permissions: { contains: "write" } }
]);

// Custom function filters
const complexLogic = db(function() {
  const hasValidEmail = this.email && this.email.includes('@');
  const isActive = this.active === true;
  const hasRecentActivity = new Date(this.lastLogin) > new Date('2023-01-01');
  
  return hasValidEmail && isActive && hasRecentActivity;
});

// Combining function filters with chaining
const filtered = db(function() {
  return this.department === 'Engineering';
}).filter(function() {
  return this.salary > 75000;
});

/**
 * Apply additional filtering to existing query results
 * @param filterObj - Additional filter conditions
 * @returns New QueryResult with combined filters
 */
filter(filterObj: object | function): QueryResult;

/**
 * Sort query results by specified columns
 * @param orderString - Sort specification (e.g., "name desc, age")
 * @returns New QueryResult with applied sorting
 */
order(orderString: string): QueryResult;

/**
 * Limit the number of results returned
 * @param count - Maximum number of records to return
 * @returns New QueryResult with limited results
 */
limit(count: number): QueryResult;

/**
 * Set starting offset for results (1-based indexing)
 * @param offset - Starting record number
 * @returns New QueryResult with offset applied
 */
start(offset: number): QueryResult;

// Chain multiple filters
const result = db({ department: "Sales" })
  .filter({ active: true })
  .filter({ performance: { gte: 4.0 } });

// Apply sorting
const sorted = db({ active: true })
  .order("salary desc, name");

// Pagination with limit and start
const page2 = db({ active: true })
  .order("name")
  .start(11)  // Start from 11th record (1-based)
  .limit(10); // Return 10 records

// Complex query chain
const complexQuery = db({ department: "Engineering" })
  .filter({ salary: { gte: 80000 } })
  .filter(function() { return this.skills.length >= 3; })
  .order("experience desc, salary desc")
  .limit(5);

// Query by single record ID
database(recordId: string): QueryResult;

// Query by array of record IDs
database(recordIds: string[]): QueryResult;

// Query by single record ID
const specificRecord = db("T000001R000005");

// Query by multiple record IDs
const multipleRecords = db([
  "T000001R000002",
  "T000001R000007",
  "T000001R000010"
]);

// Record IDs are automatically generated in format: T{dbId}R{recordId}
// You can get record IDs from query results:
const users = db({ active: true }).get();
users.forEach(user => {
  console.log('Record ID:', user.___id);
});

// Query context information (internal)
interface QueryContext {
  limit: number | false;      // Result limit
  start: number | false;      // Starting offset
  q: function[];              // Applied filters
  filterRaw: any[];           // Raw filter input
  index: any[];               // Index conditions
  order: string[];            // Sort order
  results: object[] | false;  // Cached results
  run: Date | null;           // Last execution time
  sort: Date | null;          // Last sort time
}

// Queries are cached automatically
const query1 = db({ active: true });
const results1 = query1.get(); // Executes query

const query2 = db({ active: true });
const results2 = query2.get(); // Uses cached results (if unchanged)

// Cache is invalidated on database modifications
db.insert({ name: "New User", active: true });
const results3 = query2.get(); // Re-executes query (cache invalidated)

// Function-based filters disable caching
const uncached = db(function() { return Math.random() > 0.5; });