`state_groups` (& `state_groups_state` & `state_group_edges`) are not fully purged alongside the rooms

This issue has been migrated from [#12821](https://github.com/matrix-org/synapse/issues/12821).

---

While investigating my database size, I found that `state_groups_state` and `state_groups` hold many stale entries that reference old rooms that have long since been removed from `rooms` and all related events tables.

It appears these are not purged alongside other tables, particularly via the `DELETE _synapse/admin/v1/rooms/{room_id}` API (with `purge: true`)

### Version information

- **Version**: Synapse 1.57.1

- **Install method**: Docker image

- **Platform**: Linux (Debian)

Start by tracing the DELETE /_synapse/admin/v1/rooms/{room_id} API with purge: true and inspect how it removes data from rooms and related event tables. Compare that cleanup with state_groups, state_groups_state, and state_group_edges; done means stale rows for a purged room are removed without affecting retained rooms.

Written by the indexing model from the issue text.