#!/s/std/bin/perl

# $Id: affiliated-faculty.pl,v 1.1 2023/11/13 15:29:21 dparter Exp $

# affiliated-faculty as a separate list

use strict;
use warnings;
use Getopt::Std;
use DBI;
use CSL::Pg;
use CSL::DBs;

our($opt_o, $opt_s, $opt_d);

my $schema = 'csdb';
my $debug = 0;

getopts('do:s:');

if ($opt_o) {
    open(LISTFILE, ">$opt_o") || die "can't open $opt_o: $!";
    open(ALTFILE, ">$opt_o" . "-alts") || die "can't open $opt_o" ."-alts: $!";
} else {
    die "need -o out_name (will create outname and outname-alts)";
}
if ($opt_s) {
    $schema = $opt_s;
}
if ($opt_d) {
    $debug = 1;
}

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

my $query_body = " $schema.people p where p.status like '%J%' and p.login is not null";

# Header
# no header this time

# get the data now
my $faculty_query = $dbh->prepare(
    "select login from "
    . $query_body
    . " order by login");

my $alt_query =  $dbh->prepare(
    "select e.o365_address from $schema.o365_email_addresses e, " 
    . $query_body 
    . " and p.login = e.domain_address and e.domain = 'cs.wisc.edu'");

$faculty_query->execute;
while (my $row = $faculty_query->fetchrow_hashref) {
  print LISTFILE "$row->{login}\@cs.wisc.edu\n";
}
$faculty_query->finish;
close(LISTFILE);

$alt_query->execute;
while (my $row = $alt_query->fetchrow_arrayref) {
    print ALTFILE "$$row[0]\n";
}
$alt_query->finish;
close(ALTFILE);


# Footer
# no footer this time

# clean up
$dbh->disconnect;
