import { db as defaultDb } from '$lib/server/db';
import { groceries, categories, references } from '$lib/server/db/schema';
import { eq, desc, inArray, asc } 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(asc(categories.order), asc(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(asc(categories.order), asc(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: Temporal.Now.instant().toString({
				smallestUnit: 'millisecond',
			}),
		})
		.returning();

	return { item: inserted, created: true };
}

export async function updateGrocery(
	id: number,
	name: string,
	categoryId: number | null,
	db = defaultDb,
): Promise<void> {
	await db
		.update(groceries)
		.set({ name, categoryId })
		.where(eq(groceries.id, id));
}

export async function updateGroceryCategory(
	id: number,
	categoryId: number | null,
	db = defaultDb,
): Promise<void> {
	await db.update(groceries).set({ categoryId }).where(eq(groceries.id, id));
}

export async function bulkUpdateGroceryCategory(
	ids: number[],
	categoryId: number | null,
	db = defaultDb,
): Promise<void> {
	await db
		.update(groceries)
		.set({ categoryId })
		.where(inArray(groceries.id, ids));
}

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,
	}));
}