It’s been branded Ireland’s worst main street but, from October next, it will be transformed into one of the finest town thoroughfares.