Transactions & Raw Queries

/**
 * Execute operations within a transaction
 * @param callback - Function containing transaction operations
 * @param config - Optional transaction configuration
 * @returns Promise resolving to callback result
 */
function transaction<T>(callback: (trx: Knex.Transaction) => Promise<T>, config?: TransactionConfig): Promise<T>;

/**
 * Create a transaction provider for reusable transaction configuration
 * @param config - Transaction configuration
 * @returns Function that creates transactions with the given config
 */
function transactionProvider(config?: TransactionConfig): <T>(callback: (trx: Knex.Transaction) => Promise<T>) => Promise<T>;

interface Transaction extends Knex.QueryBuilder {
  /**
   * Commit the transaction
   * @param value - Optional value to resolve the transaction with
   * @returns Promise that resolves when transaction is committed
   */
  commit(value?: any): Promise<any>;

  /**
   * Rollback the transaction
   * @param error - Optional error to reject the transaction with
   * @returns Promise that rejects when transaction is rolled back
   */
  rollback(error?: any): Promise<any>;

  /**
   * Create a savepoint within the transaction
   * @param callback - Function to execute within the savepoint
   * @returns Promise resolving to callback result
   */
  savepoint<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>;

  /**
   * Check if the transaction has been completed
   * @returns true if transaction has been committed or rolled back
   */
  isCompleted(): boolean;

  /**
   * Promise that resolves when transaction execution completes
   */
  executionPromise: Promise<any>;

  /**
   * Reference to parent transaction (for nested transactions)
   */
  parentTransaction?: Transaction;

  /**
   * Transaction-specific user parameters
   */
  userParams: Record<string, any>;
}

interface TransactionConfig {
  /**
   * Transaction isolation level
   */
  isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable';

  /**
   * Whether the transaction is read-only
   */
  readOnly?: boolean;

  /**
   * Custom connection to use for the transaction
   */
  connection?: any;

  /**
   * Whether to disable acquiring connection from pool
   */
  doNotRejectOnRollback?: boolean;
}

/**
 * Create a raw SQL query
 * @param sql - Raw SQL string with optional parameter placeholders
 * @param bindings - Values to bind to SQL parameters
 * @returns Raw query instance
 */
function raw(sql: string, bindings?: readonly RawBinding[] | ValueDict): Knex.Raw;

/**
 * Create a column reference
 * @param columnName - Name of the column to reference
 * @returns Reference object for use in queries
 */
function ref<TMapping extends Record<string, string>>(columnName: string): Knex.Ref<string, TMapping>;

interface Raw<TResult = any> extends Promise<TResult> {
  /**
   * Wrap the raw query with prefix and suffix strings
   * @param before - String to prepend
   * @param after - String to append
   * @returns New Raw instance with wrapped SQL
   */
  wrap<TResult2 = TResult>(before: string, after: string): Raw<TResult2>;

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

  /**
   * Get SQL representation of the raw query
   * @returns SQL object with query string and bindings
   */
  toSQL(): Sql;

  /**
   * Convert raw query to string representation
   * @returns SQL string
   */
  toString(): string;

  /**
   * Set or get query context
   * @param context - Context object to set
   * @returns Raw query with context or current context
   */
  queryContext(context?: any): Raw<TResult>;

  /**
   * Convert to callback-style interface
   * @param callback - Node.js style callback
   * @returns void
   */
  asCallback(callback: (err: any, result: TResult) => void): 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;
}

interface Ref<TSrc extends string, TMapping extends Record<string, any>> {
  /**
   * Create an aliased reference
   * @param alias - Alias name for the reference
   * @returns Aliased reference
   */
  as<TAlias extends string>(alias: TAlias): Ref<TSrc, { [K in TAlias]: any }>;

  /**
   * Use the reference with a specific schema
   * @param schema - Schema name
   * @returns Reference with schema context
   */
  withSchema(schema: string): Ref<TSrc, TMapping>;

  /**
   * Convert reference to SQL representation
   * @returns SQL object
   */
  toSQL(): Sql;

  /**
   * Convert reference to string
   * @returns SQL string representation
   */
  toString(): string;
}

/**
 * Insert data in batches to handle large datasets efficiently
 * @param tableName - Target table name
 * @param data - Array of records to insert
 * @param chunkSize - Number of records per batch (default: 1000)
 * @returns Promise that resolves when all batches are inserted
 */
function batchInsert(tableName: string, data: readonly any[], chunkSize?: number): Promise<any>;

/**
 * Batch insert with custom options
 * @param tableName - Target table name
 * @param data - Array of records to insert
 * @param chunkSize - Number of records per batch
 * @param options - Batch insert options
 * @returns Promise that resolves when all batches are inserted
 */
function batchInsert(tableName: string, data: readonly any[], chunkSize: number, options: BatchInsertOptions): Promise<any>;

interface BatchInsertOptions {
  /**
   * Whether to use transactions for each batch
   */
  useTransaction?: boolean;

  /**
   * Custom transaction to use for all batches
   */
  transaction?: Transaction;

  /**
   * Columns to return after insert
   */
  returning?: string | readonly string[];
}

/**
 * Initialize the connection pool
 * @param config - Optional initialization configuration
 * @returns Promise that resolves when pool is initialized
 */
function initialize(config?: any): Promise<void>;

/**
 * Destroy the connection pool and close all connections
 * @param callback - Optional callback for completion
 * @returns Promise that resolves when pool is destroyed
 */
function destroy(callback?: (err?: any) => void): Promise<void>;

/**
 * Get a connection from the pool
 * @returns Promise resolving to database connection
 */
function acquireConnection(): Promise<any>;

/**
 * Release a connection back to the pool
 * @param connection - Connection to release
 * @returns Promise that resolves when connection is released
 */
function releaseConnection(connection: any): Promise<void>;

/**
 * Create a new knex instance with user parameters
 * @param params - User parameters to attach
 * @returns New knex instance with parameters
 */
function withUserParams(params: Record<string, any>): Knex;

/**
 * Access function helpers for database-specific functions
 */
fn: Knex.FunctionHelper;

interface FunctionHelper {
  /**
   * Current timestamp function with optional precision
   * @param precision - Optional precision for timestamp
   * @returns Raw SQL for current timestamp
   */
  now(precision?: number): Raw;

  /**
   * Generate UUID using database-specific function
   * @returns Raw SQL for UUID generation
   */
  uuid(): Raw;

  /**
   * Convert UUID to binary format (MySQL)
   * @param uuid - UUID string or column reference
   * @param ordered - Whether to use ordered binary format
   * @returns Raw SQL for UUID to binary conversion
   */
  uuidToBin(uuid: string | Raw, ordered?: boolean): Raw;

  /**
   * Convert binary to UUID format (MySQL)
   * @param binary - Binary data or column reference
   * @param ordered - Whether binary is in ordered format
   * @returns Raw SQL for binary to UUID conversion
   */
  binToUuid(binary: string | Raw, ordered?: boolean): Raw;
}

/**
 * Add event listener
 * @param event - Event name
 * @param callback - Event handler function
 * @returns Knex instance for chaining
 */
on(event: string, callback: Function): Knex;

/**
 * Remove event listener
 * @param event - Event name
 * @param callback - Event handler function to remove
 * @returns Knex instance for chaining
 */
off(event: string, callback: Function): Knex;

/**
 * Add one-time event listener
 * @param event - Event name
 * @param callback - Event handler function
 * @returns Knex instance for chaining
 */
once(event: string, callback: Function): Knex;

/**
 * Remove all listeners for an event
 * @param event - Event name
 * @returns Knex instance for chaining
 */
removeAllListeners(event?: string): Knex;

// Event types
interface QueryEvent {
  __knexUid: string;
  __knexTxId?: string;
  sql: string;
  bindings: readonly RawBinding[];
  options: any;
  queryContext?: any;
}

interface QueryResponseEvent extends QueryEvent {
  response: any;
  duration: [number, number]; // [seconds, nanoseconds]
}

interface QueryErrorEvent extends QueryEvent {
  error: Error;
  duration: [number, number]; // [seconds, nanoseconds]
}

type RawBinding = Value | QueryBuilder | Raw;
type Value = string | number | boolean | Date | Array<string | number | boolean | Date> | null | undefined;
type ValueDict = Record<string, RawBinding>;

interface Sql {
  method: string;
  sql: string;
  bindings: readonly RawBinding[];
  options: any;
  toNative(): SqlNative;
}

interface SqlNative {
  sql: string;
  bindings: readonly RawBinding[];
}

interface QueryContext {
  [key: string]: any;
}

interface ConnectionConfig {
  host?: string;
  port?: number;
  user?: string;
  password?: string;
  database?: string;
  ssl?: boolean | object;
  connection?: {
    timezone?: string;
    charset?: string;
    typeCast?: boolean | ((field: any, next: () => void) => any);
  };
}

interface PoolConfig {
  min?: number;
  max?: number;
  createTimeoutMillis?: number;
  acquireTimeoutMillis?: number;
  idleTimeoutMillis?: number;
  reapIntervalMillis?: number;
  createRetryIntervalMillis?: number;
  propagateCreateError?: boolean;
}

const knex = require('knex')({ client: 'postgresql', connection: process.env.DATABASE_URL });

// Basic transaction
await knex.transaction(async trx => {
  const user = await trx('users').insert({
    email: 'john@example.com',
    name: 'John Doe'
  }).returning('*');

  await trx('user_profiles').insert({
    user_id: user[0].id,
    bio: 'Software developer'
  });

  // Transaction will automatically commit if no errors
});

// Transaction with explicit commit/rollback control
const trx = await knex.transaction();
try {
  const result = await trx('users').insert(userData).returning('*');
  await trx('logs').insert({ action: 'user_created', user_id: result[0].id });
  
  await trx.commit();
  console.log('Transaction committed');
} catch (error) {
  await trx.rollback();
  console.error('Transaction rolled back:', error);
}

// Savepoints for nested transactions
await knex.transaction(async trx => {
  await trx('users').insert({ name: 'User 1' });
  
  await trx.savepoint(async sp => {
    await sp('users').insert({ name: 'User 2' });
    // This will rollback to the savepoint if it fails
    throw new Error('Rollback to savepoint');
  });
  
  // This insert will still happen
  await trx('users').insert({ name: 'User 3' });
});

// Raw queries with parameter binding
const users = await knex.raw('SELECT * FROM users WHERE age > ? AND city = ?', [18, 'New York']);

// Raw query with named parameters
const result = await knex.raw('SELECT * FROM users WHERE name = :name', { name: 'John' });

// Complex raw query with joins
const stats = await knex.raw(`
  SELECT 
    u.id,
    u.name,
    COUNT(p.id) as post_count,
    AVG(p.view_count) as avg_views
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  WHERE u.created_at > ?
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > ?
  ORDER BY post_count DESC
`, [new Date('2023-01-01'), 5]);

// Raw query with transaction
await knex.transaction(async trx => {
  await trx.raw('LOCK TABLE users IN EXCLUSIVE MODE');
  
  const maxId = await trx.raw('SELECT MAX(id) as max_id FROM users');
  const nextId = maxId.rows[0].max_id + 1;
  
  await trx.raw('INSERT INTO users (id, name) VALUES (?, ?)', [nextId, 'New User']);
});

// Column references
const query = knex('posts')
  .select('title', knex.ref('users.name').as('author_name'))
  .join('users', 'posts.user_id', 'users.id')
  .where(knex.ref('posts.published_at'), '>', knex.fn.now());

// Function helpers
await knex('events').insert({
  name: 'User signup',
  event_id: knex.fn.uuid(),
  created_at: knex.fn.now()
});

// MySQL UUID functions
await knex('sessions').insert({
  id: knex.fn.uuidToBin(knex.fn.uuid(), true),
  user_id: userId,
  expires_at: knex.fn.now()
});

// Batch insert for large datasets
const users = [];
for (let i = 0; i < 10000; i++) {
  users.push({
    name: `User ${i}`,
    email: `user${i}@example.com`,
    created_at: new Date()
  });
}

await knex.batchInsert('users', users, 500); // Insert in chunks of 500

// Event listeners for monitoring
knex.on('query', (query) => {
  console.log('Executing:', query.sql);
  console.log('Bindings:', query.bindings);
});

knex.on('query-response', (response, query) => {
  console.log('Query completed in', response.duration, 'ms');
});

knex.on('query-error', (error, query) => {
  console.error('Query failed:', error.message);
  console.error('SQL:', query.sql);
});

// Connection management
await knex.initialize(); // Initialize connection pool

// Custom connection for specific operations
const connection = await knex.acquireConnection();
try {
  await knex.raw('SELECT pg_advisory_lock(12345)').connection(connection);
  // Perform operations with locked resource
  await knex('critical_table').insert(data).connection(connection);
} finally {
  await knex.raw('SELECT pg_advisory_unlock(12345)').connection(connection);
  await knex.releaseConnection(connection);
}

// Graceful shutdown
process.on('SIGINT', async () => {
  await knex.destroy();
  process.exit(0);
});

// Database-specific raw operations
// PostgreSQL array operations
await knex.raw("UPDATE users SET tags = tags || ? WHERE id = ?", [['new-tag'], userId]);

// MySQL JSON operations
await knex.raw("UPDATE users SET preferences = JSON_SET(preferences, '$.theme', ?) WHERE id = ?", ['dark', userId]);

// SQLite pragma settings
await knex.raw("PRAGMA foreign_keys = ON");

// Transaction isolation levels
await knex.transaction(async trx => {
  await trx.raw('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
  // Perform serializable operations
}, { isolationLevel: 'serializable' });