CREATE TABLE `RankOption` (
    `id` CHAR(25) NOT NULL,
    `value` VARCHAR(191) NOT NULL,
    `position` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `RankOption_value_key`(`value`),
    UNIQUE INDEX `RankOption_position_key`(`position`),
    INDEX `RankOption_position_idx`(`position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET @rank_position := -1;

INSERT INTO `RankOption` (`id`, `value`, `position`, `createdAt`, `updatedAt`)
SELECT
    `source`.`id`,
    `source`.`value`,
    (@rank_position := @rank_position + 1) AS `position`,
    `source`.`createdAt`,
    `source`.`updatedAt`
FROM (
    SELECT `id`, `value`, `createdAt`, `updatedAt`
    FROM `ProfileOption`
    WHERE `kind` = 'RANK'
    ORDER BY `value` ASC, `createdAt` ASC
) AS `source`;

CREATE INDEX `TrackProfile_rank_idx` ON `TrackProfile`(`rank`);
