Implement ordering on the Surge Alert table based on status and opens fields

[samshara]

based on IFRCGo/go-web-app#512 (comment)

Default Ordering Rules

1. Open alerts should be displayed at the top.
2. Stood downs should follow open alerts.
3. Closed alerts should be displayed last.
   Within the open alerts category, sorting should be done based on the alert date (opens).

The status calculation is performed on the client side right now, and the logic is implemented as follows:

1. If the alert status is marked as stood_down, then the status is Stood Down.
2. If the closing timestamp (closes) is earlier than the current date, the status is displayed as Closed. Otherwise, it is displayed as Open.

Workplan

• Add Status field
  • Index the field
  • Define Enum values according to the ordering rules
• Define a cron job to update the status field, update every 12 hours (first trigger on 0:1 UTC, first hour of UTC)
• Add option to order by status and opens (date field)

cc @thenav56 @k9845

[tovari]

could you please explain why do we have no Open alerts in this emergency page:
https://go-stage.ifrc.org/emergencies/6646/surge

while we have many Open alerts on the All Alerts page for this emergency:
https://go-stage.ifrc.org/alerts/all?event=6646

[szabozoltan69]

On the page where there are many open alerts we do not use these constraints:
is_active=true
and
end >= 2024-03-04

These filters filter out all the surge alerts concerning to event 6646.
So maybe the "is_active" vs. "open" conflicts here.

[szabozoltan69]

(If I resend the 0-giving request with curl with removing these 2 constraints, in the response there are all the 57 alerts that is on the other page, other request.)

[samshara]

On the page where there are many open alerts we do not use these constraints: is_active=true and end >= 2024-03-04

These filters filter out all the surge alerts concerning to event 6646. So maybe the "is_active" vs. "open" conflicts here.

@szabozoltan69 Thanks for the quick assessment. @tovari this is indeed the case. We could remove those constraints (is_active=true, end >=2024-03-04[today's date]) from the query or include those constraints when calculating the status. @anamariaescobar do you have any suggestions?