校园外卖平台选模板还是定制：订单状态机与异常验收

import sqlite3

DDL = '''
CREATE TABLE orders (
  id TEXT PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('created','accepted','picked','delivered','cancelled')),
  version INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE assignments (
  order_id TEXT NOT NULL,
  rider_id TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('accepted','picked','delivered','cancelled'))
);
CREATE UNIQUE INDEX uq_active_rider ON assignments(order_id)
WHERE status IN ('accepted','picked');
CREATE TABLE settlements (
  order_id TEXT PRIMARY KEY,
  rider_income_cents INTEGER NOT NULL CHECK (rider_income_cents >= 0)
);
CREATE TABLE idempotency_keys (
  key TEXT PRIMARY KEY,
  result TEXT NOT NULL
);
'''

def accept(conn, order_id, rider_id, idem_key):
    existing = conn.execute('SELECT result FROM idempotency_keys WHERE key=?', (idem_key,)).fetchone()
    if existing:
        return 'duplicate_request'
    row = conn.execute('SELECT status, version FROM orders WHERE id=?', (order_id,)).fetchone()
    if row is None or row[0] != 'created':
        return 'status_not_created'
    try:
        conn.execute('INSERT INTO idempotency_keys(key,result) VALUES (?,?)', (idem_key, 'accepted'))
        conn.execute('INSERT INTO assignments(order_id,rider_id,status) VALUES (?,?,?)',
                     (order_id, rider_id, 'accepted'))
        conn.execute('UPDATE orders SET status=?, version=version+1 WHERE id=? AND version=?',
                     ('accepted', order_id, row[1]))
        conn.commit()
        return 'accepted'
    except sqlite3.IntegrityError:
        conn.rollback()
        existing = conn.execute('SELECT result FROM idempotency_keys WHERE key=?', (idem_key,)).fetchone()
        if existing:
            return 'duplicate_request'
        return 'already_assigned'

def settle(conn, order_id, rider_income_cents):
    try:
        conn.execute('INSERT INTO settlements(order_id,rider_income_cents) VALUES (?,?)',
                     (order_id, rider_income_cents))
        conn.commit()
        return 'settled'
    except sqlite3.IntegrityError:
        conn.rollback()
        return 'duplicate_settlement'

conn = sqlite3.connect(':memory:')
conn.executescript(DDL)
conn.execute('INSERT INTO orders(id,status) VALUES (?,?)', ('O1','created'))
print(accept(conn, 'O1', 'R1', 'accept:O1:R1'))  # 预期 accepted
print(accept(conn, 'O1', 'R2', 'accept:O1:R2'))  # 预期 status_not_created
print(accept(conn, 'O1', 'R1', 'accept:O1:R1'))  # 预期 duplicate_request
print(settle(conn, 'O1', 500))  # 预期 settled
print(settle(conn, 'O1', 500))  # 预期 duplicate_settlement