-- Database schema for MVP Career Orientation Test

CREATE DATABASE IF NOT EXISTS mvp_career CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mvp_career;

-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(32) PRIMARY KEY,
    created_at DATETIME NOT NULL,
    INDEX idx_created_at (created_at)
);

-- Answers table (RIASEC questions)
CREATE TABLE IF NOT EXISTS answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    question_id INT NOT NULL,
    answer_value INT NOT NULL,
    time_ms INT DEFAULT 0,
    created_at DATETIME NOT NULL,
    UNIQUE KEY unique_answer (session_id, question_id),
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Visual answers (IAT pairs)
CREATE TABLE IF NOT EXISTS visual_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    pair_id INT NOT NULL,
    choice ENUM('left', 'right') NOT NULL,
    time_ms INT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Reaction game answers
CREATE TABLE IF NOT EXISTS reaction_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    round INT NOT NULL,
    selected_index INT NOT NULL,
    time_ms INT NOT NULL,
    correct TINYINT(1) DEFAULT 0,
    created_at DATETIME NOT NULL,
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Voice analysis
CREATE TABLE IF NOT EXISTS voice_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    analysis_data JSON,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Palm photo analysis
CREATE TABLE IF NOT EXISTS palm_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    analysis_data JSON,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Results table
CREATE TABLE IF NOT EXISTS results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    computed_result_json JSON NOT NULL,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

