-- Add question_set_id and position to questions; backfill to Legacy Set
ALTER TABLE `questions`
  ADD COLUMN IF NOT EXISTS `question_set_id` int(10) unsigned DEFAULT NULL AFTER `question`,
  ADD COLUMN IF NOT EXISTS `position` smallint(5) unsigned NOT NULL DEFAULT 0 AFTER `question_set_id`;

-- Backfill: set question_set_id for all existing questions to Legacy Set
UPDATE `questions` q
JOIN (
  SELECT id FROM question_sets ORDER BY id ASC LIMIT 1
) qs ON 1=1
SET q.question_set_id = COALESCE(q.question_set_id, qs.id)
WHERE q.question_set_id IS NULL;

-- Backfill positions if zero
SET @rownum := 0;
UPDATE `questions` q
JOIN (
  SELECT question_id FROM questions WHERE position=0 ORDER BY question_id ASC
) t ON t.question_id = q.question_id
SET q.position = (@rownum := @rownum + 1)
WHERE q.position = 0;

-- Indexes and FK
ALTER TABLE `questions`
  ADD INDEX IF NOT EXISTS `idx_questions_set_pos` (`question_set_id`, `position`),
  ADD INDEX IF NOT EXISTS `idx_questions_set` (`question_set_id`);

ALTER TABLE `questions`
  ADD CONSTRAINT `fk_questions_set` FOREIGN KEY (`question_set_id`) REFERENCES `question_sets`(`id`);

