English mayors have been granted new powers to introduce an overnight tourist tax across England, with local leaders expected to set out their spending plans by March 2028.