-- DROP TABLE USK_DA_column;
-- DROP TABLE USK_DA_wst;
-- DROP TABLE USK_DA_owner;
-- DROP TABLE USK_DA_river;

CREATE TABLE USK_DA_river (
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) UNIQUE NOT NULL
);

CREATE TABLE USK_DA_owner (
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(200)
);

CREATE TABLE USK_DA_wst (
    id       INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    river_id INTEGER NOT NULL,
    owner_id INTEGER,
    filename VARCHAR(200),
    secret   VARCHAR(20),
    source   BLOB,

    FOREIGN KEY (river_id) REFERENCES USK_DA_river ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES USK_DA_owner ON DELETE CASCADE
);

CREATE TABLE USK_DA_column (
    id                INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    wst_id            INTEGER NOT NULL,
    column_num        INTEGER NOT NULL,
    description       VARCHAR(200),
    description_short VARCHAR(20),
    measured          DATE,

    FOREIGN KEY (wst_id) REFERENCES USK_DA_wst ON DELETE CASCADE
);
