-- AlterTable
ALTER TABLE `medias` ADD COLUMN `position` VARCHAR(50) NOT NULL DEFAULT 'n';

-- CreateIndex
CREATE INDEX `medias_id_media_category_position_idx` ON `medias`(`id_media_category`, `position`);

-- Backfill: distribui as posições respeitando a ordem atual de cada categoria
UPDATE `medias` m
JOIN (
  SELECT `id`,
         ROW_NUMBER() OVER (
           PARTITION BY `id_media_category`
           ORDER BY `created_at` ASC, `id` ASC
         ) - 1 AS rn
  FROM `medias`
) ordered ON ordered.`id` = m.`id`
SET m.`position` = CONCAT(
  CHAR(103 + FLOOR(ordered.rn / 676) USING utf8mb4),
  CHAR(97  + FLOOR(ordered.rn /  26) % 26 USING utf8mb4),
  CHAR(97  + (ordered.rn % 26) USING utf8mb4)
);
