Configuration and Utilities

/**
 * Initialize pg-promise library with configuration options
 * @param options - Library initialization options
 * @returns Configured pg-promise instance
 */
function pgPromise(options?: IInitOptions): IMain

interface IInitOptions {
  // Query formatting options
  pgFormatting?: boolean // Use pg driver formatting instead of pg-promise
  capSQL?: boolean // Capitalize SQL keywords
  
  // Connection options
  pgNative?: boolean // Use native PostgreSQL bindings
  schema?: ValidSchema | ((dc: any) => ValidSchema) // Default database schema(s)
  noWarnings?: boolean // Disable diagnostic warnings
  
  // Event handlers
  connect?(e: IConnectEvent): void // Client connected event
  disconnect?(e: IDisconnectEvent): void // Client disconnected event
  query?(e: IEventContext): void // Query execution event
  receive?(e: IReceiveEvent): void // Data received event
  task?(e: IEventContext): void // Task started event
  transact?(e: IEventContext): void // Transaction started event
  error?(err: any, e: IEventContext): void // Error occurred event
  extend?(obj: IDatabase, dc: any): void // Database instance extension
}

type ValidSchema = string | string[] | null | void

// Basic initialization
const pgp = require('pg-promise')();

// With query formatting options
const pgp = require('pg-promise')({
  capSQL: true, // Capitalize SQL keywords
  pgFormatting: false // Use pg-promise advanced formatting
});

// With schema configuration
const pgp = require('pg-promise')({
  schema: ['app', 'public'], // Search in app schema first, then public
  // Or dynamic schema:
  // schema: (dc) => dc.userType === 'admin' ? ['admin', 'public'] : ['public']
});

// With native bindings (requires pg-native)
const pgp = require('pg-promise')({
  pgNative: true, // Use native PostgreSQL bindings for performance
  noWarnings: true // Disable warnings
});

// With comprehensive event handling
const pgp = require('pg-promise')({
  connect: (e) => {
    console.log('Connected:', e.client.processID, 'use count:', e.useCount);
  },
  
  disconnect: (e) => {
    console.log('Disconnected:', e.client.processID);
  },
  
  query: (e) => {
    console.log('Query:', e.query.slice(0, 50) + '...');
  },
  
  receive: (e) => {
    console.log('Received:', e.data.length, 'rows');
  },
  
  error: (err, e) => {
    console.error('Error:', err.message, 'Query:', e.query);
  }
});

// Event context interfaces
interface IConnectEvent {
  client: IClient // Database client instance
  dc: any // Database context
  useCount: number // Connection usage count
}

interface IDisconnectEvent {
  client: IClient // Database client instance
  dc: any // Database context
}

interface IReceiveEvent {
  data: any[] // Received data rows
  result: IResultExt | void // Full result object (undefined for streams)
  ctx: IEventContext // Query execution context
}

interface IEventContext {
  client: IClient // Database client
  cn: any // Connection parameters
  dc: any // Database context
  query: any // Query being executed
  params: any // Query parameters
  values: any // Parameter values
  queryFilePath?: string // Query file path (if QueryFile used)
  ctx: ITaskContext // Task/transaction context
}

// Performance monitoring
const pgp = require('pg-promise')({
  query: (e) => {
    e.ctx.startTime = Date.now();
  },
  
  receive: (e) => {
    const duration = Date.now() - e.ctx.startTime;
    if (duration > 1000) { // Log slow queries
      console.log('Slow query detected:', {
        duration: duration,
        query: e.ctx.query,
        rows: e.data.length
      });
    }
  }
});

// Query logging with context
const pgp = require('pg-promise')({
  query: (e) => {
    console.log(`[${e.ctx.tag || 'query'}] ${e.query}`, e.params || e.values);
  }
});

// Connection pool monitoring
let activeConnections = 0;

const pgp = require('pg-promise')({
  connect: (e) => {
    activeConnections++;
    console.log(`Connection pool: ${activeConnections} active connections`);
  },
  
  disconnect: (e) => {
    activeConnections--;
    console.log(`Connection pool: ${activeConnections} active connections`);
  }
});

// Error aggregation
const errorStats = new Map();

const pgp = require('pg-promise')({
  error: (err, e) => {
    const errorType = err.code || err.name;
    const count = errorStats.get(errorType) || 0;
    errorStats.set(errorType, count + 1);
    
    console.error(`Error [${errorType}] occurred ${count + 1} times:`, err.message);
  }
});

/**
 * Database extension handler
 * @param obj - Database instance to extend
 * @param dc - Database context
 */
interface ExtendHandler {
  (obj: IDatabase, dc: any): void
}

// Adding custom methods
const pgp = require('pg-promise')({
  extend: (obj, dc) => {
    // Add custom query methods
    obj.findUser = function(email) {
      return this.oneOrNone('SELECT * FROM users WHERE email = $1', [email]);
    };
    
    obj.createUser = function(userData) {
      return this.one(`
        INSERT INTO users(name, email, created_at) 
        VALUES($1, $2, NOW()) 
        RETURNING *
      `, [userData.name, userData.email]);
    };
    
    obj.getUserWithProfile = function(userId) {
      return this.one(`
        SELECT u.*, p.bio, p.avatar_url
        FROM users u
        LEFT JOIN profiles p ON u.id = p.user_id
        WHERE u.id = $1
      `, [userId]);
    };
    
    // Add context-aware methods
    obj.log = function(message) {
      console.log(`[${dc.environment || 'dev'}] ${message}`);
    };
  }
});

const db = pgp(connectionString, { environment: 'production' });

// Use custom methods
const user = await db.findUser('john@example.com');
const newUser = await db.createUser({ name: 'Jane', email: 'jane@example.com' });
db.log('Custom method executed');

// Adding repository pattern
const pgp = require('pg-promise')({
  extend: (obj, dc) => {
    obj.users = {
      findById: (id) => obj.oneOrNone('SELECT * FROM users WHERE id = $1', [id]),
      findByEmail: (email) => obj.oneOrNone('SELECT * FROM users WHERE email = $1', [email]),
      create: (data) => obj.one('INSERT INTO users(${this:name}) VALUES(${this:csv}) RETURNING *', data),
      update: (id, data) => obj.oneOrNone('UPDATE users SET ${this:name} = ${this:csv} WHERE id = $1 RETURNING *', [data, id]),
      delete: (id) => obj.none('DELETE FROM users WHERE id = $1', [id])
    };
    
    obj.orders = {
      findByUser: (userId) => obj.any('SELECT * FROM orders WHERE user_id = $1', [userId]),
      create: (orderData) => obj.tx(async t => {
        const order = await t.one('INSERT INTO orders(user_id, total) VALUES($1, $2) RETURNING *', 
          [orderData.userId, orderData.total]);
        
        if (orderData.items) {
          const items = orderData.items.map(item => 
            t.none('INSERT INTO order_items(order_id, product_id, quantity) VALUES($1, $2, $3)', 
              [order.id, item.productId, item.quantity])
          );
          await Promise.all(items);
        }
        
        return order;
      })
    };
  }
});

// Usage with repository pattern
const user = await db.users.findById(123);
const orders = await db.orders.findByUser(123);
const newOrder = await db.orders.create({
  userId: 123,
  total: 99.99,
  items: [{ productId: 1, quantity: 2 }]
});

/**
 * Utility functions namespace
 */
interface IUtils {
  camelize(text: string): string // Convert text to camelCase
  camelizeVar(text: string): string // Convert text to valid camelCase variable name
  enumSql(dir: string, options?: IEnumSqlOptions, cb?: EnumSqlCallback): object // Enumerate SQL files
  taskArgs(args: Arguments): ITaskArguments // Normalize task/transaction arguments
}

interface IEnumSqlOptions {
  recursive?: boolean // Include subdirectories
  ignoreErrors?: boolean // Ignore access/naming errors
}

type EnumSqlCallback = (file: string, name: string, path: string) => any

interface ITaskArguments {
  options: any // Parsed options object
  cb: Function // Callback function
  [0]: any // Options (same as options property)
  [1]: Function // Callback (same as cb property)
}

// Text utilities
console.log(pgp.utils.camelize('user_full_name')); // 'userFullName'
console.log(pgp.utils.camelize('first-name')); // 'firstName'
console.log(pgp.utils.camelizeVar('123_invalid-var')); // 'invalidVar'

// SQL file enumeration
const sql = pgp.utils.enumSql('./sql', { recursive: true }, (file, name, path) => {
  return new pgp.QueryFile(file, { minify: true });
});

// Usage: sql.users.getById, sql.orders.create, etc.

// Task argument parsing (for custom extensions)
const pgp = require('pg-promise')({
  extend: (obj) => {
    obj.customTx = function() {
      const args = pgp.utils.taskArgs(arguments);
      
      // Add default transaction mode if not specified
      if (!args.options.mode) {
        args.options.mode = new pgp.txMode.TransactionMode({
          tiLevel: pgp.txMode.isolationLevel.readCommitted
        });
      }
      
      return obj.tx.apply(this, args);
    };
  }
});

// Directory structure utilities
function createSqlRepository(baseDir) {
  const queries = {};
  
  const files = pgp.utils.enumSql(baseDir, { recursive: true }, (file, name, path) => {
    const queryFile = new pgp.QueryFile(file, { minify: true });
    if (queryFile.error) {
      console.warn(`Failed to load ${file}: ${queryFile.error.message}`);
      return null;
    }
    return queryFile;
  });
  
  // Convert flat structure to nested object
  function buildNested(obj, keys, value) {
    if (keys.length === 1) {
      obj[keys[0]] = value;
    } else {
      const key = keys.shift();
      obj[key] = obj[key] || {};
      buildNested(obj[key], keys, value);
    }
  }
  
  Object.entries(files).forEach(([path, queryFile]) => {
    if (queryFile) {
      const keys = path.split('.');
      buildNested(queries, keys, queryFile);
    }
  });
  
  return queries;
}

/**
 * Transaction Mode namespace
 */
interface ITxMode {
  isolationLevel: typeof isolationLevel // Isolation level enum
  TransactionMode: typeof TransactionMode // Transaction mode class
}

/**
 * Transaction isolation levels
 */
enum isolationLevel {
  none = 0,           // No specific isolation
  serializable = 1,   // Serializable isolation
  repeatableRead = 2, // Repeatable read isolation
  readCommitted = 3   // Read committed isolation (default)
}

/**
 * Transaction Mode configuration class
 */
class TransactionMode {
  constructor(options?: ITransactionModeOptions)
  
  begin(cap?: boolean): string // Generate BEGIN statement
}

interface ITransactionModeOptions {
  tiLevel?: isolationLevel // Transaction isolation level
  readOnly?: boolean // Read-only transaction
  deferrable?: boolean // Deferrable transaction (only with serializable + readOnly)
}

// Basic transaction modes
const readOnlyMode = new pgp.txMode.TransactionMode({
  readOnly: true
});

const serializedMode = new pgp.txMode.TransactionMode({
  tiLevel: pgp.txMode.isolationLevel.serializable
});

const deferrableMode = new pgp.txMode.TransactionMode({
  tiLevel: pgp.txMode.isolationLevel.serializable,
  readOnly: true,
  deferrable: true
});

// Using transaction modes
await db.tx({ mode: readOnlyMode }, async t => {
  // Read-only transaction - no modifications allowed
  const users = await t.any('SELECT * FROM users');
  const stats = await t.one('SELECT COUNT(*) as count FROM orders', [], r => r.count);
  return { users, orderCount: stats };
});

await db.tx({ mode: serializedMode }, async t => {
  // Serializable transaction - highest isolation level
  const balance = await t.one('SELECT balance FROM accounts WHERE id = $1', [accountId], r => r.balance);
  
  if (balance >= amount) {
    await t.none('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, accountId]);
    await t.none('INSERT INTO transactions(account_id, amount) VALUES($1, $2)', [accountId, -amount]);
  } else {
    throw new Error('Insufficient funds');
  }
});

// Dynamic transaction mode selection
function getTransactionMode(operationType) {
  switch (operationType) {
    case 'report':
      return new pgp.txMode.TransactionMode({
        readOnly: true,
        tiLevel: pgp.txMode.isolationLevel.readCommitted
      });
      
    case 'financial':
      return new pgp.txMode.TransactionMode({
        tiLevel: pgp.txMode.isolationLevel.serializable
      });
      
    case 'bulk_import':
      return new pgp.txMode.TransactionMode({
        tiLevel: pgp.txMode.isolationLevel.readCommitted
      });
      
    default:
      return null; // Use default
  }
}

// Usage
const mode = getTransactionMode('financial');
await db.tx({ mode }, async t => {
  // Financial operations with serializable isolation
});

// Environment-specific configuration
function createPgPromise(environment) {
  const config = {
    development: {
      capSQL: false,
      noWarnings: false,
      query: (e) => console.log('DEV Query:', e.query),
      error: (err, e) => console.error('DEV Error:', err.message)
    },
    
    production: {
      capSQL: true,
      noWarnings: true,
      error: (err, e) => {
        // Log to external service
        logger.error('Database error', {
          error: err.message,
          query: e.query,
          client: e.client.processID
        });
      }
    },
    
    test: {
      noWarnings: true
    }
  };
  
  return require('pg-promise')(config[environment] || {});
}

// Schema-aware configuration
const pgp = require('pg-promise')({
  schema: (dc) => {
    // Dynamic schema based on context
    if (dc.tenant) {
      return [`tenant_${dc.tenant}`, 'shared', 'public'];
    } else if (dc.userRole === 'admin') {
      return ['admin', 'public'];
    }
    return 'public';
  }
});

// Multi-tenant database instances
const tenantDbs = new Map();

function getTenantDb(tenantId) {
  if (!tenantDbs.has(tenantId)) {
    const db = pgp(connectionString, { tenant: tenantId });
    tenantDbs.set(tenantId, db);
  }
  return tenantDbs.get(tenantId);
}

// Configuration with custom promise library
const Bluebird = require('bluebird');

const pgp = require('pg-promise')({
  promiseLib: Bluebird, // Use Bluebird instead of native promises
  capSQL: true
});

// Library termination
process.on('exit', () => {
  pgp.end(); // Close all connection pools
});

// Main library interface
interface IMain {
  // Database factory function
  (cn: string | IConnectionParameters, dc?: any): IDatabase
  
  // Static properties and classes
  readonly PreparedStatement: typeof PreparedStatement
  readonly ParameterizedQuery: typeof ParameterizedQuery
  readonly QueryFile: typeof QueryFile
  readonly queryResult: typeof queryResult
  readonly minify: typeof pgMinify
  readonly spex: ISpex
  readonly errors: typeof errors
  readonly utils: IUtils
  readonly txMode: ITxMode
  readonly helpers: IHelpers
  readonly as: IFormatting
  readonly pg: typeof pg
  
  // Library termination
  end(): void
}

// Library configuration
interface ILibConfig {
  version: string // pg-promise version
  promise: IGenericPromise // Promise library used
  options: IInitOptions // Initialization options
  pgp: IMain // Main pg-promise instance
  $npm: any // Internal npm modules
}

// Generic promise interface
interface IGenericPromise {
  (cb: (resolve: (value?: any) => void, reject: (reason?: any) => void) => void): Promise<any>
  resolve(value?: any): Promise<any>
  reject(reason?: any): Promise<any>
  all(iterable: any): Promise<any>
}

// Spex library interface for batch operations
interface ISpex {
  batch: typeof spexBatch // Batch processing
  page: typeof spexPage // Page processing  
  sequence: typeof spexSequence // Sequence processing
  errors: {
    BatchError: typeof BatchError
    PageError: typeof PageError
    SequenceError: typeof SequenceError
  }
}