-- Survey Builder Migration
-- Creates tables for flexible, customizable surveys with multiple question types

-- Table to store individual survey questions (replaces fixed columns in study_surveys)
CREATE TABLE IF NOT EXISTS `survey_questions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `study_id` INT UNSIGNED NOT NULL,
  `question_text` TEXT NOT NULL,
  `question_type` ENUM('text', 'textarea', 'multiple_choice', 'checkbox', 'rating', 'scale') NOT NULL DEFAULT 'text',
  `options_json` TEXT DEFAULT NULL COMMENT 'JSON array of options for multiple_choice/checkbox types',
  `scale_min` INT DEFAULT NULL COMMENT 'For scale type - minimum value',
  `scale_max` INT DEFAULT NULL COMMENT 'For scale type - maximum value',
  `scale_min_label` VARCHAR(255) DEFAULT NULL COMMENT 'Label for minimum scale value',
  `scale_max_label` VARCHAR(255) DEFAULT NULL COMMENT 'Label for maximum scale value',
  `is_required` TINYINT(1) DEFAULT 1,
  `sequence` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_study_sequence` (`study_id`, `sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to store individual question responses
CREATE TABLE IF NOT EXISTS `survey_question_responses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `survey_response_id` INT UNSIGNED NOT NULL COMMENT 'Links to survey_responses.id',
  `question_id` INT UNSIGNED NOT NULL,
  `response_text` TEXT DEFAULT NULL COMMENT 'For text/textarea responses',
  `response_json` TEXT DEFAULT NULL COMMENT 'For checkbox (multiple selections) - JSON array',
  `response_value` INT DEFAULT NULL COMMENT 'For rating/scale numeric responses',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_response` (`survey_response_id`),
  INDEX `idx_question` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Keep study_surveys table for backwards compatibility and survey activation
-- But remove fixed question columns in favor of survey_questions table
-- We'll keep the table structure but repurpose it for survey metadata

-- Note: The old response_1 through response_5 columns in survey_responses
-- are kept for backwards compatibility but new responses will use survey_question_responses