#!/s/std/bin/perl

use warnings;

use DBI;
use CSL::Pg;
use CSL::DBs;
#use Text::CSV;

my $dbh = DBI->connect($CSL::DBs::csdb)
  or die "Could not connect to CSDB: $!";
$dbh->do("set search_path to csdb");

my %csdb_alums;
my %manifest_alums;
my @new_alums;

my $name; 
my $email;
my $netid;

# must supply one argument, a CSV file for input
if ($#ARGV != 0 ) {die "Usage: compare-alumni.pl [CSV-file]\n";}
my $csv_file = $ARGV[0]; 
if (! -f $csv_file ) {die "CSV file $csv_file does not exist!\n";}

# get data from CSDB
my $alum_query = $dbh->prepare("select i.netid from people p, id_crosswalk_uwmsn i where p.univid = i.univid and p.alum = 1;");
$alum_query->execute;

while (my $row = $alum_query->fetchrow_hashref) {
  # stash each entry in a hash
  $csdb_alums{lc($row->{netid})} = lc($row->{netid});
}

# now get data from CSV file
#my $csv = Text::CSV->new || die "Can't initialize Text::CSV for some reason!\n";
open CSVFILE, $csv_file || die "Can't open CSV file $csv_file!\n";
while (<CSVFILE>) {
  chomp;
  ($name, $email, $netid) = split(/,/);
  $netid =~ s/\"//g;
  $netid =~ s/\s*//g;
  if ($netid ne "") {
    # not-null netid, run through lc and stash in %manifest_alums
    $manifest_alums{lc($netid)} = lc($netid);
  }
}
close CSVFILE;

for (sort(keys(%csdb_alums))) {
  if (! $manifest_alums{$_} ) {
    print "Adding $_ as new alum\n";
    push(@new_alums, $_);
  }
}

if ( @new_alums > 0 ) {
  # we have alums to add
  print "You will need to add the following alumni to Manifest at\n";
  print "https://manifest.services.wisc.edu in the CS Alumni group\n\n";
  print "The following comma-separated list can be added directly:\n\n";
  foreach (@new_alums) {
    print "$_,";
  }
}

$alum_query->finish;
$dbh->disconnect;
