Untitled

-- 1. Table for personalities
CREATE TABLE personalities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    place_of_birth VARCHAR(255),
    image_url VARCHAR(255),
    description TEXT
);

-- 2. Table for quizzes
CREATE TABLE quizzes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Table for questions
CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quiz_id INT,  -- reference to quiz
    personality_id INT,  -- reference to personality
    question_text TEXT NOT NULL,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
    FOREIGN KEY (personality_id) REFERENCES personalities(id) ON DELETE CASCADE
);

-- 4. Table for answer options
CREATE TABLE answer_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT,  -- reference to question
    answer_text VARCHAR(255) NOT NULL,
    is_correct BOOLEAN DEFAULT 0,  -- 0 - incorrect, 1 - correct
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

-- 5. Optional: Table for correct answers (if needed)
CREATE TABLE correct_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT,
    answer_id INT,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    FOREIGN KEY (answer_id) REFERENCES answer_options(id) ON DELETE CASCADE
);