[Bug]: sp-dialog: auto-focus should prioritize focusable content over footer slot buttons regardless of DOM order

### Code of conduct

- [x] I agree to follow this project's code of conduct.

### Impacted component(s)

sp-dialog, sp-dialog-base, sp-overlay

### Library version

1.7.0

### Expected behavior

When a dialog opens, focus should always be placed on the first focusable element inside the content area (non-slotted button), regardless of where slot="button" elements appear in the DOM order. Footer buttons should be deprioritized for initial focus.

### Actual behavior

Focus lands on the slot="button" element if it appears before focusable content in the DOM, rather than the focusable content element.

### Screenshots

### What browsers are you seeing the problem in?

Chrome

### How can we reproduce this issue?

Reproduction sandbox: https://stackblitz.com/edit/vitejs-vite-r7ip6q1y?file=package.json

POC: LrWeb Claire Wu yitongw@adobe.com

### Sample code or abstract reproduction which illustrates the problem

```

Title

Close
// This is not auto-focused in this case

```
https://stackblitz.com/edit/vitejs-vite-r7ip6q1y?file=package.json,src%2Fmy-element.ts,src%2Findex.css

### Severity

None

### Logs taken while reproducing problem

_No response_

### Would you like to track this issue in Jira?

- [x] Yes, please tell me the ticket number!

Start by locating the sp-dialog, sp-dialog-base, and sp-overlay implementations and their focus-management tests. Reproduce the behavior with the linked StackBlitz example, then trace how initial focus is selected when slot="button" appears before content. Done means the first focusable content element receives focus regardless of DOM order, with footer slot buttons deprioritized.

Written by the indexing model from the issue text.