Unfortunately, there is no such management. It is certainly a highly desirable feature the present manual positioning is not satisfactory.
From my experience, wrap mode is a key factor.
Wrap None is the safest mode. The frame becomes part of text flow and you roughly get what you describe, albeit it may leave an unused “hole” at the bottom of a page.
Wrap Through is intended for “watermarks”. Frame content is displayed “behind” text. I will not consider this case as not relevant for the question.
In all other modes, you have text “parallel” to the frame. It seems that only the paragraph is taken into account for text flow and the frame is unconditionally set aside the paragraph. If the paragraph is too high or too low in the page, the frame overflows the paragraph text area without constraint. I mean that paragraph text area is not the active limit, the page limits are. Consequently, not only the frame may cover footnotes, but also footer or header (depending on relative positionning to anchor) and even margins.
I found no automatic solution but manual tuning, which is not satisfactory because any editing may change the circumstances and compel to review the tuning.
Sure, management à la LATeX would be a good feature. You can fill in an enhancement request but I don’t think developers are available for that in short term.