-- 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_laengsprofil_func(kartierabsch_id INTEGER)
RETURNS INTEGER AS
$$
DECLARE
    -- necessary meta information
    gew_typ_id    INTEGER;
    querb_anz     NUMERIC;
    abschn_laenge NUMERIC;
    stat_laenge   NUMERIC;
    abs_value     NUMERIC;
    stroem_div_id INTEGER;
    tiefenvari_id INTEGER;
    fliessgsch_id INTEGER;
    num_entries   INTEGER;

    -- ratings
    rating_querbaenke INTEGER;
    rating_stroemungd INTEGER;
    rating_tiefenvari INTEGER;
    rating_fliessgsch INTEGER;
    rating_total      INTEGER := 0;
    num_rating        INTEGER := 0;


    -- this factor is necessary to transform kilometer into meter
    factor INTEGER := 1;

BEGIN

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

        RAISE DEBUG '------- AUSWERTUNG LAENGSPROFIL -------';
        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 'Gewassertyp ID = %', gew_typ_id;

    SELECT querbaenke_anzahl INTO querb_anz FROM laengsprofil
           WHERE kartierabschnitt_id = kartierabsch_id;
    RAISE DEBUG 'Anzahl Querbaenke = %', querb_anz;

    SELECT abschnittslaenge INTO abschn_laenge FROM querbaenke_auswertung
           WHERE id_gewaessertyp = gew_typ_id;
    RAISE DEBUG 'Abschnittslaenge Querbaenke = %', abschn_laenge;

    SELECT abs(stationierung_bis - stationierung_von) INTO stat_laenge
           FROM kartierabschnitt
           WHERE id = kartierabsch_id;
    RAISE DEBUG 'Absolute Laenge der Stationierung = %', stat_laenge;

    IF querb_anz = 0.5 THEN
        abs_value := querb_anz;
    ELSE
        SELECT fgsk_round(querb_anz/(stat_laenge*factor/abschn_laenge))
               INTO abs_value;
    END IF;
    RAISE DEBUG 'Absolute Anzahl Querbaenke pro XXXm = %', abs_value;


    SELECT stroemungs_diversitaet_id INTO stroem_div_id FROM laengsprofil
           WHERE kartierabschnitt_id = kartierabsch_id;
    RAISE DEBUG 'Stroemungsdiversitaet ID = %', stroem_div_id;

    SELECT tiefenvarianz_id INTO tiefenvari_id FROM laengsprofil
           WHERE kartierabschnitt_id = kartierabsch_id;
    RAISE DEBUG 'Tiefenvarianz ID = %', tiefenvari_id;

    SELECT fliessgeschwindigkeit_id INTO fliessgsch_id FROM laengsprofil
           WHERE kartierabschnitt_id = kartierabsch_id;
    RAISE DEBUG 'Fliessgeschwindigkeit ID = %', fliessgsch_id;


    --
    -- collect ratings
    --
    SELECT bewertung INTO rating_querbaenke FROM querbaenke_auswertung
           WHERE id_gewaessertyp = gew_typ_id
                 AND anz_querbaenke_von <= abs_value
                 AND anz_querbaenke_bis >= abs_value;
    RAISE DEBUG '-> Querbaenke: %', rating_querbaenke;

    SELECT bewertung INTO rating_stroemungd
           FROM stroemungs_diversitaet_auswertung
           WHERE id_gewaessertyp = gew_typ_id
                 AND id_stroemungs_diversitaet = stroem_div_id;
    RAISE DEBUG '-> Stroemungsdiversitaet: %', rating_stroemungd;

    SELECT bewertung INTO rating_tiefenvari FROM tiefenvarianz_auswertung
           WHERE id_gewaessertyp = gew_typ_id
                 AND id_tiefenvarianz = tiefenvari_id;
    RAISE DEBUG '-> Tiefenvarianz: %', rating_tiefenvari;

    SELECT bewertung INTO rating_fliessgsch
           FROM fliessgeschwindigkeit_auswertung
           WHERE id_gewaessertyp = gew_typ_id
                 AND id_fliessgeschwindigkeit = fliessgsch_id;
    RAISE DEBUG '-> Fliessgeschwindigkeit: %', rating_fliessgsch;


    --
    -- calculate the total rating and return it
    --
    IF rating_querbaenke > 0 THEN
        rating_total := rating_total + rating_querbaenke;
        num_rating := num_rating + 1;
    END IF;

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

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

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

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

    RAISE DEBUG '=> Laengsprofil 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_laengsprofil_func()
RETURNS TRIGGER AS
$$
DECLARE
BEGIN

    RAISE DEBUG '%: TABLE NAME = %', TG_OP, TG_TABLE_NAME;
    IF (TG_TABLE_NAME = 'laengsprofil') THEN
        IF NEW.fliessgeschwindigkeit_id IS NULL OR NEW.querbaenke_anzahl IS NULL
        OR NEW.stroemungs_diversitaet_id IS NULL OR NEW.tiefenvarianz_id IS NULL
        OR NEW.tiefenerosion_id IS NULL THEN
            RAISE DEBUG 'Keine Aktualisierung fuer Laengsprofil noetig.';
           RETURN NEW;
        END IF;
    END IF;

    IF (TG_OP = 'UPDATE') THEN
        IF (TG_TABLE_NAME = 'laengsprofil') THEN
            IF (NEW.fliessgeschwindigkeit_id = OLD.fliessgeschwindigkeit_id) AND
               (NEW.querbaenke_anzahl        = OLD.querbaenke_anzahl) AND
               (NEW.stroemungs_diversitaet_id = OLD.stroemungs_diversitaet_id) AND
               (NEW.tiefenvarianz_id         = OLD.tiefenvarianz_id) AND
               (NEW.tiefenerosion_id         = OLD.tiefenerosion_id) THEN

               RAISE DEBUG 'Keine Aktualisierung für Laengsprofil nötig.';
               RETURN NEW;
            END IF;
        END IF;
    END IF;

    PERFORM calculate_points_laengsprofil_func(NEW.kartierabschnitt_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


--
-- remove existing triggers before creating new ones
--
DROP TRIGGER IF EXISTS update_laengsprofil_points
ON laengsprofil;


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


-- END TRANSACTION
END;
