BEGIN TRANSACTION;

-- Gewaesser
CREATE TABLE rivers (
    id   INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(256)        NOT NULL UNIQUE
);

-- Bruecke, Haefen, etc.
CREATE TABLE attributes (
    id    INTEGER PRIMARY KEY NOT NULL,
    value VARCHAR(256)        NOT NULL UNIQUE
);

-- segments from/to at a river
CREATE TABLE ranges (
    id       INTEGER PRIMARY KEY NOT NULL,
    river_id INTEGER NOT NULL REFERENCES river(id),
    a        NUMERIC NOT NULL,
    b        NUMERIC,
    UNIQUE (river_id, a, b)
);

-- Lage 'links', 'rechts', etc.
CREATE TABLE positions (
    id    INTEGER PRIMARY KEY NOT NULL,
    value VARCHAR(256)        NOT NULL UNIQUE
);

-- Some object (eg. Hafen) at a segment of river
-- plus its position.
CREATE TABLE annotations (
    id           INTEGER PRIMARY KEY NOT NULL,
    range_id     INTEGER NOT NULL REFERENCES ranges(id),
    attribute_id INTEGER NOT NULL REFERENCES attributes(id),
    position_id  INTEGER REFERENCES positions(id)
);

-- Pegel
CREATE TABLE gauges (
    id           INTEGER PRIMARY KEY NOT NULL,
    name         VARCHAR(256)        NOT NULL,
    river_id     INTEGER             NOT NULL REFERENCES river(id),
    station      NUMERIC             NOT NULL UNIQUE,
    aeo          NUMERIC             NOT NULL,

    -- Pegelnullpunkt
    datum        NUMERIC NOT NULL,
    -- Streckengueltigkeit
    range_id     INTEGER NOT NULL REFERENCES ranges (id),

    UNIQUE (name, river_id)
);

-- Type of a Hauptwert 'W', 'Q', 'D', etc.
CREATE TABLE main_value_types (
    id           INTEGER PRIMARY KEY NOT NULL,
    name         VARCHAR(256)        NOT NULL UNIQUE
);

--  Named type of a Hauptwert (eg. HQ100)
CREATE TABLE named_main_values (
    id           INTEGER PRIMARY KEY NOT NULL,
    name         VARCHAR(256)        NOT NULL UNIQUE,
    type_id      INTEGER NOT NULL REFERENCES main_value_types(id),
    UNIQUE (name, type_id)
);

-- Table for time intervals
CREATE TABLE time_intervals (
    id         INTEGER PRIMARY KEY NOT NULL,
    start_time TIMESTAMP NOT NULL,
    stop_time  TIMESTAMP,
    CHECK (start_time <= stop_time)
);

-- Stammdaten
CREATE TABLE main_values (
    id               INTEGER PRIMARY KEY NOT NULL,
    gauge_id         INTEGER NOT NULL REFERENCES gauges(id),
    named_value_id   INTEGER NOT NULL REFERENCES named_main_values(id),
    value            NUMERIC NOT NULL,

    time_interval_id INTEGER REFERENCES time_intervals(id),

    -- TODO: better checks
    UNIQUE (gauge_id, named_value_id, time_interval_id)
);

-- Abflusstafeln
CREATE TABLE discharge_table (
    id               INTEGER PRIMARY KEY NOT NULL,
    gauge_id         INTEGER NOT NULL REFERENCES gauges(id),

    time_interval_id INTEGER REFERENCES time_intervals(id),

    -- TODO: better checks
    UNIQUE (gauge_id, time_interval_id)
);

-- Values of the Abflusstafeln
CREATE TABLE discharge_table_values (
    id             INTEGER PRIMARY KEY NOT NULL,
    table_id       INTEGER NOT NULL REFERENCES discharge_table(id),
    q              NUMERIC NOT NULL,
    w              NUMERIC NOT NULL,

    UNIQUE (table_id, q, w)
);

-- WST files
CREATE TABLE wst (
    id             INTEGER PRIMARY KEY NOT NULL,
    river_id       INTEGER NOT NULL REFERENCES river(id),
    description    VARCHAR(256) NOT NULL,
    -- TODO: more meta infos
    UNIQUE (river_id, description)
);

-- columns of WST files
CREATE TABLE wst_column (
    id             INTEGER PRIMARY KEY NOT NULL,
    wst_id         INTEGER NOT NULL REFERENCES wst(id),
    name           VARCHAR(256) NOT NULL,
    description    VARCHAR,

    time_interval_id INTEGER REFERENCES time_intervals(id),

    UNIQUE (wst_id, name)
);

-- w values in  WST file column
CREATE TABLE wst_column_values (
    id             INTEGER PRIMARY KEY NOT NULL,
    wst_column_id  INTEGER NOT NULL REFERENCES wst_column(id),
    position       NUMERIC NOT NULL,
    w              NUMERIC NOT NULL,

    UNIQUE (position, wst_column_id),
    UNIQUE (position, wst_column_id, w)
);

-- bind q values to range
CREATE TABLE wst_q_ranges (
    id             INTEGER PRIMARY KEY NOT NULL,
    range_id       INTEGER NOT NULL REFERENCES ranges(id),
    q              NUMERIC NOT NULL
);

-- bind q ranges to wst columns
CREATE TABLE wst_column_q_ranges (
    id             INTEGER PRIMARY KEY NOT NULL,
    wst_column_id  INTEGER NOT NULL REFERENCES wst_column(id),
    wst_q_range_id INTEGER NOT NULL REFERENCES wst_q_ranges(id),

    UNIQUE (wst_column_id, wst_q_range_id)
);

END TRANSACTION;