8 files changed, 451 insertions, 0 deletions
diff --git a/src/lib/server/services/categories.test.ts b/src/lib/server/services/categories.test.ts
new file mode 100644
index 0000000..cfde1da
--- /dev/null
+++ b/src/lib/server/services/categories.test.ts
@@ -0,0 +1,51 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { groceries, categories } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+import { createTestDb } from '$lib/server/db/test-helper';
+import { loadAll, add, deleteCategory } from './categories';
+
+describe('categories service', () => {
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
+
+	beforeEach(async () => {
+		const testDb = await createTestDb();
+		db = testDb.db;
+	});
+
+	it('loadAll returns categories sorted by name', async () => {
+		await db.insert(categories).values([{ name: 'Zebra' }, { name: 'Apple' }]);
+
+		const result = await loadAll(db);
+		expect(result.map((c) => c.name)).toEqual(['Apple', 'Zebra']);
+	});
+
+	it('add creates a category', async () => {
+		const cat = await add('Beverages', db);
+		expect(cat.name).toBe('Beverages');
+
+		const rows = await db.select().from(categories);
+		expect(rows).toHaveLength(1);
+	});
+
+	it('deleteCategory removes the category and nullifies grocery categoryId', async () => {
+		const [cat] = await db
+			.insert(categories)
+			.values({ name: 'Dairy' })
+			.returning();
+		const now = new Date().toISOString();
+		await db.insert(groceries).values({
+			name: 'milk',
+			checked: false,
+			createdAt: now,
+			categoryId: cat.id,
+		});
+
+		await deleteCategory(cat.id, db);
+
+		const catRows = await db.select().from(categories);
+		expect(catRows).toHaveLength(0);
+
+		const [item] = await db.select().from(groceries);
+		expect(item.categoryId).toBeNull();
+	});
+});
diff --git a/src/lib/server/services/categories.ts b/src/lib/server/services/categories.ts
new file mode 100644
index 0000000..f8b420f
--- /dev/null
+++ b/src/lib/server/services/categories.ts
@@ -0,0 +1,21 @@
+import { db as defaultDb } from '$lib/server/db';
+import { categories } from '$lib/server/db/schema';
+import { eq, asc } 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));
+}
+
+export async function add(name: string, db = defaultDb): Promise<Category> {
+	const [inserted] = await db.insert(categories).values({ name }).returning();
+	return inserted;
+}
+
+export async function deleteCategory(
+	id: number,
+	db = defaultDb,
+): Promise<void> {
+	await db.delete(categories).where(eq(categories.id, id));
+}
diff --git a/src/lib/server/services/groceries.test.ts b/src/lib/server/services/groceries.test.ts
new file mode 100644
index 0000000..83f8d95
--- /dev/null
+++ b/src/lib/server/services/groceries.test.ts
@@ -0,0 +1,98 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { groceries, categories, references } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+import { createTestDb } from '$lib/server/db/test-helper';
+import {
+	loadUnchecked,
+	loadAll,
+	toggle,
+	addOrUncheck,
+	deleteGrocery,
+} from './groceries';
+
+describe('groceries service', () => {
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
+
+	beforeEach(async () => {
+		const testDb = await createTestDb();
+		db = testDb.db;
+	});
+
+	it('loadUnchecked returns only unchecked items', async () => {
+		const now = new Date().toISOString();
+		await db.insert(groceries).values([
+			{ name: 'milk', checked: false, createdAt: now },
+			{ name: 'bread', checked: true, createdAt: now },
+		]);
+
+		const result = await loadUnchecked(db);
+		expect(result.items).toHaveLength(1);
+		expect(result.items[0].name).toBe('milk');
+	});
+
+	it('loadAll returns all items', async () => {
+		const now = new Date().toISOString();
+		await db.insert(groceries).values([
+			{ name: 'milk', checked: false, createdAt: now },
+			{ name: 'bread', checked: true, createdAt: now },
+		]);
+
+		const result = await loadAll(db);
+		expect(result.items).toHaveLength(2);
+	});
+
+	it('toggle flips checked state', async () => {
+		const now = new Date().toISOString();
+		await db
+			.insert(groceries)
+			.values({ name: 'milk', checked: false, createdAt: now });
+		const [item] = await db.select().from(groceries);
+
+		await toggle(item.id, db);
+
+		const [updated] = await db
+			.select()
+			.from(groceries)
+			.where(eq(groceries.id, item.id));
+		expect(updated.checked).toBe(true);
+	});
+
+	it('addOrUncheck creates a new grocery', async () => {
+		const { item, created } = await addOrUncheck('eggs', null, db);
+
+		expect(created).toBe(true);
+		expect(item.name).toBe('eggs');
+
+		const rows = await db.select().from(groceries);
+		expect(rows).toHaveLength(1);
+	});
+
+	it('addOrUncheck unchecks an existing checked item', async () => {
+		const now = new Date().toISOString();
+		await db
+			.insert(groceries)
+			.values({ name: 'milk', checked: true, createdAt: now });
+
+		const { item, created } = await addOrUncheck('milk', null, db);
+
+		expect(created).toBe(false);
+		expect(item.checked).toBe(false);
+
+		const rows = await db.select().from(groceries);
+		expect(rows).toHaveLength(1);
+		expect(rows[0].checked).toBe(false);
+	});
+
+	it('deleteGrocery removes the item', async () => {
+		const now = new Date().toISOString();
+		await db
+			.insert(groceries)
+			.values({ name: 'milk', checked: false, createdAt: now });
+		const [item] = await db.select().from(groceries);
+
+		await deleteGrocery(item.id, db);
+
+		const rows = await db.select().from(groceries);
+		expect(rows).toHaveLength(0);
+	});
+});
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,
+	}));
+}
diff --git a/src/lib/server/services/references.test.ts b/src/lib/server/services/references.test.ts
new file mode 100644
index 0000000..c02f9e6
--- /dev/null
+++ b/src/lib/server/services/references.test.ts
@@ -0,0 +1,70 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { groceries, references } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+import { createTestDb } from '$lib/server/db/test-helper';
+import { loadForGrocery, add, deleteReference } from './references';
+
+describe('references service', () => {
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
+
+	beforeEach(async () => {
+		const testDb = await createTestDb();
+		db = testDb.db;
+	});
+
+	it('loadForGrocery returns refs for the given grocery', async () => {
+		const now = new Date().toISOString();
+		const [g1] = await db
+			.insert(groceries)
+			.values({ name: 'coffee', checked: false, createdAt: now })
+			.returning();
+		const [g2] = await db
+			.insert(groceries)
+			.values({ name: 'tea', checked: false, createdAt: now })
+			.returning();
+
+		await db.insert(references).values([
+			{ groceryId: g1.id, reference: 'Brand A' },
+			{ groceryId: g2.id, reference: 'Brand B' },
+		]);
+
+		const refs = await loadForGrocery(g1.id, db);
+		expect(refs).toHaveLength(1);
+		expect(refs[0].reference).toBe('Brand A');
+	});
+
+	it('add creates a reference', async () => {
+		const now = new Date().toISOString();
+		const [g] = await db
+			.insert(groceries)
+			.values({ name: 'pasta', checked: false, createdAt: now })
+			.returning();
+
+		const ref = await add(g.id, 'Barilla', 2.5, '€/kg', 85, db);
+		expect(ref.reference).toBe('Barilla');
+		expect(ref.price).toBe(2.5);
+
+		const rows = await db
+			.select()
+			.from(references)
+			.where(eq(references.groceryId, g.id));
+		expect(rows).toHaveLength(1);
+	});
+
+	it('deleteReference removes the ref', async () => {
+		const now = new Date().toISOString();
+		const [g] = await db
+			.insert(groceries)
+			.values({ name: 'rice', checked: false, createdAt: now })
+			.returning();
+		const [ref] = await db
+			.insert(references)
+			.values({ groceryId: g.id, reference: 'Brand X' })
+			.returning();
+
+		await deleteReference(ref.id, db);
+
+		const rows = await db.select().from(references);
+		expect(rows).toHaveLength(0);
+	});
+});
diff --git a/src/lib/server/services/references.ts b/src/lib/server/services/references.ts
new file mode 100644
index 0000000..99ba487
--- /dev/null
+++ b/src/lib/server/services/references.ts
@@ -0,0 +1,38 @@
+import { db as defaultDb } from '$lib/server/db';
+import { references } from '$lib/server/db/schema';
+import { eq, desc } from 'drizzle-orm';
+import type { AppDatabase } from './groceries';
+import type { Reference } from './types';
+
+export async function loadForGrocery(
+	groceryId: number,
+	db = defaultDb,
+): Promise<Reference[]> {
+	return db
+		.select()
+		.from(references)
+		.where(eq(references.groceryId, groceryId))
+		.orderBy(desc(references.qualityScore));
+}
+
+export async function add(
+	groceryId: number,
+	reference: string,
+	price: number | null,
+	unit: string | null,
+	qualityScore: number | null,
+	db = defaultDb,
+): Promise<Reference> {
+	const [inserted] = await db
+		.insert(references)
+		.values({ groceryId, reference, price, unit, qualityScore })
+		.returning();
+	return inserted;
+}
+
+export async function deleteReference(
+	id: number,
+	db = defaultDb,
+): Promise<void> {
+	await db.delete(references).where(eq(references.id, id));
+}
diff --git a/src/lib/server/services/types.ts b/src/lib/server/services/types.ts
new file mode 100644
index 0000000..e5e3918
--- /dev/null
+++ b/src/lib/server/services/types.ts
@@ -0,0 +1,6 @@
+import type { InferSelectModel } from 'drizzle-orm';
+import type { categories, groceries, references } from '$lib/server/db/schema';
+
+export type Category = InferSelectModel<typeof categories>;
+export type Grocery = InferSelectModel<typeof groceries>;
+export type Reference = InferSelectModel<typeof references>;
diff --git a/src/lib/server/services/validation.ts b/src/lib/server/services/validation.ts
new file mode 100644
index 0000000..a8cebd9
--- /dev/null
+++ b/src/lib/server/services/validation.ts
@@ -0,0 +1,45 @@
+import * as v from 'valibot';
+
+export const IdSchema = v.pipe(v.number(), v.integer(), v.minValue(1));
+
+export const AddGrocerySchema = v.object({
+	name: v.pipe(v.string(), v.minLength(1)),
+	categoryId: v.nullable(v.pipe(v.number(), v.integer(), v.minValue(1))),
+});
+
+export const AddReferenceSchema = v.object({
+	reference: v.pipe(v.string(), v.minLength(1)),
+	price: v.nullable(v.pipe(v.number(), v.minValue(0))),
+	unit: v.nullable(v.string()),
+	qualityScore: v.nullable(
+		v.pipe(v.number(), v.integer(), v.minValue(0), v.maxValue(100)),
+	),
+});
+
+export function numericId(
+	value: FormDataEntryValue | null | undefined,
+): number {
+	const str = value?.toString().trim() ?? '';
+	const n = Number(str);
+	if (!Number.isFinite(n) || n <= 0 || !Number.isInteger(n)) {
+		throw new Error('Invalid id');
+	}
+	return n;
+}
+
+export function parseFormString(
+	value: FormDataEntryValue | null | undefined,
+): string | null {
+	const str = value?.toString().trim() ?? '';
+	return str || null;
+}
+
+export function parseOptionalNumber(
+	value: FormDataEntryValue | null | undefined,
+): number | null {
+	const str = value?.toString().trim() ?? '';
+	if (!str) return null;
+	const n = Number(str);
+	if (!Number.isFinite(n)) return null;
+	return n;
+}