qgsadapt_zoom: "
SELECT
    min(x) AS xmin,
    min(y) AS ymin,
    max(x) AS xmax,
    max(y) AS ymax
FROM (
    SELECT x(coalesce(geop, centroid(geom))) AS x, 
           y(coalesce(geop, centroid(geom))) AS y
    FROM schaechte
    UNION 
    SELECT x(centroid(geom)) AS x, 
           y(centroid(geom)) AS y
    FROM haltungen
)"

qgsadapt_proj: "
SELECT srid
FROM geom_cols_ref_sys
WHERE Lower(f_table_name) = Lower('schaechte')
AND Lower(f_geometry_column) = Lower('geom')"


tools_knotentyp_anf: "
UPDATE schaechte SET knotentyp = 'Anfangsschacht' WHERE schaechte.schnam IN
(SELECT t_sch.schnam
FROM schaechte AS t_sch 
LEFT JOIN haltungen AS t_hob
ON t_sch.schnam = t_hob.schoben
LEFT JOIN haltungen AS t_hun
ON t_sch.schnam = t_hun.schunten
WHERE t_hun.pk IS NULL);"

tools_knotentyp_end: "
UPDATE schaechte SET knotentyp = 'Endschacht' WHERE schaechte.schnam IN
(SELECT t_sch.schnam
FROM schaechte AS t_sch 
JOIN haltungen AS t_hob                 /* nur Schaechte mit Haltung oben */
ON t_sch.schnam = t_hob.schunten
LEFT JOIN haltungen AS t_hun
ON t_sch.schnam = t_hun.schoben
WHERE t_hun.pk IS NULL);"

tools_knotentyp_hoch: "
UPDATE schaechte SET knotentyp = 'Hochpunkt' WHERE schaechte.schnam IN
( SELECT t_sch.schnam
  FROM schaechte AS t_sch 
  JOIN haltungen AS t_hob
  ON t_sch.schnam = t_hob.schunten
  JOIN haltungen AS t_hun
  ON t_sch.schnam = t_hun.schoben
  JOIN schaechte AS t_sun
  ON t_sun.schnam = t_hun.schunten
  JOIN schaechte AS t_sob
  ON t_sob.schnam = t_hob.schunten
  WHERE ifnull(t_hob.sohleunten,t_sch.sohlhoehe)>ifnull(t_hob.sohleoben,t_sob.sohlhoehe) AND 
        ifnull(t_hun.sohleoben,t_sch.sohlhoehe)>ifnull(t_hun.sohleunten,t_sun.sohlhoehe));"

tools_knotentyp_tief: "
UPDATE schaechte SET knotentyp = 'Tiefpunkt' WHERE schaechte.schnam IN
( SELECT t_sch.schnam
  FROM schaechte AS t_sch 
  JOIN haltungen AS t_hob
  ON t_sch.schnam = t_hob.schunten
  JOIN haltungen AS t_hun
  ON t_sch.schnam = t_hun.schoben
  JOIN schaechte AS t_sun
  ON t_sun.schnam = t_hun.schunten
  JOIN schaechte AS t_sob
  ON t_sob.schnam = t_hob.schunten
  WHERE ifnull(t_hob.sohleunten,t_sch.sohlhoehe)<ifnull(t_hob.sohleoben,t_sob.sohlhoehe) AND 
        ifnull(t_hun.sohleoben,t_sch.sohlhoehe)<ifnull(t_hun.sohleunten,t_sun.sohlhoehe));"

tools_knotentyp_zweig: "
UPDATE schaechte SET knotentyp = 'Verzweigung' WHERE schaechte.schnam IN
( SELECT t_sch.schnam
  FROM schaechte AS t_sch 
  JOIN haltungen AS t_hun
  ON t_sch.schnam = t_hun.schoben
  GROUP BY t_sch.pk
  HAVING count(*) > 1);"

tools_knotentyp_einzel: "
UPDATE schaechte SET knotentyp = 'Einzelschacht' WHERE schaechte.schnam IN
( SELECT t_sch.schnam 
  FROM schaechte AS t_sch 
  LEFT JOIN haltungen AS t_hun
  ON t_sch.schnam = t_hun.schoben
  LEFT JOIN haltungen AS t_hob
  ON t_sch.schnam = t_hob.schunten
  WHERE t_hun.pk IS NULL AND t_hob.pk IS NULL)"

# Datenuebernahme aus Schaechten
tools_create_trig_new_hal: "
CREATE TRIGGER IF NOT EXISTS trig_new_hal
AFTER INSERT ON haltungen
BEGIN
    UPDATE haltungen 
    SET haltnam = q.haltnam 
    FROM (
        SELECT
            coalesce(new.haltnam, s.schnam || '.1') AS haltnam
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, 1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = ST_PointN(new.geom, 1))
    ) AS q
    WHERE pk = new.pk;

    UPDATE haltungen SET
    (schoben, sohleoben) = (q.schoben, q.sohleoben)
    FROM (
        SELECT
            coalesce(new.schoben, s.schnam) AS schoben, 
            coalesce(new.sohleoben, s.sohlhoehe) AS sohleoben
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, 1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = ST_PointN(new.geom, 1))
    ) AS q
    WHERE pk = new.pk;

    UPDATE haltungen SET
    (schunten, sohleunten) = (q.schunten, q.sohleunten)
    FROM (
        SELECT
            coalesce(new.schunten, s.schnam) AS schunten, 
            coalesce(new.sohleunten, s.sohlhoehe) AS sohleunten
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, -1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = ST_PointN(new.geom, -1))
    ) AS q
    WHERE pk = new.pk;
END;"

# Datenuebernahme aus Schaechten, dabei Schacht nur, wenn leer
tools_create_trig_mod_hal_nul: "
CREATE TRIGGER IF NOT EXISTS trig_mod_hal_nul
AFTER UPDATE OF geom ON haltungen
BEGIN
    UPDATE haltungen
    SET (schoben, sohleoben) = (coalesce(old.schnam, q.schnam), coalesce(old.sohleoben, q.sohlhoehe))
    FROM (
        SELECT
            s.schnam,
            s.sohlhoehe
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s ON TRUE
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, 1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = buffer(ST_PointN(new.geom, 1), 0.1))
    ) AS q
    WHERE pk = old.pk;

    UPDATE haltungen
    SET (schunten, sohleunten) = (coalesce(old.schnam, q.schnam), coalesce(old.sohleunten, q.sohlhoehe))
    FROM (
        SELECT
            s.schnam,
            s.sohlhoehe
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s ON TRUE
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, -1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = buffer(ST_PointN(new.geom, -1), 0.1))
    ) AS q
    WHERE pk = old.pk;
END;"

# Datenuebernahme aus Schaechten, dabei Schacht immer
tools_create_trig_mod_hal_all: "
CREATE TRIGGER IF NOT EXISTS trig_mod_hal_all
AFTER UPDATE OF geom ON haltungen
BEGIN
    UPDATE haltungen
    SET (schoben, sohleoben) = (coalesce(q.schnam, old.schoben), coalesce(old.sohleoben, q.sohlhoehe))
    FROM (
        SELECT
            s.schnam,
            s.sohlhoehe
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s ON TRUE
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, 1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = ST_PointN(new.geom, 1))
    ) AS q
    WHERE pk = old.pk;

    UPDATE haltungen
    SET (schunten, sohleunten) = (coalesce(q.schnam, old.schunten), coalesce(NEW.sohleunten, old.sohleunten, q.sohlhoehe))
    FROM (
        SELECT
            s.schnam,
            s.sohlhoehe
        FROM (SELECT 1) AS temp
        LEFT JOIN schaechte AS s ON TRUE
        WHERE ST_Within(s.geop, buffer(ST_PointN(new.geom, -1), 0.1)) = 1
        AND s.ROWID IN (
            SELECT ROWID
            FROM SpatialIndex
            WHERE f_table_name = 'schaechte'
                AND F_geometry_column = 'geop'
                AND search_frame = ST_PointN(new.geom, -1))
    ) AS q
    WHERE pk = old.pk;
END;"

# Änderungen an der Profilbezeichnung in der Referenztabelle in Haltungen nachführen
tools_create_trig_ref_profile: "
CREATE TRIGGER IF NOT EXISTS trig_ref_profile AFTER UPDATE OF profilnam ON profile
BEGIN
    UPDATE haltungen
    SET profilnam = new.profilnam
    WHERE profilnam = old.profilnam AND profilnam IS NOT NULL;
    UPDATE anschlussleitungen
    SET profilnam = new.profilnam
    WHERE profilnam = old.profilnam AND profilnam IS NOT NULL;
END;"

# Änderungen an der Bezeichnung in der Referenztabelle in Haltungen nachführen
tools_create_trig_ref_entwart: "
CREATE TRIGGER IF NOT EXISTS trig_ref_entwart AFTER UPDATE OF bezeichnung ON entwaesserungsarten
BEGIN
    UPDATE haltungen
    SET entwart = new.bezeichnung
    WHERE entwart = old.bezeichnung AND entwart IS NOT NULL;
    UPDATE schaechte
    SET entwart = new.bezeichnung
    WHERE entwart = old.bezeichnung AND entwart IS NOT NULL;
    UPDATE anschlussleitungen
    SET entwart = new.bezeichnung
    WHERE entwart = old.bezeichnung AND entwart IS NOT NULL;
END;"

# Änderungen an der Bezeichnung in der Referenztabelle in Haltungen nachführen
tools_create_trig_ref_simstatus: "
CREATE TRIGGER IF NOT EXISTS trig_ref_simstatus AFTER UPDATE OF bezeichnung ON simulationsstatus
BEGIN
    UPDATE haltungen
    SET simstatus = new.bezeichnung
    WHERE simstatus = old.bezeichnung AND simstatus IS NOT NULL;
    UPDATE schaechte
    SET simstatus = new.bezeichnung
    WHERE simstatus = old.bezeichnung AND simstatus IS NOT NULL;
    UPDATE anschlussleitungen
    SET simstatus = new.bezeichnung
    WHERE simstatus = old.bezeichnung AND simstatus IS NOT NULL;
END;"

# Änderungen an der Bezeichnung in der Referenztabelle in Haltungen nachführen
tools_create_trig_ref_material: "
CREATE TRIGGER IF NOT EXISTS trig_ref_material AFTER UPDATE OF bezeichnung ON material
BEGIN
    UPDATE haltungen
    SET material = new.bezeichnung
    WHERE material = old.bezeichnung AND material IS NOT NULL;
    UPDATE schaechte
    SET material = new.bezeichnung
    WHERE material = old.bezeichnung AND material IS NOT NULL;
    UPDATE anschlussleitungen
    SET material = new.bezeichnung
    WHERE material = old.bezeichnung AND material IS NOT NULL;
END;"

# Änderungen an der Bezeichnung in der Detailtabelle nachführen
tools_create_trig_ref_abflussparameter: "
CREATE TRIGGER IF NOT EXISTS trig_ref_abflussparameter AFTER UPDATE OF apnam ON abflussparameter
BEGIN
    UPDATE flaechen
    SET abflussparameter = new.apnam
    WHERE abflussparameter = old.apnam AND abflussparameter IS NOT NULL;
    UPDATE tezg
    SET abflussparameter = new.apnam
    WHERE abflussparameter = old.apnam AND abflussparameter IS NOT NULL;
END;"

tools_drop_trig_new_hal: "
DROP TRIGGER IF EXISTS trig_new_hal;"

tools_drop_trig_mod_hal_all: "
DROP TRIGGER IF EXISTS trig_mod_hal_all;"

tools_drop_trig_mod_hal_nul: "
DROP TRIGGER IF EXISTS trig_mod_hal_nul;"

tools_drop_trig_ref_profile: "
DROP TRIGGER IF EXISTS trig_ref_profile;"

tools_drop_trig_ref_entwart: "
DROP TRIGGER IF EXISTS trig_ref_entwart;"

tools_drop_trig_ref_simstatus: "
DROP TRIGGER IF EXISTS trig_ref_simstatus;"

tools_drop_trig_ref_material: "
DROP TRIGGER IF EXISTS trig_ref_material;"

tools_drop_trig_ref_abflussparameter: "
DROP TRIGGER IF EXISTS trig_ref_abflussparameter;"

# Check der Trigger

tools_list_triggers_ref: "
SELECT name FROM sqlite_master WHERE type = 'trigger' AND substr(name, 1, 9) = 'trig_ref_';"

tools_check_triggers_fang_schacht: "
SELECT name FROM sqlite_master
WHERE type = 'trigger' 
  AND name in ('trig_mod_hal_all', 'trig_mod_hal_nul', 'trig_new_hal');"
