git.sesse.net Git - skvidarsync/blob - bin/sync.pl

   1 #! /usr/bin/perl
   2 use strict;
   3 use warnings;
   4 no warnings qw(once);
   5 use Crypt::JWT;
   6 use JSON::XS;
   7 use LWP::UserAgent;
   8 use DBI;
   9 use POSIX;
  10 binmode STDOUT, ':utf8';
  11 binmode STDERR, ':utf8';
  12 use utf8;
  13
  14 # TODO:
  15 # - detect moves between groups
  16 # - message people when they sign up and we can't find them in the spreadsheet
  17
  18 require '../include/config.pm';
  19
  20 my $dbh;
  21 my @log = ();
  22
  23 sub get_oauth_bearer_token {
  24         my $ua = shift;
  25         my $now = time();
  26         my $jwt = JSON::XS::encode_json({
  27                 "iss" => $config::jwt_key->{'client_email'},
  28                 "scope" => "https://www.googleapis.com/auth/spreadsheets",
  29                 "aud" => "https://www.googleapis.com/oauth2/v4/token",
  30                 "exp" => $now + 1800,
  31                 "iat" => $now,
  32         });
  33         my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
  34         my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
  35                 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
  36                 'assertion' => $jws_token ]);
  37         return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
  38 }
  39
  40 sub get_slack_name {
  41         my ($ua, $userid) = @_;
  42         my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
  43                'Authorization' => 'Bearer ' . $config::oauth_token
  44         ]);
  45         my $response = $ua->request($req);
  46         die $response->status_line if !$response->is_success;
  47
  48         my $user_json = JSON::XS::decode_json($response->decoded_content);
  49         die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
  50
  51         return $user_json->{'user'}{'real_name'};
  52 }
  53
  54 sub get_spreadsheet_name {
  55         my $cell = shift;
  56         my $name = $cell->{'userEnteredValue'}{'stringValue'};
  57         return undef if (!defined($name));
  58         return undef if ($name =~ /^G[1-4]\.[1-5]/);
  59         $name =~ s/🆕//;
  60         $name =~ s/\(.*\)//g;
  61         $name =~ s/\[.*\]//g;
  62         $name =~ s/ - .*//;
  63         $name =~ s/G\d\.\d?\??//;
  64         $name =~ s/\?//g;
  65         $name =~ s/\s*$//;
  66         $name =~ s/^\s*//;
  67         return $name;
  68 }
  69
  70 sub matches_name {
  71         my ($slack_name, $spreadsheet_name) = @_;
  72         if (lc($slack_name) eq lc($spreadsheet_name)) {
  73                 return 1;
  74         }
  75
  76         my @ap = split /\s+/, $slack_name;
  77         my @bp = split /\s+/, $spreadsheet_name;
  78         if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
  79                 # First name matches, try to match some surname
  80                 my $found = 0;
  81                 for my $ai (1..$#ap) {
  82                         for my $bi (1..$#bp) {
  83                                 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
  84                         }
  85                 }
  86                 if ($found) {
  87                         skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
  88                         return 1;
  89                 }
  90         }
  91
  92         return 0;
  93 }
  94
  95 sub format_cell_names_for_seen {
  96         my $seen = shift;
  97         my @cells = map { chr(ord('A') + $_->[2]) . $_->[1] } @$seen;
  98         return join(', ', @cells);
  99 }
 100
 101 sub skv_log {
 102         my $msg = shift;
 103         print STDERR "$msg\n";
 104         push @log, $msg;
 105 }
 106
 107 sub serialize_skv_log_to_sheet {
 108         return {
 109                 updateCells => {
 110                         rows => [{
 111                                 values => [{
 112                                         userEnteredValue => { stringValue => join("\n", @log) }
 113                                 }]
 114                         }],
 115                         fields => 'userEnteredValue.stringValue',
 116                         range => {
 117                                 sheetId => $config::log_tab_id,
 118                                 startRowIndex => 0,
 119                                 endRowIndex => 1,
 120                                 startColumnIndex => 0,
 121                                 endColumnIndex => 1
 122                         }
 123                 }
 124         };
 125 }
 126
 127 sub sheet_batch_update {
 128         my ($ua, $token, @requests) = @_;
 129         my $update = {
 130                 requests => \@requests
 131         };
 132         my $response = $ua->post(
 133                 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
 134                 Content => JSON::XS::encode_json($update),
 135                 Content_type => 'application/json;charset=UTF-8',
 136                 Authorization => 'Bearer ' . $token
 137         );
 138         die $response->decoded_content if !$response->is_success;
 139 }
 140
 141 sub get_spreadsheet_with_title {
 142         my ($ua, $token, $wanted_sheet_title) = @_;
 143
 144         # See if we have any spreadsheets that match this title.
 145         my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
 146                 Authorization => 'Bearer ' . $token
 147         );
 148         my $sheets_json = JSON::XS::decode_json($response->decoded_content);
 149         my ($tab_name, $tab_id);
 150         for my $sheet (@{$sheets_json->{'sheets'}}) {
 151                 my $title = $sheet->{'properties'}{'title'};
 152                 my $sheet_id = $sheet->{'properties'}{'sheetId'};
 153                 if ($title =~ /\Q$wanted_sheet_title\E/) {
 154                         # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket “$title” (fane-ID $sheet_id).");
 155                         return ($title, $sheet_id);
 156                 }
 157         }
 158         return (undef, undef);
 159 }
 160
 161 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
 162 sub find_where_each_name_is {
 163         my $json = shift;
 164
 165         my %seen_names = ();
 166         my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
 167         my $rowno = 4;
 168         for my $row (@$rows) {
 169                 my $colno = 0;
 170                 for my $val (@{$row->{'values'}}) {
 171                         my $name = get_spreadsheet_name($val);
 172                         if (defined($name)) {
 173                                 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
 174                         }
 175                         ++$colno;
 176                 }
 177                 ++$rowno;
 178         }
 179
 180         return %seen_names;
 181 }
 182
 183 # Add the reaction log. (This only takes into account the last change
 184 # for each user; earlier ones are irrelevant and don't count. But it
 185 # doesn't deduplicate across reactions. Meh.)
 186 sub create_reaction_log {
 187         my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
 188
 189         my $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');
 190         $q->execute($config::invitation_channel, $invitation_ts);
 191         my @recent_changes = ();
 192         while (my $ref = $q->fetchrow_hashref) {
 193                 my $msg = $ref->{'event_ts'};
 194                 if ($ref->{'event_type'} eq 'reaction_added') {
 195                         $msg .= ' +';
 196                 } else {
 197                         $msg .= ' –';
 198                 }
 199                 if ($ref->{'reaction'} eq 'open_mouth') {
 200                         $msg .= '😮';
 201                 } else {
 202                         $msg .= '❤️';
 203                 }
 204                 $msg .= ' ';
 205                 if (exists($slack_userid_to_real_name->{$ref->{'userid'}})) {
 206                         $msg .= $slack_userid_to_real_name->{$ref->{'userid'}};
 207                 } elsif (exists($slack_userid_to_slack_name->{$ref->{'userid'}})) {
 208                         $msg .= $slack_userid_to_slack_name->{$ref->{'userid'}} . ' (fant ikke regneark-navn)';
 209                 } else {
 210                         # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
 211                         $msg .= $ref->{'userid'} . ' (fant ikke Slack-navn)';
 212                 }
 213                 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
 214         }
 215         while (scalar @recent_changes < 50) {
 216                 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
 217         }
 218         return @recent_changes;
 219 }
 220
 221 # Also applies the diff to the database (a bit ugly).
 222 sub find_diff {
 223         my ($dbh, $invitation_ts, $want_names, $have_names, $seen_names) = @_;
 224
 225         my @diffs = ();
 226         for my $real_name (keys %$want_names) {
 227                 next if (exists($have_names->{$real_name}));
 228                 skv_log("Markerer at $real_name skal på trening.");
 229                 push @diffs, [
 230                         $real_name,
 231                         {
 232                                 backgroundColor => {
 233                                         red => 1,
 234                                         green => 1,
 235                                         blue => 0,
 236                                         alpha => 1
 237                                 }
 238                         }
 239                 ];
 240                 $dbh->do('INSERT INTO applied (channel, ts, name) VALUES (?, ?, ?)', undef,
 241                         $config::invitation_channel, $invitation_ts, $real_name);
 242         }
 243         for my $real_name (keys %$have_names) {
 244                 next if (exists($want_names->{$real_name}));
 245                 if (!exists($seen_names->{lc $real_name})) {
 246                         # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
 247                         skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
 248                 } else {
 249                         skv_log("Fjerner at $real_name skal på trening.");
 250                         push @diffs, [
 251                                 $real_name,
 252                                 {
 253                                         backgroundColor => {
 254                                                 red => 1,
 255                                                 green => 1,
 256                                                 blue => 1,
 257                                                 alpha => 0
 258                                         }
 259                                 }
 260                         ];
 261                         $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
 262                                 $config::invitation_channel, $invitation_ts, $real_name);
 263                 }
 264         }
 265         return @diffs;
 266 }
 267
 268 sub run {
 269         @log = ();
 270         skv_log("Siste sync startet: " . POSIX::ctime(time));
 271
 272         # Initialize the handles we need for communication.
 273         $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
 274                 or die "Could not connect to Postgres: " . DBI->errstr;
 275         my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
 276         my $token = get_oauth_bearer_token($ua);
 277
 278         # Find the newest message, and what it is linked to.
 279         # TODO: Support more than one, and test better for errors here.
 280         my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 1');
 281         $q->execute($config::invitation_channel);
 282         my $linkref = $q->fetchrow_hashref;
 283         my $invitation_ts = $linkref->{'ts'};
 284         my $wanted_sheet_title = $linkref->{'sheet_title'};
 285         die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
 286
 287         my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
 288         if (!defined($tab_name)) {
 289                 skv_log("Fant ikke noen fane med “$wanted_sheet_title” i navnet; kan ikke synkronisere.\n");
 290                 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
 291                 die;
 292         }
 293
 294         # Find everyone who are marked as attending on Slack (via reactions).
 295         $q = $dbh->prepare('SELECT DISTINCT userid FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\')');
 296         $q->execute($config::invitation_channel, $invitation_ts);
 297         my @attending_userids = ();
 298         while (my $ref = $q->fetchrow_hashref) {
 299                 push @attending_userids, $ref->{'userid'};
 300         }
 301
 302         # Get the list of all people in the sheet (we're going to need them soon anyway).
 303         my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
 304                 Authorization => 'Bearer ' . $token
 305         );
 306         my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
 307
 308         my %seen_names = find_where_each_name_is($main_sheet_json);
 309
 310         # Find duplicates.
 311         for my $name (sort keys %seen_names) {
 312                 my $seen = $seen_names{$name};
 313                 if (scalar @$seen >= 2) {
 314                         my $exemplar = $seen->[0][0];
 315                         skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
 316                 }
 317         }
 318
 319         # Get our existing Slack->name mapping, from the sheets.
 320         my %slack_userid_to_real_name = ();
 321         my %slack_userid_to_slack_name = ();
 322         my %slack_userid_to_row = ();
 323         $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',
 324                 Authorization => 'Bearer ' . $token
 325         );
 326         my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
 327         my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
 328         my $cur_row = 5;
 329         for my $row (@$mapping_sheet_rows) {
 330                 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
 331                 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
 332                 my $real_name = get_spreadsheet_name($row->{'values'}[2]);  # TODO support more
 333                 $slack_userid_to_row{$slack_id} = $cur_row++;
 334                 next if (!defined($slack_name));
 335                 $slack_userid_to_slack_name{$slack_id} = $slack_name;
 336                 next if (!defined($real_name));
 337                 $slack_userid_to_real_name{$slack_id} = $real_name;
 338         }
 339
 340         # See which ones we don't have a mapping for, and look them up in Slack.
 341         # TODO: Use an append call instead of $cur_row?
 342         my @slack_mapping_updates = ();
 343         for my $userid (@attending_userids) {
 344                 next if (exists($slack_userid_to_real_name{$userid}));
 345
 346                 # Make sure they have a row in the spreadsheet.
 347                 my $write_row;
 348                 if (exists($slack_userid_to_row{$userid})) {
 349                         $write_row = $slack_userid_to_row{$userid};
 350                 } else {
 351                         $write_row = $cur_row++;
 352                         $slack_userid_to_row{$userid} = $write_row;
 353                         push @slack_mapping_updates, {
 354                                 range => "Slack-mapping!A$write_row:A$write_row",
 355                                 values => [ [ $userid ]]
 356                         };
 357                 }
 358
 359                 # Fetch their Slack name if we don't already have it.
 360                 my $slack_name;
 361                 if (exists($slack_userid_to_slack_name{$userid})) {
 362                         $slack_name = $slack_userid_to_slack_name{$userid};
 363                 } else {
 364                         $slack_userid_to_slack_name{$userid} = $slack_name;
 365                         $slack_name = get_slack_name($ua, $userid);
 366                         push @slack_mapping_updates, {
 367                                 range => "Slack-mapping!B$write_row:B$write_row",
 368                                 values => [ [ $slack_name ]]
 369                         };
 370                         $slack_userid_to_slack_name{$userid} = $slack_name;
 371                 }
 372
 373                 if (exists($seen_names{lc $slack_name})) {
 374                         # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
 375                         $slack_userid_to_real_name{$userid} = $slack_name;
 376                         push @slack_mapping_updates, {
 377                                 range => "Slack-mapping!C$write_row:C$write_row",
 378                                 values => [ [ $slack_name ]]
 379                         };
 380                 } else {
 381                         # Do a search through all the available names in the sheet to find an obvious(ish) match.
 382                         my @candidates = ();
 383                         my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
 384                         for my $row (@$main_sheet_rows) {
 385                                 for my $val (@{$row->{'values'}}) {
 386                                         my $name = get_spreadsheet_name($val);
 387                                         if (defined($name) && matches_name($slack_name, $name)) {
 388                                                 push @candidates, $name;
 389                                         }
 390                                 }
 391                         }
 392                         if ($#candidates == -1) {
 393                                 skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem.");
 394                         } elsif ($#candidates == 0) {
 395                                 my $name = $candidates[0];
 396                                 $slack_userid_to_real_name{$userid} = $name;
 397                                 push @slack_mapping_updates, {
 398                                         range => "Slack-mapping!C$write_row:C$write_row",
 399                                         values => [ [ $name ]]
 400                                 };
 401                         } else {
 402                                 skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
 403                         }
 404                 }
 405         }
 406         my $update = {
 407                 valueInputOption => 'USER_ENTERED',
 408                 data => \@slack_mapping_updates
 409         };
 410         $response = $ua->post(
 411                 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
 412                 Content => JSON::XS::encode_json($update),
 413                 Content_type => 'application/json;charset=UTF-8',
 414                 Authorization => 'Bearer ' . $token
 415         );
 416         die $response->decoded_content if (!$response->is_success);
 417
 418         # Find the list of names to mark yellow.
 419         my %want_names = ();
 420         my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
 421         for my $userid (@attending_userids) {
 422                 next if (!exists($slack_userid_to_real_name{$userid}));
 423                 my $slack_name = $slack_userid_to_slack_name{$userid};
 424                 my $real_name = $slack_userid_to_real_name{$userid};
 425
 426                 # See if we can find them in the spreadsheet.
 427                 if (!exists($seen_names{lc $real_name})) {
 428                         # TODO: Perhaps move this logic further down, for consistency?
 429                         skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
 430                 } else {
 431                         my $seen = $seen_names{lc $real_name};
 432                         if (scalar @$seen >= 2) {
 433                                 skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes.");
 434                         } else {
 435                                 $want_names{$seen->[0][0]} = 1;
 436                         }
 437                 }
 438         }
 439
 440         # Find the list of names we already marked yellow.
 441         my %have_names = ();
 442         $dbh->{AutoCommit} = 0;
 443         $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
 444         $q = $dbh->prepare('SELECT name FROM applied WHERE channel=? AND ts=?');
 445         $q->execute($config::invitation_channel, $invitation_ts);
 446         while (my $ref = $q->fetchrow_hashref) {
 447                 $have_names{$ref->{'name'}} = 1;
 448         }
 449
 450         my @diffs = find_diff($dbh, $invitation_ts, \%want_names, \%have_names, \%seen_names);
 451
 452         my @yellow_updates = ();
 453         if (scalar @diffs > 0) {
 454                 # Now fill in the actual stuff.
 455                 for my $diff (@diffs) {
 456                         my $real_name = $diff->[0];
 457
 458                         # See if we can find them in the spreadsheet.
 459                         my $seen = $seen_names{lc $real_name};
 460                         die if (!defined($seen) || scalar @$seen > 1);
 461                         my $rowno = $seen->[0][1];
 462                         my $colno = $seen->[0][2];
 463                         push @yellow_updates, {
 464                                 updateCells => {
 465                                         rows => [{
 466                                                 values => [{
 467                                                         userEnteredFormat => $diff->[1]
 468                                                 }]
 469                                         }],
 470                                         fields => 'userEnteredFormat.backgroundColor',
 471                                         range => {
 472                                                 sheetId => $tab_id,
 473                                                 startRowIndex => $rowno,
 474                                                 endRowIndex => $rowno + 1,
 475                                                 startColumnIndex => $colno,
 476                                                 endColumnIndex => $colno + 1
 477                                         }
 478                                 }
 479                         };
 480                 }
 481         }
 482
 483         my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
 484         push @yellow_updates, {
 485                 updateCells => {
 486                         rows => \@recent_changes,
 487                         fields => 'userEnteredValue.stringValue',
 488                         range => {
 489                                 sheetId => $config::log_tab_id,
 490                                 startRowIndex => 4,
 491                                 endRowIndex => 4 + scalar @recent_changes,
 492                                 startColumnIndex => 0,
 493                                 endColumnIndex => 1
 494                         }
 495                 }
 496         };
 497
 498         # Push the final set of updates (including the log).
 499         skv_log("Ferdig.");
 500         push @yellow_updates, serialize_skv_log_to_sheet();
 501         sheet_batch_update($ua, $token, \@yellow_updates);
 502         $dbh->commit;
 503 }
 504
 505 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
 506         while (1) {
 507                 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
 508                         unless ($!{ENOENT}) {
 509                                 warn "/srv/skvidar-slack.sesse.net/marker: $!";
 510                         }
 511                         sleep 1;
 512                         next;
 513                 }
 514                 eval {
 515                         run();
 516                 };
 517                 if ($@) {
 518                         warn "Died with: $@";
 519                 }
 520                 $dbh->disconnect;
 521         }
 522 } else {
 523         run();
 524 }