Modify sorting criteria using views_query_alter

Question

I have a view for the content type events with fields event_name, description, summary, event_start_date, etc. What my requirement is:

1. Sort the events in ascending order of event_date equal or greater than the current date (upcoming events)
2. Sort the events in descending order of event_date less than the current date (past events)

NOTE: the upcoming data should come first and then the past data.

eg data and expected output

example data

expected result

Through the normal view UI settings, I couldn't achieve this. so I tried the views_query_alter hook to modify the sorting criteria. but I couldn't achieve this. Any help?

What I have tried

/**
 * Implements hook_views_query_alter
 */
function news_t1_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if($view->id() == 'events'){
    $query->orderby[0]['field'] = "CASE node__field_st_field_st_value WHEN DATE(node__field_st_field_st_value) >= CURDATE()  END";
    $query->orderby[0]['direction'] = "ASC";
    $query->orderby[1]['field'] = "CASE node__field_st_field_st_value WHEN DATE(node__field_st_field_st_value) < CURDATE()  END";
    $query->orderby[1]['direction'] = "DESC";
    //$view->set_items_per_page(5);  
  }
}

node__field_st is the event_date field name.

Update after @Patrick Kenny's Answer

Your solution works great. thanks!. I added a Global: View area (Global: View area) in the footer of the main display. But there is a small glitch to be removed. I have contextual and exposed filters in the master view which is inherited (this part is okay). But the problem is heading is repeated in the attached display. any way to get rid of this? screenshot attached.

Answer 1

Objective: Show upcoming events, then past events.

The simplest way to implement this entirely through the View UI is to add a second display to the view.

Basic steps

1. Create the main view display. Configure the events to display as you like.
2. Set to sort by upcoming events, and filter so that only upcoming events are displayed.
3. Add an Attachment display. For this display, modify the filter (This page: override) to only show past events, and modify the sort in the same way.
4. In the Footer of the main view display, add the attachment.