-- Collapse to a single voiceNotePath per item. The separate pending audio
-- slot was confusing — there's now one voice note per item, replaced in
-- place when the user re-records. A simple needsAIReview flag tells the
-- next identify pass to re-process the item.

ALTER TABLE "Item" ADD COLUMN "needsAIReview" BOOLEAN NOT NULL DEFAULT false;

-- If anything is currently sitting in the pending voice slot, promote it
-- to the canonical voice slot before dropping the columns. (In practice
-- prod has none of these right now, but be safe.)
UPDATE "Item"
   SET "voiceNotePath" = "pendingVoiceNotePath",
       "voiceNoteMime" = "pendingVoiceNoteMime",
       "needsAIReview" = true
 WHERE "pendingVoiceNotePath" IS NOT NULL;

ALTER TABLE "Item" DROP COLUMN "pendingVoiceNotePath";
ALTER TABLE "Item" DROP COLUMN "pendingVoiceNoteMime";

-- Existing items with a pendingNote should also be picked up by the new
-- review flag so the next pass sees them via the same query.
UPDATE "Item" SET "needsAIReview" = true WHERE "pendingNote" IS NOT NULL;