Trust in Scotland’s former first minister has ebbed since the height of the pandemic, as evidence reveals a presidential leadership style that came to exhaust her