-- 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 fgsk_round(num NUMERIC)
RETURNS NUMERIC AS
$$
DECLARE
    -- necessary declarations
    upper NUMERIC;

BEGIN
    upper := 0.5;

    IF mod(num, 1.0) > upper THEN
        RETURN ceil(abs(num));
    ELSE
        RETURN floor(abs(num));
    END IF;
END;
$$ LANGUAGE plpgsql;



-- This function takes a water code and a stationing. This information is used 
-- to determine the correct water section code (Gewässerabschnitt Kennzahl).
-- Sections behind this one will be updated.
CREATE OR REPLACE FUNCTION determine_watersection_func()
RETURNS TRIGGER AS
$$ DECLARE
    -- meta information about the water
    updated        INTEGER := 0;
    valid          INTEGER;
    section_length INTEGER;
    lo             TEXT;
    up             TEXT;
    tmp            TEXT;
    next_code      TEXT;
    majorsection   kartierabschnitt%ROWTYPE;
    subsections    kartierabschnitt%ROWTYPE;
    previous       kartierabschnitt%ROWTYPE;
BEGIN

    SELECT validate_water_section(NEW) INTO valid;
    IF valid = 0 THEN
        RAISE EXCEPTION 'Invalid stationing (% - %) !', NEW.stationierung_von,
                                                        NEW.stationierung_bis;
    END IF;

    RAISE DEBUG 'Determine watersection code for ID: %', NEW.id;

    -- determine if the current section is a minor or a major section
    SELECT * INTO majorsection FROM kartierabschnitt
        WHERE gewaesser_kennzahl = NEW.gewaesser_kennzahl
          AND ((stationierung_von = NEW.stationierung_von
          AND   stationierung_bis > NEW.stationierung_bis)
          OR   (stationierung_von < NEW.stationierung_von
          AND   stationierung_bis = NEW.stationierung_bis));

    IF majorsection IS NULL THEN
        -- we need to create a new main section
        SELECT * INTO previous FROM kartierabschnitt
            WHERE stationierung_bis <= NEW.stationierung_von
              AND gewaesser_kennzahl = NEW.gewaesser_kennzahl
            ORDER BY stationierung_bis DESC;

        IF previous IS NULL THEN
            -- in this case, the current section is the first one of the water
            RAISE DEBUG 'Create a new major section at the beginning.';
            UPDATE kartierabschnitt SET gewaesser_abschnitt = '001'
                WHERE id = NEW.id;
            SELECT update_major_sections(NEW.id, '001',
                                         NEW.gewaesser_kennzahl) INTO updated;

        ELSE
            -- we found a previous section, so the current one needs to have a
            -- code one bigger than the previous.
            RAISE DEBUG 'Create a new major section in the middle/end.';
            next_code   := cast(substring(previous.gewaesser_abschnitt
                                FROM 0 FOR 4) AS INTEGER) + 1;
            next_code   := lpad(next_code, 3, '0');
            UPDATE kartierabschnitt SET gewaesser_abschnitt = next_code
                WHERE id = NEW.id;
            SELECT update_major_sections(NEW.id, next_code,
                                         NEW.gewaesser_kennzahl) INTO updated;
        END IF;

    ELSE
        -- we need to create a new minor section
        tmp            := majorsection.gewaesser_abschnitt;
        section_length := length(tmp);

        -- determine the water section code for both sections
        IF section_length = 3 THEN
            lo := tmp || '.1';
            up := tmp || '.2';
        ELSE
            lo  := tmp;
            tmp := substring(lo FROM 5 FOR section_length-4);
            up  := substring(lo FROM 0 FOR 5) || cast(tmp AS INTEGER) + 1;
        END IF;

        RAISE DEBUG 'Lower section %:', lo;
        RAISE DEBUG 'Upper section %:', up;

        IF majorsection.stationierung_bis = NEW.stationierung_bis THEN
            RAISE DEBUG 'Insert an upper minor section between % and %',
                         majorsection.stationierung_von,
                         majorsection.stationierung_bis;

            UPDATE kartierabschnitt SET gewaesser_abschnitt = up
                WHERE id = NEW.id;

            UPDATE kartierabschnitt
                SET gewaesser_abschnitt = lo,
                    stationierung_bis   = NEW.stationierung_von
                WHERE id = majorsection.id;

            SELECT update_minor_sections(NEW.id, up, NEW.gewaesser_kennzahl)
                INTO updated;

            -- split up the major section into two parts (the new section shall
            -- have the same parameters like the older one has).
            PERFORM split_major_section(majorsection, NEW);
        ELSE
            RAISE DEBUG 'Insert a lower minor section between % and %',
                         majorsection.stationierung_von,
                         majorsection.stationierung_bis;

            UPDATE kartierabschnitt SET gewaesser_abschnitt = lo
                WHERE id = NEW.id;

            UPDATE kartierabschnitt
                SET gewaesser_abschnitt = up,
                    stationierung_von   = NEW.stationierung_bis
                WHERE id = majorsection.id;

            SELECT update_minor_sections(majorsection.id, up,
                                         NEW.gewaesser_kennzahl) INTO updated;

            -- split up the major section into two parts (the new section shall
            -- have the same parameters like the older one has).
            PERFORM split_major_section(majorsection, NEW);
        END IF;
    END IF;

    RETURN NEW;
END;
$$LANGUAGE plpgsql;


-- This procedure checks, if the range of the current kartierabschnitt is valid
-- regarding the water sections that are already stored in the database. The
-- real validation takes place in validate_water_section(numeric, numeric).
CREATE OR REPLACE FUNCTION validate_water_section(data kartierabschnitt)
RETURNS INTEGER AS
$$ DECLARE
    -- pass this
    error TEXT;
BEGIN
    SELECT validate_water_section(data.stationierung_von,
                                  data.stationierung_bis,
                                  cast(data.gewaesser_kennzahl AS TEXT))
        INTO error;

    IF error = '' THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
$$LANGUAGE plpgsql;


-- This procedure checks, if the range specified by 'st_von' and 'st_bis' is
-- valid, based on the water sections that are already stored in the database.
CREATE OR REPLACE FUNCTION validate_water_section(st_von   NUMERIC,
                                                  st_bis   NUMERIC,
                                                  gew_kenn TEXT)
RETURNS TEXT AS
$$ DECLARE
    conflict  kartierabschnitt%ROWTYPE;
BEGIN
    RAISE INFO 'validate: stationing (% - %, %)', st_von, st_bis, gew_kenn;

    -- (1)
    SELECT * INTO conflict FROM kartierabschnitt
        WHERE (st_von >= stationierung_von AND
               st_von <  stationierung_bis AND
               st_bis > stationierung_bis  AND
               gewaesser_kennzahl = gew_kenn);
    IF conflict.id <> 0 THEN
        RAISE WARNING
            'validate: (1) end (%) > new.start >= start (%) && new.end > end (%)',
            conflict.stationierung_bis,
            conflict.stationierung_von,
            conflict.stationierung_bis;
        RETURN conflict.stationierung_von || ' - ' || conflict.stationierung_bis;
    END IF;

    -- (2)
    SELECT * INTO conflict FROM kartierabschnitt
        WHERE (st_bis <= stationierung_bis AND
               st_bis >  stationierung_von AND
               st_von < stationierung_von  AND
               gewaesser_kennzahl = gew_kenn);
    IF conflict.id <> 0 THEN
        RAISE WARNING
            'validate: (2) new.start < start (%) && start (%) < new.end <= end (%)',
            conflict.stationierung_von,
            conflict.stationierung_von,
            conflict.stationierung_bis;
        RETURN conflict.stationierung_von || ' - ' || conflict.stationierung_bis;
    END IF;

    -- (3)
    SELECT * INTO conflict FROM kartierabschnitt
        WHERE (st_von >  stationierung_von AND
               st_bis <  stationierung_bis AND
               gewaesser_kennzahl = gew_kenn);
    IF conflict.id <> 0 THEN
        RAISE WARNING 'validate: (3) new.start > start (%) && new.end < end (%)',
                       conflict.stationierung_von, conflict.stationierung_bis;
        RETURN conflict.stationierung_von || ' - ' || conflict.stationierung_bis;
    END IF;

    -- (4)
    SELECT * INTO conflict FROM kartierabschnitt
        WHERE (st_von < stationierung_von AND
               st_bis > stationierung_bis AND
               gewaesser_kennzahl = gew_kenn);
    IF conflict.id <> 0 THEN
        RAISE WARNING 'validate: (4) new.start < start (%) && new.end > end (%)',
                       conflict.stationierung_von, conflict.stationierung_bis;
        RETURN conflict.stationierung_von || ' - ' || conflict.stationierung_bis;
    END IF;

    RETURN '';
END;
$$ LANGUAGE plpgsql;


-- This procedure will update the code of all water sections after the one that
-- is specified by 'code'.
CREATE OR REPLACE FUNCTION update_major_sections(current_id INTEGER, code TEXT,
                                                 gew_kenn TEXT)
RETURNS INTEGER AS
$$ DECLARE
    count       INTEGER := 0;
    code_length INTEGER;
    left        INTEGER;
    right       TEXT;
    next_code   TEXT;
    next        kartierabschnitt%ROWTYPE;
BEGIN
    RAISE INFO 'watersection code: update_major_section(%,%)', current_id, code;
    FOR next IN SELECT * FROM kartierabschnitt
        WHERE gewaesser_kennzahl = gew_kenn
          AND cast(substring(gewaesser_abschnitt FROM 0 FOR 4) AS INTEGER)
              >= cast(code AS INTEGER) AND id <> current_id
    LOOP
        RAISE INFO 'watersection code: update section with id %', next.id;
        code_length := length(next.gewaesser_abschnitt);

        IF code_length = 3 THEN
            left      := cast(next.gewaesser_abschnitt AS INTEGER) + 1;
            next_code := lpad(cast(left AS TEXT), 3, '0');

            UPDATE kartierabschnitt SET gewaesser_abschnitt = next_code
                WHERE id = next.id;

            count := count + 1;
        ELSE
            left  := cast(substring(next.gewaesser_abschnitt FROM 0 FOR 4)
                          AS INTEGER) +1;
            right := substring(next.gewaesser_abschnitt
                               FROM 5 FOR code_length-4);

            next_code := left;
            next_code := lpad(next_code, 3, '0') || '.' || right;
            UPDATE kartierabschnitt SET gewaesser_abschnitt = next_code
                WHERE id = next.id;
        END IF;
    END LOOP;

    RAISE DEBUG '-- Updated % sections --', count;
    RETURN count;
END;
$$LANGUAGE plpgsql;


-- This procedure will update the code of all minor water sections in a given
-- section that is specified by 'code'.
CREATE OR REPLACE FUNCTION update_minor_sections(current_id INTEGER, code TEXT,
                                                 gew_kenn TEXT)
RETURNS INTEGER AS
$$ DECLARE
    count          INTEGER := 0;
    section_length INTEGER;
    tmp_length     INTEGER;
    tmp            TEXT;
    next_code      TEXT;
    next           kartierabschnitt%ROWTYPE;
    left           TEXT;
    right          TEXT;
BEGIN
    section_length := length(code);
    left  := substring(code FROM 0 FOR 4);
    right := cast(substring(code FROM 5 FOR section_length-4) AS INTEGER);

    RAISE DEBUG 'Update minor sections in %', left;

    FOR next IN SELECT * FROM kartierabschnitt
        WHERE gewaesser_kennzahl = gew_kenn
          AND substring(gewaesser_abschnitt FROM 0 FOR 4) = left
          AND id <> current_id
    LOOP
        tmp_length := length(next.gewaesser_abschnitt);
        tmp := substring(next.gewaesser_abschnitt FROM 5 FOR tmp_length-4);

        IF tmp >= right THEN
            count := count + 1;
            tmp   := cast(tmp AS INTEGER) + 1;
            UPDATE kartierabschnitt SET gewaesser_abschnitt = left || '.' || tmp
                WHERE id = next.id;
        END IF;
    END LOOP;

    RAISE DEBUG '-- Updated % minor sections--', count;
    RETURN count;
END;
$$ LANGUAGE plpgsql;


-- The target of this function is to split a section into two parts. This means,
-- that all tables relating to a 'Kartierabschnitt' has to be updated (copy data
-- relating to 'old' by performing insert statements).
CREATE OR REPLACE FUNCTION split_major_section(parent kartierabschnitt,
                                               child kartierabschnitt)
RETURNS VOID AS
$$ DECLARE
    subtyp kartierabschnitt_hat_gewaessersubtyp%ROWTYPE;
    bild   kartierabschnitt_hat_bild%ROWTYPE;
    le     laufentwicklung%ROWTYPE;
    lb     laufentwicklung_hat_laengsbaenke%ROWTYPE;
    ls     laufentwicklung_hat_laufstrukturen%ROWTYPE;
    lp     laengsprofil%ROWTYPE;
    bw     laengsprofil_hat_besonderheiten_wasserfuehrung%ROWTYPE;
    qp     querprofil%ROWTYPE;
    qm     querprofil_hat_messungen%ROWTYPE;
    ss     sohlenstruktur%ROWTYPE;
    sub    sohlenstruktur_hat_sohlensubstrat%ROWTYPE;
    struk  sohlenstruktur_hat_sohlenstrukturen%ROWTYPE;
    sb     sohlenstruktur_hat_belastung_sohle%ROWTYPE;
    us     uferstruktur%ROWTYPE;
    ustru  uferstruktur_hat_uferstrukturen%ROWTYPE;
    ubel   uferstruktur_hat_uferbelastungen%ROWTYPE;
    gu     gewaesserumfeld%ROWTYPE;
    gstruk gewaesserumfeld_hat_umfeldstrukturen%ROWTYPE;
    gschae gewaesserumfeld_hat_schaedlicheumfeldstrukturen%ROWTYPE;
BEGIN
    -- xxx kartierabschnitt
    RAISE NOTICE 'Copy items of Kartierabschnitt % to %', parent.id, child.id;
    UPDATE kartierabschnitt
        SET erfassungsdatum       = parent.erfassungsdatum,
            bearbeiter            = parent.bearbeiter,
            bild_id               = parent.bild_id,
            fliessrichtung_id     = parent.fliessrichtung_id,
            wasserfuehrung_id     = parent.wasserfuehrung_id,
            unterhaltungerkennbar = parent.unterhaltungerkennbar,
            sonderfall_id         = parent.sonderfall_id,
            erlaeuterung          = parent.erlaeuterung,
            gewaessertyp_id       = parent.gewaessertyp_id,
            seeausfluss           = parent.seeausfluss,
            gewaesserbreite_id    = parent.gewaesserbreite_id,
            beschreibung          = parent.beschreibung
        WHERE id = child.id;

    FOR subtyp IN SELECT * FROM kartierabschnitt_hat_gewaessersubtyp
                           WHERE kartierabschnitt_id = parent.id LOOP
        subtyp.kartierabschnitt_id = child.id;
        INSERT INTO kartierabschnitt_hat_gewaessersubtyp VALUES (subtyp.*);
    END LOOP;

    FOR bild IN SELECT * FROM kartierabschnitt_hat_bild
                         WHERE kartierabschnitt_id = parent.id LOOP
        bild.kartierabschnitt_id = child.id;
        INSERT INTO kartierabschnitt_hat_bild VALUES (bild.*);
    END LOOP;


    -- xxx laufentwicklung
    RAISE DEBUG 'Start copying "Laufentwicklung" items...';

    SELECT * FROM laufentwicklung INTO le WHERE kartierabschnitt_id = parent.id;
    le.kartierabschnitt_id := child.id;
    INSERT INTO laufentwicklung VALUES (le.*);

    FOR lb IN SELECT * FROM laufentwicklung_hat_laengsbaenke
                       WHERE kartierabschnitt_id = parent.id LOOP
        lb.kartierabschnitt_id := child.id;
        INSERT INTO laufentwicklung_hat_laengsbaenke VALUES (lb.*);
    END LOOP;

    FOR ls IN SELECT * FROM laufentwicklung_hat_laufstrukturen
                       WHERE kartierabschnitt_id = parent.id LOOP
        ls.kartierabschnitt_id := child.id;
        INSERT INTO laufentwicklung_hat_laufstrukturen VALUES (ls.*);
    END LOOP;


    -- xxx laengsprofil
    RAISE DEBUG 'Start copying "Laengsprofil" items...';

    SELECT * FROM laengsprofil INTO lp WHERE kartierabschnitt_id = parent.id;
    lp.kartierabschnitt_id := child.id;
    INSERT INTO laengsprofil VALUES (lp.*);

    FOR bw IN SELECT * FROM laengsprofil_hat_besonderheiten_wasserfuehrung
                       WHERE kartierabschnitt_id = parent.id LOOP
        bw.kartierabschnitt_id := child.id;
        INSERT INTO laengsprofil_hat_besonderheiten_wasserfuehrung VALUES (bw.*);
    END LOOP;

    -- xxx querprofil
    RAISE DEBUG 'Start copying "Querprofil" items...';

    SELECT * FROM querprofil INTO qp WHERE kartierabschnitt_id = parent.id;
    qp.kartierabschnitt_id := child.id;
    INSERT INTO querprofil VALUES (qp.*);

    FOR qm IN SELECT * FROM querprofil_hat_messungen
                       WHERE kartierabschnitt_id = parent.id LOOP
        qm.kartierabschnitt_id := child.id;
        INSERT INTO querprofil_hat_messungen VALUES (qm.*);
    END LOOP;

    -- xxx sohlenstruktur
    RAISE DEBUG 'Start copying "Sohlenstruktur" items...';
    SELECT * FROM sohlenstruktur INTO ss WHERE kartierabschnitt_id = parent.id;
    ss.kartierabschnitt_id := child.id;
    INSERT INTO sohlenstruktur VALUES (ss.*);

    FOR sub IN SELECT * FROM sohlenstruktur_hat_sohlensubstrat
                        WHERE kartierabschnitt_id = parent.id LOOP
        sub.kartierabschnitt_id := child.id;
        INSERT INTO sohlenstruktur_hat_sohlensubstrat VALUES (sub.*);
    END LOOP;

    FOR struk IN SELECT * FROM sohlenstruktur_hat_sohlenstrukturen
                        WHERE kartierabschnitt_id = parent.id LOOP
        struk.kartierabschnitt_id := child.id;
        INSERT INTO sohlenstruktur_hat_sohlenstrukturen VALUES (struk.*);
    END LOOP;

    FOR sb IN SELECT * FROM sohlenstruktur_hat_belastung_sohle
                        WHERE kartierabschnitt_id = parent.id LOOP
        sb.kartierabschnitt_id := child.id;
        INSERT INTO sohlenstruktur_hat_belastung_sohle VALUES (sb.*);
    END LOOP;

    -- xxx uferstruktur
    RAISE DEBUG 'Start copying "Uferstruktur" items...';
    SELECT * FROM uferstruktur INTO us WHERE kartierabschnitt_id = parent.id;
    us.kartierabschnitt_id := child.id;
    INSERT INTO uferstruktur VALUES (us.*);

    FOR ustru IN SELECT * FROM uferstruktur_hat_uferstrukturen
                          WHERE kartierabschnitt_id = parent.id LOOP
        ustru.kartierabschnitt_id := child.id;
        INSERT INTO uferstruktur_hat_uferstrukturen VALUES (ustru.*);
    END LOOP;

    FOR ubel IN SELECT * FROM uferstruktur_hat_uferbelastungen
                          WHERE kartierabschnitt_id = parent.id LOOP
        ubel.kartierabschnitt_id := child.id;
        INSERT INTO uferstruktur_hat_uferbelastungen VALUES (ubel.*);
    END LOOP;

    -- xxx gewaesserumfeld
    RAISE DEBUG 'Start copying "Gewaesserumfeld" items...';
    SELECT * FROM gewaesserumfeld INTO gu WHERE kartierabschnitt_id = parent.id;
    gu.kartierabschnitt_id := child.id;
    INSERT INTO gewaesserumfeld VALUES (gu.*);

    FOR gstruk IN SELECT * FROM gewaesserumfeld_hat_umfeldstrukturen
                           WHERE kartierabschnitt_id = parent.id LOOP
        gstruk.kartierabschnitt_id := child.id;
        INSERT INTO gewaesserumfeld_hat_umfeldstrukturen VALUES (gstruk.*);
    END LOOP;

    FOR gschae IN SELECT * FROM gewaesserumfeld_hat_schaedlicheumfeldstrukturen
                           WHERE kartierabschnitt_id = parent.id LOOP
        gschae.kartierabschnitt_id := child.id;
        INSERT INTO gewaesserumfeld_hat_schaedlicheumfeldstrukturen
               VALUES (gschae.*);
    END LOOP;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS update_watersection_trigger
    ON kartierabschnitt;

-- Create a trigger that is called before an insert on 'kartierabschnitt' takes
-- place. This trigger will determine a watersection code for the given section
-- update sections that are behind the current one.
CREATE TRIGGER update_watersection_trigger AFTER INSERT
    ON kartierabschnitt
    FOR EACH ROW EXECUTE PROCEDURE determine_watersection_func();

-- END TRANSACTION
END;
