import { db as defaultDb } from '$lib/server/db';
import { categories } from '$lib/server/db/schema';
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.order), asc(categories.name));
}

export async function add(name: string, db = defaultDb): Promise<Category> {
	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,
): Promise<void> {
	await db.delete(categories).where(eq(categories.id, id));
}