-- 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_sohlenstruktur_func(kartierabsch_id INTEGER)
RETURNS INTEGER AS
$$
DECLARE
    -- necessary meta information
    gew_typ_id           INTEGER;
    gew_subtyp_id        VARCHAR(1);
    stat_laenge          NUMERIC;
    absolut              NUMERIC;
    strukturen           NUMERIC;
    str_absch_laenge     NUMERIC;
    max_malus_belastung_sohle NUMERIC;
    sohlenverb_id        INTEGER;
    z_sohlenverb_id      INTEGER;
    num_nat_substrate    INTEGER;
    sum_kuenst_substrate NUMERIC;
    sum_hart_substrate   NUMERIC;
    num_entries   INTEGER;

    -- ratings
    rating_total            NUMERIC := 0;
    num_rating              INTEGER := 0;
    rating_strukturen       INTEGER := 0;
    rating_sohlverbau       INTEGER := 0;
    malus_belastung_sohle   NUMERIC := 0;
    rating_nat_substrate    NUMERIC := 0;
    rating_kuenst_substrate NUMERIC := 0;
    rating_hart_substrate   NUMERIC := 0;
    rating_substrate        NUMERIC := 0;

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

BEGIN

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

    RAISE DEBUG '------- AUSWERTUNG SOHLENSTRUKTUR -------';
    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 gewaessersubtyp_id INTO gew_subtyp_id 
           FROM kartierabschnitt_hat_gewaessersubtyp
           WHERE kartierabschnitt_id = kartierabsch_id AND
                 (gewaessersubtyp_id = 'S' or gewaessersubtyp_id = 'M');
    RAISE DEBUG 'Gewassertypsub ID = %', gew_subtyp_id;

    SELECT abschnittslaenge INTO str_absch_laenge
           FROM  sohlenstrukturen_auswertung
           WHERE id_gewaessertyp = gew_typ_id;
    RAISE DEBUG 'Abschnittslaenge Sohlenstrukturen = %', str_absch_laenge;

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

    SELECT maximum into max_malus_belastung_sohle FROM belastung_sohle_max_auswertung
           WHERE id_gewaessertyp = gew_typ_id;
    RAISE DEBUG 'Maximaler Wert fuer Belastungen der Sohle = %',
                 max_malus_belastung_sohle;

    SELECT sohlenverbau_id, z_sohlenverbau_id
           INTO sohlenverb_id, z_sohlenverb_id
           FROM sohlenstruktur
           WHERE kartierabschnitt_id = kartierabsch_id;

    RAISE DEBUG 'ID Sohlenverbau = %',sohlenverb_id;
    RAISE DEBUG 'ID Zustand Sohlenverbau = %',z_sohlenverb_id;

    --
    --Collecting the single rating-values
    --
    SELECT COUNT(sohlensubstrat_id) INTO num_nat_substrate
           FROM sohlenstruktur_hat_sohlensubstrat, sohlensubstrat
           WHERE kartierabschnitt_id = kartierabsch_id AND
                 sohlensubstrat_id = id AND
                 natuerlich = TRUE AND
                 percentage > 0;
    RAISE DEBUG 'Anzahl natuerlicher Substrate: %', num_nat_substrate;

    SELECT SUM(percentage) INTO sum_kuenst_substrate
           FROM sohlenstruktur_hat_sohlensubstrat, sohlensubstrat
           WHERE kartierabschnitt_id = kartierabsch_id AND
                 sohlensubstrat_id = id AND
                 kuenstlich = TRUE;
    RAISE DEBUG 'Summe kuenstlicher Substrate: %', sum_kuenst_substrate;

    SELECT SUM(percentage) INTO sum_hart_substrate
           FROM sohlenstruktur_hat_sohlensubstrat, sohlensubstrat
           WHERE kartierabschnitt_id = kartierabsch_id AND
                 sohlensubstrat_id = id AND
                 hartsubstrat = TRUE;
    RAISE DEBUG 'Summe Hartsubstrate: %', sum_hart_substrate;

    IF (str_absch_laenge IS NOT NULL AND str_absch_laenge > 0) THEN
        SELECT SUM(anzahl) into strukturen 
               FROM sohlenstruktur_hat_sohlenstrukturen
               WHERE kartierabschnitt_id = kartierabsch_id
                     AND sohlenstruktur_id < 5; -- 5 == Keine

        RAISE DEBUG 'Absolute Anzahl von Strukturen: %', strukturen;

        SELECT fgsk_round(strukturen/(stat_laenge*factor/str_absch_laenge))
               INTO absolut;

        RAISE DEBUG 'Anzahl von Strukturen pro Bewertungsabschnitt: %',
                     absolut;

        SELECT bewertung INTO rating_strukturen
               FROM  sohlenstrukturen_auswertung
               WHERE id_gewaessertyp = gew_typ_id
                 AND absolut >= anz_sohlenstrukturen_von
                 AND absolut <= anz_sohlenstrukturen_bis;

    END IF;
    RAISE DEBUG '-> Sohlenstrukturen: %', rating_strukturen;

    SELECT bewertung INTO rating_nat_substrate
           FROM natuerliche_substrate_auswertung
           WHERE anz_natuerliche_substrate_von <= num_nat_substrate AND
                 anz_natuerliche_substrate_bis >= num_nat_substrate AND
                 id_gewaessertyp = gew_typ_id AND
                 (id_gewaessersubtyp = gew_subtyp_id OR
                  id_gewaessersubtyp = '*');
    RAISE DEBUG '-> Natuerliche Substrate: %', rating_nat_substrate;
    
    SELECT bewertung INTO rating_kuenst_substrate
           FROM prozent_kuenstliche_substrate_auswertung
           WHERE pro_kuenstliche_substrate_von <= sum_kuenst_substrate AND
                 pro_kuenstliche_substrate_bis >= sum_kuenst_substrate AND
                 id_gewaessertyp = gew_typ_id AND
                 (id_gewaessersubtyp = gew_subtyp_id OR
                  id_gewaessersubtyp = '*');
    RAISE DEBUG '-> Kuenstliche Substrate: %', rating_kuenst_substrate;
    
    SELECT bewertung INTO rating_hart_substrate
           FROM prozent_organische_hartsubstrate_auswertung
           WHERE pro_organische_hartsubstrate_von <= sum_hart_substrate AND
                 pro_organische_hartsubstrate_bis >= sum_hart_substrate AND
                 id_gewaessertyp = gew_typ_id AND
                 (id_gewaessersubtyp = gew_subtyp_id OR
                  id_gewaessersubtyp = '*');
    RAISE DEBUG '-> Hartsubstrate: %', rating_hart_substrate;

    SELECT fgsk_round((rating_nat_substrate +
                       rating_kuenst_substrate +
                       rating_hart_substrate)/3.0) INTO rating_substrate;
    RAISE DEBUG '-> Substrate: %', rating_substrate;

    SELECT bewertung INTO rating_sohlverbau
           FROM sohlenverbau_auswertung
           WHERE id_sohlenverbau = sohlenverb_id AND
                 id_z_sohlenverbau = z_sohlenverb_id AND
                 id_gewaessertyp = gew_typ_id;
    RAISE DEBUG '-> Sohlenverbau: %', rating_sohlverbau;

    SELECT SUM(bewertung) INTO malus_belastung_sohle
           FROM belastung_sohle_auswertung, 
                sohlenstruktur_hat_belastung_sohle
           WHERE kartierabschnitt_id = kartierabsch_id AND
                 belastung_sohle_id = id_belastung_sohle AND
                 id_gewaessertyp = gew_typ_id AND
                 anzahl> 0;
    RAISE DEBUG '->Malus Belastungen der Sohle: %', malus_belastung_sohle;

    if (malus_belastung_sohle IS NOT NULL AND
        malus_belastung_sohle < max_malus_belastung_sohle) THEN
        malus_belastung_sohle := max_malus_belastung_sohle;
        RAISE DEBUG '-> Malus Belastungen der Sohle korrigiert: %',
                     malus_belastung_sohle;
    END IF;
    
    --
    -- calculating the rating
    ---

    IF (rating_substrate IS NOT NULL AND rating_substrate > 0) THEN
        rating_total := rating_total + rating_substrate;
        num_rating := num_rating + 1;
    END IF;

    IF (rating_strukturen IS NOT NULL AND rating_strukturen > 0) THEN
        rating_total := rating_total + rating_strukturen;
        num_rating := num_rating + 1;
    END IF;

    IF (rating_sohlverbau IS NOT NULL AND rating_sohlverbau > 0) THEN
        rating_total := rating_total + rating_sohlverbau;
        num_rating := num_rating + 1;
    END IF;

    IF (malus_belastung_sohle IS NOT NULL AND malus_belastung_sohle < 0) THEN
        rating_total := rating_total + malus_belastung_sohle;
    END IF;

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

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

    RAISE DEBUG '%: TABLE NAME = %', TG_OP, TG_TABLE_NAME;
    IF (TG_TABLE_NAME = 'sohlenstruktur') THEN
        IF NEW.sohlenverbau_id IS NULL
           OR NEW.z_sohlenverbau_id IS NULL THEN

           RAISE DEBUG 'Keine Aktualisierung fuer Sohlenstruktur noetig.';
           RETURN NEW;
       END IF;
    END IF;
    
    IF (TG_OP = 'UPDATE') THEN
        IF (TG_TABLE_NAME = 'sohlenstruktur') THEN
            IF (NEW.sohlenverbau_id = OLD.sohlenverbau_id) AND
               (NEW.z_sohlenverbau_id = OLD.z_sohlenverbau_id) THEN

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

    PERFORM calculate_points_sohlenstruktur_func(NEW.kartierabschnitt_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


--
-- remove existing triggers before creating new ones
--
DROP TRIGGER IF EXISTS update_sohlenstruktur_points
ON sohlenstruktur;

DROP TRIGGER IF EXISTS update_sohlenstruktur_points
ON sohlenstruktur_hat_belastung_sohle;

DROP TRIGGER IF EXISTS update_sohlenstruktur_points
ON sohlenstruktur_hat_sohlensubstrat;

DROP TRIGGER IF EXISTS update_sohlenstruktur_points
ON sohlenstruktur_hat_sohlenstrukturen;


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

CREATE TRIGGER update_sohlenstruktur_points
AFTER UPDATE OR INSERT
    ON sohlenstruktur_hat_belastung_sohle
    FOR EACH ROW EXECUTE PROCEDURE
        update_points_sohlenstruktur_func();

CREATE TRIGGER update_sohlenstruktur_points
AFTER UPDATE OR INSERT
    ON sohlenstruktur_hat_sohlensubstrat
    FOR EACH ROW EXECUTE PROCEDURE
        update_points_sohlenstruktur_func();

CREATE TRIGGER update_sohlenstruktur_points
AFTER UPDATE OR INSERT
    ON sohlenstruktur_hat_sohlenstrukturen
    FOR EACH ROW EXECUTE PROCEDURE
        update_points_sohlenstruktur_func();


-- END TRANSACTION
END;
