#!/usr/bin/perl
#
# Author: Petter Reinholdtsen <pere@hungry.com>
# Date:   2002-11-20
#
# Import the CSV-formated member database for NUUG into an SQL table.
#
# Dependencies: libtext-csv-perl

use strict;
use warnings;

use Getopt::Std;
use DBI;
use Text::CSV_XS;

sub usage {
    print <<EOF;
Usage: $0 [-de] [-f <filename>] [-t type]
Import or export member CVS file from SQL database.

  -f filename  load member database from filename
  -d           enable debug mode
  -e           extract CVS file from database
  -u dbuser    SQL user name to use when connecting
  -t type      select SQL database driver (SQLite, mysql, Pg)
EOF
    exit 1;
}

use vars qw($dbdriver $dbname $dbhost $dbuser $dbpasswd $filename $dbh $dropped);

my %opts;
getopts("def:u:t:", \%opts) || usage();

$dbdriver = "Pg"; # SQLite, mysql or Pg
$dbname = "nuugdb";
$dbhost = "sqldb.nuug.no";
$dbuser = $opts{u} || "pere";
$dbpasswd = load_pw($ENV{'HOME'}. '/.nuug-fpj-pwd') || "secret" ;

$| = 1;

my $dropped = 0;
my $added = 0;

my $dbtable = "nuug_member_list";
my @fields;

my $sql_timetype;
my $sql_timetype_default_now;
my $sql_timetype_default_now_update;
my $sql_utf8_row;
my $sql_extra;

$dbdriver = $opts{t} || $dbdriver;

$filename = $opts{f} || "medlemsliste.csv";

if ("Pg" eq $dbdriver) {
    $sql_timetype = "timestamp without time zone";
    $sql_timetype_default_now = "timestamp not null default now()";
    $sql_timetype_default_now_update = "timestamp default now()";
    $sql_utf8_row = "";
    $sql_extra = ""; # XXX Add trigger
} elsif ("mysql" eq $dbdriver) {
    $sql_timetype = "datetime";
    $sql_timetype_default_now = "timestamp not null default 'NOW()'";
    $sql_timetype_default_now = "timestamp default current_timestamp";
    $sql_timetype_default_now_update
        = "timestamp default current_timestamp on update current_timestamp";
    $sql_utf8_row = "character set utf8";
} elsif ("SQLite" eq $dbdriver) {
    $sql_timetype = "timestamp";
    die "undefined variable \$sql_timetype_default_now";
    $dbname = "medlemsliste.sqlite";
} else {
    die "Unhandled database driver $dbdriver";
}

my $newtablespec = <<EOF;
CREATE TABLE $dbtable (
  CustomerNo           integer PRIMARY KEY NOT NULL,
  Name                 text,
  EmailAddress         text,
  USENIX               integer,
  SAGE                 integer,
  ZUsrMedlemstatus     text,
  ZUsrMedlemstatusNo   integer,
  ZUsrArbeidsgiver     text,
  ZUsrArbeidsgiverNo   integer,
  MobileTelephone      text,
  Telephone            text,
  Address1             text,
  Address2             text,
  Address3             text,
  PostCode             text,
  PostOffice           text,
  Country              text,
  Kontaktperson        text,
  unixlogin            text,
  Informasjon          integer,
  ZUsrFakturertPeriode text,
  ZBetaltTil           text,
  ZUsrFakturaReferanse text,
  ErArbeidsgiver       integer,
  ZUsrInnmeldtDato     $sql_timetype,
  ZUsrUtmeldtDato      $sql_timetype,
  recipientOrgNo       text,
  StudentBevis         integer,
  EHFfaktura           integer,
  record_updated       $sql_timetype_default_now_update
) $sql_utf8_row;
$sql_extra;
EOF

my @newtableent = qw(CustomerNo Name EmailAddress USENIX SAGE
                     ZUsrArbeidsgiverNo ZUsrArbeidsgiver Kontaktperson
                     ZUsrMedlemstatusNo ZUsrMedlemstatus
                     MobileTelephone Telephone Address1 Address2
                     Address3 PostCode PostOffice Country unixlogin
                     Informasjon ZUsrFakturertPeriode
                     ZUsrFakturaReferanse ErArbeidsgiver
                     ZUsrInnmeldtDato ZUsrUtmeldtDato ZBetaltTil
                     recipientOrgNo StudentBevis EHFfaktura );

sub clean_or_create_table_if_missing {
    my ($dbh) = @_;

#    $dbh->do("DROP TABLE $dbtable;");

    if ( ! $dbh->do("SELECT count(*) from $dbtable;") ) {
        $dbh->do($newtablespec) ||
            die "Unable to create table:\n$newtablespec\n";
    } else {
        $dbh->do("DELETE FROM $dbtable;");
    }
}

sub add_member {
    my ($dbh, @f) = @_;

    # Handle the old data format, without email address
    if (11 == scalar(@f)) {
        my $type = pop @f;
        push @f, ""; # no email address
        push @f, $type;
    }

    my $values = join(",", map { "?" } @f);

    # Convert empty values to null/undef
    @f = map { $_ ne "" ? $_ : undef } @f;

    my $sql = "INSERT INTO $dbtable (". join(",", @fields) .
        ") VALUES ($values)";
    my $sth = $dbh->prepare($sql);
    if ( ! $sth->execute(@f) ) {
        print "Problem importing person:\n  '", join ("'\n  '", @f),"'\n";
        $dropped++;
        print "  $sql\n";
    } else {
        $added++;
        print ".";
    }
}

sub dump_cvs {
    my $dbh = shift;
    binmode(STDOUT, ":encoding(iso-8859-1)");

    print join("\t", @newtableent), "\n";
    my $sql = "SELECT " . join(",", @newtableent) . " FROM $dbtable ORDER BY CustomerNo;";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my @row = $sth->fetchrow_array()) {
        print join("\t", map { defined $_ ? $_ : "" } @row), "\n";
    }
}

$dbh = DBI->connect("dbi:$dbdriver:dbname=$dbname;host=$dbhost", $dbuser, $dbpasswd) # ;host=$dbhost
    || die "Unable to connect to DB: $DBI::errstr";

if ($dbdriver eq "Pg") { # How is this done with other databases?
    $dbh->do("SET CLIENT_ENCODING TO 'UTF-8'");
} elsif ("mysql" eq $dbdriver) {
    $dbh->do("SET NAMES 'latin1'");
} elsif ("SQLite" eq $dbdriver) {
    print STDERR "error: unable to set latin1 charset with $dbdriver\n";
}

if ($opts{e}) {
    dump_cvs($dbh);
} else {
    clean_or_create_table_if_missing($dbh);

    open(FILE, '<:encoding(iso-8859-1)', "$filename")
        || die "Unable to read from $filename";
    my $csv = Text::CSV_XS->new({'binary' => 1, sep_char => "\t"});
    my $line = <FILE>;
    my $status = $csv->parse($line);
    die unless $status;
    @fields = $csv->fields();
    while (<FILE>) {
        chomp;
        s/\r//;
        if (my $status = $csv->parse($_) ) {
            add_member($dbh, $csv->fields() );
        } else {
            print "Unable to parse $_\n";
        }
    }
    print "\n";

    print "Inserted $added entries, dropped $dropped problematic entries\n";
}

$dbh->disconnect();

sub load_pw {
    my $filename = shift;
    open(my $fh, "<", $filename) || return;
    my $pwd = <$fh>;
    close ($fh);
    chomp $pwd;
    return $pwd;
}