feat: drag-to-reorder categories with persistent order

9 files changed, 305 insertions, 12 deletions

diff --git a/README.md b/README.md
index 2bf5ab3..166c16f 100644
--- a/README.md
+++ b/README.md
@@ -53,7 +53,8 @@ can compare against your references right from the item. If a new brand
 wins, you can add it or switch the reference.
 
 **Categories** — items are manually assigned to categories. Categories
-correspond to physical store aisles for an efficient shopping walk.
+correspond to physical store aisles for an efficient shopping walk. Drag
+categories to reorder them so the list matches your actual store layout.
 
 **PWA** — installed as a standalone app via `@vite-pwa/sveltekit`. Service
 worker precaches assets; works offline after first visit.
@@ -62,7 +63,7 @@ worker precaches assets; works offline after first visit.
 
 ### Bugs / Polish
 
-- [ ] Fixed category order in both lists (drag to reorder aisles)
+- [x] Fixed category order in both lists (drag to reorder aisles)
 
 ### Features
 
diff --git a/drizzle/0002_rainy_wind_dancer.sql b/drizzle/0002_rainy_wind_dancer.sql
new file mode 100644
index 0000000..2e86a30
--- /dev/null
+++ b/drizzle/0002_rainy_wind_dancer.sql
@@ -0,0 +1 @@
+ALTER TABLE `categories` ADD `order` integer DEFAULT 0 NOT NULL;
\ No newline at end of file
diff --git a/drizzle/meta/0002_snapshot.json b/drizzle/meta/0002_snapshot.json
new file mode 100644
index 0000000..5801861
--- /dev/null
+++ b/drizzle/meta/0002_snapshot.json
@@ -0,0 +1,180 @@
+{
+	"version": "6",
+	"dialect": "sqlite",
+	"id": "9fa05004-a34b-48ee-80c5-c97293f55154",
+	"prevId": "ac842614-304d-4599-8104-88b6efe0ed33",
+	"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": {}
+		},
+		"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
+				}
+			},
+			"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": {}
+		}
+	},
+	"views": {},
+	"enums": {},
+	"_meta": {
+		"schemas": {},
+		"tables": {},
+		"columns": {}
+	},
+	"internal": {
+		"indexes": {}
+	}
+}
diff --git a/drizzle/meta/_journal.json b/drizzle/meta/_journal.json
index 467ba8b..08d6c41 100644
--- a/drizzle/meta/_journal.json
+++ b/drizzle/meta/_journal.json
@@ -15,6 +15,13 @@
 			"when": 1785236926984,
 			"tag": "0001_big_rachel_grey",
 			"breakpoints": true
+		},
+		{
+			"idx": 2,
+			"version": "6",
+			"when": 1785314243051,
+			"tag": "0002_rainy_wind_dancer",
+			"breakpoints": true
 		}
 	]
 }
diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index fae002d..31c2f65 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -3,6 +3,7 @@ import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core';
 export const categories = sqliteTable('categories', {
 	id: integer('id').primaryKey({ autoIncrement: true }),
 	name: text('name').notNull().unique(),
+	order: integer('order').notNull().default(0),
 });
 
 export const groceries = sqliteTable('groceries', {
diff --git a/src/lib/server/services/categories.ts b/src/lib/server/services/categories.ts
index f8b420f..d6fa2c5 100644
--- a/src/lib/server/services/categories.ts
+++ b/src/lib/server/services/categories.ts
@@ -1,18 +1,39 @@
 import { db as defaultDb } from '$lib/server/db';
 import { categories } from '$lib/server/db/schema';
-import { eq, asc } from 'drizzle-orm';
+import { eq, asc, sql } from 'drizzle-orm';
 import type { AppDatabase } from './groceries';
 import type { Category } from './types';
 
 export async function loadAll(db = defaultDb): Promise<Category[]> {
-	return db.select().from(categories).orderBy(asc(categories.name));
+	return db
+		.select()
+		.from(categories)
+		.orderBy(asc(categories.order), asc(categories.name));
 }
 
 export async function add(name: string, db = defaultDb): Promise<Category> {
-	const [inserted] = await db.insert(categories).values({ name }).returning();
+	const [{ maxOrder }] = await db
+		.select({ maxOrder: sql<number>`COALESCE(MAX(${categories.order}), 0)` })
+		.from(categories);
+	const [inserted] = await db
+		.insert(categories)
+		.values({ name, order: (maxOrder ?? 0) + 1 })
+		.returning();
 	return inserted;
 }
 
+export async function reorder(
+	orderedIds: number[],
+	db = defaultDb,
+): Promise<void> {
+	for (let i = 0; i < orderedIds.length; i++) {
+		await db
+			.update(categories)
+			.set({ order: i + 1 })
+			.where(eq(categories.id, orderedIds[i]));
+	}
+}
+
 export async function deleteCategory(
 	id: number,
 	db = defaultDb,
diff --git a/src/lib/server/services/groceries.ts b/src/lib/server/services/groceries.ts
index 3c6de70..8ffcde3 100644
--- a/src/lib/server/services/groceries.ts
+++ b/src/lib/server/services/groceries.ts
@@ -1,6 +1,6 @@
 import { db as defaultDb } from '$lib/server/db';
 import { groceries, categories, references } from '$lib/server/db/schema';
-import { eq, desc, inArray } from 'drizzle-orm';
+import { eq, desc, inArray, asc } from 'drizzle-orm';
 import type { LibSQLDatabase } from 'drizzle-orm/libsql';
 import type * as schema from '$lib/server/db/schema';
 import type { Category, Grocery, Reference } from './types';
@@ -21,7 +21,10 @@ export async function loadUnchecked(
 			.from(groceries)
 			.where(eq(groceries.checked, false))
 			.orderBy(desc(groceries.createdAt)),
-		db.select().from(categories).orderBy(desc(categories.name)),
+		db
+			.select()
+			.from(categories)
+			.orderBy(asc(categories.order), asc(categories.name)),
 		db.select().from(references).orderBy(desc(references.qualityScore)),
 	]);
 
@@ -36,7 +39,10 @@ export async function loadAll(
 ): Promise<{ items: EnrichedGrocery[]; categories: Category[] }> {
 	const [items, cats, refs] = await Promise.all([
 		db.select().from(groceries).orderBy(desc(groceries.createdAt)),
-		db.select().from(categories).orderBy(desc(categories.name)),
+		db
+			.select()
+			.from(categories)
+			.orderBy(asc(categories.order), asc(categories.name)),
 		db.select().from(references).orderBy(desc(references.qualityScore)),
 	]);
 
diff --git a/src/routes/categories/+page.server.ts b/src/routes/categories/+page.server.ts
index ac53ca8..c97b619 100644
--- a/src/routes/categories/+page.server.ts
+++ b/src/routes/categories/+page.server.ts
@@ -1,5 +1,10 @@
 import { fail } from '@sveltejs/kit';
-import { loadAll, add, deleteCategory } from '$lib/server/services/categories';
+import {
+	loadAll,
+	add,
+	deleteCategory,
+	reorder,
+} from '$lib/server/services/categories';
 import { numericId, parseFormString } from '$lib/server/services/validation';
 import type { Category } from '$lib/server/services/types';
 import type { Actions, PageServerLoad } from './$types';
@@ -35,4 +40,14 @@ export const actions: Actions = {
 		}
 		return { success: true };
 	},
+
+	reorder: async ({ request }) => {
+		const data = await request.formData();
+		const ids = data.getAll('orderedIds').map(numericId);
+		if (ids.length === 0) {
+			return fail(400, { error: 'Aucune catégorie' });
+		}
+		await reorder(ids);
+		return { success: true };
+	},
 };
diff --git a/src/routes/categories/+page.svelte b/src/routes/categories/+page.svelte
index 1060eaa..9c91578 100644
--- a/src/routes/categories/+page.svelte
+++ b/src/routes/categories/+page.svelte
@@ -3,6 +3,36 @@
 
 	let { data, form } = $props();
 	let name = $state('');
+	let draggedIndex = $state<number | null>(null);
+	// eslint-disable-next-line svelte/prefer-writable-derived
+	let localCategories = $state(data?.categories ?? []);
+
+	// Sync with server data on navigation
+	$effect(() => {
+		localCategories = data?.categories ?? [];
+	});
+
+	let reorderFormRef: HTMLFormElement;
+
+	function handleDragStart(index: number) {
+		draggedIndex = index;
+	}
+
+	function handleDragOver(e: DragEvent, index: number) {
+		e.preventDefault();
+		if (draggedIndex === null || draggedIndex === index) return;
+
+		const next = [...localCategories];
+		const [moved] = next.splice(draggedIndex, 1);
+		next.splice(index, 0, moved);
+		localCategories = next;
+		draggedIndex = index;
+	}
+
+	function handleDrop() {
+		draggedIndex = null;
+		reorderFormRef?.requestSubmit();
+	}
 </script>
 
 <main>
@@ -30,10 +60,33 @@
 		<p class="error">{form.error}</p>
 	{/if}
 
+	<form
+		bind:this={reorderFormRef}
+		method="POST"
+		action="?/reorder"
+		use:enhance={() => {
+			return async ({ update }) => {
+				await update({ reset: false });
+			};
+		}}
+	>
+		{#each localCategories as cat (cat.id)}
+			<input type="hidden" name="orderedIds" value={cat.id} />
+		{/each}
+	</form>
+
 	<form method="POST" action="?/delete" use:enhance>
 		<ul class="item-list">
-			{#each data?.categories ?? [] as cat (cat.id)}
-				<li class="item-row item-row--card">
+			{#each localCategories as cat, index (cat.id)}
+				<li
+					class="item-row item-row--card"
+					draggable="true"
+					ondragstart={() => handleDragStart(index)}
+					ondragover={(e) => handleDragOver(e, index)}
+					ondrop={handleDrop}
+					ondragend={() => (draggedIndex = null)}
+				>
+					<span class="drag-handle">⋮⋮</span>
 					<span class="cat-name">{cat.name}</span>
 					<button
 						class="btn-ghost"
@@ -49,7 +102,7 @@
 		</ul>
 	</form>
 
-	{#if (data?.categories?.length ?? 0) === 0}
+	{#if (localCategories.length ?? 0) === 0}
 		<p class="empty">Aucune catégorie</p>
 	{/if}
 </main>
@@ -61,6 +114,14 @@
 		color: var(--text);
 	}
 
+	.drag-handle {
+		color: var(--text-secondary);
+		font-size: 0.875rem;
+		cursor: grab;
+		padding-right: 0.5rem;
+		user-select: none;
+	}
+
 	.error {
 		margin-top: -0.25rem;
 		margin-bottom: 0.5rem;