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 use Time::HiRes;
  11 use IO::Select;
  12 use Unicode::Collate;
  13 use IO::Socket::SSL;
  14 binmode STDOUT, ':utf8';
  15 binmode STDERR, ':utf8';
  16 use utf8;
  17
  18 require '../include/config.pm';
  19
  20 my $global_ctx = IO::Socket::SSL::SSL_Context->new(
  21         SSL_session_cache_size => 100,  # Probably overkill.
  22 );
  23 IO::Socket::SSL::set_default_context($global_ctx);
  24
  25 my @log = ();
  26 my $uca = Unicode::Collate->new(level => 1);
  27
  28 my %rgb = (
  29         yellow => {
  30                 red => 1,
  31                 green => 1,
  32                 blue => 0,
  33                 alpha => 1
  34         },
  35         blue => {
  36                 red => 0,
  37                 green => 1,
  38                 blue => 1,
  39                 alpha => 1
  40         },
  41         white => {
  42                 red => 1,
  43                 green => 1,
  44                 blue => 1,
  45                 alpha => 0
  46         }
  47 );
  48
  49 sub log_timing {
  50         my ($start, $msg) = @_;
  51         my $elapsed = Time::HiRes::tv_interval($start);
  52         printf "%s: %.0f ms.\n", $msg, 1e3 * $elapsed;
  53 }
  54
  55 # Unicode::Collate is seemingly slow, so add a cache for each name part
  56 # (which, of course, only works for equality). Helps especially in
  57 # --daemon mode, where even the first request gets a warm cache.
  58 my %sort_key_cache = ();
  59 my $sort_key_sp = $uca->getSortKey(' ');
  60
  61 sub sort_key {
  62         my $m = shift;
  63         my $sk;
  64         for my $part (split /\s+/, $m) {
  65                 my $psk = \$sort_key_cache{$part};
  66                 if (!defined($$psk)) {
  67                         $$psk = $uca->getSortKey($part);
  68                 }
  69                 if (defined($sk)) {
  70                         $sk .= $sort_key_sp;
  71                         $sk .= $$psk;
  72                 } else {
  73                         $sk = $$psk;
  74                 }
  75         }
  76         return $sk;
  77 }
  78
  79 sub get_oauth_bearer_token {
  80         my ($dbh, $ua) = @_;
  81         my $now = time();
  82
  83         # See if the database already has a token we could use, that doesn't expire in a while.
  84         my $ref = $dbh->selectrow_hashref('SELECT token FROM oauth_tokens WHERE expiry - (TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\') > INTERVAL \'1 minute\' ORDER BY expiry DESC LIMIT 1', undef, $now);
  85         if (defined($ref->{'token'})) {
  86                 return $ref->{'token'};
  87         }
  88
  89         my $jwt = JSON::XS::encode_json({
  90                 "iss" => $config::jwt_key->{'client_email'},
  91                 "scope" => "https://www.googleapis.com/auth/spreadsheets",
  92                 "aud" => "https://www.googleapis.com/oauth2/v4/token",
  93                 "exp" => $now + 1800,
  94                 "iat" => $now,
  95         });
  96         my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
  97         my $start = [Time::HiRes::gettimeofday];
  98         my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
  99                 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
 100                 'assertion' => $jws_token ]);
 101         log_timing($start, '/oauth2/v4/token');
 102         my $token = JSON::XS::decode_json($response->decoded_content)->{'access_token'};
 103         $dbh->do('INSERT INTO oauth_tokens (token, acquired, expiry) VALUES (?, TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\', TIMESTAMPTZ \'1970-01-01\' + ? * INTERVAL \'1 second\')',
 104                 undef, $token, $now, $now + 1800);
 105         return $token;
 106 }
 107
 108 sub get_slack_name {
 109         my ($ua, $userid) = @_;
 110         my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
 111                'Authorization' => 'Bearer ' . $config::slack_oauth_token
 112         ]);
 113         my $start = [Time::HiRes::gettimeofday];
 114         my $response = $ua->request($req);
 115         log_timing($start, '/users.info');
 116         die $response->status_line if !$response->is_success;
 117
 118         my $user_json = JSON::XS::decode_json($response->decoded_content);
 119         die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
 120
 121         return $user_json->{'user'}{'real_name'};
 122 }
 123
 124 sub get_spreadsheet_name {
 125         my $cell = shift;
 126         my $name = $cell->{'userEnteredValue'}{'stringValue'};
 127         return undef if (!defined($name));
 128         return undef if ($name =~ /^G[1-4]\.[1-5]/);
 129         return undef if ($name =~ /^1r/);
 130         $name =~ s/🆕//;
 131         $name =~ s/\(.*\)//g;
 132         $name =~ s/\[.*\]//g;
 133         $name =~ s/ - .*//;
 134         $name =~ s/G\d\.\d?\??//;
 135         $name =~ s/\?//g;
 136         $name =~ s/\s*$//;
 137         $name =~ s/^\s*//;
 138         return $name;
 139 }
 140
 141 sub matches_name {
 142         my ($slack_name, $spreadsheet_name, $ap) = @_;
 143
 144         # No need to check for an exact match; we already did that through $seen_names.
 145         # if (sort_key($slack_name) eq sort_key($spreadsheet_name)) {
 146         #       return 1;
 147         # }
 148
 149         # @ap is precalculated by the caller.
 150         # my @ap = map { sort_key($_) } split /\s+/, $slack_name;
 151         my @bp = map { sort_key($_) } split /\s+/, $spreadsheet_name;
 152         if (scalar @$ap >= 2 && scalar @bp >= 2 && $ap->[0] eq $bp[0]) {
 153                 # First name matches, try to match some surname
 154                 my $found = 0;
 155                 for my $ai (1..(scalar @$ap - 1)) {
 156                         for my $bi (1..$#bp) {
 157                                 $found = 1 if ($ap->[$ai] eq $bp[$bi]);
 158                         }
 159                 }
 160                 if ($found) {
 161                         skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
 162                         return 1;
 163                 }
 164         }
 165
 166         return 0;
 167 }
 168
 169 sub format_cell_names_for_seen {
 170         my $seen = shift;
 171         my @cells = map { chr(ord('A') + $_->[2]) . ($_->[1] + 1) } @$seen;
 172         return join(', ', @cells);
 173 }
 174
 175 sub skv_log {
 176         my $msg = shift;
 177         print STDERR "$msg\n";
 178         push @log, $msg;
 179 }
 180
 181 sub serialize_skv_log_to_sheet {
 182         return {
 183                 updateCells => {
 184                         rows => [{
 185                                 values => [{
 186                                         userEnteredValue => { stringValue => join("\n", @log) }
 187                                 }]
 188                         }],
 189                         fields => 'userEnteredValue.stringValue',
 190                         range => {
 191                                 sheetId => $config::log_tab_id,
 192                                 startRowIndex => 0,
 193                                 endRowIndex => 1,
 194                                 startColumnIndex => 0,
 195                                 endColumnIndex => 1
 196                         }
 197                 }
 198         };
 199 }
 200
 201 sub sheet_batch_update {
 202         my ($ua, $token, @requests) = @_;
 203         my $update = {
 204                 requests => \@requests
 205         };
 206         my $start = [Time::HiRes::gettimeofday];
 207         my $response = $ua->post(
 208                 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
 209                 Content => JSON::XS::encode_json($update),
 210                 Content_type => 'application/json;charset=UTF-8',
 211                 Authorization => 'Bearer ' . $token
 212         );
 213         log_timing($start, '/spreadsheets/values:batchUpdate');
 214         die $response->decoded_content if !$response->is_success;
 215 }
 216
 217 sub get_group_assignments {
 218         my $json = shift;
 219
 220         my %assignments = ();
 221         my $rows = $json->{'data'}[0]{'rowData'};
 222         my @curr_groups = ();
 223         for my $row (@$rows) {
 224                 my $col = 0;
 225                 for my $val (@{$row->{'values'}}) {
 226                         ++$col;
 227                         my $contents = $val->{'userEnteredValue'}{'stringValue'};
 228                         next if !defined($contents);
 229                         if ($contents =~ /Gruppe /) {
 230                                 @curr_groups = ();
 231                                 last;
 232                         }
 233                         next if $contents =~ /^VL:/;
 234                         next if $contents =~ /^LT\b/;
 235                         next if $contents =~ /^400m/;
 236                         next if $contents =~ /^546m/;
 237                         next if $contents =~ /^1r/;
 238                         if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye løpere.*)/) {
 239                                 $curr_groups[$col] = $1;
 240                         } else {
 241                                 my $name = get_spreadsheet_name($val);
 242                                 next if (!defined($name));
 243                                 my $group = $curr_groups[$col] // $curr_groups[$col - 1];
 244                                 # print $group, " ", $name, "\n";
 245                                 if (exists($assignments{$name})) {
 246                                         $assignments{$name} = "(flere grupper)";
 247                                 } else {
 248                                         $assignments{$name} = $group;
 249                                 }
 250                         }
 251                 }
 252         }
 253         return %assignments;
 254 }
 255
 256 sub update_assignment_db {
 257         my ($dbh, $channel, $ts, $assignments) = @_;
 258
 259         my %db_assignments = ();
 260         my $q = $dbh->prepare('SELECT name,group_name FROM current_group_membership_history WHERE channel=? AND ts=?');
 261         $q->execute($channel, $ts);
 262         while (my $ref = $q->fetchrow_hashref) {
 263                 if (defined($ref->{'group_name'})) {
 264                         $db_assignments{$ref->{'name'}} = $ref->{'group_name'};
 265                 }
 266         }
 267
 268         $q = $dbh->prepare('INSERT INTO group_membership_history (channel, ts, name, change_seen, group_name) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)');
 269         for my $name (keys %$assignments) {
 270                 if (!exists($db_assignments{$name}) || $db_assignments{$name} ne $assignments->{$name}) {
 271                         $q->execute($channel, $ts, $name, $assignments->{$name});
 272                 }
 273         }
 274         for my $name (keys %db_assignments) {
 275                 if (!exists($assignments->{$name})) {
 276                         $q->execute($channel, $ts, $name, undef);
 277                 }
 278         }
 279 }
 280
 281 sub get_spreadsheet_with_title {
 282         my ($dbh, $ua, $token, $invitation_ts, $wanted_sheet_title) = @_;
 283
 284         # See if we have any spreadsheets that match this title.
 285         my $start = [Time::HiRes::gettimeofday];
 286         my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
 287                 Authorization => 'Bearer ' . $token,
 288                 Accept_Encoding => HTTP::Message::decodable
 289         );
 290         log_timing($start, '/spreadsheets/properties');
 291         my $sheets_json = JSON::XS::decode_json($response->decoded_content);
 292         my ($tab_name, $tab_id);
 293         for my $sheet (@{$sheets_json->{'sheets'}}) {
 294                 my $title = $sheet->{'properties'}{'title'};
 295                 my $sheet_id = $sheet->{'properties'}{'sheetId'};
 296                 if ($title =~ /\Q$wanted_sheet_title\E/) {
 297                         # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket “$title” (fane-ID $sheet_id).");
 298                         $dbh->do('UPDATE message_sheet_link SET tab_name=?, tab_id=? WHERE channel=? AND ts=?',
 299                                 undef, $title, $sheet_id, $config::invitation_channel, $invitation_ts);
 300                         return ($title, $sheet_id);
 301                 }
 302         }
 303         return (undef, undef);
 304 }
 305
 306 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
 307 sub find_where_each_name_is {
 308         my $json = shift;
 309
 310         my %seen_names = ();
 311         my $rows = $json->{'data'}[0]{'rowData'};
 312         my $rowno = 3;
 313         for my $row (@$rows) {
 314                 my $colno = 0;
 315                 for my $val (@{$row->{'values'}}) {
 316                         my $name = get_spreadsheet_name($val);
 317                         if (defined($name)) {
 318                                 push @{$seen_names{sort_key($name)}}, [$name, $rowno, $colno];
 319                         }
 320                         ++$colno;
 321                 }
 322                 ++$rowno;
 323         }
 324
 325         return %seen_names;
 326 }
 327
 328 sub best_name_for_log {
 329         my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
 330         if (exists($slack_userid_to_real_name->{$userid})) {
 331                 return $slack_userid_to_real_name->{$userid};
 332         } elsif (exists($slack_userid_to_slack_name->{$userid})) {
 333                 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
 334         } else {
 335                 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
 336                 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
 337                 return $userid . ' (fant ikke Slack-navn)';
 338         }
 339 }
 340
 341 # Add the reaction log. (This only takes into account the last change
 342 # for each user; earlier ones are irrelevant and don't count. But it
 343 # doesn't deduplicate across reactions. Meh.)
 344 sub create_reaction_log {
 345         my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
 346
 347         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\',\'blue_heart\') 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');
 348         $q->execute($config::invitation_channel, $invitation_ts);
 349         my @recent_changes = ();
 350         while (my $ref = $q->fetchrow_hashref) {
 351                 my $msg = $ref->{'event_ts'};
 352                 if ($ref->{'event_type'} eq 'reaction_added') {
 353                         $msg .= ' +';
 354                 } else {
 355                         $msg .= ' –';
 356                 }
 357                 if ($ref->{'reaction'} eq 'open_mouth') {
 358                         $msg .= '😮';
 359                 } elsif ($ref->{'reaction'} eq 'blue_heart') {
 360                         $msg .= '💙';
 361                 } else {
 362                         $msg .= '❤️';
 363                 }
 364                 $msg .= ' ';
 365                 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
 366                 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
 367         }
 368         while (scalar @recent_changes < 50) {
 369                 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
 370         }
 371         return @recent_changes;
 372 }
 373
 374 sub create_move_log {
 375          my ($dbh, $invitation_ts, $prev_invitation_ts) = @_;
 376          my $q = $dbh->prepare(<<"EOF");
 377 SELECT
 378   name, g_old.group_name as old_group, g_new.group_name as new_group, TO_CHAR(g_new.change_seen, \'YYYY-mm-dd HH24:MI\') AS change_seen
 379 FROM ( SELECT * FROM current_group_membership_history WHERE channel=? AND ts=? ) g_old
 380   FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE channel=? AND ts=? ) g_new USING (name)
 381 WHERE
 382   g_new.group_name IS DISTINCT FROM g_old.group_name
 383   AND g_new.group_name IS NOT NULL
 384 ORDER BY g_new.change_seen DESC, name
 385 LIMIT 50
 386 EOF
 387         $q->execute($config::invitation_channel, $prev_invitation_ts, $config::invitation_channel, $invitation_ts);
 388         my @recent_moves = ();
 389         while (my $ref = $q->fetchrow_hashref) {
 390                 my $name = $ref->{'name'};
 391                 my $old_group = $ref->{'old_group'};
 392                 my $new_group = $ref->{'new_group'};
 393
 394                 my $msg = $ref->{'change_seen'} . " ";
 395                 if (!defined($old_group)) {
 396                         $msg .= "$name, (ny løper) → $new_group";
 397                 } else {
 398                         $msg .= "$name, $old_group → $new_group";
 399                 }
 400                 push @recent_moves, { values => [{ userEnteredValue => { stringValue => $msg } }] };
 401         }
 402         while (scalar @recent_moves < 50) {
 403                 push @recent_moves, { values => [{ userEnteredValue => { stringValue => '' } }] };
 404         }
 405         return @recent_moves;
 406 }
 407
 408 # Also applies the diff to the database (a bit ugly).
 409 sub find_diff {
 410         my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
 411
 412         my @diffs = ();
 413         for my $real_name (keys %$want_colors) {
 414                 my $wc = $want_colors->{$real_name};
 415                 if (exists($have_colors->{$real_name})) {
 416                         if ($have_colors->{$real_name} eq $wc) {
 417                                 # Already good.
 418                                 next;
 419                         }
 420                         skv_log("Markerer at $real_name har byttet treningssted.");
 421                         push @diffs, [
 422                                 $real_name, { backgroundColor => $rgb{$wc} }
 423                         ];
 424                         $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
 425                                 $wc, $config::invitation_channel, $invitation_ts, $real_name);
 426                 } else {
 427                         skv_log("Markerer at $real_name skal på trening.");
 428                         push @diffs, [
 429                                 $real_name, { backgroundColor => $rgb{$wc} }
 430                         ];
 431                         $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
 432                                 $config::invitation_channel, $invitation_ts, $real_name, $wc);
 433                 }
 434         }
 435         for my $real_name (keys %$have_colors) {
 436                 next if (exists($want_colors->{$real_name}));
 437                 my $sk = sort_key($real_name);
 438                 if (!exists($seen_names->{$sk})) {
 439                         # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
 440                         skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
 441                 } elsif (scalar @{$seen_names->{$sk}} > 1) {
 442                         # Don't touch them.
 443                 } else {
 444                         skv_log("Fjerner at $real_name skal på trening.");
 445                         push @diffs, [
 446                                 $real_name, { backgroundColor => $rgb{white} }
 447                         ];
 448                         $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
 449                                 $config::invitation_channel, $invitation_ts, $real_name);
 450                 }
 451         }
 452         return @diffs;
 453 }
 454
 455 sub possibly_nag_user {
 456         my ($dbh, $ua, $userid, $invitation_ts, $group, $slack_userid_to_slack_name) = @_;
 457
 458         my $slack_name = $slack_userid_to_slack_name->{$userid};
 459
 460         # See if we've nagged this user before.
 461         my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=? AND ts=?');
 462         $q->execute($userid, $invitation_ts);
 463         if (defined($q->fetchrow_hashref)) {
 464                 return;
 465         }
 466
 467         my $msg;
 468         if (!defined($group)) {
 469                 $msg = "Hei! Du meldte deg akkurat på trening, men vi klarer ikke å finne deg i en gruppe i regnearket. For at det skal være enklere for trenerne, ønsker vi gjerne at du går inn på https://regneark.skvidar.run/ og skriver deg inn der med samme navn som du bruker på Slack. Om du er usikker på hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen på trening og til klubben!";
 470                 skv_log("Sender Slack-melding til $slack_name ($userid) for å spørre om gruppe.");
 471         } elsif ($group eq '(flere grupper)') {
 472                 $msg = "Hei! Du meldte deg akkurat på trening, men du ser ut til å stå i flere forskjellige grupper i regnearket. For at det skal være enklere for trenerne, ønsker vi gjerne at du går inn på https://regneark.skvidar.run/ og retter der. Om du er usikker på hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen på trening!";
 473                 skv_log("Sender Slack-melding til $slack_name ($userid) for å spørre om gruppe.");
 474         } else {
 475                 $msg = "Hei! Du er påmeldt gruppe *$group*. Om dette er feil, gå gjerne inn og endre på https://regneark.skvidar.run/. Vi gleder oss til å se deg på trening!";
 476                 skv_log("Sender Slack-melding om at $slack_name ($userid) er i gruppe $group.");
 477         }
 478
 479         my $content = {
 480                 channel => $config::invitation_channel,
 481                 user => $userid,
 482                 text => $msg
 483         };
 484         my $start = [Time::HiRes::gettimeofday];
 485         my $response = $ua->post(
 486                 'https://slack.com/api/chat.postEphemeral',
 487                 Content => JSON::XS::encode_json($content),
 488                 Content_type => 'application/json;charset=UTF-8',
 489                 Authorization => 'Bearer ' . $config::slack_oauth_token
 490         );
 491         log_timing($start, 'chat.postEphemeral');
 492         die $response->status_line if !$response->is_success;
 493         my $msg_json = JSON::XS::decode_json($response->decoded_content);
 494         die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
 495
 496         # Mark that we've sent the message, so it won't happen again.
 497         $dbh->do('INSERT INTO users_nagged (userid, ts, last_nag) VALUES (?, ?, CURRENT_TIMESTAMP)', undef, $userid, $invitation_ts);
 498 }
 499
 500 sub db_connect {
 501         my $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
 502                 or warn "Could not connect to Postgres: " . DBI->errstr;
 503         if (!defined($dbh)) {
 504                 return undef;
 505         }
 506         $dbh->{AutoCommit} = 0;
 507         $dbh->do('LISTEN skvupdate') or return undef;
 508         return $dbh;
 509 }
 510
 511 sub run {
 512         my ($dbh, $ua) = @_;
 513         my $total_start = [Time::HiRes::gettimeofday];
 514
 515         @log = ();
 516         skv_log("Siste sync startet: " . POSIX::ctime(time));
 517
 518         # For the logic on the “applied” table below.
 519         $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
 520
 521         my $token = get_oauth_bearer_token($dbh, $ua);
 522
 523         # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
 524         # TODO: Support more than one, and test better for errors here.
 525         my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 2');
 526         $q->execute($config::invitation_channel);
 527         my $linkref = $q->fetchrow_hashref;
 528         my $invitation_ts = $linkref->{'ts'};
 529         my $wanted_sheet_title = $linkref->{'sheet_title'};
 530         die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
 531         my $tab_name = $linkref->{'tab_name'};
 532         my $tab_id = $linkref->{'tab_id'};
 533
 534         # Store away the second-newest ID.
 535         my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
 536
 537         if (!defined($tab_name) || !defined($tab_id)) {
 538                 ($tab_name, $tab_id) = get_spreadsheet_with_title($dbh, $ua, $token, $invitation_ts, $wanted_sheet_title);
 539                 if (!defined($tab_name)) {
 540                         skv_log("Fant ikke noen fane med «$wanted_sheet_title» i navnet; kan ikke synkronisere.\n");
 541                         sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
 542                         die;
 543                 }
 544         }
 545
 546         # Find everyone who are marked as attending on Slack (via reactions).
 547         $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
 548         $q->execute($config::invitation_channel, $invitation_ts);
 549         my @attending_userids = ();
 550         my %colors = ();
 551         my %double = ();
 552         while (my $ref = $q->fetchrow_hashref) {
 553                 my $userid = $ref->{'userid'};
 554                 push @attending_userids, $userid;
 555                 if ($ref->{'reaction'} eq 'blue_heart') {
 556                         if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
 557                                 $double{$userid} = 1;
 558                         }
 559                         $colors{$userid} = 'blue';
 560                 } else {
 561                         if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
 562                                 $double{$userid} = 1;
 563                         }
 564                         $colors{$userid} = 'yellow';
 565                 }
 566         }
 567
 568         # Remove double-attenders (we will log them as warnings further down).
 569         @attending_userids = grep { !exists($double{$_}) } @attending_userids;
 570         for my $userid (keys %double) {
 571                 delete $colors{$userid};
 572         }
 573
 574         # Get the list of all people in the sheet (we're going to need them soon).
 575         # Also get the Slack mapping when we're doing an API request anyway.
 576         my $start = [Time::HiRes::gettimeofday];
 577         my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue',
 578                 Authorization => 'Bearer ' . $token,
 579                 Accept_Encoding => HTTP::Message::decodable
 580         );
 581         log_timing($start, "/spreadsheets/");
 582
 583         my $sheets_json = JSON::XS::decode_json($response->decoded_content);
 584         if (!exists($sheets_json->{'sheets'})) {
 585                 die "Missing sheets (error response?): " . $response->decoded_content;
 586         }
 587         my $main_sheet_json = $sheets_json->{'sheets'}[0];
 588         my $mapping_sheet_json = $sheets_json->{'sheets'}[1];
 589
 590         # Update the list of groups we've seen people in.
 591         $start = [Time::HiRes::gettimeofday];
 592         my %assignments = get_group_assignments($main_sheet_json);
 593         log_timing($start, "Parsing group assignments");
 594         $start = [Time::HiRes::gettimeofday];
 595         update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
 596         log_timing($start, "Updating assignments in database");
 597
 598         $start = [Time::HiRes::gettimeofday];
 599         my %seen_names = find_where_each_name_is($main_sheet_json);
 600         log_timing($start, "Making sort key reverse mapping");
 601
 602         # Find duplicates.
 603         for my $name (sort keys %seen_names) {
 604                 my $seen = $seen_names{$name};
 605                 if (scalar @$seen >= 2) {
 606                         my $exemplar = $seen->[0][0];
 607                         skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
 608                 }
 609         }
 610
 611         # Get our existing Slack->name mapping, from the sheets.
 612         my %slack_userid_to_real_name = ();
 613         my %slack_userid_to_slack_name = ();
 614         my %slack_userid_to_row = ();
 615
 616         my $mapping_sheet_rows = $mapping_sheet_json->{'data'}[0]{'rowData'};
 617         my $cur_row = 5;
 618         for my $row (@$mapping_sheet_rows) {
 619                 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
 620                 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
 621                 my $real_name = get_spreadsheet_name($row->{'values'}[2]);  # TODO support more
 622                 $slack_userid_to_row{$slack_id} = $cur_row++;
 623                 next if (!defined($slack_name));
 624                 $slack_userid_to_slack_name{$slack_id} = $slack_name;
 625                 next if (!defined($real_name));
 626                 $slack_userid_to_real_name{$slack_id} = $real_name;
 627         }
 628
 629         # See which ones we don't have a mapping for, and look them up in Slack.
 630         # TODO: Use an append call instead of $cur_row?
 631         my @slack_mapping_updates = ();
 632         for my $userid (@attending_userids) {
 633                 next if (exists($slack_userid_to_real_name{$userid}));
 634
 635                 # Make sure they have a row in the spreadsheet.
 636                 my $write_row;
 637                 if (exists($slack_userid_to_row{$userid})) {
 638                         $write_row = $slack_userid_to_row{$userid};
 639                 } else {
 640                         $write_row = $cur_row++;
 641                         $slack_userid_to_row{$userid} = $write_row;
 642                         push @slack_mapping_updates, {
 643                                 range => "Slack-mapping!A$write_row:A$write_row",
 644                                 values => [ [ $userid ]]
 645                         };
 646                 }
 647
 648                 # Fetch their Slack name if we don't already have it.
 649                 my $slack_name;
 650                 if (exists($slack_userid_to_slack_name{$userid})) {
 651                         $slack_name = $slack_userid_to_slack_name{$userid};
 652                 } else {
 653                         $slack_userid_to_slack_name{$userid} = $slack_name;
 654                         $slack_name = get_slack_name($ua, $userid);
 655                         push @slack_mapping_updates, {
 656                                 range => "Slack-mapping!B$write_row:B$write_row",
 657                                 values => [ [ $slack_name ]]
 658                         };
 659                         $slack_userid_to_slack_name{$userid} = $slack_name;
 660                 }
 661
 662                 if (exists($seen_names{sort_key($slack_name)})) {
 663                         # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
 664                         $slack_userid_to_real_name{$userid} = $slack_name;
 665                         push @slack_mapping_updates, {
 666                                 range => "Slack-mapping!C$write_row:C$write_row",
 667                                 values => [ [ $slack_name ]]
 668                         };
 669                 } else {
 670                         # Do a search through all the available names in the sheet to find an obvious(ish) match.
 671                         my @candidates = ();
 672                         my $main_sheet_rows = $main_sheet_json->{'data'}[0]{'rowData'};
 673                         $start = [Time::HiRes::gettimeofday];
 674                         my @ap = map { sort_key($_) } split /\s+/, $slack_name;  # Precalc for matches_name().
 675                         for my $row (@$main_sheet_rows) {
 676                                 for my $val (@{$row->{'values'}}) {
 677                                         my $name = get_spreadsheet_name($val);
 678                                         if (defined($name) && matches_name($slack_name, $name, \@ap)) {
 679                                                 push @candidates, $name;
 680                                         }
 681                                 }
 682                         }
 683                         log_timing($start, "Fuzzy-searching for Slack name $slack_name");
 684                         if ($#candidates == -1) {
 685                                 skv_log("$slack_name ($userid) er påmeldt på Slack, men fant ikke et regneark-navn for dem.");
 686                                 possibly_nag_user($dbh, $ua, $userid, $invitation_ts, undef, \%slack_userid_to_slack_name);
 687                         } elsif ($#candidates == 0) {
 688                                 my $name = $candidates[0];
 689                                 $slack_userid_to_real_name{$userid} = $name;
 690                                 push @slack_mapping_updates, {
 691                                         range => "Slack-mapping!C$write_row:C$write_row",
 692                                         values => [ [ $name ]]
 693                                 };
 694                         } else {
 695                                 skv_log("$slack_name ($userid) er påmeldt på Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
 696                         }
 697                 }
 698         }
 699         if (scalar @slack_mapping_updates > 0) {
 700                 my $update = {
 701                         valueInputOption => 'USER_ENTERED',
 702                         data => \@slack_mapping_updates
 703                 };
 704                 $start = [Time::HiRes::gettimeofday];
 705                 $response = $ua->post(
 706                         'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
 707                         Content => JSON::XS::encode_json($update),
 708                         Content_type => 'application/json;charset=UTF-8',
 709                         Authorization => 'Bearer ' . $token
 710                 );
 711                 log_timing($start, "/spreadsheets/values:batchUpdate");
 712                 die $response->decoded_content if (!$response->is_success);
 713         }
 714
 715         # Now that we have Slack names, we can log double-reacters.
 716         for my $userid (keys %double) {
 717                 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
 718                 skv_log("$name er påmeldt flere steder på Slack; vet ikke hvilken som skal brukes.");
 719         }
 720
 721         # ...and possibly send welcome messages to remind them of groups.
 722         for my $userid (@attending_userids) {
 723                 my $real_name = $slack_userid_to_real_name{$userid};
 724                 next if (!defined($real_name));
 725                 my $group = $assignments{$real_name};
 726                 next if (!defined($group));
 727                 possibly_nag_user($dbh, $ua, $userid, $invitation_ts, $group, \%slack_userid_to_slack_name);
 728         }
 729
 730         # Find the list of names to mark yellow.
 731         my %want_colors = ();
 732         my $main_sheet_rows = $main_sheet_json->{'data'}[0]{'rowData'};
 733         for my $userid (@attending_userids) {
 734                 next if (!exists($slack_userid_to_real_name{$userid}));
 735                 my $slack_name = $slack_userid_to_slack_name{$userid};
 736                 my $real_name = $slack_userid_to_real_name{$userid};
 737
 738                 # See if we can find them in the spreadsheet.
 739                 my $sk = sort_key($real_name);
 740                 if (!exists($seen_names{$sk})) {
 741                         # TODO: Perhaps move this logic further down, for consistency?
 742                         skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
 743                 } else {
 744                         my $seen = $seen_names{$sk};
 745                         if (scalar @$seen >= 2) {
 746                                 skv_log("$slack_name ($userid) er påmeldt på Slack, men står flere steder (se over); vet ikke hvilken celle som skal brukes.");
 747                         } else {
 748                                 $want_colors{$seen->[0][0]} = $colors{$userid};
 749                         }
 750                 }
 751         }
 752
 753         # Find the list of names we already marked yellow.
 754         my %have_colors = ();
 755         $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
 756         $q->execute($config::invitation_channel, $invitation_ts);
 757         while (my $ref = $q->fetchrow_hashref) {
 758                 $have_colors{$ref->{'name'}} = $ref->{'color'};
 759         }
 760
 761         my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
 762
 763         my @yellow_updates = ();
 764         if (scalar @diffs > 0) {
 765                 # Now fill in the actual stuff.
 766                 for my $diff (@diffs) {
 767                         my $real_name = $diff->[0];
 768
 769                         my $seen = $seen_names{sort_key($real_name)};
 770
 771                         # We've already complained about these earlier, so just skip them silently.
 772                         next if (scalar @$seen > 1);
 773
 774                         # See if we can find them in the spreadsheet.
 775                         die "Could not find $real_name" if (!defined($seen));
 776                         my $rowno = $seen->[0][1];
 777                         my $colno = $seen->[0][2];
 778                         push @yellow_updates, {
 779                                 updateCells => {
 780                                         rows => [{
 781                                                 values => [{
 782                                                         userEnteredFormat => $diff->[1]
 783                                                 }]
 784                                         }],
 785                                         fields => 'userEnteredFormat.backgroundColor',
 786                                         range => {
 787                                                 sheetId => $tab_id,
 788                                                 startRowIndex => $rowno,
 789                                                 endRowIndex => $rowno + 1,
 790                                                 startColumnIndex => $colno,
 791                                                 endColumnIndex => $colno + 1
 792                                         }
 793                                 }
 794                         };
 795                 }
 796         }
 797
 798         my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
 799         push @yellow_updates, {
 800                 updateCells => {
 801                         rows => \@recent_changes,
 802                         fields => 'userEnteredValue.stringValue',
 803                         range => {
 804                                 sheetId => $config::log_tab_id,
 805                                 startRowIndex => 4,
 806                                 endRowIndex => 4 + scalar @recent_changes,
 807                                 startColumnIndex => 0,
 808                                 endColumnIndex => 1
 809                         }
 810                 }
 811         };
 812
 813         my @recent_moves = create_move_log($dbh, $invitation_ts, $prev_invitation_ts);
 814         push @yellow_updates, {
 815                 updateCells => {
 816                         rows => \@recent_moves,
 817                         fields => 'userEnteredValue.stringValue',
 818                         range => {
 819                                 sheetId => $config::log_tab_id,
 820                                 startRowIndex => 4,
 821                                 endRowIndex => 4 + scalar @recent_moves,
 822                                 startColumnIndex => 1,
 823                                 endColumnIndex => 2
 824                         }
 825                 }
 826         };
 827
 828         # Push the final set of updates (including the log).
 829         skv_log("Ferdig.");
 830         push @yellow_updates, serialize_skv_log_to_sheet();
 831         sheet_batch_update($ua, $token, \@yellow_updates);
 832         $dbh->commit;
 833
 834         my $elapsed = Time::HiRes::tv_interval($total_start);
 835         printf "Tok %.0f ms.\n", 1e3 * $elapsed;
 836         print "\n";
 837 }
 838
 839 # Initialize the handles we need for communication.
 840 my $dbh = db_connect() or die;
 841 my $ua = LWP::UserAgent->new(agent => 'SKVidarLang/1.0', keep_alive => 50);
 842 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
 843         # Start with a single, forced run.
 844         run($dbh, $ua);
 845
 846         while (1) {
 847                 while (!defined($dbh) || !$dbh->ping) {
 848                         print STDERR "Database connection lost, reconnecting...\n";
 849                         sleep 1;
 850                         eval {
 851                                 $dbh = db_connect();
 852                         };
 853                 }
 854                 my $s = IO::Select->new($dbh->{pg_socket});
 855                 my @ready = $s->can_read(150.0);  # slack.com HTTP timeout is ~3 minutes, sheets.googleapis.com is ~4 minutes.
 856                 my @exceptions = $s->has_exception(0.0);
 857
 858                 if (scalar @exceptions > 0) {
 859                         $dbh->disconnect;
 860                         $dbh = undef;
 861                         next;
 862                 }
 863                 if (scalar @ready > 0) {
 864                         eval {
 865                                 run($dbh, $ua);
 866                         };
 867                         if ($@) {
 868                                 warn "Died with: $@";
 869                                 $dbh = undef;
 870                         }
 871                 } else {
 872                         # Keep the connections alive and the token in the database fresh.
 873                         # (The two URLs we use don't really exist. Note that the first time,
 874                         # we might be making the initial connection to slack.com, since it's
 875                         # not a given that run() needed to talk to them.)
 876                         get_oauth_bearer_token($dbh, $ua);
 877                         $dbh->commit;
 878                         #my $start = [Time::HiRes::gettimeofday];
 879                         $ua->get('https://sheets.googleapis.com/ping');
 880                         #log_timing($start, 'sheets.googleapis.com (keepalive)');
 881                         #$start = [Time::HiRes::gettimeofday];
 882                         $ua->get('https://slack.com/api/ping');
 883                         #log_timing($start, 'slack.com (keepalive)');
 884                         #print STDERR "\n";
 885                 }
 886         }
 887 } elsif ($#ARGV >= 0 && $ARGV[0] eq '--benchmark') {
 888         for my $i (0..9) {
 889                 run($dbh, $ua);
 890         }
 891 } else {
 892         run($dbh, $ua);
 893 }