2 files changed, 74 insertions, 1 deletions
diff --git a/src/lib/server/db/groceries.test.ts b/src/lib/server/db/groceries.test.ts
new file mode 100644
index 0000000..b4f5050
--- /dev/null
+++ b/src/lib/server/db/groceries.test.ts
@@ -0,0 +1,73 @@
+import { describe, it, expect, beforeEach } from 'vitest';
+import { drizzle } from 'drizzle-orm/libsql';
+import { createClient } from '@libsql/client';
+import { groceries } from '$lib/server/db/schema';
+import { eq } from 'drizzle-orm';
+
+describe('groceries add', () => {
+	const client = createClient({ url: ':memory:' });
+	const db = drizzle(client, { schema: { groceries } });
+
+	beforeEach(async () => {
+		await db.run(`DROP TABLE IF EXISTS groceries`);
+		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
+		)`);
+	});
+
+	async function addItem(name: string) {
+		const existing = await db
+			.select()
+			.from(groceries)
+			.where(eq(groceries.name, name))
+			.limit(1);
+
+		if (existing.length > 0) {
+			await db
+				.update(groceries)
+				.set({ checked: false })
+				.where(eq(groceries.id, existing[0].id));
+		} else {
+			await db.insert(groceries).values({
+				name,
+				createdAt: new Date().toISOString(),
+			});
+		}
+	}
+
+	it('creates a new item when name is new', async () => {
+		await addItem('milk');
+
+		const items = await db.select().from(groceries);
+		expect(items).toHaveLength(1);
+		expect(items[0].name).toBe('milk');
+		expect(items[0].checked).toBe(false);
+	});
+
+	it('unchecks an existing checked item instead of creating duplicate', async () => {
+		const now = new Date().toISOString();
+		await db
+			.insert(groceries)
+			.values({ name: 'milk', checked: true, createdAt: now });
+		await addItem('milk');
+
+		const items = await db.select().from(groceries);
+		expect(items).toHaveLength(1);
+		expect(items[0].checked).toBe(false);
+	});
+
+	it('keeps an unchecked item unchanged (already on list)', async () => {
+		const now = new Date().toISOString();
+		await db
+			.insert(groceries)
+			.values({ name: 'milk', checked: false, createdAt: now });
+		await addItem('milk');
+
+		const items = await db.select().from(groceries);
+		expect(items).toHaveLength(1);
+		expect(items[0].checked).toBe(false);
+	});
+});
diff --git a/src/lib/server/db/schema.ts b/src/lib/server/db/schema.ts
index a8527fd..403ef51 100644
--- a/src/lib/server/db/schema.ts
+++ b/src/lib/server/db/schema.ts
@@ -2,7 +2,7 @@ import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
 
 export const groceries = sqliteTable('groceries', {
 	id: integer('id').primaryKey({ autoIncrement: true }),
-	name: text('name').notNull(),
+	name: text('name').notNull().unique(),
 	checked: integer('checked', { mode: 'boolean' }).notNull().default(false),
 	createdAt: text('created_at').notNull(),
 });