DROP TABLE USK_DA_wst_column;
DROP TABLE USK_DA_wst;
DROP TABLE USK_DA_river;
DROP TABLE USK_DA_owner;
DROP SEQUENCE SEQ_USK_DA_owner_id;
DROP SEQUENCE SEQ_USK_DA_river_id;
DROP SEQUENCE SEQ_USK_DA_wst_id;
DROP SEQUENCE SEQ_USK_DA_wst_column_id;

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

CREATE TABLE USK_DA_river (
    id   INTEGER NOT NULL,
    name VARCHAR(200) NOT NULL
)
/

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

CREATE OR REPLACE TRIGGER TR_USK_DA_river_id_BIR
BEFORE INSERT
ON USK_DA_river
FOR EACH ROW
BEGIN
  select SEQ_USK_DA_river_id.nextval into :new.id from DUAL;
END;
/

-- PRIMARY KEY
ALTER TABLE USK_DA_river ADD
(
  CONSTRAINT PK_USK_DA_river_id
  PRIMARY KEY
  (
   id
  )
)
/

ALTER TABLE USK_DA_river ADD
(
  CONSTRAINT USK_DA_river_name_UK
  UNIQUE
  (
    name
  )
)
/
------------------------------------------------------------------------------

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

CREATE TABLE USK_DA_owner (
    id   INTEGER NOT NULL,
    name VARCHAR(200)
);
/

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

CREATE OR REPLACE TRIGGER TR_USK_DA_owner_id_BIR
BEFORE INSERT
ON USK_DA_owner
FOR EACH ROW
BEGIN
  select SEQ_USK_DA_owner_id.nextval into :new.id from DUAL;
END;
/

-- PRIMARY KEY
ALTER TABLE USK_DA_owner ADD
(
  CONSTRAINT PK_USK_DA_owner_id
  PRIMARY KEY
  (
   id
  )
)
/

------------------------------------------------------------------------------

--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_rivers(id) ON DELETE CASCADE,
--    FOREIGN KEY (owner_id) REFERENCES USK_DA_owners(id) ON DELETE CASCADE
--);

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

CREATE TABLE USK_DA_wst (
    id       INTEGER NOT NULL,
    river_id INTEGER NOT NULL,
    owner_id INTEGER,
    filename VARCHAR(200),
    secret   VARCHAR(20),
    source   BLOB
);
/

CREATE OR REPLACE TRIGGER TR_USK_DA_wst_id_BIR
BEFORE INSERT
ON USK_DA_wst
FOR EACH ROW
BEGIN
  select SEQ_USK_DA_wst_id.nextval into :new.id from DUAL;
END;
/

-- PRIMARY KEY
ALTER TABLE USK_DA_wst ADD
(
  CONSTRAINT PK_USK_DA_wst_id
  PRIMARY KEY
  (
   id
  )
)
/

ALTER TABLE USK_DA_wst ADD
(
  CONSTRAINT USK_DA_wst_river_id_FK
  FOREIGN KEY
  (
    river_id
  )
  REFERENCES USK_DA_river
  (
    id
  )
  ON DELETE CASCADE
)
/

ALTER TABLE USK_DA_wst ADD
(
  CONSTRAINT USK_DA_wst_owner_id_FK
  FOREIGN KEY
  (
    owner_id
  )
  REFERENCES USK_DA_owner
  (
    id
  )
  ON DELETE CASCADE
)
/

------------------------------------------------------------------------------

--CREATE TABLE wst_columns (
--    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(id) ON DELETE CASCADE
--);

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

CREATE TABLE USK_DA_wst_column (
    id                INTEGER NOT NULL,
    wst_id            INTEGER NOT NULL,
    column_num        INTEGER NOT NULL,
    description       VARCHAR(200),
    description_short VARCHAR(20),
    measured          DATE
);
/

CREATE OR REPLACE TRIGGER TR_USK_DA_wst_column_id_BIR
BEFORE INSERT
ON USK_DA_wst_column
FOR EACH ROW
BEGIN
  select SEQ_USK_DA_wst_column_id.nextval into :new.id from DUAL;
END;
/

-- PRIMARY KEY
ALTER TABLE USK_DA_wst_column ADD
(
  CONSTRAINT PK_USK_DA_wst_column_id
  PRIMARY KEY
  (
   id
  )
)
/

ALTER TABLE USK_DA_wst_column ADD
(
  CONSTRAINT USK_DA_wst_column_wst_id_FK
  FOREIGN KEY
  (
    wst_id
  )
  REFERENCES USK_DA_wst
  (
    id
  )
  ON DELETE CASCADE
)
/
