BEGIN;

CREATE SEQUENCE LOCATION_SYSTEM_SEQ;

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


CREATE SEQUENCE ELEVATION_MODEL_SEQ;

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


-- lookup table for bedheight types
CREATE TABLE bed_height_type (
    id          NUMERIC(9,0) NOT NULL,
    name        VARCHAR(64) NOT NULL,
    PRIMARY KEY(id)
);
INSERT INTO bed_height_type VALUES (1, 'Querprofile');
INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen');
INSERT INTO bed_height_type VALUES (4, 'DGM');
INSERT INTO bed_height_type VALUES (5, 'TIN');
INSERT INTO bed_height_type VALUES (6, 'Modell');


CREATE SEQUENCE BED_HEIGHT_ID_SEQ;

CREATE TABLE bed_height (
    id                      NUMERIC(9,0) NOT NULL,
    river_id                NUMERIC(9,0) NOT NULL,
    year                    NUMERIC(9,0),
    type_id                 NUMERIC(9,0) NOT NULL,
    location_system_id      NUMERIC(9,0) NOT NULL,
    cur_elevation_model_id  NUMERIC(9,0) NOT NULL,
    old_elevation_model_id  NUMERIC(9,0),
    range_id                NUMERIC(9,0),
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_bh_river_id FOREIGN KEY (river_id)
        REFERENCES rivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_bh_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
    CONSTRAINT fk_bh_location_system FOREIGN KEY (location_system_id)
        REFERENCES location_system(id),
    CONSTRAINT fk_bh_cur_elevation_model FOREIGN KEY (cur_elevation_model_id)
        REFERENCES elevation_model(id),
    CONSTRAINT fk_bh_old_elevation_model FOREIGN KEY (old_elevation_model_id)
        REFERENCES elevation_model(id),
    CONSTRAINT fk_bh_range FOREIGN KEY (range_id)
        REFERENCES ranges(id) ON DELETE CASCADE
);


CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ;

--FIXME: make precision and scale of station column equal with the km columns of the other tables
--FIXME: replace double precision with exact types
CREATE TABLE bed_height_values (
    id                      NUMERIC(9,0) NOT NULL,
    bed_height_id    NUMERIC(9,0) NOT NULL,
    station                 DOUBLE PRECISION NOT NULL,
    height                  DOUBLE PRECISION,
    uncertainty             DOUBLE PRECISION,
    data_gap                DOUBLE PRECISION,
    sounding_width          DOUBLE PRECISION,
    PRIMARY KEY(id),
    UNIQUE (station, bed_height_id),
    CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id)
        REFERENCES bed_height(id) ON DELETE CASCADE
);


CREATE SEQUENCE DEPTHS_ID_SEQ;

CREATE TABLE depths (
    id      NUMERIC(9,0) NOT NULL,
    lower   NUMERIC(6,2) NOT NULL,
    upper   NUMERIC(6,2) NOT NULL,
    PRIMARY KEY(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;

CREATE TABLE sediment_density (
    id          NUMERIC(9,0) NOT NULL,
    river_id    NUMERIC(9,0) NOT NULL,
    depth_id    NUMERIC(9,0) NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;

--FIXME: make precision and scale of station column equal with the km columns of the other tables
CREATE TABLE sediment_density_values (
    id                  NUMERIC(9,0) NOT NULL,
    sediment_density_id NUMERIC(9,0) NOT NULL,
    station             NUMERIC(6,2) NOT NULL,
    shore_offset        NUMERIC(6,2),
    density             NUMERIC(8,2) NOT NULL,
    description         VARCHAR(256),
    year                NUMERIC(4,0),
    PRIMARY KEY(id),
    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
);


CREATE SEQUENCE POROSITY_ID_SEQ;

CREATE TABLE porosity (
    id               NUMERIC(9,0) NOT NULL,
    river_id         NUMERIC(9,0) NOT NULL,
    depth_id         NUMERIC(9,0) NOT NULL,
    description      VARCHAR(256),
    time_interval_id NUMERIC(9,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
    CONSTRAINT fk_p_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
);


CREATE SEQUENCE POROSITY_VALUES_ID_SEQ;

--FIXME: make precision and scale of station column equal with the km columns of the other tables
CREATE TABLE porosity_values (
    id                  NUMERIC(9,0) NOT NULL,
    porosity_id         NUMERIC(9,0) NOT NULL,
    station             DOUBLE PRECISION NOT NULL,
    shore_offset        DOUBLE PRECISION,
    porosity            DOUBLE PRECISION NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE
);


CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;

CREATE TABLE morphologic_width (
    id          NUMERIC(9,0) NOT NULL,
    river_id    NUMERIC(9,0) NOT NULL,
    unit_id     NUMERIC(9,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
);


CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;

CREATE TABLE morphologic_width_values (
    id                      NUMERIC(9,0) NOT NULL,
    morphologic_width_id    NUMERIC(9,0) NOT NULL,
    station                 NUMERIC(7,3) NOT NULL,
    width                   NUMERIC(7,3) NOT NULL,
    description             VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
);


CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;

CREATE TABLE discharge_zone (
    id                      NUMERIC(9,0) NOT NULL,
    river_id                NUMERIC(9,0) NOT NULL,
    gauge_name              VARCHAR(64)  NOT NULL, -- this is not very proper, but there are gauges with no db instance
    value                   NUMERIC(8,3) NOT NULL,
    lower_discharge         VARCHAR(64)  NOT NULL,
    upper_discharge         VARCHAR(64),
    PRIMARY KEY(id),
    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
);


CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;

CREATE TABLE flow_velocity_model (
    id                  NUMERIC(9,0) NOT NULL,
    discharge_zone_id   NUMERIC(9,0) NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
);


CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;

CREATE TABLE flow_velocity_model_values (
    id                      NUMERIC(9,0) NOT NULL,
    flow_velocity_model_id  NUMERIC(9,0) NOT NULL,
    station                 NUMERIC(7,3) NOT NULL,
    q                       NUMERIC(8,3) NOT NULL,
    total_channel           NUMERIC(5,3) NOT NULL,
    main_channel            NUMERIC(5,3) NOT NULL,
    shear_stress            NUMERIC(6,3) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (station, flow_velocity_model_id),
    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
);



CREATE SEQUENCE FV_MEASURE_ID_SEQ;

CREATE TABLE flow_velocity_measurements (
    id          NUMERIC(9,0) NOT NULL,
    river_id    NUMERIC(9,0) NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
);

CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;

CREATE TABLE flow_velocity_measure_values (
    id              NUMERIC(9,0) NOT NULL,
    measurements_id NUMERIC(9,0) NOT NULL,
    station         NUMERIC(7,3) NOT NULL,
    datetime        TIMESTAMP(0),
    w               NUMERIC(7,3) NOT NULL,
    q               NUMERIC(8,3) NOT NULL,
    v               NUMERIC(5,3) NOT NULL,
    description     VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
);


CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;

CREATE TABLE grain_fraction (
    id      NUMERIC(9,0)   NOT NULL,
    name    VARCHAR(64)    NOT NULL,
    lower   NUMERIC(6,3),
    upper   NUMERIC(6,3),
    PRIMARY KEY (id),
    UNIQUE(name, lower, upper)
);
-- single fractions
INSERT INTO grain_fraction VALUES (1, 'coarse', 16, 200);
INSERT INTO grain_fraction VALUES (2, 'fine_middle', 2, 16);
INSERT INTO grain_fraction VALUES (3, 'sand', 0.063, 2);
INSERT INTO grain_fraction VALUES (4, 'susp_sand', 0.063, 2);
INSERT INTO grain_fraction VALUES (5, 'susp_sand_bed', 0.063, 2);
INSERT INTO grain_fraction VALUES (6, 'suspended_sediment', 0, 0.063);
-- aggregations of fractions
INSERT INTO grain_fraction VALUES (7, 'total', 0, 200);
INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200);
INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2);


--lookup table for sediment load kinds
CREATE TABLE sediment_load_kinds (
    id 	     NUMERIC(9,0) PRIMARY KEY NOT NULL,
    kind     VARCHAR(64) NOT NULL
);
INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official');
INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official');

CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ;

CREATE TABLE sediment_load_ls (
    id                  NUMERIC(9,0) NOT NULL,
    river_id            NUMERIC(9,0) NOT NULL,
    grain_fraction_id   NUMERIC(9,0),
    unit_id             NUMERIC(9,0) NOT NULL,
    time_interval_id    NUMERIC(9,0) NOT NULL,
    sq_time_interval_id NUMERIC(9,0),
    description         VARCHAR(256),
    kind                NUMERIC(9,0),
    PRIMARY KEY (id),
    CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id)
        REFERENCES rivers(id) ON DELETE CASCADE,
    CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind)
        REFERENCES sediment_load_kinds(id),
    CONSTRAINT fk_slls_grain_fraction_id FOREIGN KEY (grain_fraction_id)
        REFERENCES grain_fraction(id),
    CONSTRAINT fk_slls_unit_id FOREIGN KEY (unit_id)
        REFERENCES units(id),
    CONSTRAINT fk_slls_time_interval_id FOREIGN KEY (time_interval_id)
        REFERENCES time_intervals(id),
    CONSTRAINT fk_slls_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
        REFERENCES time_intervals(id)
);


CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ;

CREATE TABLE sediment_load_ls_values (
    id                  NUMERIC(9,0) NOT NULL,
    sediment_load_ls_id   NUMERIC(9,0) NOT NULL,
    station             NUMERIC(7,3) NOT NULL,
    value               NUMERIC(10,3) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id)
        REFERENCES sediment_load_ls(id) ON DELETE CASCADE
);


CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
CREATE TABLE measurement_station (
    id                       NUMERIC(9,0)          NOT NULL,
    range_id                 NUMERIC(9,0)          NOT NULL,
    reference_gauge_id       NUMERIC(9,0),
    time_interval_id         NUMERIC(9,0),
    name                     VARCHAR(256) NOT NULL,
    measurement_type         VARCHAR(64)  NOT NULL,
    riverside                VARCHAR(16),
    -- store name of reference gauges here too, as not all are in gauges
    reference_gauge_name     VARCHAR(64),
    operator                 VARCHAR(64),
    commentary               VARCHAR(512),
    PRIMARY KEY (id),
    CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff')),
    CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id)
        REFERENCES ranges(id) ON DELETE CASCADE,
    CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id)
        REFERENCES gauges(id) ON DELETE CASCADE,
    CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id)
        REFERENCES time_intervals(id)
);


CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ;

CREATE TABLE sediment_load (
    id                    NUMERIC(9,0) NOT NULL,
    grain_fraction_id     NUMERIC(9,0) NOT NULL,
    time_interval_id      NUMERIC(9,0) NOT NULL,
    sq_time_interval_id   NUMERIC(9,0),
    description           VARCHAR(256),
    kind                  NUMERIC(9,0),
    PRIMARY KEY (id),
    CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind)
        REFERENCES sediment_load_kinds(id),
    CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id)
        REFERENCES grain_fraction(id),
    CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id)
        REFERENCES time_intervals(id),
    CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
        REFERENCES time_intervals(id)
);


CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ;

CREATE TABLE sediment_load_values (
    id                      NUMERIC(9,0) NOT NULL,
    sediment_load_id        NUMERIC(9,0) NOT NULL,
    measurement_station_id  NUMERIC(9,0) NOT NULL,
    value                   DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id)
        REFERENCES sediment_load(id) ON DELETE CASCADE,
    CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id)
        REFERENCES measurement_station(id) ON DELETE CASCADE
);


CREATE SEQUENCE SQ_RELATION_ID_SEQ;

CREATE TABLE sq_relation (
    id               NUMERIC(9,0) NOT NULL,
    time_interval_id NUMERIC(9,0) NOT NULL,
    description      VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id)
        REFERENCES time_intervals(id)
);


CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;

--FIXME: adjust precision and scale of the numerics
CREATE TABLE sq_relation_value (
    id                     NUMERIC(9,0) NOT NULL,
    sq_relation_id         NUMERIC(9,0) NOT NULL,
    measurement_station_id NUMERIC(9,0) NOT NULL,
    parameter              VARCHAR(1) NOT NULL,
    a                      NUMERIC(38,20) NOT NULL,
    b                      NUMERIC(38,20) NOT NULL,
    qmax                   NUMERIC(38,20) NOT NULL,
    rsq                    NUMERIC(38,3),
    ntot                   NUMERIC(9,0),
    noutl                  NUMERIC(9,0),
    cferguson              NUMERIC(38,20),
    cduan                  NUMERIC(38,20),
    PRIMARY KEY (id),
    UNIQUE(sq_relation_id, measurement_station_id, parameter),
    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id)
        REFERENCES sq_relation(id) ON DELETE CASCADE,
    CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id)
        REFERENCES measurement_station(id) ON DELETE CASCADE
);
COMMIT;