-- Manual gallery order for item photos.
ALTER TABLE "Photo" ADD COLUMN "position" INTEGER NOT NULL DEFAULT 0;

-- Backfill: preserve the order users have been seeing (primary first, then by
-- creation time) so nothing visually reshuffles after the deploy.
UPDATE "Photo" p
SET "position" = sub.rn
FROM (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY "itemId"
           ORDER BY "isPrimary" DESC, "createdAt" ASC
         ) - 1 AS rn
  FROM "Photo"
) sub
WHERE p.id = sub.id;