src/db.ts

import postgres from 'postgres';
import dotenv from 'dotenv';
import path from 'path';

// Load environment variables from the server root directory (one level up from src)
// .env.local is optional and overrides .env when present.
dotenv.config({ path: path.resolve(__dirname, '../.env') });
dotenv.config({ path: path.resolve(__dirname, '../.env.local') });

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
    throw new Error(
        'DATABASE_URL environment variable is not set. Make sure server/.env or server/.env.local exists and is loaded.'
    );
}

const shouldUseSsl = (() => {
    const sslEnv = (process.env.DATABASE_SSL || '').toLowerCase();
    if (sslEnv === 'require' || sslEnv === 'true' || sslEnv === '1') return true;
    if (sslEnv === 'disable' || sslEnv === 'false' || sslEnv === '0') return false;

    // Default behavior: Supabase requires SSL, local Postgres typically doesn't.
    return /supabase\.co|pooler\.supabase\.com/i.test(connectionString);
})();

// Create postgres.js connection with optimized settings for Supabase
const sql = postgres(connectionString, {
    // SSL is REQUIRED for Supabase, but should be disabled for local Postgres.
    ssl: shouldUseSsl ? 'require' : false,

    // Connection pool configuration
    max: 20,                      // Maximum number of connections in the pool
    idle_timeout: 30,             // Close idle connections after 30 seconds
    connect_timeout: 10,          // Timeout for connection attempts (10 seconds)

    // Keep alive to prevent idle disconnections
    keep_alive: 1,

    // Automatically handle connection retries
    max_lifetime: 60 * 30,        // Max connection lifetime: 30 minutes

    // Error handling
    onnotice: () => { },           // Suppress notices
    debug: false,                 // Set to true for debugging

    // Transform options for better compatibility
    transform: postgres.camel,    // Convert snake_case to camelCase
});

// Graceful shutdown
process.on('SIGINT', async () => {
    console.log('Closing database connection...');
    await sql.end({ timeout: 5 });
    process.exit(0);
});

process.on('SIGTERM', async () => {
    console.log('Closing database connection...');
    await sql.end({ timeout: 5 });
    process.exit(0);
});

export default sql;