BEGIN;

-- Geodaesie/Flussachse+km/achse
CREATE TABLE river_axes (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    kind     int             NOT NULL DEFAULT 0
);
SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);


-- Geodaesie/Querprofile/*
CREATE TABLE cross_section_tracks (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    km       NUMERIC NOT NULL,
    z        NUMERIC NOT NULL DEFAULT 0
);
SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);


-- Geodaesie/Linien/rohre-und-spreen
CREATE TABLE lines (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    kind     int             NOT NULL DEFAULT 0,
    z        NUMERIC DEFAULT 0
);
SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
-- 'kind':
-- 0: ROHR1
-- 1: DAMM


-- Geodaesie/Bauwerke/Wehre.shp
CREATE TABLE buildings (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    name     VARCHAR(50)
);
SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);


-- Geodaesie/Festpunkte/Festpunkte.shp
CREATE TABLE fixpoints (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    x        int,
    y        int,
    km       NUMERIC NOT NULL,
    HPGP     VARCHAR(2)
);
SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);


-- Hydrologie/Hydr. Grenzen/talaue.shp
CREATE TABLE floodplain (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);


-- Geodaesie/Hoehenmodelle/*
-- XXX DGM in englisch DEM, um dies zu vereinheitlichen
CREATE TABLE dgm (
    id       SERIAL PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    -- XXX Should we use the ranges table instead?
    lower    NUMERIC,
    upper    NUMERIC,
    path     VARCHAR(256),
    UNIQUE (river_id, lower, upper)
);


-- TODO
-- Flussachse+km/km

-- TODO
-- Hydrologie/Einzugsgebiete

-- TODO
-- Hydrologie/HW-Schutzanlagen

-- TODO
-- Hydrologie/Hydr. Grenzen/Linien

-- TODO
-- Hydrologie/Streckendaten

-- TODO
-- Hydrologie/UeSG/Berechnung

-- TODO
-- Hydrologie/UeSG/Messung

END;