A Worker

Since the last lesson, paying an order doesn't send the receipt. It adds a row to the jobs table, with the status 'waiting'. Somebody has to do those jobs: that is a worker. In a real shop it is a second program that takes the waiting jobs every few seconds. Yours is a route, POST /admin/run-jobs, so one request runs it.

A worker does three things: it takes the rows still to do, it does each one, and it marks each one done, so nothing is done twice. Here is the same shape on another table, reminders:

var cmd = new SqliteCommand("SELECT * FROM reminders WHERE sent = 0", db);
using var reader = cmd.ExecuteReader();
var rows = new List<Dictionary<string, object>>();
while (reader.Read()) {
    rows.Add(RowToDict(reader));
}
foreach (var row in rows) {
    Console.WriteLine("Reminder for customer " + row["customer_id"]);
    var sent = new SqliteCommand("UPDATE reminders SET sent = 1 WHERE id = $id", db);
    sent.Parameters.AddWithValue("$id", row["id"]);
    sent.ExecuteNonQuery();
}

The UPDATE runs inside the loop, right after each row, and ExecuteNonQuery saves it at once: if the program stops halfway, the rows already done stay done. For your jobs, that means no customer gets the same email twice. The route is a POST, because it changes things; before you add it, ASP.NET answers 404 for it.

The tests pay two orders, run the worker twice, then pay a third order and run it again. They read /check/sent (the emails the mail service received) and /check/jobs (the jobs table):

POST /orders/8/pay    →  201  {"amount": 80, "order_id": 8, "payment_id": "pay_1"}
POST /orders/10/pay   →  201  {"amount": 21, "order_id": 10, "payment_id": "pay_2"}
GET /check/sent       →  200  []
POST /admin/run-jobs  →  200  {"ran": 2}
GET /check/sent       →  200  [{"subject": "Your order 8 is paid", "to": "eva@example.com", …},
                               {"subject": "Your order 10 is paid", "to": "chloe@example.com", …}]
GET /check/jobs       →  200  [{"id": 1, "order_id": 8, "status": "done"},
                               {"id": 2, "order_id": 10, "status": "done"}]
POST /admin/run-jobs  →  200  {"ran": 0}
POST /orders/11/pay   →  201  {"amount": 18, "order_id": 11, "payment_id": "pay_3"}
POST /admin/run-jobs  →  200  {"ran": 1}
GET /check/sent       →  200  [… the same two, then {"subject": "Your order 11 is paid", "to": "ana@example.com", …}]

Your task: at the end of the file, add the route app.MapPost("/admin/run-jobs", async () => …). It reads the waiting jobs, oldest first, into a list jobs, sends each job's receipt with your method SendReceipt, marks the job 'done', then answers {"ran": …} with the number of jobs it took.

Assignment
Follow the Coding Tutorial to write the worker route /admin/run-jobs.

Hint
Take the jobs with WHERE status = 'waiting' ORDER BY id. job["order_id"] is an object: Convert.ToInt32 turns it into the int that SendReceipt takes. The UPDATE and its ExecuteNonQuery() go inside the foreach; the return goes after it, with jobs.Count.