import { db as defaultDb } from '$lib/server/db';
import { references } from '$lib/server/db/schema';
import { eq, ne, and, desc, asc } from 'drizzle-orm';
import type { AppDatabase } from './groceries';
import type { Reference } from './types';

/** Primary refs first, then best score, then insertion order. */
export const referenceOrder = [
	desc(references.isPrimary),
	desc(references.qualityScore),
	asc(references.id),
];

export async function loadForGrocery(
	groceryId: number,
	db = defaultDb,
): Promise<Reference[]> {
	return db
		.select()
		.from(references)
		.where(eq(references.groceryId, groceryId))
		.orderBy(...referenceOrder);
}

/**
 * Marks `id` as the primary ref of its grocery, clearing any sibling primary
 * so at most one ref per grocery is promoted.
 */
export async function promote(id: number, db = defaultDb): Promise<void> {
	const [target] = await db
		.select()
		.from(references)
		.where(eq(references.id, id));
	if (!target) throw new Error('Reference not found');

	await db
		.update(references)
		.set({ isPrimary: true })
		.where(eq(references.id, id));
	await db
		.update(references)
		.set({ isPrimary: false })
		.where(
			and(eq(references.groceryId, target.groceryId), ne(references.id, id)),
		);
}

export async function add(
	groceryId: number,
	reference: string,
	price: number | null,
	unit: string | null,
	qualityScore: number | null,
	db = defaultDb,
): Promise<Reference> {
	const [inserted] = await db
		.insert(references)
		.values({ groceryId, reference, price, unit, qualityScore })
		.returning();
	return inserted;
}

export async function deleteReference(
	id: number,
	db = defaultDb,
): Promise<void> {
	await db.delete(references).where(eq(references.id, id));
}