-- 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;
-- BEGIN TRANSACTION
CREATE OR REPLACE FUNCTION 
                calculate_points_querprofil_func(kartierabsch_id INTEGER)
RETURNS INTEGER AS
$$
DECLARE
    -- necessary meta information
    gew_typ_id    INTEGER;
    prof_typ_id   INTEGER;
    br_vari_id    INTEGER;
    br_eros_id    INTEGER;
    prof_tiefe    NUMERIC;
    wsp_breite    NUMERIC;
    verhaeltnis   NUMERIC;
    num_entries   INTEGER;

    einsch_tiefe_id INTEGER;
    wasser_tiefe_id INTEGER;
    wsp_breite_id   INTEGER;

    -- ratings
    rating_total      INTEGER := 0;
    num_rating        INTEGER := 0;
    rating_tief_breit INTEGER;
    rating_erosion    INTEGER;
    rating_varianz    INTEGER;
    rating_profiltyp  INTEGER;

BEGIN

    SELECT COUNT(*) INTO num_entries
                    FROM querprofil
                    WHERE kartierabschnitt_id  = kartierabsch_id;
    IF (num_entries = 0 )THEN
        RAISE DEBUG 'Kein Eintrag fuer querprofil vorhanden. --> Abbruch';
        RETURN kartierabsch_id;
    END IF;

    RAISE DEBUG '------- AUSWERTUNG QUERPROFIL -------';
    RAISE DEBUG 'Kartierabschnitt ID %', kartierabsch_id;

    --
    -- collect necessary meta information
    --
    SELECT gewaessertyp_id INTO gew_typ_id FROM kartierabschnitt
           WHERE id = kartierabsch_id;
    RAISE DEBUG 'Gewaessertyp ID = %', gew_typ_id;

    SELECT profiltyp_id, breitenvarianz_id, breitenerosion_id
           INTO prof_typ_id, br_vari_id, br_eros_id
           FROM querprofil
           WHERE kartierabschnitt_id = kartierabsch_id;

    RAISE DEBUG 'Profiltyp ID = %', prof_typ_id;
    RAISE DEBUG 'Breitenvarianz ID = %', br_vari_id;
    RAISE DEBUG 'Breitenerosion ID = %', br_eros_id;

    SELECT id INTO einsch_tiefe_id FROM messungen
           WHERE beschreibung = 'Einschnitttiefe';

    SELECT id INTO wasser_tiefe_id FROM messungen
           WHERE beschreibung = 'Wassertiefe';

    SELECT id INTO wsp_breite_id FROM messungen
           WHERE beschreibung = 'obere Profilbreite';

    SELECT meter INTO wsp_breite FROM querprofil_hat_messungen
           WHERE kartierabschnitt_id = kartierabsch_id AND
                 messungen_id = wsp_breite_id;
    RAISE DEBUG 'obere Profilbreite = %', wsp_breite;

    SELECT sum(meter) into prof_tiefe FROM querprofil_hat_messungen
           WHERE kartierabschnitt_id = kartierabsch_id
                 AND (messungen_id = wasser_tiefe_id
                 OR   messungen_id = einsch_tiefe_id);
    RAISE DEBUG 'Profiltiefe = %', prof_tiefe;

    IF prof_typ_id IS NULL OR br_eros_id IS NULL OR br_vari_id IS NULL THEN
        RAISE DEBUG 'Auswertung nicht moeglich - fehlende Daten!';
        RETURN kartierabsch_id;
    END IF;


    --
    -- collect ratings
    --
    verhaeltnis := fgsk_round(wsp_breite / prof_tiefe);
    RAISE DEBUG 'Verhaeltnis obere Profilbreite/Sohltiefe = %', verhaeltnis;
    SELECT bewertung INTO rating_tief_breit
           FROM verhaeltnis_profiltiefe_wspbreite_auswertung
           WHERE verhaeltnis >= verhaeltnis_von
             AND verhaeltnis <= verhaeltnis_bis
             AND id_gewaessertyp = gew_typ_id;

    -- if we could not determine a range, we try to fetch the biggest value
    IF rating_tief_breit IS NULL THEN
        SELECT bewertung INTO rating_tief_breit
               FROM verhaeltnis_profiltiefe_wspbreite_auswertung
               WHERE verhaeltnis > verhaeltnis_bis
                 AND id_gewaessertyp = gew_typ_id
                 AND bewertung       = 5;
    END IF;

    -- if we could not determine a range, we try to fetch the lowest value
    IF rating_tief_breit IS NULL THEN
        SELECT bewertung INTO rating_tief_breit
               FROM verhaeltnis_profiltiefe_wspbreite_auswertung
               WHERE verhaeltnis < verhaeltnis_von
                 AND id_gewaessertyp = gew_typ_id
                 AND bewertung       = 1;
    END IF;

    RAISE DEBUG '-> Verhaeltnis Profiltiefe/obere Profilbreite: %', rating_tief_breit;

    SELECT bewertung INTO rating_erosion FROM breitenerosion_auswertung
           WHERE id_breitenerosion = br_eros_id
             AND id_gewaessertyp = gew_typ_id;
    RAISE DEBUG '-> Breitenerosion: %', rating_erosion;

    SELECT bewertung INTO rating_varianz FROM breitenvarianz_auswertung
           WHERE id_breitenvarianz = br_vari_id
             AND id_gewaessertyp = gew_typ_id;
    RAISE DEBUG '-> Breitenvarianz: %', rating_varianz;

    SELECT bewertung INTO rating_profiltyp FROM profiltyp_auswertung
           WHERE id_profiltyp = prof_typ_id
             AND id_gewaessertyp = gew_typ_id;
    RAISE DEBUG '-> Profiltyp: %', rating_profiltyp;

    IF rating_erosion > 0  THEN
        rating_total := rating_total + rating_erosion;
        num_rating := num_rating + 1;
    END IF;

    IF rating_varianz > 0 THEN
        rating_total := rating_total + rating_varianz;
        num_rating := num_rating + 1;
    END IF;

    IF rating_profiltyp > 0 THEN
        rating_total := rating_total + rating_profiltyp;
        num_rating := num_rating + 1;
    END IF;

    IF rating_tief_breit > 0 THEN
        rating_total := rating_total + rating_tief_breit;
        num_rating := num_rating + 1;
    END IF;

    UPDATE querprofil SET summe_punktzahl = rating_total,
                          anzahl_kriterien = num_rating
           WHERE kartierabschnitt_id = kartierabsch_id;

    RAISE DEBUG '=> Querprofil gesamt: %', rating_total;
    RAISE DEBUG '=> Anzahl der Kriterien: %', num_rating;

    -- Call Gesamtbewertungs-function
    PERFORM update_points_kartierabschnitt_func(kartierabsch_id);
    RETURN kartierabsch_id;
END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_points_querprofil_func()
RETURNS TRIGGER AS
$$
DECLARE
    -- necessary meta information

BEGIN

    RAISE DEBUG '%: TABLE NAME = %', TG_OP, TG_TABLE_NAME;
    IF (TG_TABLE_NAME = 'querprofil') THEN
        IF NEW.profiltyp_id IS NULL
           OR NEW.breitenvarianz_id IS NULL
           OR NEW.breitenerosion_id IS NULL THEN

           RAISE DEBUG 'Keine Aktualisierung fuer Querprofil noetig.';
           RETURN NEW;
       END IF;
    END IF;

    IF (TG_OP = 'UPDATE') THEN
        IF (TG_TABLE_NAME = 'querprofil') THEN
            IF (NEW.profiltyp_id = OLD.profiltyp_id) AND
               (NEW.breitenvarianz_id = OLD.breitenvarianz_id) AND
               (NEW.breitenerosion_id = OLD.breitenerosion_id) THEN

               RAISE DEBUG 'Keine Aktualisierung fuer Querprofil noetig.';
               RETURN NEW;
            END IF;
        END IF;
    END IF;

    PERFORM calculate_points_querprofil_func(NEW.kartierabschnitt_id);

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

--
-- remove existing triggers before creating new ones
--
DROP TRIGGER IF EXISTS update_querprofil_points
ON querprofil;

DROP TRIGGER IF EXISTS update_querprofil_points
ON querprofil_hat_messungen;


--
-- necessary trigger for each table that has a relation to LAUFENTWICKLUNG
--
CREATE TRIGGER update_querprofil_points
AFTER UPDATE OR INSERT ON querprofil
    FOR EACH ROW EXECUTE PROCEDURE update_points_querprofil_func();


CREATE TRIGGER update_querprofil_points
AFTER UPDATE OR INSERT ON querprofil_hat_messungen
    FOR EACH ROW EXECUTE PROCEDURE update_points_querprofil_func();

-- END TRANSACTION
END;
