-- 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;

INSERT INTO gewaesser (
    SELECT DISTINCT gwk, '-no name-' FROM route_35833);

-- A function that returns the station depending on a given water and a wkt
-- point
CREATE OR REPLACE FUNCTION stationierung_func(digipunkt TEXT, w_gwk_achs NUMERIC)
RETURNS NUMERIC AS '
SELECT (ST_Length(foo.the_geom) * ST_line_locate_point(
    foo.the_geom, ST_GeomFromText($1, 35833)))::numeric
 FROM
(SELECT the_geom from route_35833 WHERE gwk = $2) as foo;
' LANGUAGE 'SQL';


-- A function that returns a geometry depending on a given water and a station
CREATE OR REPLACE FUNCTION geom_by_station_func(km NUMERIC, w_gwk_achs NUMERIC)
RETURNS geometry AS
$$
    SELECT ST_line_interpolate_point(foo.the_geom,
                                     $1/ST_Length(foo.the_geom))::geometry
        FROM (SELECT the_geom FROM route_35833 WHERE gwk = $2) AS foo;
$$ LANGUAGE SQL;


-- This function takes a start and an end point (as WKT) and searches for a
-- valid water. The bbox parameter is used to reduce the number of geometries
-- that are used for this search.
CREATE OR REPLACE FUNCTION gewaesser_kennung_by_station(von      TEXT,
                                                        bis      TEXT,
                                                        bbox     TEXT,
                                                        max_dist NUMERIC)
RETURNS TEXT AS
$$
DECLARE
    first  route_35833%ROWTYPE;
    second route_35833%ROWTYPE;
BEGIN
    RAISE INFO 'gew by station: gew_kennung_by_station(%, %, %, %)',
        von, bis, bbox, max_dist;

    -- iterate about all waters nearby the start point
    FOR first IN SELECT * FROM route_35833
        WHERE ST_Intersects(ST_GeomFromText(bbox, 35833), the_geom)
          AND ST_Distance(ST_GeomFromText(von, 35833), the_geom) < max_dist
        ORDER BY ST_Distance(ST_GeomFromText(von, 35833), the_geom)
    LOOP

        -- iterate about all waters nearby the end point
        FOR second IN SELECT * FROM route_35833
            WHERE ST_Intersects(ST_GeomFromText(bbox, 35833), the_geom)
              AND ST_Distance(ST_GeomFromText(bis, 35833), the_geom) < max_dist
            ORDER BY ST_Distance(ST_GeomFromText(bis, 35833), the_geom)
        LOOP
            IF first.gwk = second.gwk THEN
                RAISE INFO 'gew by station: Determined gew_kennung: %' , first.gwk;
                RETURN cast(first.gwk AS TEXT);
            END IF;
        END LOOP;
    END LOOP;

    RAISE INFO 'gew by station: Could not determine gew_kennung!';
    RETURN '';
END;
$$ LANGUAGE plpgsql;


-- A trigger function that will update the geometry of a 'kartierabschnitt'
-- after an UPDATE or INSERT.
CREATE OR REPLACE FUNCTION update_kartierabschnitt_func()
RETURNS TRIGGER AS
$$
DECLARE

BEGIN
    RAISE DEBUG 'TRIGGER: Update geometry of a kartierabschnitt.';

    IF (TG_OP = 'UPDATE') THEN
        IF NEW.stationierung_von = OLD.stationierung_von AND
           NEW.stationierung_bis = OLD.stationierung_bis THEN
            RAISE INFO 'TRIGGER: No geometry update necessary.';
            RETURN NEW;
        END IF;
    END IF;

    RAISE INFO 'TRIGGER: Update geometry now!';
    PERFORM update_geometry_func(NEW.id,
                                 cast(NEW.gewaesser_kennzahl AS NUMERIC),
                                 NEW.stationierung_von,
                                 NEW.stationierung_bis);
    RETURN NEW;
END;
$$LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_kartierabschnitt_geom ON kartierabschnitt;
CREATE TRIGGER update_kartierabschnitt_geom AFTER INSERT OR UPDATE
    ON kartierabschnitt FOR EACH ROW EXECUTE PROCEDURE
        update_kartierabschnitt_func();
CREATE OR REPLACE FUNCTION sub_geometry_func(gew_kenn NUMERIC,
                                             von NUMERIC,
                                             bis NUMERIC)
RETURNS geometry AS
$$
    SELECT ST_Line_SubString(
        (SELECT the_geom FROM route_35833 WHERE gwk = $1),
        (SELECT ST_Line_Locate_Point(
            the_geom, (SELECT geom_by_station_func($2, $1)))),
        (SELECT ST_Line_Locate_Point(
            the_geom, (SELECT geom_by_station_func($3, $1)))))
        FROM route_35833 WHERE gwk = $1;
$$ LANGUAGE SQL;
END; 

-- A function that creates a geometry depending on the given station parameters.
-- A kartierabschnitt with the ID referenced by 'kid' is updated with that geom.
CREATE OR REPLACE FUNCTION update_geometry_func(kid NUMERIC, gew_kenn NUMERIC,
                                                von NUMERIC, bis  NUMERIC)
RETURNS VOID AS
$$
    UPDATE kartierabschnitt
        SET the_geom = (SELECT sub_geometry_func($2, $3, $4))
        WHERE id = $1;
$$ LANGUAGE SQL;

END;
