import { readFileSync } from "node:fs";
import { join } from "node:path";
import postgres from "postgres";

export const SQL_FUNCTION_FILES = [
	"deductFromRollovers.sql",
	"deductFromMainBalance.sql",
	"unwindFromLockReceipt.sql",
	"getTotalBalance.sql",
	"deductFromAdditionalBalance.sql",
	"getAvailableOverageFromSpendLimit.sql",
	"performDeduction.sql",
	"syncBalances.sql",
	"syncBalancesV2.sql",
	"resetCusEnts.sql",
] as const;

const SQL_DIR = join(
	import.meta.dir,
	"..",
	"..",
	"server",
	"src",
	"internal",
	"balances",
	"utils",
	"sql",
);

export const shouldRequireSslForDatabaseFunctions = (databaseUrl: string) => {
	const hostname = new URL(databaseUrl).hostname;
	return hostname !== "localhost" && hostname !== "127.0.0.1";
};

export const initializeDatabaseFunctions = async (
	databaseUrl = process.env.DATABASE_URL,
) => {
	if (!databaseUrl) {
		throw new Error("DATABASE_URL is required to initialize database functions");
	}

	const sql = postgres(databaseUrl, {
		max: 1,
		prepare: false,
		connect_timeout: 30,
		ssl: shouldRequireSslForDatabaseFunctions(databaseUrl)
			? "require"
			: undefined,
	});

	try {
		for (const file of SQL_FUNCTION_FILES) {
			const body = readFileSync(join(SQL_DIR, file), "utf8");
			await sql.unsafe(body);
			console.log(`loaded ${file}`);
		}
	} finally {
		await sql.end({ timeout: 5 });
	}
};