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.

13 files changed, 532 insertions, 19 deletions

diff --git a/README.md b/README.md
index 0eed298..b047498 100644
--- a/README.md
+++ b/README.md
@@ -12,7 +12,9 @@ chores).
   refresh to see your partner's changes.
 - **Product references** — for items like "coffee" or "pasta": one or more
   reference brands with a price per unit (€/kg or €/L) and a quality score
-  (0–100), comparable from the item while in the store.
+  (0–100), comparable from the item while in the store. One reference can be
+  promoted as the item's primary — it is listed first and is the one shown
+  under the item in the catalog and shopping list.
 - **Categories** — items assigned to store aisles, drag to reorder the list
   to match the physical layout.
 - **Projects & routines** — projects ("Household", "Personal growth", …)
diff --git a/drizzle/0007_ancient_george_stacy.sql b/drizzle/0007_ancient_george_stacy.sql
new file mode 100644
index 0000000..a33aa26
--- /dev/null
+++ b/drizzle/0007_ancient_george_stacy.sql
@@ -0,0 +1 @@
+ALTER TABLE `references` ADD `is_primary` integer DEFAULT false NOT NULL;
\ No newline at end of file
diff --git a/drizzle/meta/0007_snapshot.json b/drizzle/meta/0007_snapshot.json
new file mode 100644
index 0000000..dbd1764
--- /dev/null
+++ b/drizzle/meta/0007_snapshot.json
@@ -0,0 +1,371 @@
+{
+	"version": "6",
+	"dialect": "sqlite",
+	"id": "1925edb3-e77c-4c7a-9394-29282efea4e9",
+	"prevId": "9a66d36d-d7f6-42ef-9108-05fcfb21a1ce",
+	"tables": {
+		"categories": {
+			"name": "categories",
+			"columns": {
+				"id": {
+					"name": "id",
+					"type": "integer",
+					"primaryKey": true,
+					"notNull": true,
+					"autoincrement": true
+				},
+				"name": {
+					"name": "name",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"order": {
+					"name": "order",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false,
+					"default": 0
+				}
+			},
+			"indexes": {
+				"categories_name_unique": {
+					"name": "categories_name_unique",
+					"columns": ["name"],
+					"isUnique": true
+				}
+			},
+			"foreignKeys": {},
+			"compositePrimaryKeys": {},
+			"uniqueConstraints": {},
+			"checkConstraints": {}
+		},
+		"groceries": {
+			"name": "groceries",
+			"columns": {
+				"id": {
+					"name": "id",
+					"type": "integer",
+					"primaryKey": true,
+					"notNull": true,
+					"autoincrement": true
+				},
+				"name": {
+					"name": "name",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"checked": {
+					"name": "checked",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false,
+					"default": false
+				},
+				"created_at": {
+					"name": "created_at",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"category_id": {
+					"name": "category_id",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": false,
+					"autoincrement": false
+				}
+			},
+			"indexes": {
+				"groceries_name_unique": {
+					"name": "groceries_name_unique",
+					"columns": ["name"],
+					"isUnique": true
+				}
+			},
+			"foreignKeys": {
+				"groceries_category_id_categories_id_fk": {
+					"name": "groceries_category_id_categories_id_fk",
+					"tableFrom": "groceries",
+					"tableTo": "categories",
+					"columnsFrom": ["category_id"],
+					"columnsTo": ["id"],
+					"onDelete": "set null",
+					"onUpdate": "no action"
+				}
+			},
+			"compositePrimaryKeys": {},
+			"uniqueConstraints": {},
+			"checkConstraints": {}
+		},
+		"projects": {
+			"name": "projects",
+			"columns": {
+				"id": {
+					"name": "id",
+					"type": "integer",
+					"primaryKey": true,
+					"notNull": true,
+					"autoincrement": true
+				},
+				"name": {
+					"name": "name",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"archived": {
+					"name": "archived",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false,
+					"default": false
+				}
+			},
+			"indexes": {
+				"projects_name_unique": {
+					"name": "projects_name_unique",
+					"columns": ["name"],
+					"isUnique": true
+				}
+			},
+			"foreignKeys": {},
+			"compositePrimaryKeys": {},
+			"uniqueConstraints": {},
+			"checkConstraints": {}
+		},
+		"references": {
+			"name": "references",
+			"columns": {
+				"id": {
+					"name": "id",
+					"type": "integer",
+					"primaryKey": true,
+					"notNull": true,
+					"autoincrement": true
+				},
+				"grocery_id": {
+					"name": "grocery_id",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"reference": {
+					"name": "reference",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"price": {
+					"name": "price",
+					"type": "real",
+					"primaryKey": false,
+					"notNull": false,
+					"autoincrement": false
+				},
+				"unit": {
+					"name": "unit",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": false,
+					"autoincrement": false
+				},
+				"quality_score": {
+					"name": "quality_score",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": false,
+					"autoincrement": false
+				},
+				"is_primary": {
+					"name": "is_primary",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false,
+					"default": false
+				}
+			},
+			"indexes": {},
+			"foreignKeys": {
+				"references_grocery_id_groceries_id_fk": {
+					"name": "references_grocery_id_groceries_id_fk",
+					"tableFrom": "references",
+					"tableTo": "groceries",
+					"columnsFrom": ["grocery_id"],
+					"columnsTo": ["id"],
+					"onDelete": "cascade",
+					"onUpdate": "no action"
+				}
+			},
+			"compositePrimaryKeys": {},
+			"uniqueConstraints": {},
+			"checkConstraints": {}
+		},
+		"routine_completions": {
+			"name": "routine_completions",
+			"columns": {
+				"id": {
+					"name": "id",
+					"type": "integer",
+					"primaryKey": true,
+					"notNull": true,
+					"autoincrement": true
+				},
+				"routine_id": {
+					"name": "routine_id",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"routine_version": {
+					"name": "routine_version",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"done_at": {
+					"name": "done_at",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"note": {
+					"name": "note",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": false,
+					"autoincrement": false
+				}
+			},
+			"indexes": {},
+			"foreignKeys": {
+				"routine_completions_routine_id_routine_version_routines_id_version_fk": {
+					"name": "routine_completions_routine_id_routine_version_routines_id_version_fk",
+					"tableFrom": "routine_completions",
+					"tableTo": "routines",
+					"columnsFrom": ["routine_id", "routine_version"],
+					"columnsTo": ["id", "version"],
+					"onDelete": "cascade",
+					"onUpdate": "no action"
+				}
+			},
+			"compositePrimaryKeys": {},
+			"uniqueConstraints": {},
+			"checkConstraints": {}
+		},
+		"routines": {
+			"name": "routines",
+			"columns": {
+				"id": {
+					"name": "id",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"version": {
+					"name": "version",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"project_id": {
+					"name": "project_id",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"name": {
+					"name": "name",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"frequency": {
+					"name": "frequency",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				},
+				"description": {
+					"name": "description",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false,
+					"default": "''"
+				},
+				"archived": {
+					"name": "archived",
+					"type": "integer",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false,
+					"default": false
+				},
+				"archived_at": {
+					"name": "archived_at",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": false,
+					"autoincrement": false
+				},
+				"created_at": {
+					"name": "created_at",
+					"type": "text",
+					"primaryKey": false,
+					"notNull": true,
+					"autoincrement": false
+				}
+			},
+			"indexes": {},
+			"foreignKeys": {
+				"routines_project_id_projects_id_fk": {
+					"name": "routines_project_id_projects_id_fk",
+					"tableFrom": "routines",
+					"tableTo": "projects",
+					"columnsFrom": ["project_id"],
+					"columnsTo": ["id"],
+					"onDelete": "cascade",
+					"onUpdate": "no action"
+				}
+			},
+			"compositePrimaryKeys": {
+				"routines_id_version_pk": {
+					"columns": ["id", "version"],
+					"name": "routines_id_version_pk"
+				}
+			},
+			"uniqueConstraints": {},
+			"checkConstraints": {}
+		}
+	},
+	"views": {},
+	"enums": {},
+	"_meta": {
+		"schemas": {},
+		"tables": {},
+		"columns": {}
+	},
+	"internal": {
+		"indexes": {}
+	}
+}
diff --git a/drizzle/meta/_journal.json b/drizzle/meta/_journal.json
index 9624f97..d5d0b66 100644
--- a/drizzle/meta/_journal.json
+++ b/drizzle/meta/_journal.json
@@ -50,6 +50,13 @@
 			"when": 1790747866343,
 			"tag": "0006_chunky_meteorite",
 			"breakpoints": true
+		},
+		{
+			"idx": 7,
+			"version": "6",
+			"when": 1791392796134,
+			"tag": "0007_ancient_george_stacy",
+			"breakpoints": true
 		}
 	]
 }
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(
diff --git a/src/routes/groceries/categories/+page.server.ts b/src/routes/groceries/categories/+page.server.ts
index d02b328..6e0e3b7 100644
--- a/src/routes/groceries/categories/+page.server.ts
+++ b/src/routes/groceries/categories/+page.server.ts
@@ -6,7 +6,7 @@ import {
 } from '$lib/server/services/categories';
 import {
 	addCategoryAction,
-	deleteByIdAction,
+	idAction,
 } from '$lib/server/services/grocery-actions';
 import { numericId } from '$lib/server/services/validation';
 import type { Category } from '$lib/server/services/types';
@@ -23,7 +23,7 @@ export const load: PageServerLoad = async () => {
 export const actions: Actions = {
 	add: ({ request }) => addCategoryAction(request),
 
-	delete: ({ request }) => deleteByIdAction(request, deleteCategory),
+	delete: ({ request }) => idAction(request, deleteCategory),
 
 	reorder: async ({ request }) => {
 		const data = await request.formData();
diff --git a/src/routes/grocery/[id]/+page.server.ts b/src/routes/grocery/[id]/+page.server.ts
index 656e19c..a4ad377 100644
--- a/src/routes/grocery/[id]/+page.server.ts
+++ b/src/routes/grocery/[id]/+page.server.ts
@@ -6,10 +6,11 @@ import { loadAll as loadCategories } from '$lib/server/services/categories';
 import {
 	loadForGrocery,
 	add as addRef,
+	promote,
 	deleteReference,
 } from '$lib/server/services/references';
 import { updateGrocery, deleteGrocery } from '$lib/server/services/groceries';
-import { deleteByIdAction } from '$lib/server/services/grocery-actions';
+import { idAction } from '$lib/server/services/grocery-actions';
 import {
 	numericId,
 	parseFormString,
@@ -70,7 +71,9 @@ export const actions: Actions = {
 	},
 
 	delete: ({ request }) =>
-		deleteByIdAction(request, deleteReference, { type: 'delete' }),
+		idAction(request, deleteReference, { type: 'delete' }),
+
+	promote: ({ request }) => idAction(request, promote, { type: 'promote' }),
 
 	update: async ({ request, params }) => {
 		const data = await request.formData();
diff --git a/src/routes/grocery/[id]/+page.svelte b/src/routes/grocery/[id]/+page.svelte
index 027c079..1a9aac8 100644
--- a/src/routes/grocery/[id]/+page.svelte
+++ b/src/routes/grocery/[id]/+page.svelte
@@ -156,6 +156,9 @@
 			{#each data?.references ?? [] as ref (ref.id)}
 				<Row variant="ref">
 					<div class="ref-info">
+						{#if ref.isPrimary}
+							<span class="primary-badge" title="Référence principale">★</span>
+						{/if}
 						<strong>{ref.reference}</strong>
 						{#if ref.price}
 							<span class="ref-detail"
@@ -166,6 +169,16 @@
 							<span class="ref-detail">Note : {ref.qualityScore}/100</span>
 						{/if}
 					</div>
+					{#if !ref.isPrimary}
+						<button
+							type="submit"
+							class="promote-btn"
+							name="id"
+							value={ref.id}
+							formaction="?/promote"
+							aria-label="Définir comme référence principale">☆</button
+						>
+					{/if}
 					<Button variant="ghost" name="id" value={ref.id} ariaLabel="Supprimer"
 						>&times;</Button
 					>
@@ -226,6 +239,30 @@
 		font-weight: 500;
 	}
 
+	.primary-badge {
+		color: var(--accent);
+		font-size: 0.875rem;
+	}
+
+	.promote-btn {
+		flex-shrink: 0;
+		width: 2rem;
+		aspect-ratio: 1;
+		padding: 0;
+		background: transparent;
+		border: none;
+		color: var(--text-secondary);
+		font-size: 1.25rem;
+		cursor: pointer;
+		display: flex;
+		align-items: center;
+		justify-content: center;
+	}
+
+	.promote-btn:hover {
+		color: var(--accent);
+	}
+
 	.error {
 		margin-top: -0.25rem;
 		margin-bottom: 0.5rem;