From 1c64ac4a27f325dabcc622347b9c192aa7e5d80b Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Mon, 29 Sep 2025 23:03:33 +0000
Subject: [PATCH] Convert into the FOLLOWS table

---
 conversion/load_data.sql | 72 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 72 insertions(+)

diff --git a/conversion/load_data.sql b/conversion/load_data.sql
index 4649283..a437855 100644
--- a/conversion/load_data.sql
+++ b/conversion/load_data.sql
@@ -204,6 +204,78 @@ INSERT INTO comm_memb_log (
     ORDER BY date_of_update, chimp_id, update_rationale, made_by;
 
 
+--
+-- Follow stuff
+--
+
+-- Sanity checks
+DO $$
+BEGIN
+-- Start of follow time is NULL
+PERFORM * FROM clean.follow_arrival WHERE fa_time_start IS NULL;
+IF FOUND THEN
+  RAISE data_exception USING
+    MESSAGE = 'There are follow_arrivals where fa_time_start is NULL';
+END IF;
+
+-- End of follow time is NULL
+PERFORM * FROM clean.follow_arrival WHERE fa_time_end IS NULL;
+IF FOUND THEN
+  RAISE data_exception USING
+    MESSAGE = 'There are follow_arrivals where fa_time_end is NULL';
+END IF;
+
+-- follow_arrival.fa_type_of_nesting has a valid value
+PERFORM *
+  FROM clean.follow_arrival
+  WHERE fa_type_of_nesting NOT IN (0, 1, 2, 3);
+IF FOUND THEN
+  RAISE data_exception USING
+    MESSAGE = 'There are follow_arrivals where fa_type_of_nesting is not '
+              || '0, 1, 2, 3, 4';
+END IF;
+
+-- follow.fol_flag_begin_in_nest is not 0 or 1
+PERFORM *
+  FROM clean.follow
+  WHERE fol_flag_begin_in_nest <> 0 AND fol_flag_begin_in_nest <> 1;
+IF FOUND THEN
+  RAISE data_exception USING
+    MESSAGE = 'There are follows where fol_flag_begin_in_nest is not '
+              || '0 or 1';
+END IF;
+
+-- follow.fol_flag_end_in_nest is not 0 or 1
+PERFORM *
+  FROM clean.follow
+  WHERE fol_flag_end_in_nest <> 0 AND fol_flag_end_in_nest <> 1;
+IF FOUND THEN
+  RAISE data_exception USING
+    MESSAGE = 'There are follows where fol_flag_end_in_nest is not '
+              || '0 or 1';
+END IF;
+
+END;
+$$;
+
+--
+-- follow -> FOLLOWS
+--
+INSERT INTO follows (
+  focal
+, community
+, date
+, notes)
+SELECT upper(  -- problem #31)
+             btrim(  -- Problem #32
+                   fol_b_animid))
+     , fol_cl_community_id
+     , fol_date
+     , ''
+  FROM clean.follow
+  ORDER BY fol_b_animid, fol_date;
+
+
 --
 -- Finish
 --
-- 
2.34.1