From 8f4a2a4e0c9ccedb07e1d0590620ed6ba8435b17 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Tue, 12 May 2026 15:57:24 +0000
Subject: [PATCH] Convert AGGRESSION_EVENT_LOG table

---
 conversion/Makefile                      |  7 ++++
 conversion/load_aggression_event_log.sql | 53 ++++++++++++++++++++++++
 2 files changed, 60 insertions(+)
 create mode 100644 conversion/load_aggression_event_log.sql

diff --git a/conversion/Makefile b/conversion/Makefile
index 7e9d394..a0594da 100644
--- a/conversion/Makefile
+++ b/conversion/Makefile
@@ -300,6 +300,7 @@ after_compare_state: load_data
 .PHONY: load_data
 load_data: load_support load_biography load_community_membership \
            load_biography_update_log load_community_membership_update_log \
+           load_aggression_event_log \
            load_follow_to_follows load_follow_to_other \
            load_follow_arrival_sanity load_follow_arrival \
            load_finish
@@ -336,6 +337,12 @@ load_community_membership_update_log: $(PSQL_DEPENDS)
 	  cat load_community_membership_update_log.sql ;) \
 	| psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
 
+.PHONY: load_aggression_event_log
+load_aggression_event_log: $(PSQL_DEPENDS)
+	( $(PSQL_SETUP) \
+	  cat load_aggression_event_log.sql ;) \
+	| psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
 .PHONY: load_follow_to_follows
 load_follow_to_follows: $(PSQL_DEPENDS)
 	( $(PSQL_SETUP) \
diff --git a/conversion/load_aggression_event_log.sql b/conversion/load_aggression_event_log.sql
new file mode 100644
index 0000000..cf76e69
--- /dev/null
+++ b/conversion/load_aggression_event_log.sql
@@ -0,0 +1,53 @@
+-- Copyright (C) 2026 The Meme Factory, Inc.  http://www.karlpinc.com/
+--
+--   This program is free software: you can redistribute it and/or modify
+--   it under the terms of the GNU Affero General Public License as
+--   published by the Free Software Foundation, either version 3 of the
+--   License, or (at your option) any later version.
+--
+--   This program is distributed in the hope that it will be useful,
+--   but WITHOUT ANY WARRANTY; without even the implied warranty of
+--   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+--   GNU Affero General Public License for more details.
+--
+--   You should have received a copy of the GNU Affero General Public License
+--   along with this program.  If not, see <https://www.gnu.org/licenses/>.
+--
+-- Karl O. Pinc <kop@karlpinc.com>
+
+-- Setup the search path
+SET search_path TO sokwedb, codes, lib, clean, libconv;
+
+
+-- aggression_event_log -> aggression_event_log
+INSERT INTO aggression_event_log (
+   year
+  ,community
+  ,brecenglish
+  ,arecswahili
+  ,arecenglishnotes
+  ,arecbookextract
+  ,brecswahili
+  ,brecbookextract
+  ,mainfbookextract
+  ,mainf
+  ,schroepfer
+  ,notes)
+  SELECT
+     year
+    ,community
+    ,COALESCE(b_rec_english, '')
+    ,COALESCE(a_rec_swahili, '')
+    ,COALESCE(a_rec_english_notes, '')
+    ,COALESCE(a_rec_book_extract, '')
+    ,COALESCE(b_rec_swahili, '')
+    ,COALESCE(b_rec_book_extract, '')
+    ,COALESCE(ma_inf_book_extract, '')
+    ,COALESCE(ma_inf, '')
+    ,COALESCE(schroepfer, '')
+    ,COALESCE(notes, '')
+    FROM clean.aggression_event_log as log
+    -- Get a consistent id generation
+    ORDER BY year, community;
+
+ANALYZE aggression_event_log;
-- 
2.34.1