-- Part 10: small durable workflow engine tables
-- Compose after: content/series/durable-work-postgres/source/schema.sql if workflow workers share the workers table.

CREATE TABLE workflow_instances (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  workflow_key text NOT NULL UNIQUE,
  workflow_type text NOT NULL,
  workflow_version int NOT NULL,
  status text NOT NULL DEFAULT 'running',
  current_step text,
  input jsonb NOT NULL,
  state jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE workflow_steps (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  workflow_id uuid NOT NULL REFERENCES workflow_instances(id),
  step_key text NOT NULL,
  step_name text NOT NULL,
  step_order int NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  input jsonb NOT NULL DEFAULT '{}',
  attempts int NOT NULL DEFAULT 0,
  claimed_by text,
  lease_expires_at timestamptz,
  available_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workflow_id, step_key)
);

CREATE INDEX idx_workflow_steps_claim
  ON workflow_steps (available_at, created_at, id)
  WHERE status = 'pending';
