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) ON DELETE CASCADE,
    kind     int             NOT NULL DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('river_axes', 'geom', 31467, '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) ON DELETE CASCADE,
    km       FLOAT8 NOT NULL,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, '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) ON DELETE CASCADE,
    km       FLOAT8 NOT NULL,
    z        FLOAT8 NOT NULL DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, '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) ON DELETE CASCADE,
    kind     VARCHAR(16) NOT NULL,
    z        FLOAT8 DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
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) ON DELETE CASCADE,
    name     VARCHAR(256),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('buildings', 'geom', 31467, '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) ON DELETE CASCADE,
    x        FLOAT8,
    y        FLOAT8,
    km       FLOAT8 NOT NULL,
    HPGP     VARCHAR(2),
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('fixpoints', 'geom', 31467, '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) ON DELETE CASCADE,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('floodplain', 'geom', 31467, '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) ON DELETE CASCADE,
    -- XXX Should we use the ranges table instead?
    name            VARCHAR(64),
    lower           FLOAT8,
    upper           FLOAT8,
    year_from       VARCHAR(32) NOT NULL,
    year_to         VARCHAR(32) NOT NULL,
    projection      VARCHAR(32) NOT NULL,
    srid	    int NOT NULL,
    elevation_state VARCHAR(32),
    format          VARCHAR(32),
    border_break    BOOLEAN NOT NULL DEFAULT FALSE,
    resolution      VARCHAR(16),
    description     VARCHAR(256),
    path            VARCHAR(256)
);
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) ON DELETE CASCADE,
    area FLOAT8,
    name VARCHAR(256),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');

-- Static lookup tables for Hochwasserschutzanlagen
CREATE TABLE hws_kinds (
    id int PRIMARY KEY NOT NULL,
    kind VARCHAR(64) NOT NULL
);
INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');

CREATE TABLE fed_states (
    id int PRIMARY KEY NOT NULL,
    name VARCHAR(23) NOT NULL
);
INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');

--Hydrologie/HW-Schutzanlagen/*Linien.shp
CREATE SEQUENCE HWS_LINES_ID_SEQ;
CREATE TABLE hws_lines (
    id int PRIMARY KEY NOT NULL,
    ogr_fid int,
    kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
    fed_state_id int REFERENCES fed_states(id),
    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
    name VARCHAR(256),
    path VARCHAR(256),
    offical INT DEFAULT 0,
    agency VARCHAR(256),
    range VARCHAR(256),
    shore_side INT DEFAULT 0,
    source VARCHAR(256),
    status_date TIMESTAMP,
    description VARCHAR(256)
);
SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2);
SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ?
SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ?
-- TODO: dike_km_from dike_km_to, are they geometries?

ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');

--Hydrologie/HW-Schutzanlagen/*Punkte.shp
CREATE SEQUENCE HWS_POINTS_ID_SEQ;
CREATE TABLE hws_points (
    id int PRIMARY KEY NOT NULL,
    ogr_fid int,
    kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
    fed_state_id int REFERENCES fed_states(id),
    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
    name VARCHAR,
    path VARCHAR,
    offical INT DEFAULT 0,
    agency VARCHAR,
    range VARCHAR,
    shore_side INT DEFAULT 0,
    source VARCHAR,
    status_date VARCHAR,
    description VARCHAR,
    freeboard FLOAT8,
    dike_km FLOAT8,
    z FLOAT8,
    z_target FLOAT8,
    rated_level FLOAT8
);
SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);

ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_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_ID_SEQ;
CREATE TABLE floodmaps (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
    name       varchar(64) NOT NULL,
    kind       int NOT NULL,
    diff       FLOAT8,
    count      int,
    area       FLOAT8,
    perimeter  FLOAT8,
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('floodmaps', 'geom', 31467, '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_ID_SEQ');

CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
CREATE TABLE hydr_boundaries (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
    name       VARCHAR(255),
    kind       int, -- 1 BfG / 2 Land / 3 Sonstige
    sectie     int, -- 1 stromführend / 2 stromspeichernd
    sobek      int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');


CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
CREATE TABLE hydr_boundaries_poly (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
    name       VARCHAR(255),
    kind       int, -- 1 BfG / 2 Land / 3 Sonstige
    sectie     int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
    sobek      int, -- 1 stromführend / 2 stromspeichernd
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');


CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
CREATE TABLE gauge_location (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
    name       VARCHAR(255),
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');

COMMIT;