-- Quilenz Assessment Platform v3 migration
-- Run this once after migration_v2_company_portal.sql on an existing v2 database.

ALTER TABLE paper_sections
  ADD COLUMN selection_mode ENUM('manual','random') NOT NULL DEFAULT 'manual' AFTER duration_minutes,
  ADD COLUMN question_count INT NULL AFTER selection_mode,
  ADD COLUMN shuffle_questions TINYINT(1) NOT NULL DEFAULT 0 AFTER question_count;

CREATE TABLE IF NOT EXISTS assignment_questions (
  assignment_id BIGINT UNSIGNED NOT NULL,
  section_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  PRIMARY KEY (assignment_id, section_id, question_id),
  KEY idx_assignment_questions_order (assignment_id, section_id, sort_order),
  CONSTRAINT fk_aq_assignment FOREIGN KEY (assignment_id) REFERENCES assignments(id) ON DELETE CASCADE,
  CONSTRAINT fk_aq_section FOREIGN KEY (section_id) REFERENCES paper_sections(id) ON DELETE CASCADE,
  CONSTRAINT fk_aq_question FOREIGN KEY (question_id) REFERENCES questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;