admin.gaertan.art/prisma/scripts/backfillSortKeys.ts

/* 
  Backfill sortKey for images using a Hilbert (order 15) index on OKLab (a,b).

  - Safe to resume: processes in ID order, skips rows with existing sortKey.
  - Batches writes via Prisma transaction.
  - Uses existing swatches from imageColor/color; if none, falls back to mid-gray.
*/

import { PrismaClient } from "@prisma/client";
import { converter, parse } from "culori";

const prisma = new PrismaClient();
const toOklab = converter("oklab");

// ---------- Config ----------
const BATCH_SIZE = 300;          // tune for your DB
const LOG_EVERY = 5;             // batches
// Fixed OKLab box for incremental safety (covers real gamut with cushion)
const A_MIN = -0.5, A_MAX = 0.5;
const B_MIN = -0.5, B_MAX = 0.5;

// Weight Node-Vibrant swatch types toward a perceptual centroid.
// Adjust these if your dataset prefers different emphasis.
const TYPE_WEIGHTS: Record<string, number> = {
  Vibrant: 0.7,
  Muted: 0.15,
  DarkVibrant: 0.07,
  DarkMuted: 0.05,
  LightVibrant: 0.02,
  LightMuted: 0.01,
};

// ---------- Math helpers ----------
function clamp01(x: number) { return Math.max(0, Math.min(1, x)); }
function norm(x: number, lo: number, hi: number) { return clamp01((x - lo) / (hi - lo)); }

// Hilbert curve index for a 32768 x 32768 grid (order = 15) → fits INT4
function hilbertIndex15(x01: number, y01: number): number {
  let x = Math.floor(clamp01(x01) * 32767);
  let y = Math.floor(clamp01(y01) * 32767);
  let index = 0;
  for (let s = 1 << 14; s > 0; s >>= 1) {
    const rx = (x & s) ? 1 : 0;
    const ry = (y & s) ? 1 : 0;
    index += s * s * ((3 * rx) ^ ry);
    if (ry === 0) {
      if (rx === 1) { x = 32767 - x; y = 32767 - y; }
      const t = x; x = y; y = t;
    }
  }
  // stays within 0..(2^30-1)
  return index | 0;
}

type SwatchRow = { imageId: string; type: string; color: { hex: string } };

function centroidFromSwatches(swatches: SwatchRow[]) {
  // Build hexByType and choose a fallback if any missing
  const byType: Record<string, string | undefined> = {};
  for (const s of swatches) {
    // Ensure we only keep one hex per type (first wins)
    if (!byType[s.type]) byType[s.type] = s.color?.hex;
  }
  const fallbackHex =
    byType["Vibrant"] ||
    byType["Muted"] ||
    byType["DarkVibrant"] ||
    byType["DarkMuted"] ||
    byType["LightVibrant"] ||
    byType["LightMuted"];

  if (!fallbackHex) {
    // No swatches at all: mid-gray
    return { l: 0.5, a: 0, b: 0 };
  }

  let L = 0, A = 0, B = 0, W = 0;
  for (const [type, w] of Object.entries(TYPE_WEIGHTS)) {
    const hex = byType[type] ?? fallbackHex;
    if (!hex || w <= 0) continue;
    const c = toOklab(parse(hex));
    if (!c) continue;
    L += c.l * w; A += c.a * w; B += c.b * w; W += w;
  }
  if (W === 0) return { l: 0.5, a: 0, b: 0 };
  return { l: L / W, a: A / W, b: B / W };
}

async function fetchBatch(afterId?: string) {
  // Only rows that still need a key; stable ID ordering so we can resume
  return prisma.portfolioImage.findMany({
    where: { sortKey: null },
    orderBy: { id: "asc" },
    ...(afterId ? { cursor: { id: afterId }, skip: 1 } : {}),
    take: BATCH_SIZE,
    select: { id: true },
  });
}

async function backfill() {
  let processed = 0;
  let batches = 0;
  let cursor: string | undefined = undefined;

  while (true) {
    const images = await fetchBatch(cursor);
    if (images.length === 0) break;

      const ids = images.map((i: { id: string }) => i.id);
    cursor = ids[ids.length - 1];

    // Pull all swatches for this batch in one go
    const swatches = await prisma.imageColor.findMany({
      where: { imageId: { in: ids } },
      select: { imageId: true, type: true, color: { select: { hex: true } } },
    });

    // Group swatches by imageId
    const byImage = new Map<string, SwatchRow[]>();
    for (const s of swatches) {
      const arr = byImage.get(s.imageId) ?? [];
      arr.push(s as SwatchRow);
      byImage.set(s.imageId, arr);
    }

    // Prepare updates
    const updates = images.map(({ id }: { id: string }) => {
      const rows = byImage.get(id) ?? [];
      const { a, b, l } = centroidFromSwatches(rows);
      const ax = norm(a, A_MIN, A_MAX);
      const bx = norm(b, B_MIN, B_MAX);
      const sortKey = hilbertIndex15(ax, bx);
      // Optional: also persist okLab* for debugging/analytics
      return prisma.portfolioImage.update({
        where: { id },
        data: { sortKey, okLabL: l, okLabA: a, okLabB: b },
      });
    });

    await prisma.$transaction(updates);
    processed += images.length;
    batches += 1;

    if (batches % LOG_EVERY === 0) {
      console.log(`[backfill] processed ${processed} images… last id: ${cursor}`);
    }
  }

  console.log(`[backfill] done. processed ${processed} images total.`);
}

backfill()
  .catch((e) => {
    console.error("[backfill] error:", e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });