-- Add pay_mode and bank_mode columns to employees table if they don't exist

-- Add pay_mode if it does not exist
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'pay_mode') = 0,
    'ALTER TABLE `employees` ADD COLUMN `pay_mode` varchar(50) AFTER `bank_ifsc`;',
    'SELECT ''Column pay_mode already exists'';'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add bank_mode if it does not exist
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'bank_mode') = 0,
    'ALTER TABLE `employees` ADD COLUMN `bank_mode` varchar(50) AFTER `pay_mode`;',
    'SELECT ''Column bank_mode already exists'';'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

