ALTER TABLE "collection_recipes" ADD COLUMN "position" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "collections" ADD COLUMN "notes" text;--> statement-breakpoint
ALTER TABLE "collections" ADD COLUMN "tags" text[] DEFAULT '{}' NOT NULL;--> statement-breakpoint
CREATE INDEX "collection_recipes_collection_position_idx" ON "collection_recipes" USING btree ("collection_id","position");--> statement-breakpoint
-- Backfill existing rows so they keep their current (added-at) order instead
-- of all collapsing to position 0 once the app starts ordering by position.
UPDATE "collection_recipes" cr
SET "position" = ranked.rn
FROM (
  SELECT collection_id, recipe_id, row_number() OVER (PARTITION BY collection_id ORDER BY added_at) - 1 AS rn
  FROM "collection_recipes"
) ranked
WHERE cr.collection_id = ranked.collection_id AND cr.recipe_id = ranked.recipe_id;