sexy/lib/db/queries/devices.ts

import { desc, eq, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { devices, type DeviceCapabilities } from "@/lib/db/schema";
import { PAGE_SIZE, type Page } from "@/lib/pagination";

export async function listDevices() {
  return db.select().from(devices).orderBy(devices.lastConnectedAt);
}

export async function listDevicesPage(page: number, pageSize = PAGE_SIZE): Promise<Page<typeof devices.$inferSelect>> {
  const [{ count }] = await db.select({ count: sql<number>`count(*)` }).from(devices);
  const items = await db
    .select()
    .from(devices)
    .orderBy(desc(devices.lastConnectedAt))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  return { items, page, pageSize, total: count };
}

export async function getDevice(id: number) {
  const [row] = await db.select().from(devices).where(eq(devices.id, id));
  return row;
}

/** Upserts by advertised BLE name - the only stable-ish identity Web Bluetooth exposes. */
export async function upsertDeviceByBleName(input: {
  bleName: string;
  deviceClass?: string | null;
  capabilities?: DeviceCapabilities;
}): Promise<typeof devices.$inferSelect> {
  const now = Date.now();
  const [existing] = await db.select().from(devices).where(eq(devices.bleName, input.bleName));

  if (existing) {
    const [updated] = await db
      .update(devices)
      .set({
        lastConnectedAt: now,
        deviceClass: input.deviceClass ?? existing.deviceClass,
        capabilities: input.capabilities ?? existing.capabilities,
      })
      .where(eq(devices.id, existing.id))
      .returning();
    return updated;
  }

  const [created] = await db
    .insert(devices)
    .values({
      bleName: input.bleName,
      deviceClass: input.deviceClass,
      capabilities: input.capabilities,
      createdAt: now,
      lastConnectedAt: now,
    })
    .returning();
  return created;
}

export async function renameDevice(id: number, displayName: string) {
  const [updated] = await db.update(devices).set({ displayName }).where(eq(devices.id, id)).returning();
  return updated;
}