feat: promote a reference as the primary shown in lists

Add an is_primary flag to references; the promoted ref sorts first on the item page and is the one shown under the item in the list and catalog. Item page gets a star toggle per reference.

6 files changed, 106 insertions, 14 deletions

diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index b6404ce..5eb6c15 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -32,6 +32,10 @@ export const references = sqliteTable('references', {
 	price: real('price'),
 	unit: text('unit'),
 	qualityScore: integer('quality_score'),
+	// Promoted ref shown first in lists; at most one per grocery.
+	isPrimary: integer('is_primary', { mode: 'boolean' })
+		.notNull()
+		.default(false),
 });
 
 export const projects = sqliteTable('projects', {
diff --git a/src/lib/server/services/groceries.test.ts b/src/lib/server/services/groceries.test.ts
index d12111f..dbf46b7 100644
--- a/src/lib/server/services/groceries.test.ts
+++ b/src/lib/server/services/groceries.test.ts
@@ -77,6 +77,30 @@ describe('groceries service', () => {
 		expect(result.items[0].bestRef?.reference).toBe('Brand A');
 	});
 
+	it('bestRef prefers the promoted ref over a higher score', async () => {
+		const now = Temporal.Now.instant().toString({
+			smallestUnit: 'millisecond',
+		});
+		const [g] = await db
+			.insert(groceries)
+			.values({ name: 'coffee', checked: false, createdAt: now })
+			.returning();
+		const [, b] = await db
+			.insert(references)
+			.values([
+				{ groceryId: g.id, reference: 'Brand A', qualityScore: 80 },
+				{ groceryId: g.id, reference: 'Brand B', qualityScore: 20 },
+			])
+			.returning();
+		await db
+			.update(references)
+			.set({ isPrimary: true })
+			.where(eq(references.id, b.id));
+
+		const result = await loadAll(db);
+		expect(result.items[0].bestRef?.reference).toBe('Brand B');
+	});
+
 	it('toggle flips checked state', async () => {
 		const now = Temporal.Now.instant().toString({
 			smallestUnit: 'millisecond',
diff --git a/src/lib/server/services/groceries.ts b/src/lib/server/services/groceries.ts
index fc66300..c56599c 100644
--- a/src/lib/server/services/groceries.ts
+++ b/src/lib/server/services/groceries.ts
@@ -1,6 +1,7 @@
 import { db as defaultDb } from '$lib/server/db';
 import { groceries, categories, references } from '$lib/server/db/schema';
-import { eq, desc, inArray, asc } from 'drizzle-orm';
+import { eq, inArray, asc, desc } from 'drizzle-orm';
+import { referenceOrder } from './references';
 import type { LibSQLDatabase } from 'drizzle-orm/libsql';
 import type * as schema from '$lib/server/db/schema';
 import type { Category, Grocery, Reference } from './types';
@@ -28,7 +29,7 @@ export async function loadUnchecked(
 		db
 			.select()
 			.from(references)
-			.orderBy(desc(references.qualityScore), asc(references.id)),
+			.orderBy(...referenceOrder),
 	]);
 
 	return {
@@ -52,7 +53,7 @@ export async function loadAll(
 		db
 			.select()
 			.from(references)
-			.orderBy(desc(references.qualityScore), asc(references.id)),
+			.orderBy(...referenceOrder),
 	]);
 
 	return {
diff --git a/src/lib/server/services/grocery-actions.ts b/src/lib/server/services/grocery-actions.ts
index 2138f2d..1cfb05d 100644
--- a/src/lib/server/services/grocery-actions.ts
+++ b/src/lib/server/services/grocery-actions.ts
@@ -14,18 +14,18 @@ export async function toggleGroceryAction(request: Request) {
 	return { success: true };
 }
 
-export type DeleteRowResult =
-	ActionFailure<{ error: string }> | { success: boolean; type?: 'delete' };
+export type RowActionResult =
+	ActionFailure<{ error: string }> | { success: boolean; type?: string };
 
-/** Shared 'delete by id form field' action for category and reference lists. */
-export async function deleteByIdAction(
+/** Shared 'run fn with the id form field' action for row-based lists. */
+export async function idAction(
 	request: Request,
-	deleteFn: (id: number) => Promise<unknown>,
-	extra?: { type: 'delete' },
-): Promise<DeleteRowResult> {
+	fn: (id: number) => Promise<unknown>,
+	extra?: { type: string },
+): Promise<RowActionResult> {
 	const data = await request.formData();
 	try {
-		await deleteFn(numericId(data.get('id')));
+		await fn(numericId(data.get('id')));
 	} catch {
 		return fail(400, { error: 'ID invalide' });
 	}
diff --git a/src/lib/server/services/references.test.ts b/src/lib/server/services/references.test.ts
index bcefbd0..fa3fc97 100644
--- a/src/lib/server/services/references.test.ts
+++ b/src/lib/server/services/references.test.ts
@@ -2,7 +2,7 @@ import { describe, it, expect, beforeEach } from 'vitest';
 import { groceries, references } from '$lib/server/db/schema';
 import { eq } from 'drizzle-orm';
 import { createTestDb, type TestDb } from '$lib/server/db/test-helper';
-import { loadForGrocery, add, deleteReference } from './references';
+import { loadForGrocery, add, promote, deleteReference } from './references';
 
 describe('references service', () => {
 	let db: TestDb['db'];
@@ -73,6 +73,39 @@ describe('references service', () => {
 		expect(rows).toHaveLength(1);
 	});
 
+	it('promote puts the ref first and clears any sibling primary', async () => {
+		const now = Temporal.Now.instant().toString({
+			smallestUnit: 'millisecond',
+		});
+		const [g] = await db
+			.insert(groceries)
+			.values({ name: 'coffee', checked: false, createdAt: now })
+			.returning();
+		const [a] = await db
+			.insert(references)
+			.values({ groceryId: g.id, reference: 'A', qualityScore: 90 })
+			.returning();
+		const [b] = await db
+			.insert(references)
+			.values({ groceryId: g.id, reference: 'B' })
+			.returning();
+
+		await promote(b.id, db);
+		let refs = await loadForGrocery(g.id, db);
+		expect(refs.map((r) => r.reference)).toEqual(['B', 'A']);
+
+		await promote(a.id, db);
+		refs = await loadForGrocery(g.id, db);
+		expect(refs.map((r) => r.reference)).toEqual(['A', 'B']);
+
+		const rows = await db
+			.select()
+			.from(references)
+			.where(eq(references.groceryId, g.id));
+		expect(rows.filter((r) => r.isPrimary)).toHaveLength(1);
+		expect(rows.find((r) => r.id === a.id)?.isPrimary).toBe(true);
+	});
+
 	it('deleteReference removes the ref', async () => {
 		const now = Temporal.Now.instant().toString({
 			smallestUnit: 'millisecond',
diff --git a/src/lib/server/services/references.ts b/src/lib/server/services/references.ts
index c5b6630..d48fb9b 100644
--- a/src/lib/server/services/references.ts
+++ b/src/lib/server/services/references.ts
@@ -1,9 +1,16 @@
 import { db as defaultDb } from '$lib/server/db';
 import { references } from '$lib/server/db/schema';
-import { eq, desc, asc } from 'drizzle-orm';
+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,
@@ -12,7 +19,30 @@ export async function loadForGrocery(
 		.select()
 		.from(references)
 		.where(eq(references.groceryId, groceryId))
-		.orderBy(desc(references.qualityScore), asc(references.id));
+		.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(