-- DROP TRIGGER TR_USK_DA_JOB_enqueue_time_BI;
-- DROP INDEX   USK_DA_JOBS_enqueue_IX;
-- DROP TABLE   USK_DA_JOBS;

CREATE TABLE USK_DA_JOBS (
    -- Spalten, um die Auftraege zu verwalten --------------------
    job_id       INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    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),

    -- weitere Constraints ---------------------------------------
    CONSTRAINT status_ck  CHECK (status IN (0, 1, 2, 3, 4, 5)),
    CONSTRAINT delta_ck   CHECK (delta_from < delta_to),
    CONSTRAINT strecke_ck CHECK (range_from < range_to),
    CONSTRAINT gel_ck     CHECK (gel IN (0, 1, 2))
);

CREATE INDEX USK_DA_JOBS_enqueue_IX ON USK_DA_JOBS (enqueue_time);

CREATE TRIGGER TR_USK_DA_JOB_enqueue_time_BI AFTER INSERT ON USK_DA_JOBS
    REFERENCING NEW AS NROW
    FOR EACH ROW MODE DB2SQL
    UPDATE USK_DA_JOBS SET enqueue_time = CURRENT_TIMESTAMP WHERE NROW.job_id = job_id;


