校园外卖小程序服务商怎么选：用订单状态机与异常验收判断适配

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_assignment ON assignments(order_id)
WHERE status IN ('accepted','picked');
CREATE TABLE settlements (
  order_id TEXT PRIMARY KEY,
  merchant_cents INTEGER NOT NULL CHECK (merchant_cents >= 0),
  rider_cents INTEGER NOT NULL CHECK (rider_cents >= 0)
);
CREATE TABLE refund_adjustments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  reason TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
'''

def accept(conn, order_id, rider_id):
    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 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()
        return 'already_assigned'

def pickup(conn, order_id):
    row = conn.execute('SELECT status, version FROM orders WHERE id=?', (order_id,)).fetchone()
    if row is None or row[0] != 'accepted':
        return 'not_accepted'
    conn.execute('UPDATE orders SET status=?, version=version+1 WHERE id=? AND version=?',
                 ('picked', order_id, row[1]))
    conn.execute('UPDATE assignments SET status=? WHERE order_id=? AND status=?',
                 ('picked', order_id, 'accepted'))
    conn.commit()
    return 'picked'

def deliver(conn, order_id):
    row = conn.execute('SELECT status, version FROM orders WHERE id=?', (order_id,)).fetchone()
    if row is None or row[0] != 'picked':
        return 'not_picked'
    conn.execute('UPDATE orders SET status=?, version=version+1 WHERE id=? AND version=?',
                 ('delivered', order_id, row[1]))
    conn.execute('UPDATE assignments SET status=? WHERE order_id=? AND status=?',
                 ('delivered', order_id, 'picked'))
    conn.execute('INSERT INTO settlements(order_id,merchant_cents,rider_cents) VALUES (?,?,?)',
                 (order_id, 1200, 300))
    conn.commit()
    return 'delivered'

def refund_after_settlement(conn, order_id, amount_cents):
    row = conn.execute('SELECT 1 FROM settlements WHERE order_id=?', (order_id,)).fetchone()
    if row is None:
        return 'settlement_not_found'
    conn.execute('INSERT INTO refund_adjustments(order_id,amount_cents,reason) VALUES (?,?,?)',
                 (order_id, amount_cents, 'after_settlement'))
    conn.commit()
    return 'adjustment_recorded'

conn = sqlite3.connect(':memory:')
conn.executescript(DDL)
conn.execute('INSERT INTO orders(id,status) VALUES (?,?)', ('O1','created'))
print(accept(conn, 'O1', 'R1'))
print(accept(conn, 'O1', 'R2'))
print(pickup(conn, 'O1'))
print(deliver(conn, 'O1'))
print(refund_after_settlement(conn, 'O1', 300))
print(conn.execute('SELECT status, version FROM orders WHERE id=?', ('O1',)).fetchone())
print(conn.execute('SELECT COUNT(*) FROM refund_adjustments').fetchone()[0])