-- Copyright 2010, Intevation GmbH, Germany, <info@intevation.de>
--
-- This file is part of FGSK.
--
-- FGSK is free software: you can redistribute it and/or modify it under
-- the terms of the GNU General Public License as published by the
-- Free Software Foundation, either version 2 of the License, or (at your
-- option) any later version.
--
-- FGSK is distributed in the hope that it will be useful, but WITHOUT
-- ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
-- FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public
-- License for more details.
--
-- You should have received a copy of the GNU General Public
-- License along with FGSK. If not, see <http://www.gnu.org/licenses/>.
--
-- FGSK has been developed on behalf of the
-- Landesamt fuer Umwelt und Geologie Mecklenburg Vorpommern (LUNG-MV)
-- by Intevation GmbH. 
-- biota Institut fuer oekologische Forschung und Planung GmbH has provided
-- the functional background.
--
-- Authors:
-- Tim Englich <tim.englich@intevation.de>
-- Ingo Weinzierl <ingo.weinzierl@intevation.de>

BEGIN;
/* Anfang Lookuptabelen für Stammdaten*/

CREATE TABLE sonderfall(
    /*Lookuptabelle für WK-Typ*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE bild(
    /*Tabelle für die Speicherung von Grafiken zu einem Kartierabschnitt*/
    id serial,
    bild bytea, -- Blob-wert des Bildes
    kurzbeschreibung TEXT,
    aufnahmedatum timestamp,
    PRIMARY KEY(id)
);

CREATE TABLE wasserfuehrung(
    /*Lookuptabelle für die Werte für die Wasserführung*/
    id integer not null,
    kurzbeschreibung text unique,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE gewaessertyp(
      /* Lookuptabelle für den Gewässertyp*/
    id numeric,
    beschreibung text,
    PRIMARY KEY (id)
);

CREATE TABLE gewaessersubtyp(
    /* Lookuptabelle für den Gewässertyp*/
    id varchar(1),
    beschreibung text,
    PRIMARY KEY (id)
);

CREATE TABLE gewaessertyp_hat_gewaessersubtyp(
    gewaessertyp_id integer references gewaessertyp(id),
    gewaessersubtyp_id varchar(1) references gewaessersubtyp(id),
    PRIMARY KEY (gewaessertyp_id,gewaessersubtyp_id)
);

CREATE TABLE gewaesserbreite(
    /* Lookuptabelle für die Größenklasse,/ Gewässerbreite*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE gewaesser(
    gewaesser_kennzahl text,
    gewaesser_name text,
    PRIMARY KEY(gewaesser_kennzahl)
);

CREATE TABLE fliessrichtung(
    id INTEGER NOT NULL,
    beschreibung TEXT NOT NULL,
    PRIMARY KEY(id)
);


/* Ende lookuptabellen für Stammdaten*/

CREATE TABLE kartierabschnitt (
    id serial,

    /* Kartierabschnitt Anfang */
    gewaesser_kennzahl text references gewaesser(gewaesser_kennzahl),
    gewaesser_abschnitt text,
    stationierung_von integer,
    stationierung_bis integer,

    erfassungsdatum timestamp,
    bearbeiter text,
    bild_id integer references bild(id),
    fliessrichtung_id integer references fliessrichtung(id),
    wasserfuehrung_id integer references wasserfuehrung(id),
    unterhaltungerkennbar boolean,
    sonderfall_id integer references sonderfall(id),
    erlaeuterung text,
    gewaessertyp_id integer references gewaessertyp(id),
    seeausfluss boolean default false,
    gewaesserbreite_id integer references gewaesserbreite(id),
    /*Kartierabschnitt Ende */

    beschreibung text,
    punktzahl_gesamt numeric,
    punktzahl_sohle numeric,
    punktzahl_ufer numeric,
    punktzahl_land numeric,
    PRIMARY KEY(id)
);
SELECT AddGeometryColumn('kartierabschnitt','the_geom','35833','LINESTRING',2);

CREATE TABLE kartierabschnitt_hat_gewaessersubtyp(
    kartierabschnitt_id integer references kartierabschnitt(id),
    gewaessersubtyp_id varchar(1) references gewaessersubtyp(id),
    PRIMARY KEY (kartierabschnitt_id,gewaessersubtyp_id)
);

CREATE TABLE kartierabschnitt_hat_bild(
    kartierabschnitt_id integer references kartierabschnitt(id),
    bild_id integer references bild(id),
    PRIMARY KEY (kartierabschnitt_id,bild_id)
);

/*Anfang Lookuptabellen für Laufentwicklung*/

CREATE TABLE laufstrukturen(
    /*Lookuptabelle für Laufstrukturen*/
    id integer not null,
    kurzbeschreibung text unique,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE laengsbaenke(
    /*Lookuptabelle für Längsbänke*/
    id integer not null,
    kurzbeschreibung text unique,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE laufkruemmung(
    /*Lookuptabelle für Laufkrümmung*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE kruemmungserosion(
    /*Lookuptabelle für Krümmungserosion*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

/*Ende Lookuptabelölen für Lauifentwicklung*/

CREATE TABLE fliessgeschwindigkeit(
    /*Lookuptabelle für Fließgeschwindigeit*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE stroemungs_diversitaet(
    /*Lookuptabelle für Strömungs diversität*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);


CREATE TABLE tiefenvarianz(
    /*Lookuptabelle für Tiefenvarianz*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);


CREATE TABLE tiefenerosion(
    /*Lookuptabelle für Tiefenerosion*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE besonderheiten_wasserfuehrung(
    /*Lookuptabelle für Besonderheiten der Wasserführung*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE profiltyp(
    /*Lookuptabelle für profityp*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE breitenvarianz(
    /*Lookuptabelle für Breitenvarianz*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE bereitenerosion(
    /*Lookuptabelle für Bereitenerosion*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE messungen(
    /*Lookuptabelle für Messungen Tiefe/Breite für Querprofil*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE sohlenverbau(
    /*Lookuptabelle für Sohlenverbau*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE z_sohlenverbau(
    /*Lookuptabelle für den Zustand des Sohlenverbau*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);


CREATE TABLE flaechennutzung(
    /*Lookuptabelle für Flächennutzng*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE gewaesserrandstreifen(
    /*Lookuptabelle für Anzahl belastungen Sohle*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE sohlensubstrat(
    /*Lookuptabelle für Sohlensubstrat*/
    id integer not null,
    beschreibung text,
    natuerlich boolean,
    hartsubstrat boolean,
    kuenstlich boolean,
    PRIMARY KEY(id)
);

CREATE TABLE sohlenstrukturen(
    /*Lookuptabelle für Sohlenstrukturen*/
    id integer not null,
    kurzbeschreibung text,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE belastung_sohle(
    /*Lookuptabelle für belastungen Sohle*/
    id integer not null,
    kurzbeschreibung text,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE z_uferverbau(
    /*Lookuptabelle für Z-Uferverbau*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE uferverbau(
    /*Lookuptabelle für Uferverbau*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE ufervegetation (
    /*Lookuptabelle für Ufervegetation*/
    id integer not null,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE uferstrukturen(
    /*Tabelle für Uferstrukturen*/
    id integer not null,
    kurzbeschreibung text UNIQUE,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE uferbelastungen(
    /*Tabelle für Uferbelastungen*/
    id integer not null,
    kurzbeschreibung text UNIQUE,
    beschreibung text,
    PRIMARY KEY(id)
);
  
CREATE TABLE umfeldstrukturen(
    /*Tabelle für Umfeldstrukturen*/
    id integer not null,
    kurzbeschreibung text UNIQUE,
    beschreibung text,
    PRIMARY KEY(id)
);

CREATE TABLE schaedlicheumfeldstrukturen(
    /*Tabelle für schädliche Umfeldstrukturen*/
    id integer not null,
    kurzbeschreibung text UNIQUE,
    beschreibung text,
    PRIMARY KEY(id)
);

/*Ende UFERSTRUKTUREN/Gewässerumfeld*/

/* Längsprofile */
CREATE TABLE laengsprofil(
    kartierabschnitt_id integer references kartierabschnitt(id),
    /*Längsprofil Anfang*/
    fliessgeschwindigkeit_id integer references fliessgeschwindigkeit(id),
    querbaenke_anzahl numeric,
    stroemungs_diversitaet_id  integer references stroemungs_diversitaet(id),
    tiefenvarianz_id integer references tiefenvarianz(id),
    tiefenerosion_id integer references tiefenerosion(id),
    /*Längsprofil Ende */
    summe_punktzahl numeric,
    anzahl_kriterien integer,
    PRIMARY KEY(kartierabschnitt_id)
);

CREATE TABLE laengsprofil_hat_besonderheiten_wasserfuehrung(
    kartierabschnitt_id integer references laengsprofil(kartierabschnitt_id),
    besonderheiten_wasserfuehrung_id integer references besonderheiten_wasserfuehrung(id),
    PRIMARY KEY(kartierabschnitt_id,besonderheiten_wasserfuehrung_id)
);
/* Ende Längsprofil*/

/*Anfang Querprofil*/
CREATE TABLE querprofil(
    kartierabschnitt_id integer references kartierabschnitt(id),
    /*Querprofil Anfang*/
    profiltyp_id integer references profiltyp(id),
    breitenvarianz_id integer references breitenvarianz(id),
    breitenerosion_id integer references bereitenerosion(id),
    /*Querprofil Ende*/
    summe_punktzahl numeric,
    anzahl_kriterien integer,
    PRIMARY KEY(kartierabschnitt_id)
);

CREATE TABLE querprofil_hat_messungen(
    /* N zu M Beziehung zwischen dem Querprofil und den für die Querprofile gemessenden Werten.*/
    kartierabschnitt_id integer references querprofil(kartierabschnitt_id),
    messungen_id integer references messungen(id),
    meter numeric, /* Der gemessende Wert */
    PRIMARY KEY(kartierabschnitt_id,messungen_id)
);
/*Ende Querprofil*/

/*Anfang Sohlenstruktur*/

CREATE TABLE sohlenstruktur(
    kartierabschnitt_id integer references kartierabschnitt(id),
    /*Sohlenstruktur Anfang*/
    sohlenverbau_id integer references sohlenverbau(id),
    z_sohlenverbau_id integer references z_sohlenverbau(id),
    /*Sohlenstruktur Ende*/
    summe_punktzahl numeric,
    anzahl_kriterien integer,
    PRIMARY KEY(kartierabschnitt_id)
);

CREATE TABLE sohlenstruktur_hat_sohlensubstrat(
    kartierabschnitt_id integer references sohlenstruktur(kartierabschnitt_id),
    sohlensubstrat_id integer references sohlensubstrat(id),
    percentage numeric,
    PRIMARY KEY(kartierabschnitt_id,sohlensubstrat_id)
);

CREATE TABLE sohlenstruktur_hat_sohlenstrukturen(
    kartierabschnitt_id integer references sohlenstruktur(kartierabschnitt_id),
    sohlenstruktur_id integer references sohlenstrukturen(id),
    anzahl numeric,
    PRIMARY KEY(kartierabschnitt_id,sohlenstruktur_id)
);

CREATE TABLE sohlenstruktur_hat_belastung_sohle(
    kartierabschnitt_id integer references sohlenstruktur(kartierabschnitt_id),
    belastung_sohle_id integer references belastung_sohle(id),
    anzahl numeric,
    PRIMARY KEY(kartierabschnitt_id,belastung_sohle_id)
); 
/*Ende Sohlenstruktur*/

/*Anfang Uferstruktur*/
CREATE TABLE uferstruktur(
    kartierabschnitt_id integer references kartierabschnitt(id),
    /*Uferstruktur Anfang*/
    ufervegetation_links_id integer references ufervegetation(id),
    ufervegetation_rechts_id integer references ufervegetation(id),
    ufervegetation_links_typical boolean,
    ufervegetation_rechts_typical boolean,
    -- FIXME: Uferverbau-Zustand links rechts
    uferverbau_links_id integer references uferverbau(id),
    uferverbau_rechts_id integer references uferverbau(id),
    z_uferverbau_links_id integer references z_uferverbau(id),
    z_uferverbau_rechts_id integer references z_uferverbau(id),
    /*Uferstruktur Ende*/
    summe_punktzahl numeric,
    anzahl_kriterien integer,
    PRIMARY KEY(kartierabschnitt_id)
);

CREATE TABLE uferstruktur_hat_uferstrukturen(
    kartierabschnitt_id integer references uferstruktur(kartierabschnitt_id),
    uferstrukturen_id integer references uferstrukturen(id),
    anzahl_links numeric,
    anzahl_rechts numeric,
    PRIMARY KEY(kartierabschnitt_id,uferstrukturen_id)
);
 
CREATE TABLE uferstruktur_hat_uferbelastungen(
    kartierabschnitt_id integer references uferstruktur(kartierabschnitt_id),
    uferbelastungen_id integer references uferbelastungen(id),
    anzahl_links numeric,
    anzahl_rechts numeric,
    PRIMARY KEY(kartierabschnitt_id,uferbelastungen_id)
);


/*Ende Uferstruktur*/

/*Anfang Gewaesserumfeld*/

CREATE TABLE gewaesserumfeld(
    kartierabschnitt_id integer references kartierabschnitt(id),
    flaechennutzung_links_id integer references flaechennutzung(id),
    flaechennutzung_rechts_id integer references flaechennutzung(id),
    gewaesserrandstreifen_links_id integer references gewaesserrandstreifen(id),
    gewaesserrandstreifen_rechts_id integer references gewaesserrandstreifen(id),
    summe_punktzahl numeric,
    anzahl_kriterien integer,
    PRIMARY KEY(kartierabschnitt_id)
);

CREATE TABLE gewaesserumfeld_hat_umfeldstrukturen(
    kartierabschnitt_id integer references gewaesserumfeld(kartierabschnitt_id),
    umfeldstrukturen_id integer references umfeldstrukturen(id),
    anzahl_links numeric,
    anzahl_rechts numeric,
    PRIMARY KEY(kartierabschnitt_id,umfeldstrukturen_id)
);

CREATE TABLE gewaesserumfeld_hat_schaedlicheumfeldstrukturen(
    kartierabschnitt_id integer references gewaesserumfeld(kartierabschnitt_id),
    schaedlicheumfeldstrukturen_id integer references schaedlicheumfeldstrukturen(id),
    anzahl_links numeric,
    anzahl_rechts numeric,
    PRIMARY KEY(kartierabschnitt_id,schaedlicheumfeldstrukturen_id)
);

/*Ende Gewaesserumfeld*/

/* Anfang Laufentwicklung*/
CREATE TABLE laufentwicklung(
    kartierabschnitt_id integer references kartierabschnitt(id),
    /*Laufentwicklung Anfang */
    laufkruemmung_id integer references laufkruemmung(id),
    kruemmungserosion_id integer references kruemmungserosion(id),
    /*Laufentwicklung Ende */
    summe_punktzahl numeric,
    anzahl_kriterien integer,
    PRIMARY KEY(kartierabschnitt_id)
);

CREATE TABLE laufentwicklung_hat_laengsbaenke(
    kartierabschnitt_id integer references laufentwicklung(kartierabschnitt_id),
    laengsbaenke_id integer references laengsbaenke(id),
    anzahl numeric,
    PRIMARY KEY(kartierabschnitt_id,laengsbaenke_id)
);

CREATE TABLE laufentwicklung_hat_laufstrukturen(
    kartierabschnitt_id integer references laufentwicklung(kartierabschnitt_id),
    laufstrukturen_id integer references laufstrukturen(id),
    anzahl numeric,
    PRIMARY KEY(kartierabschnitt_id,laufstrukturen_id)
);
/* Ende Laufentwicklung */
END;
