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');


-- Geodaesie/Querprofile/*
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     int             NOT NULL DEFAULT 0,
    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(50)
);
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, 'MULTIPOLYGON', 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
   -- Hinweise zu ezg_saar.shp wird nicht importiert:
   -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt?
   -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen
   -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen

CREATE SEQUENCE CATCHMENT_ID_SEQ;
CREATE TABLE catchment (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    "area" numeric,
    "name" VARCHAR(80)
);
SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');

-- Hydrologie/HW-Schutzanlagen
-- Wird nicht benoetigt, stattdessen verwenden wir
-- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp
    -- hws.shp beinhaltet die Geometrien von:
        -- HWS-Lisdorf.shp -- hws_anlage
        -- HWS-Mettlach.shp -- maßnahme -> hws_anlage
        -- HWS-Rehlingen.shp -- hw -> hws_anlage
        -- HWS_Saarburg.shp -- höhe? bauart?
        -- HWS-Schoden-Rhl-Pf.shp  -- hws_anlage
        -- HWS_Schoden.shp --höhe? bauart?
        -- HWS-Serrig.shp --hws_anlage
-- CREATE SEQUENCE HWS_EZG_ID_SEQ;
-- CREATE TABLE hws (
--     id int PRIMARY KEY NOT NULL,
--     oid int,
--     river_id int REFERENCES rivers(id),
--     hws_facility VARCHAR(40),
--     typ VARCHAR(254)
-- );
-- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
-- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');

-- Hydrologie/Hydr. Grenzen/Linien
   -- BfG/boeschung_*.shp 
CREATE SEQUENCE BANKS_ID_SEQ;
CREATE TABLE banks (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('banks','geom',31466,'MULTILINESTRING',2);
ALTER TABLE banks ALTER COLUMN id SET DEFAULT NEXTVAL('BANKS_ID_SEQ');

   -- BfG/hauptoeff_*.shp
CREATE SEQUENCE MAINSPANS_ID_SEQ;
CREATE TABLE mainspans(
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('mainspans','geom',31466,'MULTILINESTRING',2);
ALTER TABLE mainspans ALTER COLUMN id SET DEFAULT NEXTVAL('MAINSPANS_ID_SEQ');

   -- BfG/MNQ-*.shp
CREATE SEQUENCE MNQ_ID_SEQ;
CREATE TABLE mnq (gid serial PRIMARY KEY,
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    haltung varchar(16)
);
SELECT AddGeometryColumn('mnq', 'the_geom',31466,'MULTIPOLYGON',2);
ALTER TABLE mnq ALTER COLUMN id SET DEFAULT NEXTVAL('MNQ_ID_SEQ');

   -- BfG/modellgrenze*.shp
CREATE SEQUENCE MODELBOUNDARY_ID_SEQ;
CREATE TABLE modelboundary (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('modelboundary','geom',31466,'MULTILINESTRING',2);
ALTER TABLE modelboundary ALTER COLUMN id SET DEFAULT NEXTVAL('MODELBOUNDARY_ID_SEQ');

   -- TODO: Klaeren ob benoetigt, da einzel Geometrien in Tabelle vorland.
   -- BfG/saar-sld-vorland.shp

   -- BfG/uferlinie.shp
CREATE SEQUENCE SHORELINE_ID_SEQ;
CREATE TABLE shoreline(
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('shoreline','geom',31466,'MULTILINESTRING',2);
ALTER TABLE shoreline ALTER COLUMN id SET DEFAULT NEXTVAL('SHORELINE_ID_SEQ');

   -- BfG/vorland_*.shp
CREATE SEQUENCE FORELAND_ID_SEQ;
CREATE TABLE foreland(
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id)
);
SELECT AddGeometryColumn('foreland','geom',31466,'MULTILINESTRING',2);
ALTER TABLE foreland ALTER COLUMN id SET DEFAULT NEXTVAL('FORELANDS_ID_SEQ');


-- Hydrologie/Streckendaten
    -- pegellage_saar.shp
CREATE SEQUENCE LEVELPOSITION_ID_SEQ;
CREATE TABLE levelposition (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    x numeric(10,0),
    y numeric(10,0),
    name varchar(254)
);
SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2);
ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ');

-- Hydrologie/UeSG/Berechnung
   -- Berechnung/Aktuell/BfG
CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ;
CREATE TABLE computations_bfg (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    section varchar(254),
    area float8,
    perimeter float8
);
SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2);
ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ');

   -- Berechnung/Aktuell/Land
CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ;
CREATE TABLE computations_country(
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    text varchar(254)
);
SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2);
ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ');


-- Hydrologie/UeSG/Messung
CREATE SEQUENCE MEASUREMENTS_ID_SEQ;
CREATE TABLE measurements (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    year varchar(254),
    oid varchar(40)
);
SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2);
ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ');

END;