It’s easy to be against it now because so much content that people recognise as AI is also just bad. If professionals can start to use it to produce content that is actually good, I think opinions will shift.
There are a lot of AI videos that you can very easily tell are AI, even if they are done well. For example, I just saw a Higgsfield video of a kangaroo fighting in the UFC. You can tell it is AI, mainly because it would be an insane amount of work to create any other way. But I think it is getting close to good enough that a lot of people, even knowing it is AI, wouldn't care. Everyone other than the most ardent anti-AI people are going to be fine with this when we have people creating interesting and engaging media with AI.
I think we will look back at AI "slop" as a temporary point in time where people were creating bad content, and people were defending it as good even when it was not. Instead, as you say, AI video will fall into the background as a tool creators use, just like cameras or CGI. But in my opinion it won't be that people can't tell that AI was used at all. Rather, it will be that they won't care if there is still a creative vision behind it.
At least, that is what I hope compared to the outcome where there are no creators and people just watch Sora videos tailored to them all day.