BEGIN;

--FIXME: Adjust precision and scale to reasonable numbers
--FIXME: Make precision and scale equal for all km columns in the database

CREATE TABLE axis_kinds(
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64)
);
INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');

-- Geodaesie/Flussachse+km/achse
CREATE SEQUENCE RIVER_AXES_ID_SEQ;
CREATE TABLE river_axes (
    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    kind_id  NUMERIC(9,0) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 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       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    km       NUMERIC(7,3) NOT NULL,
    fedstate_km NUMERIC(7,3),
    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 TABLE cross_section_track_kinds(
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64)
);
INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');

CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
CREATE TABLE cross_section_tracks (
    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    kind_id  NUMERIC(9,0) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
    km       NUMERIC(16,12) NOT NULL,
    z        NUMERIC(16,12) 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');

CREATE TABLE building_kinds(
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64)
);
INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');

-- Geodaesie/Bauwerke
CREATE SEQUENCE BUILDINGS_ID_SEQ;
CREATE TABLE buildings (
    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    description VARCHAR(256), -- Name taken from attributes,
    name     VARCHAR(256), -- The layername
    km       NUMERIC(15,11),
    kind_id  NUMERIC(9,0) REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
    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       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    x        NUMERIC(15,11),
    y        NUMERIC(15,11),
    km       NUMERIC(15,11) NOT NULL,
    HPGP     VARCHAR(64),
    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 TABLE floodplain_kinds(
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64)
);
INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');

CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
CREATE TABLE floodplain (
    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    kind_id  NUMERIC(9,0) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
    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       NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    name             VARCHAR(64),
    range_id         NUMERIC(9,0) REFERENCES ranges(id) ON DELETE CASCADE,
    time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
    projection       VARCHAR(32),
    srid             NUMERIC(9,0) NOT NULL,
    elevation_state  VARCHAR(32),
    format           VARCHAR(32),
    border_break     NUMERIC(1,0) NOT NULL DEFAULT 0,
    resolution       VARCHAR(16),
    description      VARCHAR(256),
    path             VARCHAR(256) NOT NULL
);
ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');


-- Static lookup tables for Hochwasserschutzanlagen
CREATE TABLE hws_kinds (
    id NUMERIC(9,0) 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 NUMERIC(9,0) 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 NUMERIC(9,0) PRIMARY KEY NOT NULL,
    ogr_fid NUMERIC(9,0),
    kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2,
    fed_state_id NUMERIC(9,0) REFERENCES fed_states(id),
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    name VARCHAR(256),
    path VARCHAR(256),
    official NUMERIC(9,0) DEFAULT 0,
    agency VARCHAR(256),
    range VARCHAR(256),
    shore_side NUMERIC(9,0) DEFAULT 0,
    source VARCHAR(256),
    status_date TIMESTAMP(0),
    description VARCHAR(256)
);
SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3);
-- 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 NUMERIC(9,0) PRIMARY KEY NOT NULL,
    ogr_fid NUMERIC(9,0),
    kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2,
    fed_state_id NUMERIC(9,0) REFERENCES fed_states(id),
    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    name VARCHAR,
    path VARCHAR,
    official NUMERIC(9,0) DEFAULT 0,
    agency VARCHAR,
    range VARCHAR,
    shore_side NUMERIC(9,0) DEFAULT 0,
    source VARCHAR,
    status_date VARCHAR,
    description VARCHAR,
    freeboard NUMERIC(19,5),
    dike_km NUMERIC(19,5),
    z NUMERIC(19,5),
    z_target NUMERIC(19,5),
    rated_level NUMERIC(19,5)
);
SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);

ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');

--
--Hydrologie/UeSG
CREATE TABLE floodmap_kinds (
    id 	     NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name     varchar(64) NOT NULL
);
INSERT INTO floodmap_kinds VALUES (200, 'Messung');
INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');

CREATE SEQUENCE FLOODMAPS_ID_SEQ;
CREATE TABLE floodmaps (
    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    name       varchar(64) NOT NULL,
    kind       NUMERIC(9,0) NOT NULL REFERENCES floodmap_kinds(id),
    diff       NUMERIC(19,5),
    count      NUMERIC(9,0),
    area       NUMERIC(19,5),
    perimeter  NUMERIC(19,5),
    waterbody  varchar(64),
    path     VARCHAR(256),
    source   varchar(64)
);
SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');

CREATE TABLE sectie_kinds (
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64) NOT NULL
);
INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');

CREATE TABLE sobek_kinds (
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64) NOT NULL
);
INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');

CREATE TABLE boundary_kinds (
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64) NOT NULL
);
INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');

CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
CREATE TABLE hydr_boundaries (
    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    name       VARCHAR(255),
    kind       NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
    sectie     NUMERIC(9,0) REFERENCES sectie_kinds(id),
    sobek      NUMERIC(9,0) REFERENCES sobek_kinds(id),
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',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         NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    name       VARCHAR(255),
    kind       NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
    sectie     NUMERIC(9,0) REFERENCES sectie_kinds(id),
    sobek      NUMERIC(9,0) REFERENCES sobek_kinds(id),
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3);
ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');


CREATE TABLE jetty_kinds(
    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
    name VARCHAR(64)
);
INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');

CREATE SEQUENCE JETTIES_ID_SEQ;
CREATE TABLE jetties (
    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    path       VARCHAR(256),
    kind_id    NUMERIC(9,0) REFERENCES jetty_kinds(id),
    km         FLOAT8,
    z	       FLOAT8
);
SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2);
ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ');

CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
CREATE TABLE flood_marks (
    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
    path       VARCHAR(256),
    km         NUMERIC(7,3),
    z	         NUMERIC(16,12),
    location   VARCHAR(64),
    year       NUMERIC(4,0)
);
SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2);
ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');

COMMIT;