import { drizzle as drizzlePg } from 'drizzle-orm/node-postgres';
import { Pool as PgPool } from 'pg';
import { sql } from 'drizzle-orm';
import ws from "ws";
import { neonConfig } from '@neondatabase/serverless';
import * as schema from "@shared/schema";
import { loadDbConfig } from './config';
import 'dotenv/config';

// Configure Neon WebSocket for serverless environments
neonConfig.webSocketConstructor = ws;
neonConfig.connectionTimeoutMillis = 10000;

const config = loadDbConfig();

// Define PostgreSQL type
export type Database = ReturnType<typeof drizzlePg>;

// Function to create PostgreSQL connection
function createPostgresConnection(): Database {
	if (!process.env.DATABASE_URL) {
		throw new Error("DATABASE_URL must be set for PostgreSQL connection.");
	}

	const pool = new PgPool({
		connectionString: process.env.DATABASE_URL,
		max: 20,
		idleTimeoutMillis: 30000,
		connectionTimeoutMillis: 10000,
	});

	pool.on('error', (err) => {
		console.error('Unexpected error on idle PostgreSQL client', err);
	});

	return drizzlePg(pool, { schema });
}

// Function to connect and test PostgreSQL
async function createDbConnection(): Promise<Database> {
	console.log(`Initializing PostgreSQL database connection...`);
	let retries = 3;
	let lastError: Error | null = null;

	while (retries > 0) {
		try {
			const connection = createPostgresConnection();
			await connection.execute(sql`SELECT 1`);
			console.log('Database connection test successful');
			return connection;
		} catch (error) {
			lastError = error as Error;
			retries--;
			console.warn(`Connection attempt failed. Retrying (${retries} retries left)...`);
			await new Promise((resolve) => setTimeout(resolve, 1000));
		}
	}

	throw lastError || new Error('Failed to connect to PostgreSQL after multiple attempts');
}

// Initialize database connection
export let db: Database;

async function initializeDatabase() {
	const maxAttempts = 5;
	let attempts = 0;
	const delayMs = 2000;

	while (attempts < maxAttempts) {
		try {
			db = await createDbConnection();
			console.log('PostgreSQL database connection established successfully');
			return;
		} catch (error) {
			attempts++;
			console.error(`Initialization attempt ${attempts}/${maxAttempts} failed:`, error);

			if (attempts >= maxAttempts) {
				console.error('All attempts to initialize the database failed.');
				return;
			}

			console.log(`Retrying in ${delayMs / 1000} seconds...`);
			await new Promise(resolve => setTimeout(resolve, delayMs));
		}
	}
}

initializeDatabase();

// Helper to check if db is PostgreSQL (redundant now, but kept for future compatibility)
export function isPostgres(db: Database): db is ReturnType<typeof drizzlePg> {
	return true;
}

export { sql };