#!/usr/bin/perl

use v5.12;
use strict;
use warnings;

use DBI;
use File::Slurp;
use Text::CSV_XS;
use Tie::File;
use Data::Dumper;

#  Start logging
use Log::Log4perl qw(:easy);
binmode( STDOUT, ":utf8" );
use Log::Log4perl::CommandLine
	qw(:all :logconfig logging.cfg);

my $time_start = time;
INFO qq{Begin Program: $time_start};
DEBUG("Debug flag turned on");
TRACE("Trace flag turned on");

my $commit_frequency = 500000;
my $memory_limit = q{0};

#my $data_file = q{ShortVoters.txt};
my $data_file = q{AllVoters_01122016.txt};

# grant all on tmp_voters.* to testuser identified by 'testpassword';
my $database = q{tmp_voters};
my $hostname = q{localhost};
my $port     = q{3306};
my $user = q{testuser};
my $password = q{testpassword};

###  Get Column Names
my @column_names = read_file("record.cfg", chomp => 1) or LOGDIE;

###  Setup Database
my $dsn = qq{DBI:mysql:database=$database;host=$hostname;port=$port};
my $dbh = DBI->connect($dsn, $user, $password);
$dbh->{AutoCommit} = 0;  # enable transactions, if possible
$dbh->{RaiseError} = 1;

my $create_stmt = q{create or replace table voters (} . join(q{ TEXT, }, @column_names) . q{ TEXT);};
INFO $create_stmt;
$dbh->do($create_stmt) or LOGDIE;

my $insert_stmt = q{insert into voters values (} . "?" . ", ?" x ( (scalar @column_names) - 1) . q{)};
INFO $insert_stmt;
my $sth_insert = $dbh->prepare($insert_stmt);


###  Setup Data File
use Fcntl 'O_RDONLY';
tie my @data_file, 'Tie::File', $data_file , autochomp => 0 , mode => O_RDONLY , memory => $memory_limit or LOGDIE;

###  Setup CSV Parser
my $csv = Text::CSV_XS->new ( { binary => 1, auto_diag => 1 } )  # should set binary attribute.
	or LOGDIE "Cannot use CSV: ".Text::CSV_XS->error_diag ();

my $nbr_rows = 0;

INFO q{Entering foreach loop at } . (time - $time_start) . q{ second(s)};
foreach my $line (@data_file) {

	TRACE $line;
	$csv->parse( $line ) or LOGDIE( qq{CSV Error: } . Text::CSV_XS->error_diag () );
             
	if ( not $nbr_rows ) {
		INFO q{First Record Parsed at } . (time - $time_start) . q{ seconds};
	}

	$sth_insert->execute($csv->fields) or LOGDIE;
	$nbr_rows++;

	if ( $nbr_rows % $commit_frequency == 0 ) {
		$dbh->commit or LOGDIE;
		dump_elapsed_time();
	}
}

$dbh->commit or LOGDIE;
dump_elapsed_time();

$csv->eof or $csv->error_diag();

exit;


#############################################################################
#
#
#
#############################################################################
sub dump_elapsed_time {

	my $time_elapsed = time - $time_start;

	INFO sprintf(q{% 5.1fM rows processed in %3d minutes | %5d rows/second | %.1e seconds/row}, 
					$nbr_rows/1000000, $time_elapsed/60, $nbr_rows/$time_elapsed , $time_elapsed/$nbr_rows);

}

__END__