From 6244335f0f9807c3df58319367f5ff7340f61732 Mon Sep 17 00:00:00 2001
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
Date: Fri, 27 Oct 2023 15:30:52 +0200
Subject: [PATCH] Store messages with dates in them, for future sync/matching.

---
 skvidarlang.sql |  9 +++++++++
 www/event.pl    | 18 +++++++++++++++++-
 2 files changed, 26 insertions(+), 1 deletion(-)

diff --git a/skvidarlang.sql b/skvidarlang.sql
index ae0fb8d..536a1d3 100644
--- a/skvidarlang.sql
+++ b/skvidarlang.sql
@@ -15,6 +15,13 @@ CREATE TABLE reaction_log (
 );
 CREATE INDEX lookup_idx ON reaction_log USING btree (channel, ts, userid, reaction, event_ts DESC);
 
+CREATE TABLE message_sheet_link (
+    channel VARCHAR NOT NULL,
+    ts VARCHAR NOT NULL,
+    sheet_title VARCHAR NOT NULL,
+    PRIMARY KEY (channel, ts)
+);
+
 CREATE VIEW current_reactions AS
   SELECT userid, channel, ts, reaction
     FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
@@ -25,4 +32,6 @@ CREATE VIEW current_reactions AS
 GRANT SELECT,INSERT,DELETE ON TABLE applied TO skvidarlang;
 GRANT SELECT,INSERT ON TABLE reaction_log TO skvidarlang;
 GRANT SELECT ON TABLE current_reactions TO skvidarlang;
+GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
+
 
diff --git a/www/event.pl b/www/event.pl
index 75106df..04e7cda 100755
--- a/www/event.pl
+++ b/www/event.pl
@@ -44,11 +44,27 @@ if (exists($json->{'challenge'})) {
 
 if (exists($json->{'event'}) && exists($json->{'event'}{'type'})) {
 	my $type = $json->{'event'}{'type'};
+	my $user = $json->{'event'}{'user'};
+
+	if ($type eq 'message') {
+		if ($json->{'event'}{'message'}{'text'} =~ /(20\d{2}-\d{2}-\d{2})/) {
+			# TODO: What if edits happen out-of-order?
+			my $date = $1;
+			my $channel = $json->{'event'}{'channel'};
+			my $ts = $json->{'event'}{'ts'};
+			print "Matching message {$channel, $ts} to date $date\n";
+			$dbh->do('INSERT INTO message_sheet_link (channel, ts, sheet_title) VALUES (?,?,?)', undef,
+				$channel, $ts, $date);
+		} else {
+			print STDERR "No date found in message, ignoring\n";
+		}
+		exit;
+	}
+
 	my $reaction = $json->{'event'}{'reaction'};
 	my $channel = $json->{'event'}{'item'}{'channel'};
 	my $ts = $json->{'event'}{'item'}{'ts'};
 	my $event_ts = $json->{'event'}{'event_ts'};
-	my $user = $json->{'event'}{'user'};
 
 	if (!defined($channel) || !defined($ts)) {
 		print STDERR "Not reacting to a message; ignoring.\n";
-- 
2.39.2