BEGIN;

-- Gewaesser
CREATE SEQUENCE RIVERS_ID_SEQ;

CREATE TABLE rivers (
    id    int PRIMARY KEY NOT NULL,
    name  VARCHAR(256)    NOT NULL UNIQUE,
    km_up BOOLEAN         NOT NULL DEFAULT true
);

-- Bruecke, Haefen, etc.
CREATE SEQUENCE ATTRIBUTES_ID_SEQ;

CREATE TABLE attributes (
    id    int PRIMARY KEY NOT NULL,
    value VARCHAR(256)    NOT NULL UNIQUE
);

-- segments from/to at a river
CREATE SEQUENCE RANGES_ID_SEQ;

CREATE TABLE ranges (
    id       int PRIMARY KEY NOT NULL,
    river_id int             NOT NULL REFERENCES rivers(id),
    a        NUMERIC         NOT NULL,
    b        NUMERIC,
    UNIQUE (river_id, a, b)
);

-- Lage 'links', 'rechts', etc.
CREATE SEQUENCE POSITIONS_ID_SEQ;

CREATE TABLE positions (
    id    int PRIMARY KEY NOT NULL,
    value VARCHAR(256)    NOT NULL UNIQUE
);

-- Some object (eg. Hafen) at a segment of river
-- plus its position.
CREATE SEQUENCE ANNOTATIONS_ID_SEQ;

CREATE TABLE annotations (
    id           int PRIMARY KEY NOT NULL,
    range_id     int             NOT NULL REFERENCES ranges(id),
    attribute_id int             NOT NULL REFERENCES attributes(id),
    position_id  int REFERENCES positions(id)
);

-- Pegel
CREATE SEQUENCE GAUGES_ID_SEQ;

CREATE TABLE gauges (
    id       int PRIMARY KEY NOT NULL,
    name     VARCHAR(256)    NOT NULL,
    river_id int             NOT NULL REFERENCES rivers(id),
    station  NUMERIC         NOT NULL UNIQUE,
    aeo      NUMERIC         NOT NULL,

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

    UNIQUE (name, river_id),
    UNIQUE (river_id, station)
);

-- Type of a Hauptwert 'W', 'Q', 'D', etc.
CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;

CREATE TABLE main_value_types (
    id   int PRIMARY KEY NOT NULL,
    name VARCHAR(256)    NOT NULL UNIQUE
);

--  Named type of a Hauptwert (eg. HQ100)
CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;

CREATE TABLE named_main_values (
    id      int PRIMARY KEY NOT NULL,
    name    VARCHAR(256)    NOT NULL UNIQUE,
    type_id int NOT NULL REFERENCES main_value_types(id),
    UNIQUE (name, type_id)
);

-- Table for time intervals
CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;

CREATE TABLE time_intervals (
    id         int PRIMARY KEY NOT NULL,
    start_time TIMESTAMP       NOT NULL,
    stop_time  TIMESTAMP,
    CHECK (start_time <= stop_time)
);

-- Stammdaten
CREATE SEQUENCE MAIN_VALUES_ID_SEQ;

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

    time_interval_id int REFERENCES time_intervals(id),

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

-- Abflusstafeln
CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;

CREATE TABLE discharge_tables (
    id               int PRIMARY KEY NOT NULL,
    gauge_id         int NOT NULL REFERENCES gauges(id),
    description      VARCHAR(256) NOT NULL,
    kind             int NOT NULL DEFAULT 0,
    time_interval_id int REFERENCES time_intervals(id)

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

-- Values of the Abflusstafeln
CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;

CREATE TABLE discharge_table_values (
    id       int PRIMARY KEY NOT NULL,
    table_id int NOT NULL REFERENCES discharge_tables(id),
    q        NUMERIC NOT NULL,
    w        NUMERIC NOT NULL,

    UNIQUE (table_id, q, w)
);

-- WST files
CREATE SEQUENCE WSTS_ID_SEQ;

CREATE TABLE wsts (
    id          int PRIMARY KEY NOT NULL,
    river_id    int NOT NULL REFERENCES rivers(id),
    description VARCHAR(256) NOT NULL,
    kind        int NOT NULL DEFAULT 0,
    -- TODO: more meta infos
    UNIQUE (river_id, description)
);

-- columns of WST files
CREATE SEQUENCE WST_COLUMNS_ID_SEQ;

CREATE TABLE wst_columns (
    id          int PRIMARY KEY NOT NULL,
    wst_id      int NOT NULL REFERENCES wsts(id),
    name        VARCHAR(256) NOT NULL,
    description VARCHAR(256),
    position    int NOT NULL DEFAULT 0,

    time_interval_id int REFERENCES time_intervals(id),

    UNIQUE (wst_id, name),
    UNIQUE (wst_id, position)
);

-- w values in  WST file column
CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;

CREATE TABLE wst_column_values (
    id            int PRIMARY KEY NOT NULL,
    wst_column_id int NOT NULL REFERENCES wst_columns(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 SEQUENCE WST_Q_RANGES_ID_SEQ;

CREATE TABLE wst_q_ranges (
    id       int PRIMARY KEY NOT NULL,
    range_id int NOT NULL REFERENCES ranges(id),
    q        NUMERIC NOT NULL
);

-- bind q ranges to wst columns
CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;

CREATE TABLE wst_column_q_ranges (
    id             int PRIMARY KEY NOT NULL,
    wst_column_id  int NOT NULL REFERENCES wst_columns(id),
    wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),

    UNIQUE (wst_column_id, wst_q_range_id)
);

CREATE VIEW wst_value_table AS
    SELECT wcv.position AS position,
           w,
           (SELECT q
            FROM   wst_column_q_ranges wcqr
                   JOIN wst_q_ranges wqr
                     ON wcqr.wst_q_range_id = wqr.id
                   JOIN ranges r
                     ON r.id = wqr.range_id
            WHERE  wcqr.wst_column_id = wc.id
                   AND wcv.position BETWEEN r.a AND r.b) AS q,
           wc.position                                   AS column_pos,
           w.id                                          AS wst_id
    FROM   wst_column_values wcv
           JOIN wst_columns wc
             ON wcv.wst_column_id = wc.id
           JOIN wsts w
             ON wc.wst_id = w.id
    ORDER  BY wcv.position ASC,
          wc.position DESC;

-- view to select the w values of a WST
CREATE VIEW wst_w_values AS
    SELECT wcv."position" AS km, 
           wcv.w          AS w,  
           wc."position"  AS column_pos, 
           w.id           AS wst_id
        FROM wst_column_values wcv
        JOIN wst_columns wc ON wcv.wst_column_id = wc.id
        JOIN wsts w         ON wc.wst_id = w.id
    ORDER BY wcv."position", wc."position";

COMMIT;