USE `silkhea2_jlfc`;

ALTER TABLE `competitions`
    ADD COLUMN `sequence_order` INT UNSIGNED NOT NULL DEFAULT 1 AFTER `name`;

SET @season_counter := 0;
SET @sequence_counter := 0;

UPDATE `competitions` c
JOIN (
    SELECT id,
           season_id,
           (@sequence_counter := IF(@season_counter = season_id, @sequence_counter + 1, 1)) AS new_sequence_order,
           (@season_counter := season_id) AS season_tracker
    FROM `competitions`
    ORDER BY season_id ASC, created_at ASC, id ASC
) ordered_competitions ON ordered_competitions.id = c.id
SET c.sequence_order = ordered_competitions.new_sequence_order;

ALTER TABLE `competitions`
    ADD UNIQUE KEY `uq_competitions_season_sequence_order` (`season_id`, `sequence_order`);