PRAGMA foreign_keys=ON;
CREATE TABLE IF NOT EXISTS orders (
 sh_order TEXT PRIMARY KEY, money TEXT NOT NULL, ptype TEXT NOT NULL, expected TEXT
);
CREATE TABLE IF NOT EXISTS states (
 sh_order TEXT NOT NULL REFERENCES orders(sh_order), stream TEXT NOT NULL,
 seq INTEGER NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'awaiting',
 PRIMARY KEY(sh_order,stream)
);
CREATE TABLE IF NOT EXISTS events (
 event_id TEXT PRIMARY KEY, sh_order TEXT NOT NULL REFERENCES orders(sh_order),
 stream TEXT NOT NULL, seq INTEGER NOT NULL, digest TEXT NOT NULL,
 UNIQUE(sh_order,stream,seq)
);
-- This is a transactional business outbox. No external delivery is performed here.
-- An external worker must use sh_order+stream as its idempotency key too.
CREATE TABLE IF NOT EXISTS outbox (
 sh_order TEXT NOT NULL REFERENCES orders(sh_order), stream TEXT NOT NULL,
 event_id TEXT NOT NULL, PRIMARY KEY(sh_order,stream)
);