# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1477589432 -7200
# Node ID 2f42a7607bbdbc5731c3c16e91d90a9575f12858
# Parent  ccd077c29f721638f10adf5f39102f280e29e1ae
Client and server now expect these combinations to be modelled, too.

diff -r ccd077c29f72 -r 2f42a7607bbd db_schema/stammdaten_schema.sql
--- a/db_schema/stammdaten_schema.sql	Thu Oct 27 19:20:28 2016 +0200
+++ b/db_schema/stammdaten_schema.sql	Thu Oct 27 19:30:32 2016 +0200
@@ -556,7 +556,6 @@
     wert_id integer REFERENCES status_wert NOT NULL,
     UNIQUE(stufe_id, wert_id)
 );
--- 'zurückgesetzt' is left out here deliberately!
 INSERT INTO status_kombi VALUES (1, 1, 0);
 INSERT INTO status_kombi VALUES (2, 1, 1);
 INSERT INTO status_kombi VALUES (3, 1, 2);
@@ -570,6 +569,9 @@
 INSERT INTO status_kombi VALUES (11, 3, 2);
 INSERT INTO status_kombi VALUES (12, 3, 3);
 INSERT INTO status_kombi VALUES (13, 3, 4);
+INSERT INTO status_kombi VALUES (14, 1, 8);
+INSERT INTO status_kombi VALUES (15, 2, 8);
+INSERT INTO status_kombi VALUES (16, 3, 8);
 
 
 CREATE TABLE status_reihenfolge (
@@ -602,9 +604,12 @@
            INSERT INTO status_reihenfolge (von_id, zu_id)
                   VALUES (kombi_from.id, kombi_to.id);
 
-        ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN
+        ELSEIF s_to = s_from + 1
+               AND w_from <> 0 AND w_from <> 4
+               AND w_from <> 8 AND w_to <> 8 THEN
            -- Going to the next 'stufe' all available status_kombi are allowed
-           -- in case current wert is not 'nicht vergeben' or 'Rückfrage'
+           -- in case current wert is not 'nicht vergeben', 'Rückfrage' or
+           -- 'zurückgesetzt' and we are not trying to set 'zurückgesetzt'
            INSERT INTO status_reihenfolge (von_id, zu_id)
                   VALUES (kombi_from.id, kombi_to.id);
 
@@ -614,6 +619,16 @@
            INSERT INTO status_reihenfolge (von_id, zu_id)
                   VALUES (kombi_from.id, kombi_to.id);
 
+        ELSEIF w_to = 8 AND s_from = s_to THEN
+           -- 'zurückgesetzt' can only be set on the same 'stufe'
+           INSERT INTO status_reihenfolge (von_id, zu_id)
+                  VALUES (kombi_from.id, kombi_to.id);
+
+        ELSEIF w_from = 8 AND s_to = s_from - 1 THEN
+           -- after 'zurückgesetzt' always follows the next lower 'stufe'
+           INSERT INTO status_reihenfolge (von_id, zu_id)
+                  VALUES (kombi_from.id, kombi_to.id);
+
         END IF;
     END LOOP;
 END LOOP;