DROP TRIGGER USK_DA_JOB_enqueue_time_TR;
DROP INDEX   USK_DA_JOB_enqueue_IX;
DROP SEQUENCE SEQ_USK_DA_JOB_job_id;
DROP TABLE   USK_DA_JOB;

CREATE TABLE USK_DA_JOB (
    -- Spalten, um die Auftraege zu verwalten --------------------
    job_id       INTEGER NOT NULL,
    user_id      VARCHAR(256) NOT NULL,
    status       INTEGER NOT NULL,
    enqueue_time TIMESTAMP,
    stop_time    TIMESTAMP,
    user_msg     VARCHAR(1024),
    result_msg   VARCHAR(2048),
    email        VARCHAR(256),

    -- Aus dem Gewaessernamen werden weitere Parameter
    -- beim Starten des Auftrags hergeleitet.
    river       VARCHAR(256) NOT NULL,

    -- Datenspalten fuer WSPLGEN ---------------------------------
    -- Delta (optional)
    delta_from   DOUBLE PRECISION,
    delta_to     DOUBLE PRECISION,
    delta_diff   DOUBLE PRECISION,

    -- Sperre SPERRE|NOSPERRE (optional)
    gel          INTEGER,

    -- Strecke (optional)
    range_from  DOUBLE PRECISION,
    range_to    DOUBLE PRECISION,

    -- WSP (ggf. erforderlich)
    wsp          VARCHAR(1024),

    -- WSPTAG (ggf. erforderlich)
    wsptag       VARCHAR(256),

    -- LIN (optional)
    lin          VARCHAR(1024)
);
/

CREATE SEQUENCE SEQ_USK_DA_JOB_job_id
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 1E27
  MINVALUE 1
  NOCACHE
  NOCYCLE
  ORDER
/

CREATE OR REPLACE TRIGGER TR_USK_DA_JOB_job_id_BIR
BEFORE INSERT
ON USK_DA_JOB
FOR EACH ROW
BEGIN
  select SEQ_USK_DA_JOB_job_id.nextval into :new.job_id from DUAL;
END;
/

-- PRIMARY KEY
ALTER TABLE USK_DA_JOB ADD
(
  CONSTRAINT PK_USK_DA_JOB_job_id
  PRIMARY KEY
  (
   job_id
  )
)
/

-- Constraints ---------------------------------------

ALTER TABLE USK_DA_JOB ADD
(
  CONSTRAINT USK_DA_JOB_status_CHK
  CHECK
  (
    status IN (0, 1, 2, 3, 4, 5)
  )
)
/

ALTER TABLE USK_DA_JOB ADD
(
  CONSTRAINT USK_DA_JOB_delta_CHK
  CHECK
  (
    delta_from < delta_to
  )
)
/

ALTER TABLE USK_DA_JOB ADD
(
  CONSTRAINT USK_DA_JOB_strecke_CHK
  CHECK
  (
    range_from < range_to
  )
)
/

ALTER TABLE USK_DA_JOB ADD
(
  CONSTRAINT USK_DA_JOB_gel_CHK
  CHECK
  (
    gel IN (0, 1, 2, 3)
  )
)
/

CREATE INDEX USK_DA_JOBS_enqueue_IX ON USK_DA_JOB (enqueue_time);

/*
CREATE OR REPLACE TRIGGER TR_USK_DA_JOB_enqueue_time_AI
AFTER INSERT ON USK_DA_JOB
REFERENCING NEW AS NROW
 FOR EACH ROW MODE DB2SQL
 UPDATE USK_DA_JOB
 SET enqueue_time = CURRENT_TIMESTAMP
 WHERE NROW.job_id = job_id;
*/

CREATE OR REPLACE TRIGGER TR_USK_DA_JOB_enqueue_time_BI
BEFORE INSERT
ON USK_DA_JOB
FOR EACH ROW
BEGIN
 :new.enqueue_time := CURRENT_TIMESTAMP;
END;


