Sign Up in C#

So far only the owner and the staff have keys, and they are written into the code. Customers need accounts of their own: they sign up with an email and a password, and the shop keeps each one as a row in a new table, users.

A table is created once with CREATE TABLE IF NOT EXISTS, so the shop can start again and again; ExecuteNonQuery() runs a command that gives no rows back. A column marked UNIQUE never holds the same value twice. To know whether a value is taken, look for it with a SELECT: ExecuteScalar() gives the first value of the first matching row, or null when nothing matches. Here is the same idea for a made-up coupons table:

new SqliteCommand("CREATE TABLE IF NOT EXISTS coupons (id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE)", db).ExecuteNonQuery();
var cmd = db.CreateCommand();
cmd.CommandText = "SELECT id FROM coupons WHERE code = $code";
cmd.Parameters.AddWithValue("$code", "SPRING10");
var row = cmd.ExecuteScalar();
if (row == null) {
    Console.WriteLine("No such coupon");
}

An email that is already registered gets 409 Conflict: the request clashes with what the server already has. A new user gets 201 Created with their id and email. The answer never sends the password back.

These are the requests your code must answer:

POST /register  {"email": "ana@example.com", "password": "secret123"}  →  201  {"email": "ana@example.com", "id": 1}
POST /register  {"email": "ana@example.com", "password": "hello456"}   →  409  {"error": "Email already registered"}
POST /register  {"email": "ben@example.com", "password": "secret123"}  →  201  {"email": "ben@example.com", "id": 2}
POST /register  {"email": "ben@example.com", "password": "other789"}   →  409  {"error": "Email already registered"}

Your task: create the users table, then add POST /register with app.MapPost. When the email is taken, return {"error": "Email already registered"} with 409; otherwise save the user and return 201 with its id and email.

Assignment
Follow the Coding Tutorial to create the users table and the sign-up route.

Hint
Look for the email before the INSERT. The same cmd can run both queries: give it the INSERT text and a value for $password, and $email keeps its value. SELECT last_insert_rowid() after the INSERT gives the id the database gave the new row. Once the function returns 409 inside the if, C# wants a return at the end too.