backend/omr-service/src/services/solutionCache.service.ts

import { query } from '../db/client.js';

export async function batchGet(nameList: string[]): Promise<any[]> {
	if (!nameList || nameList.length === 0) return [];

	// Build parameterized query for IN clause
	const placeholders = nameList.map((_, i) => `$${i + 1}`).join(', ');
	const { rows } = await query(`SELECT name, value FROM solution_cache WHERE name IN (${placeholders})`, nameList);

	// Build a map for O(1) lookup, return results in request order
	const map = new Map(rows.map((r) => [r.name, r.value]));
	return nameList.map((name) => map.get(name) ?? null);
}

export async function set(name: string, value: any): Promise<void> {
	if (value === null || value === undefined) {
		await query('DELETE FROM solution_cache WHERE name = $1', [name]);
		return;
	}

	const priority = typeof value.priority === 'number' ? value.priority : null;

	// Upsert with priority check: only overwrite if new priority >= old priority
	await query(
		`INSERT INTO solution_cache (name, value, priority, updated_at)
		VALUES ($1, $2, $3, NOW())
		ON CONFLICT (name) DO UPDATE SET
			value = EXCLUDED.value,
			priority = EXCLUDED.priority,
			updated_at = NOW()
		WHERE solution_cache.priority IS NULL
			OR EXCLUDED.priority IS NULL
			OR EXCLUDED.priority >= solution_cache.priority`,
		[name, JSON.stringify(value), priority]
	);
}

export async function remove(name: string): Promise<void> {
	await query('DELETE FROM solution_cache WHERE name = $1', [name]);
}