1 files changed, 122 insertions, 0 deletions
diff --git a/src/lib/server/services/groceries.ts b/src/lib/server/services/groceries.ts
new file mode 100644
index 0000000..fb42224
--- /dev/null
+++ b/src/lib/server/services/groceries.ts
@@ -0,0 +1,122 @@
+import { db as defaultDb } from '$lib/server/db';
+import { groceries, categories, references } from '$lib/server/db/schema';
+import { eq, desc } 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';
+
+export type AppDatabase = LibSQLDatabase<typeof schema>;
+
+export interface EnrichedGrocery extends Grocery {
+	categoryName: string | null;
+	bestRef: Reference | null;
+}
+
+export async function loadUnchecked(
+	db = defaultDb,
+): Promise<{ items: EnrichedGrocery[]; categories: Category[] }> {
+	const [items, cats, refs] = await Promise.all([
+		db
+			.select()
+			.from(groceries)
+			.where(eq(groceries.checked, false))
+			.orderBy(desc(groceries.createdAt)),
+		db.select().from(categories).orderBy(desc(categories.name)),
+		db.select().from(references).orderBy(desc(references.qualityScore)),
+	]);
+
+	return {
+		items: enrichItems(items, cats, refs),
+		categories: cats,
+	};
+}
+
+export async function loadAll(
+	db = defaultDb,
+): 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(references).orderBy(desc(references.qualityScore)),
+	]);
+
+	return {
+		items: enrichItems(items, cats, refs),
+		categories: cats,
+	};
+}
+
+export async function toggle(id: number, db = defaultDb): Promise<void> {
+	const [item] = await db.select().from(groceries).where(eq(groceries.id, id));
+	if (!item) throw new Error('Grocery not found');
+
+	await db
+		.update(groceries)
+		.set({ checked: !item.checked })
+		.where(eq(groceries.id, id));
+}
+
+export async function addOrUncheck(
+	name: string,
+	categoryId: number | null,
+	db = defaultDb,
+): Promise<{ item: Grocery; created: boolean }> {
+	const [existing] = await db
+		.select()
+		.from(groceries)
+		.where(eq(groceries.name, name))
+		.limit(1);
+
+	if (existing) {
+		await db
+			.update(groceries)
+			.set({
+				checked: false,
+				categoryId: categoryId ?? existing.categoryId,
+			})
+			.where(eq(groceries.id, existing.id));
+		return {
+			item: {
+				...existing,
+				checked: false,
+				categoryId: categoryId ?? existing.categoryId,
+			},
+			created: false,
+		};
+	}
+
+	const [inserted] = await db
+		.insert(groceries)
+		.values({
+			name,
+			categoryId,
+			createdAt: new Date().toISOString(),
+		})
+		.returning();
+
+	return { item: inserted, created: true };
+}
+
+export async function deleteGrocery(id: number, db = defaultDb): Promise<void> {
+	await db.delete(groceries).where(eq(groceries.id, id));
+}
+
+function enrichItems(
+	rawItems: Grocery[],
+	rawCategories: Category[],
+	rawReferences: Reference[],
+): EnrichedGrocery[] {
+	const catMap = new Map(rawCategories.map((c) => [c.id, c.name]));
+	const bestRef = new Map<number, Reference>();
+	for (const ref of rawReferences) {
+		if (!bestRef.has(ref.groceryId)) {
+			bestRef.set(ref.groceryId, ref);
+		}
+	}
+
+	return rawItems.map((i) => ({
+		...i,
+		categoryName: i.categoryId ? (catMap.get(i.categoryId) ?? null) : null,
+		bestRef: bestRef.get(i.id) ?? null,
+	}));
+}