chorus-services/modules/teaser/lib/db.ts

import { Pool, PoolClient } from 'pg'

// Database connection configuration
const dbConfig = {
  connectionString: process.env.DATABASE_URL,
  ssl: false, // No SSL needed for internal Docker network
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000, // Increased timeout for Docker networking
}

// Global connection pool
let globalPool: Pool | undefined

// Initialize database pool
function initializePool(): Pool {
  if (!globalPool) {
    globalPool = new Pool(dbConfig)
    
    // Handle pool errors
    globalPool.on('error', (err) => {
      console.error('Unexpected error on idle client', err)
    })
  }
  
  return globalPool
}

// Get database connection pool
export function getDbPool(): Pool {
  return initializePool()
}

// Execute a query with automatic connection handling
export async function query<T = any>(text: string, params?: any[]): Promise<T[]> {
  const pool = getDbPool()
  
  try {
    const result = await pool.query(text, params)
    return result.rows
  } catch (error) {
    console.error('Database query error:', error)
    throw error
  }
}

// Execute a transaction
export async function transaction<T>(
  callback: (client: PoolClient) => Promise<T>
): Promise<T> {
  const pool = getDbPool()
  const client = await pool.connect()
  
  try {
    await client.query('BEGIN')
    const result = await callback(client)
    await client.query('COMMIT')
    return result
  } catch (error) {
    await client.query('ROLLBACK')
    throw error
  } finally {
    client.release()
  }
}

// Health check function
export async function healthCheck(): Promise<{ status: string; timestamp: string }> {
  try {
    const result = await query('SELECT NOW() as timestamp')
    return {
      status: 'healthy',
      timestamp: result[0].timestamp
    }
  } catch (error) {
    throw new Error(`Database health check failed: ${error}`)
  }
}

// Close the pool (useful for cleanup)
export async function closePool(): Promise<void> {
  if (globalPool) {
    await globalPool.end()
    globalPool = undefined
  }
}