SET AUTOCOMMIT ON;

CREATE SEQUENCE LOCATION_SYSTEM_SEQ;

CREATE TABLE location_system (
    id          NUMBER(38,0) NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);


CREATE SEQUENCE ELEVATION_MODEL_SEQ;

CREATE TABLE elevation_model (
    id          NUMBER(38,0) NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    unit_id     NUMBER(38,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
);

CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;

CREATE TABLE bed_height_type (
    id          NUMBER(38,0) NOT NULL,
    name        VARCHAR(16)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);



CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;

CREATE TABLE bed_height_single (
    id                      NUMBER(38,0) NOT NULL,
    river_id                NUMBER(38,0) NOT NULL,
    year                    NUMBER(38,0) NOT NULL,
    sounding_width          NUMBER(38,0) NOT NULL,
    type_id                 NUMBER(38,0) NOT NULL,
    location_system_id      NUMBER(38,0) NOT NULL,
    cur_elevation_model_id  NUMBER(38,0) NOT NULL,
    old_elevation_model_id  NUMBER(38,0),
    range_id                NUMBER(38,0) NOT NULL,
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
    CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
    CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
);


CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;

CREATE TABLE bed_height_epoch (
    id                      NUMBER(38,0) NOT NULL,
    river_id                NUMBER(38,0) NOT NULL,
    time_interval_id        NUMBER(38,0) NOT NULL,
    -- sounding_with           NUMBER(38,0) NOT NULL,
    -- type_id                 NUMBER(38,0) NOT NULL,
    cur_elevation_model_id  NUMBER(38,0) NOT NULL,
    old_elevation_model_id  NUMBER(38,0),
    range_id                NUMBER(38,0) NOT NULL,
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
    CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
);


CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;

CREATE TABLE bed_height_single_values (
    id                      NUMBER(38,0) NOT NULL,
    bed_height_single_id    NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,2) NOT NULL,
    height                  NUMBER(38,2),
    uncertainty             NUMBER(38,2),
    data_gap                NUMBER(38,2) NOT NULL,
    sounding_width          NUMBER(38,2) NOT NULL,
    width                   NUMBER(38,2) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
);


CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;

CREATE TABLE bed_height_epoch_values (
    id                      NUMBER(38,0) NOT NULL,
    bed_height_epoch_id     NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,2) NOT NULL,
    height                  NUMBER(38,2),
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
);


CREATE SEQUENCE DEPTHS_ID_SEQ;

CREATE TABLE depths (
    id      NUMBER(38,0) NOT NULL,
    lower   NUMBER(38,2) NOT NULL,
    upper   NUMBER(38,2) NOT NULL,
    unit_id NUMBER(38,2) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;

CREATE TABLE sediment_density (
    id          NUMBER(38,0) NOT NULL,
    river_id    NUMBER(38,0) NOT NULL,
    depth_id    NUMBER(38,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;

CREATE TABLE sediment_density_values (
    id                  NUMBER(38,0) NOT NULL,
    sediment_density_id NUMBER(38,0) NOT NULL,
    station_id          NUMBER(38,0) NOT NULL,
    density             NUMBER(38,2) NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id),
    CONSTRAINT fk_sdv_station_id FOREIGN KEY (station_id) REFERENCES ranges(id)
);