# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1635187455 -7200
# Node ID 1fba5766d82046d7b17f9896e4db28aa8bf4d9a8
# Parent  cb62985ec252d43cff061ace2f1541eba1df9823
WIP: More on migration script.

diff -r cb62985ec252 -r 1fba5766d820 schema/updates/1469/aggregate-gm-logs.sql
--- a/schema/updates/1469/aggregate-gm-logs.sql	Mon Oct 25 15:43:38 2021 +0200
+++ b/schema/updates/1469/aggregate-gm-logs.sql	Mon Oct 25 20:44:15 2021 +0200
@@ -6,6 +6,10 @@
        WHERE im.kind = 'gm'
        ORDER BY lo.import_id, lo.time;
   last_id integer;
+  last_time timestamp with time zone;
+  curr_gid text;
+  gauge text;
+  num integer;
 BEGIN
 
   CREATE TEMP TABLE filtered_logs (
@@ -28,14 +32,80 @@
       predictions    integer NOT NULL DEFAULT 0
   );
   last_id := -1;
+
   FOR line IN logs LOOP
+
     IF last_id <> line.import_id THEN
-       RAISE NOTICE '%', line.import_id;
+       --RAISE NOTICE '%', line.import_id;
        last_id := line.import_id;
        -- TODO: Generated log lines from agg_tracker
        TRUNCATE agg_tracker;
+
+    ELSIF line.msg ~ '^Found measurements/predictions for ....................$' THEN
+       curr_gid := substring(
+          line.msg from '^Found measurements/predictions for (....................)$');
+
+    ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN
+        num := substring(
+            line.msg from '^Inserted (\d+)')::integer;
+        gauge := substring(
+            line.msg from '^Inserted \d+ measurements for (....................)$');
+
+        INSERT INTO agg_tracker (gid, measurements) VALUES (gauge, num)
+          ON CONFLICT (gid)
+          DO UPDATE SET measurements = EXCLUDED.measurements + num;
+
+    ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN
+        num := substring(
+            line.msg from '^Ignored (\d+)')::integer;
+
+        INSERT INTO agg_tracker (gid, bad_values) VALUES (curr_gid, num)
+          ON CONFLICT (gid)
+          DO UPDATE SET bad_values = EXCLUDED.bad_values + num;
+
+    ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN
+        num := substring(
+            line.msg from '^Inserted (\d+)')::integer;
+        gauge := substring(
+            line.msg from '^Inserted \d+ predictions for (....................)$');
+
+        INSERT INTO agg_tracker (gid, predictions) VALUES (gauge, num)
+          ON CONFLICT (gid)
+          DO UPDATE SET predictions = EXCLUDED.predictions + num;
+
+    ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN
+
+        INSERT INTO agg_tracker (gid, assume_zpg) VALUES (gauge, true)
+          ON CONFLICT (gid)
+          DO UPDATE SET assume_zpg = true;
+
+    ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN
+        gauge := substring(
+            line.msg from '^Cannot find gauge "(....................)" for import$');
+
+        INSERT INTO agg_tracker (gid, unknown) VALUES (gauge, true)
+          ON CONFLICT (gid)
+          DO UPDATE SET unknown = true;
+
+    ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN
+
+        INSERT INTO agg_tracker (gid, assume_cm) VALUES (gauge, 1)
+          ON CONFLICT (gid)
+          DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1;
+
+    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 'Ignored message with measure_code %s'
+        -- TODO: Handle "unknown unit '%s'"
+        -- Not handled, copy through ..
+        INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg);
+
     END IF;
-    -- TODO: insert lines into filtered_logs or aggregate in agg_tracker
+
+    last_time := line.time;
   END LOOP;
 
   -- TODO: Handle remains from agg_tracker