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