Initial commit.

diff --git a/bin/sync.pl b/bin/sync.pl
new file mode 100644 (file)
index 0000000..b26d90f
--- /dev/null
+++ b/bin/sync.pl
@@ -0,0 +1,439 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+no warnings qw(once);
+use Crypt::JWT;
+use JSON::XS;
+use LWP::UserAgent;
+use DBI;
+use POSIX;
+binmode STDOUT, ':utf8';
+binmode STDERR, ':utf8';
+use utf8;
+
+# TODO:
+# - support non-fixed sheets
+# - detect moves between groups
+
+require '../include/config.pm';
+
+my @log = ();
+
+sub get_oauth_bearer_token {
+       my $ua = shift;
+       my $now = time();
+       my $jwt = JSON::XS::encode_json({
+               "iss" => $config::jwt_key->{'client_email'},
+               "scope" => "https://www.googleapis.com/auth/spreadsheets",
+               "aud" => "https://www.googleapis.com/oauth2/v4/token",
+               "exp" => $now + 1800,
+               "iat" => $now,
+       });
+       my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
+       my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
+               'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
+               'assertion' => $jws_token ]);
+       return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
+}
+
+sub get_slack_name {
+       my ($ua, $userid) = @_;
+       my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
+              'Authorization' => 'Bearer ' . $config::oauth_token
+       ]);
+       my $response = $ua->request($req);
+       die $response->status_line if !$response->is_success;
+
+       my $user_json = JSON::XS::decode_json($response->decoded_content);
+       die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
+
+       return $user_json->{'user'}{'real_name'};
+}
+
+sub get_spreadsheet_name {
+       my $cell = shift;
+       my $name = $cell->{'userEnteredValue'}{'stringValue'};
+       return undef if (!defined($name));
+       return undef if ($name =~ /^G[1-4]\.[1-4]/);
+       $name =~ s/🆕//;
+       $name =~ s/\(.*\)//g;
+       $name =~ s/\[.*\]//g;
+       $name =~ s/ - .*//;
+       $name =~ s/G\d\.\d?\??//;
+       $name =~ s/\?//g;
+       $name =~ s/\s*$//;
+       $name =~ s/^\s*//;
+       return $name;
+}
+
+sub matches_name {
+       my ($slack_name, $spreadsheet_name) = @_;
+       if (lc($slack_name) eq lc($spreadsheet_name)) {
+               return 1;
+       }
+
+       my @ap = split /\s+/, $slack_name;
+       my @bp = split /\s+/, $spreadsheet_name;
+       if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
+               # First name matches, try to match some surname
+               my $found = 0;
+               for my $ai (1..$#ap) {
+                       for my $bi (1..$#bp) {
+                               $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
+                       }
+               }
+               if ($found) {
+                       skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
+                       return 1;
+               }
+       }
+
+       return 0;
+}
+
+sub format_cell_names_for_seen {
+       my $seen = shift;
+       my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
+       return join(', ', @cells);
+}
+
+sub skv_log {
+       my $msg = shift;
+       print STDERR "$msg\n";
+       push @log, $msg;
+}
+
+skv_log("Siste sync startet: " . POSIX::ctime(time));
+
+my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
+       or die "Could not connect to Postgres: " . DBI->errstr;
+
+my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
+my $token = get_oauth_bearer_token($ua);
+
+# Find everyone who are marked as attending on Slack (via reactions).
+my $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
+$q->execute($config::invitation_channel, $config::invitation_ts);
+my @attending_userids = ();
+while (my $ref = $q->fetchrow_hashref) {
+       push @attending_userids, $ref->{'userid'};
+}
+
+# Get the list of all people in the sheet (we're going to need them soon anyway).
+my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $config::tab_name . '!A9:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
+       Authorization => 'Bearer ' . $token
+);
+my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+
+# Duplicate detection
+my %seen_names = ();
+{
+       my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+       my $rowno = 9;
+       for my $row (@$main_sheet_rows) {
+               my $colno = 0;
+               for my $val (@{$row->{'values'}}) {
+                       my $name = get_spreadsheet_name($val);
+                       if (defined($name)) {
+                               push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
+                       }
+                       ++$colno;
+               }
+               ++$rowno;
+       }
+       for my $name (sort keys %seen_names) {
+               my $seen = $seen_names{$name};
+               if (scalar @$seen >= 2) {
+                       my $exemplar = $seen->[0][0];
+                       skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
+               }
+       }
+}
+
+# Get our existing Slack->name mapping, from the sheets.
+my %slack_userid_to_real_name = ();
+my %slack_userid_to_slack_name = ();
+my %slack_userid_to_row = ();
+my %real_name_to_slack_userid = ();
+$response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue',
+       Authorization => 'Bearer ' . $token
+);
+my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
+my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+my $cur_row = 5;
+for my $row (@$mapping_sheet_rows) {
+       my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
+       my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
+       my $real_name = get_spreadsheet_name($row->{'values'}[2]);  # TODO support more
+       $slack_userid_to_row{$slack_id} = $cur_row++;
+       next if (!defined($slack_name));
+       $slack_userid_to_slack_name{$slack_id} = $slack_name;
+       next if (!defined($real_name));
+       $slack_userid_to_real_name{$slack_id} = $real_name;
+       $real_name_to_slack_userid{$real_name} = $slack_id;
+}
+
+# See which ones we don't have a mapping for, and look them up in Slack.
+# TODO: Use an append call instead of $cur_row?
+my @slack_mapping_updates = ();
+for my $userid (@attending_userids) {
+       next if (exists($slack_userid_to_real_name{$userid}));
+
+       # Make sure they have a row in the spreadsheet.
+       my $write_row;
+       if (exists($slack_userid_to_row{$userid})) {
+               $write_row = $slack_userid_to_row{$userid};
+       } else {
+               $write_row = $cur_row++;
+               $slack_userid_to_row{$userid} = $write_row;
+               push @slack_mapping_updates, {
+                       range => "Slack-mapping!A$write_row:A$write_row",
+                       values => [ [ $userid ]]
+               };
+       }
+
+       # Fetch their Slack name if we don't already have it.
+       my $slack_name;
+       if (exists($slack_userid_to_slack_name{$userid})) {
+               $slack_name = $slack_userid_to_slack_name{$userid};
+       } else {
+               $slack_userid_to_slack_name{$userid} = $slack_name;
+               $slack_name = get_slack_name($ua, $userid);
+               push @slack_mapping_updates, {
+                       range => "Slack-mapping!B$write_row:B$write_row",
+                       values => [ [ $slack_name ]]
+               };
+               $slack_userid_to_slack_name{$userid} = $slack_name;
+       }
+
+       if (exists($seen_names{lc $slack_name})) {
+               # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
+               $slack_userid_to_real_name{$userid} = $slack_name;
+               push @slack_mapping_updates, {
+                       range => "Slack-mapping!C$write_row:C$write_row",
+                       values => [ [ $slack_name ]]
+               };
+       } else {
+               # Do a search through all the available names in the sheet to find an obvious(ish) match.
+               my @candidates = ();
+               my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+               for my $row (@$main_sheet_rows) {
+                       for my $val (@{$row->{'values'}}) {
+                               my $name = get_spreadsheet_name($val);
+                               if (defined($name) && matches_name($slack_name, $name)) {
+                                       push @candidates, $name;
+                               }
+                       }
+               }
+               if ($#candidates == -1) {
+                       skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem.");
+               } elsif ($#candidates == 0) {
+                       my $name = $candidates[0];
+                       $slack_userid_to_real_name{$userid} = $name;
+                       push @slack_mapping_updates, {
+                               range => "Slack-mapping!C$write_row:C$write_row",
+                               values => [ [ $name ]]
+                       };
+               } else {
+                       skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
+               }
+       }
+}
+my $update = {
+       valueInputOption => 'USER_ENTERED',
+       data => \@slack_mapping_updates
+};
+$response = $ua->post(
+       'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
+       Content => JSON::XS::encode_json($update),
+       Content_type => 'application/json;charset=UTF-8',
+       Authorization => 'Bearer ' . $token
+);
+die $response->decoded_content if (!$response->is_success);
+
+# Find the list of names to mark yellow.
+my %want_names = ();
+my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+for my $userid (@attending_userids) {
+       next if (!exists($slack_userid_to_real_name{$userid}));
+       my $slack_name = $slack_userid_to_slack_name{$userid};
+       my $real_name = $slack_userid_to_real_name{$userid};
+
+       # See if we can find them in the spreadsheet.
+       if (!exists($seen_names{lc $real_name})) {
+               skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
+       } else {
+               my $seen = $seen_names{lc $real_name};
+               if (scalar @$seen >= 2) {
+                       skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes.");
+               } else {
+                       $want_names{$seen->[0][0]} = 1;
+               }
+       }
+}
+
+# Find the list of names we already marked yellow.
+my %have_names = ();
+$dbh->{AutoCommit} = 0;
+$dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
+$q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
+$q->execute($config::invitation_channel, $config::invitation_ts);
+while (my $ref = $q->fetchrow_hashref) {
+       $have_names{$ref->{'name'}} = 1;
+}
+
+# Find the diff between the two.
+my @diffs = ();
+for my $real_name (keys %want_names) {
+       next if (exists($have_names{$real_name}));
+       skv_log("Markerer at $real_name skal på trening.");
+       push @diffs, [
+               $real_name,
+               {
+                       backgroundColor => {
+                               red => 1,
+                               green => 1,
+                               blue => 0,
+                               alpha => 1
+                       }
+               }
+       ];
+       $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
+               $config::invitation_channel, $config::invitation_ts, $real_name);
+}
+for my $real_name (keys %have_names) {
+       next if (exists($want_names{$real_name}));
+       skv_log("Fjerner at $real_name skal på trening.");
+       push @diffs, [
+               $real_name,
+               {
+                       backgroundColor => {
+                               red => 1,
+                               green => 1,
+                               blue => 1,
+                               alpha => 0
+                       }
+               }
+       ];
+       $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
+               $config::invitation_channel, $config::invitation_ts, $real_name);
+}
+
+my @yellow_updates = ();
+if (scalar @diffs > 0) {
+       # Now fill in the actual stuff.
+       for my $diff (@diffs) {
+               my $real_name = $diff->[0];
+
+               # See if we can find them in the spreadsheet.
+               # FIXME use seen_names
+               my $rowno = 8;  # zero-indexed
+               my $found = 0;
+               for my $row (@$main_sheet_rows) {
+                       my $colno = 0;
+                       for my $val (@{$row->{'values'}}) {
+                               my $name = get_spreadsheet_name($val);
+                               if (defined($name) && $real_name eq $name) {
+                                       push @yellow_updates, {
+                                               updateCells => {
+                                                       rows => [{
+                                                               values => [{
+                                                                       userEnteredFormat => $diff->[1]
+                                                               }]
+                                                       }],
+                                                       fields => 'userEnteredFormat.backgroundColor',
+                                                       range => {
+                                                               sheetId => $config::tab_id,
+                                                               startRowIndex => $rowno,
+                                                               endRowIndex => $rowno + 1,
+                                                               startColumnIndex => $colno,
+                                                               endColumnIndex => $colno + 1
+                                                       }
+                                               }
+                                       };
+                                       $found = 1;
+                               }
+                               ++$colno;
+                       }
+                       ++$rowno;
+               }
+
+               if ($found) {
+               } else {
+                       skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
+               }
+       }
+}
+
+skv_log("Ferdig.");
+
+# Add the log.
+push @yellow_updates, {
+       updateCells => {
+               rows => [{
+                       values => [{
+                               userEnteredValue => { stringValue => join("\n", @log) }
+                       }]
+               }],
+               fields => 'userEnteredValue.stringValue',
+               range => {
+                       sheetId => $config::log_tab_id,
+                       startRowIndex => 0,
+                       endRowIndex => 1,
+                       startColumnIndex => 0,
+                       endColumnIndex => 1
+               }
+       }
+};
+
+# Add the reaction log. (This only takes into account the last change
+# for each user; earlier ones are irrelevant and don't count. But it
+# doesn't deduplicate across reactions. Meh.)
+$q = $dbh->prepare('select userid,event_type,reaction,to_char(event_ts,\'YYYY-mm-dd HH24:MI\') as event_ts from ( select distinct on (channel,ts,userid,reaction) userid,event_type,reaction,timestamptz \'1970-01-01 utc\' + event_ts::float * interval \'1 second\' as event_ts from reaction_log where channel=? and ts=? and reaction in (\'heart\',\'open_mouth\') order by channel,ts,userid,reaction,event_ts desc ) t1 where event_ts > current_timestamp - interval \'8 hours\' order by event_ts desc limit 50');
+$q->execute($config::invitation_channel, $config::invitation_ts);
+my @recent_changes = ();
+while (my $ref = $q->fetchrow_hashref) {
+       my $msg = $ref->{'event_ts'};
+       if ($ref->{'event_type'} eq 'reaction_added') {
+               $msg .= ' +';
+       } else {
+               $msg .= ' -';
+       }
+       if ($ref->{'reaction'} eq 'open_mouth') {
+               $msg .= '😮';
+       } else {
+               $msg .= '❤️';
+       }
+       $msg .= ' ';
+       $msg .= $slack_userid_to_real_name{$ref->{'userid'}} // ($slack_userid_to_slack_name{$ref->{'userid'}} . ' (fant ikke regneark-navn)');
+       push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
+}
+while (scalar @recent_changes < 50) {
+       push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
+}
+push @yellow_updates, {
+       updateCells => {
+               rows => \@recent_changes,
+               fields => 'userEnteredValue.stringValue',
+               range => {
+                       sheetId => $config::log_tab_id,
+                       startRowIndex => 4,
+                       endRowIndex => 4 + scalar @recent_changes,
+                       startColumnIndex => 0,
+                       endColumnIndex => 1
+               }
+       }
+};
+
+# Push the final set of updates (including the log).
+$update = {
+       requests => \@yellow_updates
+};
+$response = $ua->post(
+       'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
+       Content => JSON::XS::encode_json($update),
+       Content_type => 'application/json;charset=UTF-8',
+       Authorization => 'Bearer ' . $token
+);
+die $response->decoded_content if !$response->is_success;
+$dbh->commit;

diff --git a/include/config.pm b/include/config.pm
new file mode 100644 (file)
index 0000000..4f901ec
--- /dev/null
+++ b/include/config.pm
@@ -0,0 +1,36 @@
+package config;
+
+our $client_secret;
+our $signing_secret;
+our $oauth_token;
+our $jwt_key = {
+        "type" => "service_account",
+        "project_id" => "solskogen-cubemap",
+        "private_key_id" => "9eaf56bb4d6b688c3c73bd532fecdde943eea718",
+        "private_key" => undef,
+        "client_email" => "ultimate-nm-2018\@solskogen-cubemap.iam.gserviceaccount.com",
+        "client_id" => "102636658655884526659",
+        "auth_uri" => "https://accounts.google.com/o/oauth2/auth",
+        "token_uri" => "https://accounts.google.com/o/oauth2/token",
+        "auth_provider_x509_cert_url" => "https://www.googleapis.com/oauth2/v1/certs",
+        "client_x509_cert_url" => "https://www.googleapis.com/robot/v1/metadata/x509/ultimate-nm-2018%40solskogen-cubemap.iam.gserviceaccount.com"
+};
+our $sheet_id = '1-alcvuB3184rw3pWLcvYjc2d6CnVGpMuvZYMYxn7Cgw';
+#my $tab_id = '1739147411';
+our $tab_id = '0';
+our $tab_name = 'Neste';
+our $log_tab_id = '253633356';
+our $gsheets_api_key;
+our $dbname = 'skvidarlang';
+our $dbuser = 'skvidarlang';
+our $dbpass;
+our $oauth_token;
+our $invitation_channel = 'C062GSYQ3C6';
+our $invitation_ts = '1698239546.063769';
+
+# Read in a local configuration file if it exists.
+eval {  
+        require '../include/config.local.pm';
+};
+
+1;

diff --git a/www/event.pl b/www/event.pl
new file mode 100755 (executable)
index 0000000..cf7a212
--- /dev/null
+++ b/www/event.pl
@@ -0,0 +1,76 @@
+#! /usr/bin/perl
+use strict;
+use warnings;
+no warnings qw(once);
+use CGI;
+use JSON::XS;
+use Digest::SHA qw(hmac_sha256_hex);
+use LWP::UserAgent;
+use DBI;
+
+require '../include/config.pm';
+
+sub mark {
+       print STDERR "Marking that a sync is needed.\n";
+       open my $fh, ">", "/srv/skvidar-slack.sesse.net/marker";
+       close $fh;
+}
+
+my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
+       or die "Could not connect to Postgres: " . DBI->errstr;
+
+my $cgi = CGI->new;
+my $post = $cgi->param('POSTDATA');
+my $ts = $cgi->http('X-Slack-Request-Timestamp');
+my $sig = $cgi->http('X-Slack-Signature');
+
+my $digest = Digest::SHA::hmac_sha256_hex("v0:$ts:$post", $config::signing_secret);
+die "Failed signature" unless ($sig eq "v0=$digest");
+
+print STDERR "JSON: $post\n";
+
+my $json = JSON::XS::decode_json($post);
+if (exists($json->{'challenge'})) {
+       print CGI->header(-type=>'text/plain');
+       print $json->{'challenge'}, "\n";
+       exit;
+}
+
+if (exists($json->{'event'}) && exists($json->{'event'}{'type'})) {
+       my $type = $json->{'event'}{'type'};
+       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";
+               print CGI->header(-type=>'text/plain');
+               print "OK\n";
+               exit;
+       }
+
+       if ($type eq 'reaction_added') {
+               $dbh->do('INSERT INTO reactions (userid, channel, ts, reaction) VALUES (?,?,?,?)', undef,
+                       $user, $channel, $ts, $reaction);
+               $dbh->do('INSERT INTO reaction_log (userid, channel, ts, reaction, event_type, event_ts) VALUES (?,?,?,?,?,?)', undef,
+                       $user, $channel, $ts, $reaction, $type, $event_ts);
+               mark();
+       } elsif ($type eq 'reaction_removed') {
+               $dbh->do('DELETE FROM reactions WHERE userid=? AND channel=? AND ts=? AND reaction=?', undef,
+                       $user, $channel, $ts, $reaction);
+               $dbh->do('INSERT INTO reaction_log (userid, channel, ts, reaction, event_type, event_ts) VALUES (?,?,?,?,?,?)', undef,
+                       $user, $channel, $ts, $reaction, $type, $event_ts);
+               mark();
+       } else {
+               print STDERR "Type is $type (not a reaction added or removed); ignoring.\n";
+       }
+
+       # Fall through.
+} else {
+       print STDERR "Has no type; igoring.\n";
+}
+
+print CGI->header(-type=>'text/plain');
+print "OK\n";