BEGIN;

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


-- TODO: TestMe.
-- Geodaesie/Flussachse+km/km.shp
CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
CREATE TABLE river_axes_km (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    km       NUMERIC NOT NULL
);
SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2);
ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');


--Geodaesie/Querprofile/QP-Spuren/qps.shp
CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
CREATE TABLE cross_section_tracks (
    id       int 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);
ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');


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


-- Geodaesie/Bauwerke/Wehre.shp
CREATE SEQUENCE BUILDINGS_ID_SEQ;
CREATE TABLE buildings (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    name     VARCHAR(256)
);
SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');


-- Geodaesie/Festpunkte/Festpunkte.shp
CREATE SEQUENCE FIXPOINTS_ID_SEQ;
CREATE TABLE fixpoints (
    id       int 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);
ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');


-- Hydrologie/Hydr. Grenzen/talaue.shp
CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
CREATE TABLE floodplain (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2);
ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');


-- Geodaesie/Hoehenmodelle/*
CREATE SEQUENCE DEM_ID_SEQ;
CREATE TABLE dem (
    id       int 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)
);
ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');


-- Hydrologie/Einzugsgebiete/EZG.shp
CREATE SEQUENCE CATCHMENT_ID_SEQ;
CREATE TABLE catchment (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    area NUMERIC,
    name VARCHAR(256)
);
SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');


--Hydrologie/HW-Schutzanlagen/hws.shp
CREATE SEQUENCE HWS_ID_SEQ;
CREATE TABLE hws (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    hws_facility VARCHAR(256),
    type VARCHAR(256)
);
SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');


--
--Hydrologie/UeSG
--
-- 'kind' can be one of:
-- 200 = Messung
-- 111 = Berechnung->Aktuell->BfG
-- 112 = Berechnung->Aktuell->Land
-- 121 = Berechnung->Potenziell->BfG
-- 122 = Berechnung->Potenziell->Land
--
CREATE SEQUENCE FLOODMAPS_SEQ;
CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$
    BEGIN
        NEW.id := nextval('floodmaps_seq');
        RETURN NEW;
    END;
$floodmaps_id_func$ LANGUAGE plpgsql;

CREATE TABLE floodmaps (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id),
    name       varchar(64) NOT NULL,
    kind       int NOT NULL,
    diff       real,
    count      int,
    area       real,
    perimeter  real
);
SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2);
ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ');

CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps
    FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func();
END;