12 files changed, 503 insertions, 18 deletions

diff --git a/src/lib/server/db/groceries.test.ts b/src/lib/server/db/groceries.test.ts
index bd14040..2f8baed 100644
--- a/src/lib/server/db/groceries.test.ts
+++ b/src/lib/server/db/groceries.test.ts
@@ -1,27 +1,14 @@
 import { describe, it, expect, beforeEach } from 'vitest';
-import { drizzle } from 'drizzle-orm/libsql';
-import { createClient } from '@libsql/client';
 import { groceries, categories } from '$lib/server/db/schema';
 import { eq } from 'drizzle-orm';
+import { createTestDb } from './test-helper';
 
 describe('groceries add', () => {
-	const client = createClient({ url: ':memory:' });
-	const db = drizzle(client, { schema: { groceries, categories } });
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
 
 	beforeEach(async () => {
-		await db.run(`DROP TABLE IF EXISTS groceries`);
-		await db.run(`DROP TABLE IF EXISTS categories`);
-		await db.run(`CREATE TABLE categories (
-			id INTEGER PRIMARY KEY AUTOINCREMENT,
-			name TEXT NOT NULL
-		)`);
-		await db.run(`CREATE TABLE groceries (
-			id INTEGER PRIMARY KEY AUTOINCREMENT,
-			name TEXT NOT NULL,
-			checked INTEGER NOT NULL DEFAULT 0,
-			created_at TEXT NOT NULL,
-			category_id INTEGER REFERENCES categories(id)
-		)`);
+		const testDb = await createTestDb();
+		db = testDb.db;
 	});
 
 	async function addItem(name: string, categoryId?: number | null) {
diff --git a/src/lib/server/db/ondelete.test.ts b/src/lib/server/db/ondelete.test.ts
new file mode 100644
index 0000000..c2c86de
--- /dev/null
+++ b/src/lib/server/db/ondelete.test.ts
@@ -0,0 +1,32 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { groceries, categories } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+import { createTestDb } from './test-helper';
+
+describe('category delete behavior', () => {
+	let db: Awaited<ReturnType<typeof createTestDb>>['db'];
+
+	beforeEach(async () => {
+		const testDb = await createTestDb();
+		db = testDb.db;
+	});
+
+	it('sets category_id to null when a referenced category is deleted', async () => {
+		await db.insert(categories).values({ name: 'Boissons' });
+		const cats = await db.select().from(categories);
+		const catId = cats[0].id;
+
+		await db.insert(groceries).values({
+			name: 'lait',
+			checked: false,
+			createdAt: new Date().toISOString(),
+			categoryId: catId,
+		});
+
+		await db.delete(categories).where(eq(categories.id, catId));
+
+		const items = await db.select().from(groceries);
+		expect(items).toHaveLength(1);
+		expect(items[0].categoryId).toBeNull();
+	});
+});
diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index 9113d9b..fae002d 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -10,7 +10,9 @@ export const groceries = sqliteTable('groceries', {
 	name: text('name').notNull().unique(),
 	checked: integer('checked', { mode: 'boolean' }).notNull().default(false),
 	createdAt: text('created_at').notNull(),
-	categoryId: integer('category_id').references(() => categories.id),
+	categoryId: integer('category_id').references(() => categories.id, {
+		onDelete: 'set null',
+	}),
 });
 
 export const references = sqliteTable('references', {
diff --git a/src/lib/server/db/test-helper.ts b/src/lib/server/db/test-helper.ts
new file mode 100644
index 0000000..5bae681
--- /dev/null
+++ b/src/lib/server/db/test-helper.ts
@@ -0,0 +1,13 @@
+import { createClient } from '@libsql/client';
+import { drizzle } from 'drizzle-orm/libsql';
+import { migrate } from 'drizzle-orm/libsql/migrator';
+import * as schema from './schema';
+
+export async function createTestDb() {
+	const client = createClient({ url: ':memory:' });
+	const db = drizzle(client, { schema });
+
+	await migrate(db, { migrationsFolder: './drizzle' });
+
+	return { db, client };
+}
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;
+}