-- Survey system database tables

-- Table to store survey questions for each job
CREATE TABLE job_survey_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    job_id INT NOT NULL,
    question TEXT NOT NULL,
    question_type ENUM('text', 'textarea', 'select', 'radio', 'checkbox') DEFAULT 'text',
    options TEXT NULL, -- JSON format for select/radio/checkbox options
    is_required TINYINT(1) DEFAULT 1,
    question_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
);

-- Table to store candidate responses to survey questions
CREATE TABLE job_application_survey_responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    application_id INT NOT NULL,
    question_id INT NOT NULL,
    response TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES job_survey_questions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_application_question (application_id, question_id)
);