-- In-Study Feedback Feature Setup
-- Allows participants to provide feedback during card sorting exercises

-- Add enable_feedback column to studies table
ALTER TABLE studies 
ADD COLUMN IF NOT EXISTS enable_feedback TINYINT(1) DEFAULT 0 COMMENT 'Enable in-study feedback during card sort';

-- Create in_study_feedback table to store feedback responses
CREATE TABLE IF NOT EXISTS in_study_feedback (
    id INT PRIMARY KEY AUTO_INCREMENT,
    study_id INT NOT NULL,
    session_id INT DEFAULT NULL,
    question_text TEXT NOT NULL,
    answer_text TEXT,
    rating INT DEFAULT NULL COMMENT '1-5 rating if applicable',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE SET NULL,
    INDEX idx_study_id (study_id),
    INDEX idx_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create in_study_feedback_questions table for researcher-configured questions
CREATE TABLE IF NOT EXISTS in_study_feedback_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    study_id INT NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM('text', 'textarea', 'rating') DEFAULT 'textarea',
    is_required TINYINT(1) DEFAULT 0,
    sequence INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    INDEX idx_study_id (study_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;