-- Таблица для хранения ответов на визуальные пары (новый формат ТЗ #3)
CREATE TABLE IF NOT EXISTS `visual_pairs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `session_id` VARCHAR(255) NOT NULL,
    `pair_id` INT NOT NULL COMMENT 'Номер пары (1-12)',
    `choice` ENUM('left', 'right') NOT NULL COMMENT 'Выбор: левое или правое',
    `time_ms` INT NOT NULL DEFAULT 0 COMMENT 'Время выбора в миллисекундах',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_session` (`session_id`),
    INDEX `idx_pair` (`pair_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Обновляем таблицу answers: добавляем time_ms если его еще нет
-- Проверка и добавление колонки time_ms
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'answers'
AND COLUMN_NAME = 'time_ms';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE `answers` ADD COLUMN `time_ms` INT NOT NULL DEFAULT 0 COMMENT ''Время ответа в миллисекундах''',
    'SELECT ''time_ms column already exists'' AS info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Проверяем таблицу reaction_answers (должна быть уже создана, но если нет - создаем)
CREATE TABLE IF NOT EXISTS `reaction_answers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `session_id` VARCHAR(255) NOT NULL,
    `round` INT NOT NULL COMMENT 'Номер раунда (1-6)',
    `selected_index` INT NOT NULL COMMENT 'Выбранный индекс (0-5)',
    `time_ms` INT NOT NULL DEFAULT 0 COMMENT 'Время реакции в миллисекундах',
    `correct` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Правильный ли ответ',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_session_reaction` (`session_id`),
    INDEX `idx_round` (`round`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Обновляем таблицу voice_analysis: добавляем отдельные колонки для скоринга (5% веса)
-- Проверка и добавление колонок для голосового анализа
SET @col_confidence = 0;
SELECT COUNT(*) INTO @col_confidence
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'voice_analysis'
AND COLUMN_NAME = 'confidence';

SET @query_confidence = IF(@col_confidence = 0,
    'ALTER TABLE `voice_analysis` ADD COLUMN `confidence` INT DEFAULT 0 COMMENT ''Уверенность (0-100)''',
    'SELECT ''confidence column already exists'' AS info');

PREPARE stmt FROM @query_confidence;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Добавляем emotion
SET @col_emotion = 0;
SELECT COUNT(*) INTO @col_emotion
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'voice_analysis'
AND COLUMN_NAME = 'emotion';

SET @query_emotion = IF(@col_emotion = 0,
    'ALTER TABLE `voice_analysis` ADD COLUMN `emotion` INT DEFAULT 0 COMMENT ''Эмоциональность (0-100)''',
    'SELECT ''emotion column already exists'' AS info');

PREPARE stmt FROM @query_emotion;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Добавляем stress_index
SET @col_stress = 0;
SELECT COUNT(*) INTO @col_stress
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'voice_analysis'
AND COLUMN_NAME = 'stress_index';

SET @query_stress = IF(@col_stress = 0,
    'ALTER TABLE `voice_analysis` ADD COLUMN `stress_index` INT DEFAULT 0 COMMENT ''Индекс стресса (0-100)''',
    'SELECT ''stress_index column already exists'' AS info');

PREPARE stmt FROM @query_stress;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Добавляем energy
SET @col_energy = 0;
SELECT COUNT(*) INTO @col_energy
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'voice_analysis'
AND COLUMN_NAME = 'energy';

SET @query_energy = IF(@col_energy = 0,
    'ALTER TABLE `voice_analysis` ADD COLUMN `energy` INT DEFAULT 0 COMMENT ''Энергия (0-100)''',
    'SELECT ''energy column already exists'' AS info');

PREPARE stmt FROM @query_energy;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Добавляем clarity
SET @col_clarity = 0;
SELECT COUNT(*) INTO @col_clarity
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'voice_analysis'
AND COLUMN_NAME = 'clarity';

SET @query_clarity = IF(@col_clarity = 0,
    'ALTER TABLE `voice_analysis` ADD COLUMN `clarity` INT DEFAULT 0 COMMENT ''Четкость речи (0-100)''',
    'SELECT ''clarity column already exists'' AS info');

PREPARE stmt FROM @query_clarity;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

