Neon Serverless

/**
 * Connection Validator Script
 *
 * This script tests your Neon database connection and provides diagnostic information.
 * Run with: npx ts-node validate-connection.ts
 *
 * Environment variables:
 * - DATABASE_URL: Your Neon connection string
 * - CONNECTION_TYPE: 'http' or 'websocket' (default: 'http')
 */

import { neon } from '@neondatabase/serverless';
import { Pool } from '@neondatabase/serverless';

const DATABASE_URL = process.env.DATABASE_URL;
const CONNECTION_TYPE = process.env.CONNECTION_TYPE || 'http';

if (!DATABASE_URL) {
  console.error('❌ DATABASE_URL environment variable is not set');
  process.exit(1);
}

async function validateHttpConnection() {
  console.log('\n🔍 Testing HTTP Connection...');
  try {
    const sql = neon(DATABASE_URL);

    // Test 1: Simple query
    console.log('  • Testing basic query...');
    const result = await sql`SELECT NOW() as current_time, version() as version`;
    console.log('  ✅ Query successful');

    // Test 2: Get database info
    console.log('  • Fetching database info...');
    const dbInfo = await sql`
      SELECT
        current_database() as database,
        current_user as user,
        version() as postgresql_version,
        (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public') as table_count
    `;

    console.log('\n📊 Database Information:');
    const info = dbInfo[0];
    console.log(`  • Database: ${info.database}`);
    console.log(`  • User: ${info.user}`);
    console.log(`  • PostgreSQL Version: ${info.postgresql_version.split(',')[0]}`);
    console.log(`  • Public Tables: ${info.table_count}`);

    // Test 3: Connection string validation
    console.log('\n🔐 Connection Details:');
    const url = new URL(DATABASE_URL);
    console.log(`  • Host: ${url.hostname}`);
    console.log(`  • Port: ${url.port || 5432}`);
    console.log(`  • Database: ${url.pathname.slice(1)}`);
    console.log(`  • SSL Mode: ${url.searchParams.get('sslmode') || 'require'}`);

    return true;
  } catch (error) {
    console.error('  ❌ Connection failed');
    console.error(`     Error: ${(error as any).message}`);
    return false;
  }
}

async function validateWebSocketConnection() {
  console.log('\n🔍 Testing WebSocket Connection...');
  try {
    const pool = new Pool({
      connectionString: DATABASE_URL,
      max: 1,
    });

    // Test 1: Get connection
    console.log('  • Acquiring connection...');
    const client = await pool.connect();
    console.log('  ✅ Connection acquired');

    try {
      // Test 2: Simple query
      console.log('  • Testing basic query...');
      const result = await client.query('SELECT NOW() as current_time, version() as version');
      console.log('  ✅ Query successful');

      // Test 3: Get database info
      console.log('  • Fetching database info...');
      const dbInfoResult = await client.query(`
        SELECT
          current_database() as database,
          current_user as user,
          version() as postgresql_version,
          (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public') as table_count
      `);

      console.log('\n📊 Database Information:');
      const info = dbInfoResult.rows[0];
      console.log(`  • Database: ${info.database}`);
      console.log(`  • User: ${info.user}`);
      console.log(`  • PostgreSQL Version: ${info.postgresql_version.split(',')[0]}`);
      console.log(`  • Public Tables: ${info.table_count}`);

      // Test 4: List tables
      console.log('\n📋 Public Tables:');
      const tablesResult = await client.query(`
        SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
      `);

      if (tablesResult.rows.length > 0) {
        tablesResult.rows.forEach((row) => {
          console.log(`  • ${row.table_name}`);
        });
      } else {
        console.log('  (no tables found)');
      }
    } finally {
      client.release();
    }

    // Test 5: Connection string validation
    console.log('\n🔐 Connection Details:');
    const url = new URL(DATABASE_URL);
    console.log(`  • Host: ${url.hostname}`);
    console.log(`  • Port: ${url.port || 5432}`);
    console.log(`  • Database: ${url.pathname.slice(1)}`);
    console.log(`  • SSL Mode: ${url.searchParams.get('sslmode') || 'require'}`);

    await pool.end();
    return true;
  } catch (error) {
    console.error('  ❌ Connection failed');
    console.error(`     Error: ${(error as any).message}`);
    return false;
  }
}

async function main() {
  console.log('═══════════════════════════════════════════════════════');
  console.log('  Neon Connection Validator');
  console.log('═══════════════════════════════════════════════════════');

  console.log(`\n🚀 Testing ${CONNECTION_TYPE.toUpperCase()} connection...`);
  console.log(`   Database URL: ${DATABASE_URL.split('@')[1] || '...'}`);

  let success = false;

  if (CONNECTION_TYPE === 'websocket') {
    success = await validateWebSocketConnection();
  } else {
    success = await validateHttpConnection();
  }

  console.log('\n═══════════════════════════════════════════════════════');
  if (success) {
    console.log('✅ Connection validated successfully!');
    process.exit(0);
  } else {
    console.log('❌ Connection validation failed');
    console.log('\n💡 Troubleshooting tips:');
    console.log('   • Verify DATABASE_URL is correctly set');
    console.log('   • Check your Neon console for connection details');
    console.log('   • Ensure your firewall allows outbound connections');
    console.log('   • Check if SSL mode is correctly configured');
    process.exit(1);
  }
}

main().catch((error) => {
  console.error('Unexpected error:', error);
  process.exit(1);
});

/**
 * HTTP Connection Template for Neon Serverless
 *
 * This template demonstrates the HTTP connection pattern,
 * ideal for edge functions and stateless serverless environments.
 *
 * Usage: Best for Vercel Edge Functions, AWS Lambda, Cloudflare Workers, etc.
 */

import { neon } from '@neondatabase/serverless';

// Initialize the HTTP client
// This should be done once per request or in a module-level scope
const sql = neon(process.env.DATABASE_URL!);

/**
 * Example: Query a single row
 */
export async function getUserById(userId: string) {
  try {
    const user = await sql`SELECT * FROM users WHERE id = ${userId}`;
    return user[0] || null;
  } catch (error) {
    console.error('Failed to fetch user:', error);
    throw error;
  }
}

/**
 * Example: Query multiple rows
 */
export async function getAllUsers() {
  try {
    const users = await sql`SELECT * FROM users ORDER BY created_at DESC`;
    return users;
  } catch (error) {
    console.error('Failed to fetch users:', error);
    throw error;
  }
}

/**
 * Example: Insert data
 */
export async function createUser(email: string, name: string) {
  try {
    const result = await sql`
      INSERT INTO users (email, name, created_at)
      VALUES (${email}, ${name}, NOW())
      RETURNING id, email, name, created_at
    `;
    return result[0];
  } catch (error) {
    console.error('Failed to create user:', error);
    throw error;
  }
}

/**
 * Example: Update data
 */
export async function updateUser(userId: string, updates: Record<string, any>) {
  try {
    const setClauses = Object.entries(updates)
      .map(([key, value]) => `${key} = ${value}`)
      .join(', ');

    const result = await sql`
      UPDATE users
      SET ${setClauses}, updated_at = NOW()
      WHERE id = ${userId}
      RETURNING *
    `;
    return result[0];
  } catch (error) {
    console.error('Failed to update user:', error);
    throw error;
  }
}

/**
 * Example: Delete data
 */
export async function deleteUser(userId: string) {
  try {
    const result = await sql`
      DELETE FROM users WHERE id = ${userId}
      RETURNING id
    `;
    return result.length > 0;
  } catch (error) {
    console.error('Failed to delete user:', error);
    throw error;
  }
}

/**
 * Example: Transaction-like behavior with multiple queries
 * Note: HTTP doesn't support true transactions, but you can sequence queries
 */
export async function createUserWithProfile(
  email: string,
  name: string,
  bio: string
) {
  try {
    // Step 1: Create user
    const userResult = await sql`
      INSERT INTO users (email, name)
      VALUES (${email}, ${name})
      RETURNING id
    `;
    const userId = userResult[0].id;

    // Step 2: Create profile
    const profileResult = await sql`
      INSERT INTO profiles (user_id, bio)
      VALUES (${userId}, ${bio})
      RETURNING *
    `;

    return { userId, profile: profileResult[0] };
  } catch (error) {
    console.error('Failed to create user with profile:', error);
    throw error;
  }
}

/**
 * Example: Query with filtering and pagination
 */
export async function searchUsers(
  query: string,
  limit: number = 10,
  offset: number = 0
) {
  try {
    const results = await sql`
      SELECT * FROM users
      WHERE name ILIKE ${'%' + query + '%'}
      OR email ILIKE ${'%' + query + '%'}
      ORDER BY created_at DESC
      LIMIT ${limit}
      OFFSET ${offset}
    `;
    return results;
  } catch (error) {
    console.error('Failed to search users:', error);
    throw error;
  }
}

/**
 * Example: Aggregate query
 */
export async function getUserStats() {
  try {
    const stats = await sql`
      SELECT
        COUNT(*) as total_users,
        COUNT(CASE WHEN created_at > NOW() - INTERVAL '30 days' THEN 1 END) as new_users_30d,
        MIN(created_at) as oldest_user,
        MAX(created_at) as newest_user
      FROM users
    `;
    return stats[0];
  } catch (error) {
    console.error('Failed to fetch user stats:', error);
    throw error;
  }
}

/**
 * Example: Join query
 */
export async function getUserWithProfile(userId: string) {
  try {
    const result = await sql`
      SELECT u.*, p.bio, p.avatar_url
      FROM users u
      LEFT JOIN profiles p ON u.id = p.user_id
      WHERE u.id = ${userId}
    `;
    return result[0] || null;
  } catch (error) {
    console.error('Failed to fetch user with profile:', error);
    throw error;
  }
}

/**
 * WebSocket Pool Template for Neon Serverless
 *
 * This template demonstrates the WebSocket connection pattern,
 * ideal for Node.js servers and applications needing persistent connections.
 *
 * Usage: Best for Next.js API routes, Express servers, and long-lived applications
 */

import { Pool, PoolClient } from '@neondatabase/serverless';

// Create a global pool instance (reused across requests)
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Maximum number of connections in the pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Optional: Log pool events
pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
});

/**
 * Helper: Get a connection from the pool
 */
async function withConnection<T>(
  callback: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    return await callback(client);
  } finally {
    client.release();
  }
}

/**
 * Example: Query a single row
 */
export async function getUserById(userId: string) {
  return withConnection(async (client) => {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [
      userId,
    ]);
    return result.rows[0] || null;
  });
}

/**
 * Example: Query multiple rows
 */
export async function getAllUsers() {
  return withConnection(async (client) => {
    const result = await client.query('SELECT * FROM users ORDER BY created_at DESC');
    return result.rows;
  });
}

/**
 * Example: Insert data
 */
export async function createUser(email: string, name: string) {
  return withConnection(async (client) => {
    const result = await client.query(
      `INSERT INTO users (email, name, created_at)
       VALUES ($1, $2, NOW())
       RETURNING id, email, name, created_at`,
      [email, name]
    );
    return result.rows[0];
  });
}

/**
 * Example: Update data
 */
export async function updateUser(
  userId: string,
  updates: Record<string, any>
) {
  return withConnection(async (client) => {
    const keys = Object.keys(updates);
    const values = Object.values(updates);
    const setClauses = keys
      .map((key, i) => `${key} = $${i + 1}`)
      .join(', ');

    const result = await client.query(
      `UPDATE users SET ${setClauses}, updated_at = NOW()
       WHERE id = $${keys.length + 1}
       RETURNING *`,
      [...values, userId]
    );
    return result.rows[0];
  });
}

/**
 * Example: Delete data
 */
export async function deleteUser(userId: string) {
  return withConnection(async (client) => {
    const result = await client.query('DELETE FROM users WHERE id = $1', [
      userId,
    ]);
    return result.rowCount > 0;
  });
}

/**
 * Example: Transaction support (unique to WebSocket connections)
 * Transactions allow multiple queries to be atomic
 */
export async function createUserWithProfileTx(
  email: string,
  name: string,
  bio: string
) {
  const client = await pool.connect();

  try {
    // Start transaction
    await client.query('BEGIN');

    // Step 1: Create user
    const userResult = await client.query(
      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id',
      [email, name]
    );
    const userId = userResult.rows[0].id;

    // Step 2: Create profile
    const profileResult = await client.query(
      'INSERT INTO profiles (user_id, bio) VALUES ($1, $2) RETURNING *',
      [userId, bio]
    );

    // Commit transaction
    await client.query('COMMIT');

    return { userId, profile: profileResult.rows[0] };
  } catch (error) {
    // Rollback on error
    await client.query('ROLLBACK');
    console.error('Transaction failed:', error);
    throw error;
  } finally {
    client.release();
  }
}

/**
 * Example: Query with filtering and pagination
 */
export async function searchUsers(
  query: string,
  limit: number = 10,
  offset: number = 0
) {
  return withConnection(async (client) => {
    const result = await client.query(
      `SELECT * FROM users
       WHERE name ILIKE $1 OR email ILIKE $2
       ORDER BY created_at DESC
       LIMIT $3 OFFSET $4`,
      [`%${query}%`, `%${query}%`, limit, offset]
    );
    return result.rows;
  });
}

/**
 * Example: Aggregate query
 */
export async function getUserStats() {
  return withConnection(async (client) => {
    const result = await client.query(`
      SELECT
        COUNT(*) as total_users,
        COUNT(CASE WHEN created_at > NOW() - INTERVAL '30 days' THEN 1 END) as new_users_30d,
        MIN(created_at) as oldest_user,
        MAX(created_at) as newest_user
      FROM users
    `);
    return result.rows[0];
  });
}

/**
 * Example: Join query
 */
export async function getUserWithProfile(userId: string) {
  return withConnection(async (client) => {
    const result = await client.query(
      `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]
    );
    return result.rows[0] || null;
  });
}

/**
 * Example: Batch operations
 */
export async function createMultipleUsers(
  users: Array<{ email: string; name: string }>
) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    const results = [];
    for (const user of users) {
      const result = await client.query(
        `INSERT INTO users (email, name, created_at)
         VALUES ($1, $2, NOW())
         RETURNING id, email, name`,
        [user.email, user.name]
      );
      results.push(result.rows[0]);
    }

    await client.query('COMMIT');
    return results;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

/**
 * Cleanup: Drain the pool when shutting down
 */
export async function closePool() {
  await pool.end();
  console.log('Connection pool closed');
}