src/lib/db/index.ts

import { drizzle } from 'drizzle-orm/node-postgres';
import pg from 'pg';
import * as schema from './schema.js';
import dns from 'dns';

export type Env = {
	DATABASE_URL: string;
	HF_TOKEN: string;
	HF_DATASET_REPO: string;
};

// Custom DNS resolver for Koyeb hostnames (which uses Neon behind the scenes)
// This is a workaround for DNS resolution issues in some environments
const dnsCache = new Map<string, string>();

function setupDnsWorkaround() {
	// Koyeb PostgreSQL actually uses Neon, map the hostname
	const koyebHost = 'ep-spring-haze-a1u18cbw.ap-southeast-1.pg.koyeb.app';
	const neonHost = 'ap-southeast-1.aws.neon.tech';

	// Cache the resolution
	dnsCache.set(koyebHost, neonHost);

	// Override DNS lookup for pg
	const originalLookup = dns.lookup;
	dns.lookup = function(hostname: string, options: any, callback: any) {
		// Handle different function signatures
		if (typeof options === 'function') {
			callback = options;
			options = {};
		}

		// Check if we have a cached resolution
		if (dnsCache.has(hostname)) {
			const resolvedHost = dnsCache.get(hostname)!;
			console.log(`[DB] DNS workaround: ${hostname} -> ${resolvedHost}`);
			// Use the original lookup with the resolved host
			return originalLookup(resolvedHost, options, callback);
		}

		// Otherwise use original lookup
		return originalLookup(hostname, options, callback);
	} as any;
}

// Singleton pool instance
let pool: pg.Pool | null = null;

/**
 * Create database instance for Node.js environment (Hugging Face Spaces)
 * Uses a singleton connection pool for better performance
 */
export function createDb(env: Env) {
	// Create pool only once
	if (!pool) {
		// Setup DNS workaround before creating pool
		setupDnsWorkaround();

		pool = new pg.Pool({
			connectionString: env.DATABASE_URL,
			ssl: env.DATABASE_URL.includes('koyeb.app') ? { rejectUnauthorized: false } : undefined,
			max: 10, // Maximum pool size
			idleTimeoutMillis: 30000,
			connectionTimeoutMillis: 10000,
		});

		// Log pool errors
		pool.on('error', (err) => {
			console.error('[DB] Unexpected pool error:', err);
		});
	}

	return drizzle(pool, { schema });
}

export type Database = ReturnType<typeof createDb>;