#!/usr/bin/perl
#
# initial comments section
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
# end of initial comments section
#
use strict;
use warnings;
use DBI;
use Getopt::Long;
use Text::CSV;


my $delete = "";
my $table = "";
my $dbname = "";
my $user = "";
my $password = "";
my $terminator = ",";
my $ignore = 0;
my $optenclosed = '"';
my $db;
my $prep;
my $fh;

GetOptions('delete' => \$delete,
            'table=s' =>\$table,
            'dbname=s' => \$dbname,
            'user=s' => \$user,
            'password=s' => \$password,
            'fields-terminated-by=s' => \$terminator,
            'ignore-lines=i' => \$ignore,
            'fields-optionally-enclosed-by=s' => \$optenclosed);


# check there is the mandatory options
if ($table eq '' or $dbname eq '' or $user eq '' or $password eq '' or $#ARGV == -1) {
    die("Incorrect options: Need tablename, database, user, password and at least 1 filename\n");
}

# open the database connection
$db = DBI->connect("DBI:mysql:$dbname", $user, $password) or die "Cannot open database\n";

# delete the contents of the table if delete is set
if ($delete) {
     $prep = $db->prepare("DELETE FROM $table") or die "Cannot prepare database " . $db->errstr() . "\n";
     if (!$prep->execute()) { die "Failed to delete rows" . $db->errstr() . "\n"; }
}

# create the csv object
my $csv = Text::CSV->new({binary => 1,
                        quote_char => $optenclosed,
                        sep_char => $terminator});

# main loop for each of the passed files
foreach my $argnum (0..$#ARGV) {
    if (!open($fh, '<', $ARGV[$argnum])) {
        print "Warning: Could not open " . $ARGV[$argnum] . " so file skipped\n";
    } else {
        # if there is an ignore setting, loop around discarding those lines
        for(my $i = 0; $i < $ignore; $i++) {
            my $row = $csv->getline($fh);
        }

        # now perform the database write
        while (my $row = $csv->getline($fh)) {
            $prep = $db->prepare("INSERT INTO $table VALUES (\"" . join('","', $csv->fields()) . "\")")
                    or die "Cannot prepare database " . $db->errstr() . "\n";
            if (!$prep->execute()) { die "Failed to write row " . $db->errstr() . "\n"; }
        }
        $csv->eof or $csv->error_diag();
        print "Successfully written $dbname into $table using " . $ARGV[$argnum]. "\n";
    }
}