BEGIN;

DROP TABLE IF EXISTS task;
DROP TABLE IF EXISTS queue;
DROP SEQUENCE IF EXISTS task_seq;

CREATE TABLE queue (
  name     VARCHAR(100) NOT NULL,
  interval INTEGER      NOT NULL,
  CONSTRAINT pk_queue PRIMARY KEY (name)
);

CREATE TABLE task (
  id        BIGINT       NOT NULL,
  parent    BIGINT,
  queue     VARCHAR(100) NOT NULL,
  state     VARCHAR(10)  NOT NULL,
  scheduled TIMESTAMP    NOT NULL,
  last_run  TIMESTAMP,
  run_count INT          NOT NULL,
  completed TIMESTAMP,
  arguments VARCHAR(100),
  CONSTRAINT pk_task PRIMARY KEY (id),
  CONSTRAINT fk_task__queue FOREIGN KEY (queue) REFERENCES queue (name),
  CONSTRAINT fk_task__parent FOREIGN KEY (parent) REFERENCES task (id)
);

CREATE INDEX ix_task__queue__state ON task (queue, state);

CREATE SEQUENCE task_seq;

COMMIT;