CREATE TABLE IF NOT EXISTS task_table (
   id                 BIGSERIAL   PRIMARY   KEY,
   external_id        TEXT        UNIQUE    NOT NULL,
   annotator_options  TEXT        NOT NULL
);

CREATE TABLE IF NOT EXISTS qna_state_table (
   id     BIGSERIAL   PRIMARY   KEY,
   name   TEXT        UNIQUE NOT NULL
);
INSERT INTO qna_state_table (name)
VALUES
  ('STATE_NONE'),
  ('STATE_QUEUED'),
  ('STATE_PENDING'),
  ('STATE_ANSWERED'),
  ('STATE_REJECTED'),
  ('STATE_CONFIRMED'),
  ('STATE_GOLDEN')
ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS qna_table (
  id                BIGSERIAL   PRIMARY      KEY,
  external_id       TEXT        NOT NULL,
  task_id           BIGINT      REFERENCES   task_table        (id) NOT NULL,

  state_id          BIGINT      REFERENCES   qna_state_table   (id) NOT NULL,

  data              TEXT        NOT NULL,

  UNIQUE (task_id, external_id) -- Note this automatically adds an index on task_id, external_id
);

CREATE TABLE IF NOT EXISTS qna_state_change_table (
  id                     BIGSERIAL   PRIMARY      KEY,

  qna_id                 BIGINT      REFERENCES   qna_table         (id) ON DELETE CASCADE NOT NULL,
  task_id                BIGINT      REFERENCES   task_table        (id) NOT NULL,
  user_                  TEXT        NOT NULL,
  time                   BIGINT      NOT NULL,

  from_state_id          BIGINT      REFERENCES   qna_state_table   (id) NOT NULL,
  to_state_id            BIGINT      REFERENCES   qna_state_table   (id) NOT NULL,
  golden_answer_correct  BOOLEAN     NOT NULL
);
