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