CREATE DOMAIN job_id AS UUID;
CREATE DOMAIN task_id AS UUID;
CREATE DOMAIN tick_salt AS U16;
CREATE DOMAIN task_kind AS VARCHAR(32) CHECK (value ~ '^[A-Z][A-Za-z0-9]{0,31}$');
CREATE TYPE task_status AS ENUM ('Complete', 'Available', 'Blocked');
CREATE TYPE job_tick AS
(
  time instant,
  salt tick_salt
);

-- The list of long-running jobs.
-- A job manages a single task tree.
CREATE TABLE job
(
  -- The (unique) job ID
  job_id        job_id  NOT NULL,
  -- Job creation time
  created_at    INSTANT NOT NULL,
  -- Job creator
  created_by    USER_ID NULL,
  -- Initial state, to support restarting a job from scratch
  initial_state BYTEA   NOT NULL,
  PRIMARY KEY (job_id),
  CONSTRAINT job__user__fk FOREIGN KEY (created_by) REFERENCES users (user_id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- The list of long-running tasks.
CREATE TABLE task
(
  -- The (unique) task ID
  task_id       task_id     NOT NULL,
  -- The job managing this task.
  job_id        job_id      NOT NULL,
  -- Name of the task kind (immutable)
  kind          task_kind   NOT NULL,
  -- A version number for the data stored in `options` and `state`
  kind_version  u32         NOT NULL,
  -- Task creation time
  created_at    INSTANT     NOT NULL,
  -- When the task was last polled (executed / updated)
  polled_at     job_tick    NULL,
  -- Number of steps executed
  revision      u32         NOT NULL,
  -- Status of the task
  status        task_status NOT NULL,
  -- Total execution time (excluding await points)
  cpu_duration  INTERVAL    NOT NULL,
  -- Total execution time (including await points)
  poll_duration INTERVAL    NOT NULL,
  -- Internal state of the task
  state         BYTEA       NOT NULL,
  -- Output of the task task
  output        BYTEA       NULL,
  PRIMARY KEY (task_id),
  CHECK (created_at <= (polled_at).time),
  CONSTRAINT task__job__fk FOREIGN KEY (job_id) REFERENCES job (job_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX task__available_polled_at__idx ON task (polled_at) WHERE status = 'Available';

-- Timers registered by tasks
CREATE TABLE task_timer
(
  -- When the task should be polled
  deadline instant NOT NULL,
  -- Task id
  task_id  task_id NOT NULL,
  PRIMARY KEY (deadline, task_id),
  CONSTRAINT task_timer__task__fk FOREIGN KEY (task_id) REFERENCES task (task_id) ON DELETE CASCADE ON UPDATE CASCADE
);