2 files changed, 33 insertions, 6 deletions

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)),
 	]);