import { references } from '$lib/server/db/schema';
import { eq, ne, and, desc, asc } from 'drizzle-orm';
import type { AppDatabase } from '$lib/server/db/types';
import type { Reference } from './types';

/** Primary refs first, then best score, then insertion order. */
export const referenceOrder = [
	desc(references.isPrimary),
	desc(references.qualityScore),
	asc(references.id),
];

export class ReferencesService {
	constructor(private readonly db: AppDatabase) {}

	async loadForGrocery(groceryId: number): Promise<Reference[]> {
		return this.db
			.select()
			.from(references)
			.where(eq(references.groceryId, groceryId))
			.orderBy(...referenceOrder);
	}

	/**
	 * Marks `id` as the primary ref of its grocery, clearing any sibling primary
	 * so at most one ref per grocery is promoted.
	 */
	async promote(id: number): Promise<void> {
		const [target] = await this.db
			.select()
			.from(references)
			.where(eq(references.id, id));
		if (!target) throw new Error('Reference not found');

		await this.db
			.update(references)
			.set({ isPrimary: true })
			.where(eq(references.id, id));
		await this.db
			.update(references)
			.set({ isPrimary: false })
			.where(
				and(eq(references.groceryId, target.groceryId), ne(references.id, id)),
			);
	}

	async add(
		groceryId: number,
		reference: string,
		price: number | null,
		unit: string | null,
		qualityScore: number | null,
	): Promise<Reference> {
		const [inserted] = await this.db
			.insert(references)
			.values({ groceryId, reference, price, unit, qualityScore })
			.returning();
		return inserted;
	}

	async deleteReference(id: number): Promise<void> {
		await this.db.delete(references).where(eq(references.id, id));
	}
}