-- Add display position to statements and backfill 1..4 per question
ALTER TABLE `statements`
  ADD COLUMN IF NOT EXISTS `position` tinyint(3) unsigned NOT NULL DEFAULT 0 AFTER `archetype`;

-- Backfill positions where zero using row_number per question
SET @q := 0;
SET @rn := 0;
UPDATE statements s
JOIN (
  SELECT statement_id,
         CASE WHEN @q = question_id THEN @rn := @rn + 1 ELSE @rn := 1 END AS rn,
         @q := question_id AS qid
  FROM statements
  ORDER BY question_id ASC, statement_id ASC
) x ON x.statement_id = s.statement_id
SET s.position = CASE WHEN s.position = 0 THEN x.rn ELSE s.position END
WHERE s.position = 0;

ALTER TABLE `statements`
  ADD INDEX IF NOT EXISTS `idx_statements_q_pos` (`question_id`, `position`);
