Query Building

/**
 * Create a query builder for a table
 * @param tableName - Name of the table to query
 * @returns QueryBuilder instance for chaining operations
 */
function knex<TRecord = any, TResult = any>(tableName: string): Knex.QueryBuilder<TRecord, TResult>;

/**
 * Create a standalone query builder
 * @returns QueryBuilder instance
 */
function queryBuilder(): Knex.QueryBuilder;

interface QueryBuilder<TRecord = any, TResult = any> extends Promise<TResult> {
  // Core table methods
  from<TTable extends TableNames>(tableName: TTable): QueryBuilder<ResolveTableType<TTable>, TResult>;
  as(alias: string): QueryBuilder<TRecord, TResult>;
  table(tableName: string): QueryBuilder<TRecord, TResult>;
  withSchema(schemaName: string): QueryBuilder<TRecord, TResult>;
}

/**
 * Select specific columns from the query
 * @param columns - Column names to select
 * @returns QueryBuilder with selected columns
 */
select<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, Pick<TRecord, ColNameUT>[]>;

/**
 * Select columns with aliases and expressions
 * @param columns - Object mapping aliases to column expressions
 * @returns QueryBuilder with aliased columns
 */
select<AliasUT extends string>(columns: Record<AliasUT, string | Raw | QueryBuilder>): QueryBuilder<TRecord, { [K in AliasUT]: any }[]>;

/**
 * Add columns to existing selection
 * @param columns - Additional columns to select
 * @returns QueryBuilder with added columns
 */
columns<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, TResult>;

/**
 * Select distinct values
 * @param columns - Columns for distinct operation
 * @returns QueryBuilder with distinct selection
 */
distinct<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, Pick<TRecord, ColNameUT>[]>;

/**
 * Select distinct on specific columns (PostgreSQL)
 * @param columns - Columns for distinct on
 * @returns QueryBuilder with distinct on
 */
distinctOn<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, TResult>;

/**
 * Basic where clause
 * @param columnName - Column to filter on
 * @param value - Value to compare against
 * @returns QueryBuilder with where condition
 */
where<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;

/**
 * Where clause with operator
 * @param columnName - Column to filter on
 * @param operator - Comparison operator
 * @param value - Value to compare against
 * @returns QueryBuilder with where condition
 */
where<K extends keyof TRecord>(columnName: K, operator: ComparisionOperator, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;

/**
 * Where clause with object conditions
 * @param conditions - Object with column-value pairs
 * @returns QueryBuilder with multiple where conditions
 */
where(conditions: Partial<TRecord>): QueryBuilder<TRecord, TResult>;

/**
 * Where clause with raw SQL
 * @param raw - Raw SQL condition
 * @returns QueryBuilder with raw where condition
 */
where(raw: Raw): QueryBuilder<TRecord, TResult>;

/**
 * AND where clause
 * @param columnName - Column to filter on
 * @param value - Value to compare against
 * @returns QueryBuilder with AND where condition
 */
andWhere<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;

/**
 * OR where clause
 * @param columnName - Column to filter on
 * @param value - Value to compare against
 * @returns QueryBuilder with OR where condition
 */
orWhere<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;

/**
 * WHERE NOT clause
 * @param columnName - Column to filter on
 * @param value - Value to compare against
 * @returns QueryBuilder with WHERE NOT condition
 */
whereNot<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;

/**
 * WHERE IN clause
 * @param columnName - Column to check
 * @param values - Array of values to match
 * @returns QueryBuilder with WHERE IN condition
 */
whereIn<K extends keyof TRecord>(columnName: K, values: readonly DbColumn<TRecord[K]>[]): QueryBuilder<TRecord, TResult>;

/**
 * WHERE NOT IN clause
 * @param columnName - Column to check
 * @param values - Array of values to exclude
 * @returns QueryBuilder with WHERE NOT IN condition
 */
whereNotIn<K extends keyof TRecord>(columnName: K, values: readonly DbColumn<TRecord[K]>[]): QueryBuilder<TRecord, TResult>;

/**
 * WHERE NULL clause
 * @param columnName - Column to check for null
 * @returns QueryBuilder with WHERE NULL condition
 */
whereNull<K extends keyof TRecord>(columnName: K): QueryBuilder<TRecord, TResult>;

/**
 * WHERE NOT NULL clause
 * @param columnName - Column to check for not null
 * @returns QueryBuilder with WHERE NOT NULL condition
 */
whereNotNull<K extends keyof TRecord>(columnName: K): QueryBuilder<TRecord, TResult>;

/**
 * WHERE BETWEEN clause
 * @param columnName - Column to check
 * @param range - Array with min and max values
 * @returns QueryBuilder with WHERE BETWEEN condition
 */
whereBetween<K extends keyof TRecord>(columnName: K, range: readonly [DbColumn<TRecord[K]>, DbColumn<TRecord[K]>]): QueryBuilder<TRecord, TResult>;

/**
 * WHERE NOT BETWEEN clause
 * @param columnName - Column to check
 * @param range - Array with min and max values
 * @returns QueryBuilder with WHERE NOT BETWEEN condition
 */
whereNotBetween<K extends keyof TRecord>(columnName: K, range: readonly [DbColumn<TRecord[K]>, DbColumn<TRecord[K]>]): QueryBuilder<TRecord, TResult>;

/**
 * WHERE EXISTS clause
 * @param callback - Subquery callback
 * @returns QueryBuilder with WHERE EXISTS condition
 */
whereExists(callback: QueryCallback<any, any>): QueryBuilder<TRecord, TResult>;

/**
 * WHERE NOT EXISTS clause
 * @param callback - Subquery callback
 * @returns QueryBuilder with WHERE NOT EXISTS condition
 */
whereNotExists(callback: QueryCallback<any, any>): QueryBuilder<TRecord, TResult>;

/**
 * WHERE LIKE clause
 * @param columnName - Column to check
 * @param pattern - LIKE pattern
 * @returns QueryBuilder with WHERE LIKE condition
 */
whereLike<K extends keyof TRecord>(columnName: K, pattern: string): QueryBuilder<TRecord, TResult>;

/**
 * WHERE ILIKE clause (case-insensitive)
 * @param columnName - Column to check
 * @param pattern - ILIKE pattern
 * @returns QueryBuilder with WHERE ILIKE condition
 */
whereILike<K extends keyof TRecord>(columnName: K, pattern: string): QueryBuilder<TRecord, TResult>;

/**
 * Raw WHERE clause
 * @param sql - Raw SQL condition
 * @param bindings - Parameter bindings
 * @returns QueryBuilder with raw WHERE condition
 */
whereRaw(sql: string, bindings?: RawBinding[]): QueryBuilder<TRecord, TResult>;

/**
 * Inner join with another table
 * @param tableName - Table to join
 * @param leftColumn - Left table column
 * @param rightColumn - Right table column
 * @returns QueryBuilder with inner join
 */
join(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;

/**
 * Inner join with callback for complex conditions
 * @param tableName - Table to join
 * @param callback - Join condition callback
 * @returns QueryBuilder with inner join
 */
join(tableName: string, callback: JoinCallback): QueryBuilder<TRecord, TResult>;

/**
 * Left outer join
 * @param tableName - Table to join
 * @param leftColumn - Left table column
 * @param rightColumn - Right table column
 * @returns QueryBuilder with left join
 */
leftJoin(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;

/**
 * Right outer join
 * @param tableName - Table to join
 * @param leftColumn - Left table column
 * @param rightColumn - Right table column
 * @returns QueryBuilder with right join
 */
rightJoin(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;

/**
 * Full outer join
 * @param tableName - Table to join
 * @param leftColumn - Left table column
 * @param rightColumn - Right table column
 * @returns QueryBuilder with full outer join
 */
fullOuterJoin(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;

/**
 * Cross join
 * @param tableName - Table to cross join
 * @returns QueryBuilder with cross join
 */
crossJoin(tableName: string): QueryBuilder<TRecord, TResult>;

/**
 * Raw join clause
 * @param sql - Raw SQL join statement
 * @param bindings - Parameter bindings
 * @returns QueryBuilder with raw join
 */
joinRaw(sql: string, bindings?: RawBinding[]): QueryBuilder<TRecord, TResult>;

interface JoinCallback {
  (this: JoinClause, join: JoinClause): void;
}

interface JoinClause {
  on(left: string, operator: string, right: string): JoinClause;
  on(left: string, right: string): JoinClause;
  onIn(left: string, values: any[]): JoinClause;
  onNotIn(left: string, values: any[]): JoinClause;
  onNull(column: string): JoinClause;
  onNotNull(column: string): JoinClause;
  onExists(callback: QueryCallback<any, any>): JoinClause;
  onNotExists(callback: QueryCallback<any, any>): JoinClause;
  onBetween(column: string, range: [any, any]): JoinClause;
  onNotBetween(column: string, range: [any, any]): JoinClause;
  using(columns: string | readonly string[]): JoinClause;
}

/**
 * Count rows or specific column values
 * @param column - Column to count (optional, defaults to *)
 * @returns QueryBuilder with count aggregation
 */
count<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column?: string): QueryBuilder<TRecord, TResult2>;

/**
 * Count distinct values
 * @param column - Column to count distinct values
 * @returns QueryBuilder with count distinct aggregation
 */
countDistinct<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * Sum numeric values
 * @param column - Column to sum
 * @returns QueryBuilder with sum aggregation
 */
sum<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * Sum distinct numeric values
 * @param column - Column to sum distinct values
 * @returns QueryBuilder with sum distinct aggregation
 */
sumDistinct<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * Calculate average of numeric values
 * @param column - Column to average
 * @returns QueryBuilder with average aggregation
 */
avg<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * Calculate average of distinct numeric values
 * @param column - Column to average distinct values
 * @returns QueryBuilder with average distinct aggregation
 */
avgDistinct<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * Find minimum value
 * @param column - Column to find minimum
 * @returns QueryBuilder with min aggregation
 */
min<TResult2 = AggregationQueryResult<TResult, Dict<any>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * Find maximum value
 * @param column - Column to find maximum
 * @returns QueryBuilder with max aggregation
 */
max<TResult2 = AggregationQueryResult<TResult, Dict<any>>>(column: string): QueryBuilder<TRecord, TResult2>;

/**
 * ROW_NUMBER window function
 * @returns Window function builder
 */
rowNumber(): Knex.Ref<string, { [x: string]: string }>;

/**
 * RANK window function
 * @returns Window function builder
 */
rank(): Knex.Ref<string, { [x: string]: string }>;

/**
 * DENSE_RANK window function
 * @returns Window function builder
 */
denseRank(): Knex.Ref<string, { [x: string]: string }>;

/**
 * Insert data into table
 * @param data - Data to insert (single record or array)
 * @param returning - Columns to return after insert
 * @returns QueryBuilder for insert operation
 */
insert<TResult2 = number[]>(data: TRecord | readonly TRecord[], returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;

/**
 * Update existing records
 * @param data - Data to update
 * @param returning - Columns to return after update
 * @returns QueryBuilder for update operation
 */
update<TResult2 = number>(data: DbRecordArr<TRecord>, returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;

/**
 * Delete records
 * @param returning - Columns to return after delete
 * @returns QueryBuilder for delete operation
 */
del<TResult2 = number>(returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;

/**
 * Delete records (alias for del)
 * @param returning - Columns to return after delete
 * @returns QueryBuilder for delete operation
 */
delete<TResult2 = number>(returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;

/**
 * Truncate table
 * @returns QueryBuilder for truncate operation
 */
truncate(): QueryBuilder<TRecord, void>;

/**
 * Specify returning columns for DML operations
 * @param columns - Columns to return
 * @returns QueryBuilder with returning clause
 */
returning<T extends string | readonly string[]>(columns: T): QueryBuilder<TRecord, DeferredKeySelection.Resolve<TRecord, T>[]>;

/**
 * Upsert operation (insert or update)
 * @param data - Data to upsert
 * @param conflictColumns - Columns that define conflicts
 * @param updateColumns - Columns to update on conflict
 * @returns QueryBuilder for upsert operation
 */
upsert(data: TRecord | readonly TRecord[], conflictColumns?: string | readonly string[], updateColumns?: string | readonly string[]): QueryBuilder<TRecord, number>;

/**
 * Handle insert conflicts with ON CONFLICT clause
 * @param columns - Conflict columns
 * @returns OnConflictQueryBuilder for conflict handling
 */
onConflict<TResult2 = number>(columns?: string | readonly string[]): OnConflictQueryBuilder<TRecord, TResult2>;

/**
 * Union with another query
 * @param callback - Query callback or array of callbacks
 * @param wrap - Whether to wrap in parentheses
 * @returns QueryBuilder with union
 */
union(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;

/**
 * Union all with another query
 * @param callback - Query callback or array of callbacks
 * @param wrap - Whether to wrap in parentheses
 * @returns QueryBuilder with union all
 */
unionAll(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;

/**
 * Intersect with another query
 * @param callback - Query callback or array of callbacks
 * @param wrap - Whether to wrap in parentheses
 * @returns QueryBuilder with intersect
 */
intersect(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;

/**
 * Except (difference) with another query
 * @param callback - Query callback or array of callbacks
 * @param wrap - Whether to wrap in parentheses
 * @returns QueryBuilder with except
 */
except(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;

/**
 * Group by columns
 * @param columns - Columns to group by
 * @returns QueryBuilder with group by
 */
groupBy<K extends keyof TRecord>(...columns: readonly K[]): QueryBuilder<TRecord, TResult>;

/**
 * Group by with raw SQL
 * @param sql - Raw SQL for group by
 * @param bindings - Parameter bindings
 * @returns QueryBuilder with raw group by
 */
groupByRaw(sql: string, bindings?: readonly RawBinding[]): QueryBuilder<TRecord, TResult>;

/**
 * Order by columns
 * @param column - Column to order by
 * @param order - Sort order ('asc' or 'desc')
 * @returns QueryBuilder with order by
 */
orderBy<K extends keyof TRecord>(column: K, order?: 'asc' | 'desc'): QueryBuilder<TRecord, TResult>;

/**
 * Order by with raw SQL
 * @param sql - Raw SQL for order by
 * @param bindings - Parameter bindings
 * @returns QueryBuilder with raw order by
 */
orderByRaw(sql: string, bindings?: readonly RawBinding[]): QueryBuilder<TRecord, TResult>;

/**
 * Having clause
 * @param column - Column for having condition
 * @param operator - Comparison operator
 * @param value - Value to compare against
 * @returns QueryBuilder with having condition
 */
having(column: string, operator: string, value: any): QueryBuilder<TRecord, TResult>;

/**
 * Having clause with raw SQL
 * @param sql - Raw SQL for having
 * @param bindings - Parameter bindings
 * @returns QueryBuilder with raw having
 */
havingRaw(sql: string, bindings?: readonly RawBinding[]): QueryBuilder<TRecord, TResult>;

/**
 * OR having clause
 * @param column - Column for having condition
 * @param operator - Comparison operator
 * @param value - Value to compare against
 * @returns QueryBuilder with OR having condition
 */
orHaving(column: string, operator: string, value: any): QueryBuilder<TRecord, TResult>;

/**
 * Limit number of results
 * @param limit - Maximum number of rows to return
 * @returns QueryBuilder with limit
 */
limit(limit: number): QueryBuilder<TRecord, TResult>;

/**
 * Offset results for pagination
 * @param offset - Number of rows to skip
 * @returns QueryBuilder with offset
 */
offset(offset: number): QueryBuilder<TRecord, TResult>;

/**
 * Get first row from results
 * @returns QueryBuilder that returns single record
 */
first<TResult2 = TRecord extends any[] ? TRecord[0] | undefined : TRecord>(): QueryBuilder<TRecord, TResult2>;

/**
 * Pluck values from a specific column
 * @param column - Column to extract values from
 * @returns QueryBuilder that returns array of column values
 */
pluck<K extends keyof TRecord>(column: K): QueryBuilder<TRecord, TRecord[K][]>;

/**
 * Increment a numeric column
 * @param column - Column to increment
 * @param amount - Amount to increment by (default 1)
 * @returns QueryBuilder for increment operation
 */
increment<K extends keyof TRecord>(column: K, amount?: number): QueryBuilder<TRecord, number>;

/**
 * Decrement a numeric column
 * @param column - Column to decrement
 * @param amount - Amount to decrement by (default 1)
 * @returns QueryBuilder for decrement operation
 */
decrement<K extends keyof TRecord>(column: K, amount?: number): QueryBuilder<TRecord, number>;

/**
 * Modify query with callback function
 * @param callback - Function to modify query
 * @param args - Additional arguments for callback
 * @returns Modified QueryBuilder
 */
modify<TRecord2 = TRecord, TResult2 = TResult>(callback: QueryCallbackWithArgs<TRecord, TResult>, ...args: any[]): QueryBuilder<TRecord2, TResult2>;

/**
 * Clone the query builder
 * @returns Cloned QueryBuilder instance
 */
clone(): QueryBuilder<TRecord, TResult>;

/**
 * Use specific database connection
 * @param connection - Database connection to use
 * @returns QueryBuilder with specific connection
 */
connection(connection: any): QueryBuilder<TRecord, TResult>;

/**
 * Use transaction for query
 * @param transaction - Transaction instance
 * @returns QueryBuilder within transaction
 */
transacting(transaction: Transaction): QueryBuilder<TRecord, TResult>;

/**
 * Set query timeout
 * @param ms - Timeout in milliseconds
 * @param options - Timeout options
 * @returns QueryBuilder with timeout
 */
timeout(ms: number, options?: { cancel?: boolean }): QueryBuilder<TRecord, TResult>;

/**
 * Enable/disable debug mode
 * @param enabled - Whether to enable debugging
 * @returns QueryBuilder with debug setting
 */
debug(enabled?: boolean): QueryBuilder<TRecord, TResult>;

/**
 * Get SQL query object
 * @returns SQL object with query and bindings
 */
toSQL(): Sql;

/**
 * Get SQL query string
 * @returns SQL query as string
 */
toString(): string;

/**
 * Get column information for table
 * @param column - Specific column (optional)
 * @returns Promise with column information
 */
columnInfo(column?: string): Promise<ColumnInfo>;

/**
 * Convert to callback-style interface
 * @param callback - Node.js style callback
 * @returns void
 */
asCallback(callback: Function): void;

/**
 * Return results as readable stream
 * @param options - Stream options
 * @returns Readable stream of results
 */
stream(options?: Readonly<{ objectMode?: boolean; highWaterMark?: number }>): NodeJS.ReadableStream;

/**
 * Pipe results to writable stream
 * @param writable - Destination stream
 * @param options - Pipe options
 * @returns Destination stream
 */
pipe<T extends NodeJS.WritableStream>(writable: T, options?: { end?: boolean }): T;

type ComparisionOperator = '=' | '>' | '>=' | '<' | '<=' | '<>' | '!=' | 'like' | 'ilike';
type QueryCallback<TRecord, TResult> = (this: QueryBuilder<TRecord, TResult>, builder: QueryBuilder<TRecord, TResult>) => void;
type QueryCallbackWithArgs<TRecord, TResult> = (this: QueryBuilder<TRecord, TResult>, builder: QueryBuilder<TRecord, TResult>, ...args: any[]) => void;
type JoinCallback = (this: JoinClause, join: JoinClause) => void;
type DbColumn<T> = T extends string | number | boolean | Date | null | undefined ? T : never;
type DbRecordArr<T> = Partial<T>;
type Dict<T = any> = { [k: string]: T };
type MaybeArray<T> = T | T[];

interface OnConflictQueryBuilder<TRecord = any, TResult = any> {
  ignore(): QueryBuilder<TRecord, TResult>;
  merge(updates?: Partial<TRecord>): QueryBuilder<TRecord, TResult>;
  update(updates: Partial<TRecord>): QueryBuilder<TRecord, TResult>;
}

interface ColumnInfo {
  defaultValue: Value;
  type: string;
  maxLength: number;
  nullable: boolean;
}

interface AggregationQueryResult<TResult, TAggregationResult> {
  [key: string]: TAggregationResult;
}

const knex = require('knex')({ client: 'sqlite3', connection: ':memory:' });

// Basic queries
const users = await knex('users').select('*').where('active', true);
const user = await knex('users').where('id', 1).first();

// Complex joins
const postsWithAuthors = await knex('posts')
  .join('users', 'posts.user_id', 'users.id')
  .select('posts.title', 'posts.content', 'users.name as author')
  .where('posts.published', true);

// Aggregations
const stats = await knex('orders')
  .select('user_id')
  .count('* as order_count')
  .sum('total as revenue')
  .groupBy('user_id')
  .having('order_count', '>', 5);

// Subqueries
const recentOrders = await knex('orders')
  .where('created_at', '>', 
    knex('orders').max('created_at').where('user_id', 1)
  );

// Window functions
const rankedProducts = await knex('products')
  .select('*', knex.raw('ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank'))
  .where(knex.raw('ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC)'), '<=', 3);