-- Безопасное добавление полей для системы второго ключа и напоминаний
USE avatar_bot;

-- Добавляем поля только если они не существуют
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'user_fear') = 0,
    'ALTER TABLE users ADD COLUMN user_fear VARCHAR(100) DEFAULT NULL COMMENT "Выбранная пользователем тревога"',
    'SELECT "Column user_fear already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'user_income') = 0,
    'ALTER TABLE users ADD COLUMN user_income TEXT DEFAULT NULL COMMENT "Текст дохода пользователя"',
    'SELECT "Column user_income already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'point_a_income') = 0,
    'ALTER TABLE users ADD COLUMN point_a_income DECIMAL(12,2) DEFAULT NULL COMMENT "Текущий доход (числовое значение)"',
    'SELECT "Column point_a_income already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'goal_text') = 0,
    'ALTER TABLE users ADD COLUMN goal_text TEXT DEFAULT NULL COMMENT "Текст цели пользователя"',
    'SELECT "Column goal_text already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'goal_amount') = 0,
    'ALTER TABLE users ADD COLUMN goal_amount DECIMAL(12,2) DEFAULT NULL COMMENT "Сумма цели"',
    'SELECT "Column goal_amount already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'point_b_goal') = 0,
    'ALTER TABLE users ADD COLUMN point_b_goal DECIMAL(12,2) DEFAULT NULL COMMENT "Цель пассивного дохода (числовое значение)"',
    'SELECT "Column point_b_goal already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'desired_percentage_text') = 0,
    'ALTER TABLE users ADD COLUMN desired_percentage_text TEXT DEFAULT NULL COMMENT "Текст желаемой доходности"',
    'SELECT "Column desired_percentage_text already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'desired_percentage') = 0,
    'ALTER TABLE users ADD COLUMN desired_percentage DECIMAL(5,2) DEFAULT NULL COMMENT "Желаемая доходность в процентах"',
    'SELECT "Column desired_percentage already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'reminder_time') = 0,
    'ALTER TABLE users ADD COLUMN reminder_time INT DEFAULT NULL COMMENT "Unix timestamp для напоминания"',
    'SELECT "Column reminder_time already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- НОВЫЕ ПОЛЯ ДЛЯ ВТОРОГО КЛЮЧА
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'second_key_reminder_time') = 0,
    'ALTER TABLE users ADD COLUMN second_key_reminder_time INT DEFAULT NULL COMMENT "Unix timestamp для напоминания о второй кнопке ключа"',
    'SELECT "Column second_key_reminder_time already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'second_key_opened') = 0,
    'ALTER TABLE users ADD COLUMN second_key_opened BOOLEAN DEFAULT FALSE COMMENT "Был ли открыт второй ключ"',
    'SELECT "Column second_key_opened already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Добавляем индексы только если они не существуют
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_second_key_reminder_time') = 0,
    'ALTER TABLE users ADD INDEX idx_second_key_reminder_time (second_key_reminder_time)',
    'SELECT "Index idx_second_key_reminder_time already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA = 'avatar_bot' AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_second_key_opened') = 0,
    'ALTER TABLE users ADD INDEX idx_second_key_opened (second_key_opened)',
    'SELECT "Index idx_second_key_opened already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
