<?php
require_once 'config/config.php';

try {
    $database = new Database();
    $db = $database->getConnection();

    echo "Creating survey system tables...\n";

    // Create job_survey_questions table
    $sql1 = "CREATE TABLE IF NOT EXISTS 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,
        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
    )";

    $db->exec($sql1);
    echo "✓ job_survey_questions table created successfully\n";

    // Create job_application_survey_responses table
    $sql2 = "CREATE TABLE IF NOT EXISTS 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)
    )";

    $db->exec($sql2);
    echo "✓ job_application_survey_responses table created successfully\n";

    echo "\nSurvey system database setup completed successfully!\n";

} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>