--- a/schema/updates/1469/aggregate-gm-logs.sql	Tue Oct 26 00:05:28 2021 +0200
+++ b/schema/updates/1469/aggregate-gm-logs.sql	Tue Oct 26 01:15:30 2021 +0200
@@ -1,6 +1,6 @@
 DO $$
 DECLARE
-  logs CURSOR FOR SELECT
+  logs NO SCROLL CURSOR FOR SELECT
        lo.* FROM import.imports im JOIN import.import_logs lo
        ON lo.import_id = im.id
        WHERE im.kind = 'gm'
@@ -36,6 +36,8 @@
 
   FOR line IN logs LOOP
 
+    -- RAISE NOTICE '%', line.msg;
+
     IF last_id <> line.import_id THEN
 
         -- unknown
@@ -85,8 +87,33 @@
         END IF;
 
 
-        -- TODO: ""Missing mandatory values: "
-        -- TODO: "Cannot convert units: "
+        -- missing mandatory values
+        SELECT INTO joined string_agg(
+            gid || ' ('
+                || array_to_string(ARRAY(SELECT DISTINCT unnest(missing_values)), '; ')
+                || ')', ', ')
+            FROM agg_tracker
+            WHERE missing_values IS NOT NULL;
+
+        IF joined IS NOT NULL THEN
+            INSERT INTO filtered_logs VALUES (
+                last_id, last_time, 'warn'::log_type,
+                'Missing mandatory values: ' || joined);
+        END IF;
+
+        -- convert units
+        SELECT INTO joined string_agg(
+            gid || ' ('
+                || array_to_string(ARRAY(SELECT DISTINCT unnest(rescale_errors)), '; ')
+                || ')', ', ')
+            FROM agg_tracker
+            WHERE rescale_errors IS NOT NULL;
+
+        IF joined IS NOT NULL THEN
+            INSERT INTO filtered_logs VALUES (
+                last_id, last_time, 'error'::log_type,
+                'Cannot convert units: ' || joined);
+        END IF;
 
         -- ignored measure codes
         SELECT INTO joined string_agg(
@@ -138,8 +165,9 @@
         END IF;
 
 
-       TRUNCATE agg_tracker;
-       last_id := line.import_id;
+        -- reset aggregate table
+        TRUNCATE agg_tracker;
+        last_id := line.import_id;
 
     ELSIF line.msg ~ '^Found measurements/predictions for ....................$' THEN
        curr_gid := substring(
@@ -202,13 +230,31 @@
           DO UPDATE SET ign_meas_codes =
             array_cat(agg_tracker.ign_meas_codes, EXCLUDED.ign_meas_codes);
 
+    ELSIF line.msg ~ '^Missing mandatory value at [^.]+\.' THEN
+        value := substring(
+            line.msg from '^Missing mandatory value at ([^.]+)\.');
+
+        INSERT INTO agg_tracker (gid, missing_values) VALUES (curr_gid, array[value])
+          ON CONFLICT (gid)
+          DO UPDATE SET missing_values =
+            array_cat(agg_tracker.missing_values, EXCLUDED.missing_values);
+
+    ELSIF line.msg ~ '^unknown unit ''[^'']*''' THEN
+
+        value := substring(
+            line.msg from '^unknown unit ''([^'']*)''');
+
+        INSERT INTO agg_tracker (gid, rescale_errors) VALUES (curr_gid, array[value])
+          ON CONFLICT (gid)
+          DO UPDATE SET rescale_errors =
+            array_cat(agg_tracker.rescale_errors, EXCLUDED.rescale_errors);
+
     ELSIF line.msg ~ '^Importing gauge measurements took ' THEN
 
         -- TODO: Flush aggregation because its likely the last entry.
     ELSE
-        -- TODO: Handle 'Missing mandatory value at %s. Ignored (bad service)'
-        -- TODO: Handle "unknown unit '%s'"
         -- Not handled, copy through ..
+        RAISE NOTICE '%', line.msg;
         INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg);
 
     END IF;
@@ -223,6 +269,10 @@
 
   -- INSERT INTO import.import_logs SELECT * FROM import.filtered_logs;
 
+  SELECT INTO num count(*) FROM filtered_logs;
+
+  RAISE NOTICE 'number of new gm log message entries: %', num;
+
   DROP TABLE filtered_logs;
   DROP TABLE agg_tracker;
 END $$;