BEGIN;

SET ROLE work_role;

CREATE TABLE IF NOT EXISTS work.graphs (
  graph_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  state TEXT NOT NULL,
  max_parallel INTEGER NOT NULL,
  max_attempts INTEGER NOT NULL,
  PRIMARY KEY (graph_id, revision),
  CHECK (graph_id <> ''), CHECK (revision > 0),
  CHECK (state IN ('DRAFT','ADMITTED','RUNNING','PAUSED','CANCELLING','PARTIAL','VERIFYING','COMPLETED','FAILED','UNKNOWN','RECOVERING')),
  CHECK (max_parallel > 0), CHECK (max_attempts > 0)
);

CREATE TABLE IF NOT EXISTS work.graph_nodes (
  graph_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  node_id TEXT NOT NULL,
  idempotency_key TEXT NOT NULL,
  state TEXT NOT NULL,
  attempts INTEGER NOT NULL,
  PRIMARY KEY (graph_id, revision, node_id),
  FOREIGN KEY (graph_id, revision) REFERENCES work.graphs(graph_id, revision),
  CHECK (node_id <> ''), CHECK (idempotency_key <> ''), CHECK (attempts >= 0),
  CHECK (state IN ('BLOCKED_PREREQUISITE','READY','LEASED','RUNNING','SUCCEEDED','FAILED','CANCELLED','UNKNOWN','RECOVERING'))
);

CREATE TABLE IF NOT EXISTS work.graph_edges (
  graph_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  from_node_id TEXT NOT NULL,
  to_node_id TEXT NOT NULL,
  PRIMARY KEY (graph_id, revision, from_node_id, to_node_id),
  FOREIGN KEY (graph_id, revision) REFERENCES work.graphs(graph_id, revision),
  CHECK (from_node_id <> ''), CHECK (to_node_id <> ''), CHECK (from_node_id <> to_node_id)
);

RESET ROLE;
COMMIT;