Query Formatting

/**
 * Format a query with values and options
 * @param query - SQL query template with parameter placeholders
 * @param values - Parameter values (array, object, or single value)
 * @param options - Formatting options
 * @returns Formatted SQL query string
 */
pgp.as.format(query: string | QueryFile, values?: any, options?: IFormattingOptions): string

interface IFormattingOptions {
  capSQL?: boolean // Capitalize SQL keywords
  partial?: boolean // Allow partial formatting
  def?: any // Default value for missing parameters
}

// Basic parameter substitution
const query1 = pgp.as.format('SELECT * FROM users WHERE id = $1', [123]);
// Result: "SELECT * FROM users WHERE id = 123"

// Named parameters
const query2 = pgp.as.format('SELECT * FROM users WHERE name = ${name} AND age > ${age}', {
  name: 'John',
  age: 25
});
// Result: "SELECT * FROM users WHERE name = 'John' AND age > 25"

// With formatting options
const query3 = pgp.as.format('select * from users where id = $1', [123], { capSQL: true });
// Result: "SELECT * FROM users WHERE id = 123"

/**
 * Format a value for SQL
 * @param value - Value to format (any type)
 * @returns SQL-safe string representation
 */
pgp.as.value(value: any | (() => any)): string

/**
 * Format a SQL name (identifier)
 * @param name - SQL identifier name
 * @returns Properly escaped SQL identifier
 */
pgp.as.name(name: any | (() => any)): string

/**
 * Format an alias (quoted identifier)
 * @param name - Alias name or function returning name
 * @returns Quoted SQL alias
 */
pgp.as.alias(name: string | (() => string)): string

/**
 * Format a number value
 * @param value - Number or bigint value to format
 * @returns String representation of number
 */
pgp.as.number(value: number | bigint | (() => number | bigint)): string

/**
 * Format a boolean value
 * @param value - Boolean value to format
 * @returns 'true' or 'false' string
 */
pgp.as.bool(value: any | (() => any)): string

/**
 * Format a text string
 * @param value - Text value to format
 * @param raw - Whether to return raw (unquoted) text
 * @returns SQL-safe quoted string or raw text
 */
pgp.as.text(value: any | (() => any), raw?: boolean): string

/**
 * Format a Date object
 * @param date - Date object to format
 * @param raw - Whether to return raw timestamp
 * @returns SQL timestamp string
 */
pgp.as.date(date: Date | (() => Date), raw?: boolean): string

// Value formatting
const userValue = pgp.as.value(123); // "123"
const nameValue = pgp.as.value("John O'Connor"); // "'John O''Connor'"
const nullValue = pgp.as.value(null); // "null"

// Name formatting
const tableName = pgp.as.name('user_table'); // "user_table"
const complexName = pgp.as.name('table with spaces'); // '"table with spaces"'

// Alias formatting  
const alias = pgp.as.alias('user_count'); // '"user_count"'

// Type-specific formatting
const numStr = pgp.as.number(123.45); // "123.45"
const boolStr = pgp.as.bool(true); // "true"
const textStr = pgp.as.text('Hello World'); // "'Hello World'"
const dateStr = pgp.as.date(new Date('2023-01-01')); // "'2023-01-01T00:00:00.000Z'"

/**
 * Format an array as SQL array literal
 * @param arr - Array to format
 * @param options - Array formatting options
 * @returns SQL array literal string
 */
pgp.as.array(arr: any[] | (() => any[]), options?: { capSQL?: boolean }): string

/**
 * Format values as CSV (comma-separated values)
 * @param values - Values to format as CSV
 * @returns Comma-separated SQL values
 */
pgp.as.csv(values: any | (() => any)): string

/**
 * Format data as JSON
 * @param data - Data to format as JSON
 * @param raw - Whether to return raw JSON string
 * @returns SQL JSON string
 */
pgp.as.json(data: any | (() => any), raw?: boolean): string

// Array formatting
const arrayStr = pgp.as.array([1, 2, 3]); // "ARRAY[1,2,3]"
const stringArray = pgp.as.array(['a', 'b', 'c']); // "ARRAY['a','b','c']"

// CSV formatting
const csvStr = pgp.as.csv([1, 'text', true]); // "1,'text',true"
const csvQuery = `SELECT * FROM users WHERE id IN (${pgp.as.csv([1, 2, 3])})`;

// JSON formatting
const jsonStr = pgp.as.json({ name: 'John', age: 30 }); // "'{\"name\":\"John\",\"age\":30}'"
const rawJson = pgp.as.json({ key: 'value' }, true); // "{\"key\":\"value\"}"

/**
 * Format a buffer object
 * @param obj - Buffer object to format
 * @param raw - Whether to return raw buffer data
 * @returns SQL bytea string
 */
pgp.as.buffer(obj: object | (() => object), raw?: boolean): string

/**
 * Format a function call result
 * @param func - Function to call and format result
 * @param raw - Whether to return raw result
 * @param cc - Custom context for function call
 * @returns Formatted function result
 */
pgp.as.func(func: (cc: any) => any, raw?: boolean, cc?: any): string

// Buffer formatting
const bufferStr = pgp.as.buffer(Buffer.from('hello')); // "\\x68656c6c6f"

// Function formatting
const dynamicValue = pgp.as.func(() => new Date().getTime()); // Current timestamp
const contextValue = pgp.as.func(cc => cc.userId, false, { userId: 123 }); // "123"

/**
 * Generate INSERT query for single or multiple records
 * @param data - Data object or array of objects to insert
 * @param columns - Column configuration (optional)
 * @param table - Target table name (optional)
 * @returns INSERT SQL query string
 */
pgp.helpers.insert(data: object | object[], columns?: QueryColumns<any> | null, table?: string | ITable | TableName): string

/**
 * Generate UPDATE query for single or multiple records
 * @param data - Data object or array of objects to update
 * @param columns - Column configuration (optional)  
 * @param table - Target table name (optional)
 * @param options - Update options
 * @returns UPDATE SQL query string
 */
pgp.helpers.update(data: object | object[], columns?: QueryColumns<any> | null, table?: string | ITable | TableName, options?: IUpdateOptions): string

/**
 * Generate VALUES clause for multi-row operations
 * @param data - Data array to generate values for
 * @param columns - Column configuration (optional)
 * @returns VALUES clause string
 */
pgp.helpers.values(data: object | object[], columns?: QueryColumns<any> | null): string

/**
 * Generate SET clause for UPDATE operations
 * @param data - Data object with update values
 * @param columns - Column configuration (optional)
 * @returns SET clause string
 */
pgp.helpers.sets(data: object, columns?: QueryColumns<any> | null): string

/**
 * Concatenate multiple queries into single query string
 * @param queries - Array of query objects or strings
 * @returns Concatenated query string
 */
pgp.helpers.concat(queries: Array<string | QueryFile | IQueryConfig>): string

/**
 * Create TableName object from dot-separated path
 * @param path - Dot-separated table path (e.g., 'schema.table')
 * @param ...args - Additional arguments for TableName construction
 * @returns TableName instance
 */
pgp.helpers._TN(path: string, ...args: any[]): TableName

interface IUpdateOptions {
  tableAlias?: string // Alias for target table
  valueAlias?: string // Alias for values table
  emptyUpdate?: any // Value to return for empty updates
}

interface IQueryConfig {
  query: string | QueryFile // Query text
  values?: any // Query parameters
  options?: IFormattingOptions // Formatting options
}

// INSERT helper
const insertQuery = pgp.helpers.insert([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' }
], null, 'users');
// Result: INSERT INTO "users"("name","email") VALUES('John','john@example.com'),('Jane','jane@example.com')

// UPDATE helper
const updateQuery = pgp.helpers.update(
  { name: 'John Updated', email: 'john.new@example.com' },
  null,
  'users'
) + ' WHERE id = 123';

// VALUES helper for custom operations
const valuesClause = pgp.helpers.values([
  { id: 1, value: 'a' },
  { id: 2, value: 'b' }
]);
const customQuery = `INSERT INTO temp_table(id, value) ${valuesClause} ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value`;

// SET helper
const setClause = pgp.helpers.sets({ name: 'Updated', status: 'active' });
const updateQuery2 = `UPDATE users SET ${setClause} WHERE id = $1`;

// Concatenate queries
const batchQuery = pgp.helpers.concat([
  'DELETE FROM temp_data',
  { query: 'INSERT INTO temp_data SELECT * FROM source WHERE date > $1', values: [yesterday] },
  'ANALYZE temp_data'
]);

// Table name helper with dot notation
const table = pgp.helpers._TN('app.users'); // Creates TableName for 'app'.'users'
const insertQuery = pgp.helpers.insert(userData, null, table);

/**
 * Table name helper class
 */
class TableName {
  constructor(table: string | ITable)
  readonly name: string // Full table name with schema
  readonly table: string // Table name only
  readonly schema: string // Schema name only
  toString(): string
  toPostgres(): string // Returns PostgreSQL-formatted name
}

/**
 * Column configuration helper class
 */
class Column<T> {
  constructor(col: string | IColumnConfig<T>)
  readonly name: string // Column name
  readonly prop: string // Property name
  readonly mod: FormattingFilter // Formatting modifier
  readonly cast: string // Type cast
  readonly cnd: boolean // Conditional column
  readonly def: any // Default value
  readonly castText: string // Cast text representation
  readonly escapedName: string // Escaped column name
  readonly variable: string // Variable placeholder
  readonly init: (col: IColumnDescriptor<T>) => any // Initialization function
  readonly skip: (col: IColumnDescriptor<T>) => boolean // Skip condition function
  toString(level?: number): string
}

/**
 * Column set manager class
 */
class ColumnSet<T> {
  constructor(columns: Column<T> | Array<string | IColumnConfig<T> | Column<T>>, options?: IColumnSetOptions)
  readonly columns: Column<T>[] // Array of columns
  readonly names: string // Column names string
  readonly table: TableName // Associated table
  readonly variables: string // Variable placeholders string
  
  assign(source?: IAssignOptions): string
  assignColumns(options?: IAssignColumnsOptions): string
  extend<S>(columns: Column<T> | ColumnSet<T> | Array<string | IColumnConfig<T> | Column<T>>): ColumnSet<S>
  merge<S>(columns: Column<T> | ColumnSet<T> | Array<string | IColumnConfig<T> | Column<T>>): ColumnSet<S>
  prepare(obj: object): object
  toString(level?: number): string
}

interface ITable {
  schema?: string // Schema name
  table: string // Table name
}

interface IColumnConfig<T> {
  name: string // Column name
  prop?: string // Source property name
  mod?: FormattingFilter // Formatting filter
  cast?: string // Type cast
  cnd?: boolean // Conditional column
  def?: any // Default value
  init?(col: IColumnDescriptor<T>): any // Initialization function
  skip?(col: IColumnDescriptor<T>): boolean // Skip condition
}

interface IColumnSetOptions {
  table?: string | ITable | TableName // Associated table
  inherit?: boolean // Inherit from parent
}

// Table name helper
const table = new pgp.helpers.TableName({ schema: 'public', table: 'users' });
console.log(table.name); // "public"."users"

// Column configuration
const columns = new pgp.helpers.ColumnSet([
  'id',
  'name',
  { name: 'email', prop: 'email_address' },
  { name: 'created_at', def: () => new Date() },
  { name: 'data', mod: ':json' }
], { table: 'users' });

// Advanced INSERT with column set
const insertQuery = pgp.helpers.insert(userData, columns);

// Column assignment for UPDATE
const assignQuery = columns.assign({ from: 'source', to: 'target' });
const updateQuery = `UPDATE users SET ${assignQuery} FROM (VALUES ${pgp.helpers.values(userData, columns)}) AS source(id, name, email, created_at, data) WHERE users.id = source.id`;

// Available formatting filters
type FormattingFilter = 
  | '^'      // Raw text (no escaping)
  | '~'      // SQL name formatting
  | '#'      // SQL identifier formatting
  | ':raw'   // Raw value (no quotes)
  | ':alias' // Quoted alias
  | ':name'  // SQL name
  | ':json'  // JSON formatting
  | ':csv'   // CSV formatting
  | ':list'  // List formatting
  | ':value' // Standard value formatting

// Using formatting filters in queries
const query = 'SELECT ${fields^} FROM ${table~} WHERE ${condition^}';
const formatted = pgp.as.format(query, {
  fields: 'id, name, email',
  table: 'users',
  condition: 'active = true'
});
// Result: SELECT id, name, email FROM "users" WHERE active = true

// Using filters in column configuration
const columns = new pgp.helpers.ColumnSet([
  { name: 'data', mod: ':json' },
  { name: 'tags', mod: ':csv' },
  { name: 'raw_sql', mod: '^' }
]);

// Query parameter types
type QueryColumns<T> = Column<T> | ColumnSet<T> | Array<string | IColumnConfig<T> | Column<T>>

// Column descriptor for runtime information
interface IColumnDescriptor<T> {
  source: T // Source data object
  name: string // Column name
  value: any // Column value
  exists: boolean // Whether value exists in source
}

// Assignment options for column sets
interface IAssignOptions {
  source?: object // Source object configuration
  prefix?: string // Prefix for assignments
}

interface IAssignColumnsOptions {
  from?: string // Source table alias
  to?: string // Target table alias  
  skip?: string | string[] | ((c: Column<any>) => boolean) // Columns to skip
}

// Custom Type Formatting object
interface ICTFObject {
  toPostgres(a: any): any // Convert to PostgreSQL format
}

// Formatting symbols for custom type formatting
interface ICTF {
  toPostgres: symbol // Symbol for toPostgres method
  rawType: symbol // Symbol for raw type indication
}