import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from "@shared/schema";

if (!process.env.DATABASE_URL) {
  throw new Error(
    "DATABASE_URL must be set. Did you forget to provision a database?",
  );
}

// Use regular PostgreSQL driver with improved connection management
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false,
  max: 10, // Increased from 5 to handle concurrent operations
  min: 2, // Keep minimum connections alive
  idleTimeoutMillis: 60000, // Increased from 30s to 60s
  connectionTimeoutMillis: 20000, // Increased from 10s to 20s
  statement_timeout: 30000, // 30 second query timeout
  query_timeout: 30000, // 30 second query timeout
});

// Enhanced error handling and monitoring for pool connections with retry logic
pool.on('error', (err: any, client: any) => {
  console.error('Database pool error:', err);
  console.error('Client info:', { processID: client?.processID, secretKey: client?.secretKey ? '[REDACTED]' : 'none' });
  
  // Handle specific Neon connection termination errors
  if (err.code === '57P01') {
    console.log('Connection terminated by administrator - implementing reconnection strategy');
    // Pool will automatically create new connections as needed
  }
});

pool.on('connect', (client) => {
  console.log('Database client connected, pool total:', pool.totalCount, 'idle:', pool.idleCount);
});

pool.on('acquire', (client) => {
  console.log('Database client acquired, active connections:', pool.totalCount - pool.idleCount);
});

pool.on('release', (err, client) => {
  if (err) {
    console.error('Database client release error:', err);
  }
  console.log('Database client released, idle connections:', pool.idleCount);
});

// Graceful shutdown handling
process.on('SIGINT', async () => {
  console.log('Gracefully closing database pool...');
  await pool.end();
  process.exit(0);
});

process.on('SIGTERM', async () => {
  console.log('Gracefully closing database pool...');
  await pool.end();
  process.exit(0);
});

export const db = drizzle(pool, { schema });

// Connection safety wrapper with retry logic for connection termination issues
export async function withConnection<T>(operation: (client: any) => Promise<T>): Promise<T> {
  let attempts = 0;
  const maxAttempts = 3;
  
  while (attempts < maxAttempts) {
    let client;
    try {
      client = await pool.connect();
      const result = await operation(client);
      return result;
    } catch (error: any) {
      console.error(`Database operation error (attempt ${attempts + 1}):`, error);
      
      // Retry on connection termination or timeout errors
      if ((error.code === '57P01' || error.code === 'ECONNRESET') && attempts < maxAttempts - 1) {
        attempts++;
        console.log(`Retrying database operation (attempt ${attempts + 1}/${maxAttempts})`);
        await new Promise(resolve => setTimeout(resolve, 1000 * attempts)); // Progressive backoff
        continue;
      }
      
      throw error;
    } finally {
      if (client) {
        client.release();
        console.log('Database connection explicitly released');
      }
    }
  }
}

// Transaction wrapper with retry logic for critical operations
export async function withTransaction<T>(operation: (tx: any) => Promise<T>): Promise<T> {
  let attempts = 0;
  const maxAttempts = 3;
  
  while (attempts < maxAttempts) {
    try {
      const result = await db.transaction(async (tx) => {
        return await operation(tx);
      });
      return result;
    } catch (error: any) {
      console.error(`Transaction error (attempt ${attempts + 1}):`, error);
      
      // Retry on connection issues
      if ((error.code === '57P01' || error.code === 'ECONNRESET') && attempts < maxAttempts - 1) {
        attempts++;
        console.log(`Retrying transaction (attempt ${attempts + 1}/${maxAttempts})`);
        await new Promise(resolve => setTimeout(resolve, 1000 * attempts));
        continue;
      }
      
      throw error;
    }
  }
}

// Batch operation helper for multiple related operations
export async function withBatch<T>(operations: () => Promise<T>): Promise<T> {
  try {
    return await operations();
  } catch (error: any) {
    console.error('Batch operation error:', error);
    throw error;
  }
}