PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

-- Create a new rankings table with the desired schema, including registration_id
CREATE TABLE `rankings_new` (
  `id` TEXT NOT NULL,
  `stage_id` TEXT NOT NULL,
  `registration_id` TEXT NOT NULL,
  `rank_position` INTEGER DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE
);

-- Populate the new table by joining the old rankings table with registrations
-- This ensures each new ranking record is correctly linked to its registration.
INSERT INTO `rankings_new` (`id`, `stage_id`, `registration_id`, `rank_position`, `created_at`)
SELECT
  r.id,
  r.stage_id,
  reg.id,
  r.rank_position,
  r.created_at
FROM `rankings` r
JOIN `registrations` reg ON r.user_id = reg.user_id AND r.stage_id = reg.stage_id;

-- Drop the old, outdated rankings table
DROP TABLE `rankings`;

-- Rename the new table to take the place of the old one
ALTER TABLE `rankings_new` RENAME TO `rankings`;

COMMIT;

PRAGMA foreign_keys=ON;
