START TRANSACTION;

SET @db := DATABASE();

SET @has_pos_settings_table := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'pos_settings'
);

SET @has_transfer_instruction_template := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'pos_settings'
    AND COLUMN_NAME = 'transfer_instruction_template'
);

SET @sql_transfer_instruction_template := IF(
  @has_pos_settings_table > 0 AND @has_transfer_instruction_template = 0,
  'ALTER TABLE pos_settings ADD COLUMN transfer_instruction_template TEXT NULL AFTER transfer_account_number',
  'SELECT 1'
);

PREPARE stmt FROM @sql_transfer_instruction_template; EXECUTE stmt; DEALLOCATE PREPARE stmt;

COMMIT;
