-- Opaque IDs only. The application supplies login sessions after its own login flow.
CREATE TABLE app_sessions(session_hash TEXT PRIMARY KEY,user_id TEXT NOT NULL,expires_at INTEGER NOT NULL);
CREATE TABLE store_memberships(store_id TEXT NOT NULL,user_id TEXT NOT NULL,role TEXT NOT NULL CHECK(role IN ('viewer','editor')),PRIMARY KEY(store_id,user_id));
CREATE TABLE orders(id TEXT PRIMARY KEY,store_id TEXT NOT NULL,version INTEGER NOT NULL,state TEXT NOT NULL,last_event_id TEXT NOT NULL,realtime_generation TEXT NOT NULL);
CREATE INDEX orders_store_id ON orders(store_id,id);
CREATE TABLE order_outbox(id TEXT PRIMARY KEY,order_id TEXT NOT NULL,store_id TEXT NOT NULL,version INTEGER NOT NULL,generation TEXT NOT NULL,next_attempt_at INTEGER NOT NULL DEFAULT 0,attempts INTEGER NOT NULL DEFAULT 0);
CREATE INDEX order_outbox_due ON order_outbox(next_attempt_at,id);
CREATE TRIGGER orders_outbox AFTER UPDATE OF version ON orders WHEN NEW.version=OLD.version+1 BEGIN
  INSERT INTO order_outbox(id,order_id,store_id,version,generation) VALUES(NEW.last_event_id,NEW.id,NEW.store_id,NEW.version,NEW.realtime_generation);
END;
