<?php
require_once 'config.php';

echo "<h2>Setting up Survey Tables...</h2>";

// Drop tables if they exist (to avoid foreign key issues)
$conn->query("DROP TABLE IF EXISTS survey_answers");
$conn->query("DROP TABLE IF EXISTS survey_responses");
$conn->query("DROP TABLE IF EXISTS survey_questions");
$conn->query("DROP TABLE IF EXISTS surveys");

echo "<p>Cleared old tables (if any)</p>";

// Create surveys table WITHOUT foreign key
$sql_surveys = "CREATE TABLE IF NOT EXISTS surveys (
    survey_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    target_schools VARCHAR(50) DEFAULT 'all',
    deadline DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

// Create survey_questions table
$sql_questions = "CREATE TABLE IF NOT EXISTS survey_questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    question_text TEXT NOT NULL,
    question_type VARCHAR(20) DEFAULT 'text',
    options TEXT,
    required TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

// Create survey_responses table WITHOUT foreign keys to schools (might not exist)
$sql_responses = "CREATE TABLE IF NOT EXISTS survey_responses (
    response_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    school_id INT,
    user_id INT,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

// Create survey_answers table
$sql_answers = "CREATE TABLE IF NOT EXISTS survey_answers (
    answer_id INT AUTO_INCREMENT PRIMARY KEY,
    response_id INT NOT NULL,
    question_id INT NOT NULL,
    answer_text TEXT,
    FOREIGN KEY (response_id) REFERENCES survey_responses(response_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if ($conn->query($sql_surveys) === TRUE) {
    echo "<p style='color: green;'>✓ Surveys table created successfully</p>";
} else {
    echo "<p style='color: red;'>✗ Error creating surveys table: " . $conn->error . "</p>";
}

if ($conn->query($sql_questions) === TRUE) {
    echo "<p style='color: green;'>✓ Survey questions table created successfully</p>";
} else {
    echo "<p style='color: red;'>✗ Error creating survey_questions table: " . $conn->error . "</p>";
}

if ($conn->query($sql_responses) === TRUE) {
    echo "<p style='color: green;'>✓ Survey responses table created successfully</p>";
} else {
    echo "<p style='color: red;'>✗ Error creating survey_responses table: " . $conn->error . "</p>";
}

if ($conn->query($sql_answers) === TRUE) {
    echo "<p style='color: green;'>✓ Survey answers table created successfully</p>";
} else {
    echo "<p style='color: red;'>✗ Error creating survey_answers table: " . $conn->error . "</p>";
}

echo "<h3 style='color: green;'>Survey tables setup complete!</h3>";
echo "<p><a href='super_admin_surveys.php'>Go to Surveys Page</a></p>";

$conn->close();
?>