import { and, count, eq, ilike, or, sql } from "drizzle-orm";
import type { SQL } from "drizzle-orm";
import { db as prodDb } from "../../db/index.ts";
import { globalItemTags, globalItems, items, tags } from "../../db/schema.ts";

type Db = typeof prodDb;

/**
 * Search global items by brand or model and/or tag names.
 * Text search uses ILIKE for case-insensitive matching (PostgreSQL).
 * Tag filtering uses AND logic -- items must have ALL specified tags.
 * Escapes % and _ wildcard characters in user input.
 */
export async function searchGlobalItems(
	db: Db = prodDb,
	query?: string,
	tagNames?: string[],
) {
	const conditions: SQL[] = [];

	if (query) {
		const escaped = query.replace(/%/g, "\\%").replace(/_/g, "\\_");
		const pattern = `%${escaped}%`;
		conditions.push(
			or(
				ilike(globalItems.brand, pattern),
				ilike(globalItems.model, pattern),
			)!,
		);
	}

	if (tagNames && tagNames.length > 0) {
		conditions.push(
			sql`${globalItems.id} IN (
				SELECT ${globalItemTags.globalItemId}
				FROM ${globalItemTags}
				JOIN ${tags} ON ${tags.id} = ${globalItemTags.tagId}
				WHERE ${tags.name} IN (${sql.join(
					tagNames.map((t) => sql`${t}`),
					sql`, `,
				)})
				GROUP BY ${globalItemTags.globalItemId}
				HAVING COUNT(DISTINCT ${tags.name}) = ${tagNames.length}
			)`,
		);
	}

	if (conditions.length === 0) {
		return db.select().from(globalItems);
	}

	return db
		.select()
		.from(globalItems)
		.where(and(...conditions));
}

/**
 * Get a single global item by ID with the count of user items referencing it
 * via items.globalItemId.
 */
export async function getGlobalItemWithOwnerCount(
	db: Db = prodDb,
	id: number,
) {
	const [item] = await db
		.select()
		.from(globalItems)
		.where(eq(globalItems.id, id));

	if (!item) return null;

	const [result] = await db
		.select({ ownerCount: count() })
		.from(items)
		.where(eq(items.globalItemId, id));

	return { ...item, ownerCount: result?.ownerCount ?? 0 };
}
