CREATE TABLE IF NOT EXISTS event_type_table (
  id                BIGSERIAL   PRIMARY      KEY,
  name              TEXT        UNIQUE       NOT NULL
);
comment on column event_type_table.name is 'E.g., JayWalker, Cutin and LaneChange';


CREATE TABLE IF NOT EXISTS mining_job_table (
  id                  BIGSERIAL   PRIMARY      KEY,
  event_type_name     TEXT        NOT NULL,
  miner_task_id       TEXT        NOT NULL,
  num_of_events       BIGINT,

  annotation_task_id  TEXT        NOT NULL,
  annotation_enabled  BOOLEAN     NOT NULL,

  piper_job_id        TEXT UNIQUE,
  user_name           TEXT        NOT NULL,
  created_at          BIGINT      DEFAULT date_part('epoch',NOW()) * 1e9, 
  updated_at          BIGINT,
  state               TEXT        NOT NULL,
  persistent          BOOLEAN,
  deleted             BOOLEAN
);
CREATE INDEX IF NOT EXISTS ix_miner_task_id_mining_job  on mining_job_table (event_type_name, miner_task_id, state, deleted);
CREATE INDEX IF NOT EXISTS ix_user_mining_job  on mining_job_table (event_type_name, user_name, state, deleted);
CREATE INDEX IF NOT EXISTS ix_ts_mining_job  on mining_job_table (event_type_name, created_at, state, deleted);

comment on table mining_job_table is 'Log mining job, submitting to EventBank';
comment on column mining_job_table.num_of_events is 'Number of events mined from this Job';
comment on column mining_job_table.user_name is 'user submitted this Job';
comment on column mining_job_table.created_at is 'Timestamp the job is first created, auto generated';
comment on column mining_job_table.updated_at is 'Timestamp the job was last updated';
comment on column mining_job_table.state is 'Submitted, Completed and Failed';
comment on column mining_job_table.persistent is 'If persistent is False, job and its mined events will only be kept for 90 days';

CREATE TABLE IF NOT EXISTS mined_chum_uri_table (
  id                BIGSERIAL   PRIMARY      KEY,
  mining_job_id     BIGINT, 
  event_type_name   TEXT        NOT NULL,
  miner_task_id     TEXT        NOT NULL,
  annotation_task_id  TEXT      NOT NULL,
  chum_uri_path     TEXT        NOT NULL,
  chum_uri          TEXT        NOT NULL,
  run_id            TEXT        NOT NULL,
  num_of_events     BIGINT,
  deleted           BOOLEAN,
  UNIQUE (miner_task_id, annotation_task_id, chum_uri)
);
comment on table mined_chum_uri_table is 'This table tracks the completed (miner_task_id, chum_uri_path, chum_uri)';
CREATE INDEX IF NOT EXISTS ix_mined_uri_path on mined_chum_uri_table (event_type_name, miner_task_id, chum_uri_path, deleted);
CREATE INDEX IF NOT EXISTS ix_mined_run_id on mined_chum_uri_table (event_type_name, miner_task_id, run_id, deleted);
CREATE INDEX IF NOT EXISTS ix_mined_uri on mined_chum_uri_table (event_type_name, miner_task_id, chum_uri, deleted);

INSERT INTO event_type_table (name)
VALUES
  ('MINER_EVENT_TYPE_NONE'),
  ('MINER_EVENT_TYPE_JAYWALKER'),
  ('MINER_EVENT_TYPE_FOLLOW_VEHICLE'),
  ('MINER_EVENT_TYPE_ALL_WAY_STOP'),
  ('MINER_EVENT_TYPE_CROSSWALK'),
  ('MINER_EVENT_TYPE_NARROW_HANDLING'),
  ('MINER_EVENT_TYPE_SURROGATE_UNSAFE')
ON CONFLICT DO NOTHING;

