"""Sequential SQLite handoff transitions; no messaging or account actions."""
import sqlite3
c=sqlite3.connect(':memory:')
c.execute('CREATE TABLE handoff(id TEXT PRIMARY KEY,owner TEXT,offered_to TEXT,offer_id TEXT,revision INTEGER,state TEXT)')
c.execute("INSERT INTO handoff VALUES('H18','A','B','offer7',5,'offered')")
def accept(offer,revision,person):
 return c.execute("UPDATE handoff SET owner=?,offered_to=NULL,state='accepted',revision=revision+1 WHERE id='H18' AND offer_id=? AND revision=? AND offered_to=? AND state='offered'",(person,offer,revision,person)).rowcount
assert c.execute("SELECT owner FROM handoff").fetchone()[0]=='A'
assert accept('offer7',5,'B')==1
assert accept('offer7',5,'B')==0
assert c.execute("SELECT owner FROM handoff").fetchone()[0]=='B'
# A replacement offer makes a late acceptance of the old offer ineligible.
c.execute("UPDATE handoff SET owner='A',offered_to='C',offer_id='offer8',revision=8,state='offered'")
assert accept('offer7',5,'B')==0
assert accept('offer8',8,'C')==1
print('6/6 local transition assertions passed. Notifications and human availability are not modelled.')