-- Survey System Setup
-- Creates tables for post-study surveys and responses

-- Table to store survey questions for each study
CREATE TABLE IF NOT EXISTS `study_surveys` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `study_id` INT NOT NULL,
  `question_1` VARCHAR(500) DEFAULT 'Were any terms unclear?',
  `question_2` VARCHAR(500) DEFAULT 'Which category was hardest to name?',
  `question_3` VARCHAR(500) DEFAULT 'Any missing items you expected?',
  `custom_question_1` VARCHAR(500) DEFAULT NULL,
  `custom_question_2` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`study_id`) REFERENCES `studies`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_study_survey` (`study_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table to store participant responses to surveys
CREATE TABLE IF NOT EXISTS `survey_responses` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `survey_id` INT NOT NULL,
  `session_id` INT DEFAULT NULL,
  `study_id` INT NOT NULL,
  `participant_token` VARCHAR(255) DEFAULT NULL,
  `response_1` TEXT DEFAULT NULL,
  `response_2` TEXT DEFAULT NULL,
  `response_3` TEXT DEFAULT NULL,
  `response_4` TEXT DEFAULT NULL,
  `response_5` TEXT DEFAULT NULL,
  `submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`survey_id`) REFERENCES `study_surveys`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`study_id`) REFERENCES `studies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`session_id`) REFERENCES `sessions`(`id`) ON DELETE SET NULL,
  INDEX `idx_study` (`study_id`),
  INDEX `idx_survey` (`survey_id`),
  INDEX `idx_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;