#!/usr/bin/perl

use DBI;

#example running
#csv_load_db.pl Changed.csv pat_regan_combined create_table.sql

$host="localhost";
$port=5433;
$db="geopolitical";
$user="foobar";
$pass="guess";

$incoming = $ARGV[0];
$table = $ARGV[1];
$create_sql = $ARGV[2];

unless(open OUTPUT, $incoming) {&logged("ERROR! Not open $incoming file - $!"); exit;}
@MAIN_DATA = <OUTPUT>;
unless(close OUTPUT) {&logged("ERROR! Not close $incoming file - $!"); exit;}
chomp(@MAIN_DATA);
@headers = split /\s*,\s*/,$MAIN_DATA[0];
shift @MAIN_DATA;

$dbh = DBI->connect("dbi:Pg:dbname=$db;host=$host;port=$port;", $user, $pass, {AutoCommit => 0}) or die $DBI::errstr;

&create if $create_sql ne "";

$dbh->commit if &insert;

$dbh->disconnect;
exit;

sub insert
{
    foreach my $str (@headers)
    {
        chomp($str);
        $str =~ s/\n|\r//g;
        $str = qq~"$str"~;
    }
    my $fields = lc(join (",",@headers));
    foreach my $str (@MAIN_DATA)
    {
        my $sql = "INSERT INTO $table ($fields) values (";
        @tmp = &mysplit("\s*,\s*",$str);
        my @values = ();
        foreach my $str (@tmp)
        {
            $str =~ s/\n|\r|\"|\'//g;
            if($str eq " " || $str eq "" || $str eq "." || $str eq "!!!") {$str = "NULL"}
            elsif($str =~ /^\w{3}([-|\/])(\d{2})$/)
            {
                my $year=$2;
                my $char=$1;
                my $mask="Mon".$char."yyyy";
                if($2>10){$year+=1900;}
                else{$year+=2000;}
                $str =~ s/$char$2/$char$year/;
                $str = qq~to_date('$str','$mask')~;
            }
            elsif($str =~ /\d+(.)\d+\1\d{4}/){$str = qq~to_date('$str','dd$1mm$1yyyy')~}
            #elsif($str =~ /\d+\/\d+/){$str = qq~to_date('$str','mm/yy')~; print $str."\n";}
            elsif($str =~ /^\d+$/){$str = $str;}
            else {$str = $dbh->quote($str)}
            push @values,$str;
        }
        $sql.=join(",",@values).")";
        $sth=$dbh->prepare($sql);
        $sth->execute;
        if($DBI::errstr)
        {
            &logged("\nERROR in sql - $sql\n");
            $dbh->rollback;
            return 0;
        }
    }
    #$dbh->commit;
    print "COMPLETE\n";
    return 1;
}

sub create
{
    unless(open OUTPUT, $create_sql) {&logged("ERROR! Not open $create_sql file - $!"); exit;}
    my @SQL = <OUTPUT>;
    unless(close OUTPUT) {&logged("ERROR! Not close $create_sql file - $!"); exit;}
    chomp(@SQL);
    my $sql=lc(join(" ",@SQL));
    $dbh->do($sql);
    if ($DBI::errstr) {$dbh->rollback;}
    else {print "TABLE CREATE!!\n";$dbh->commit;}
    return 1;
}

sub logged($)
{
    my $message = shift;
    print $message."\n";
    return 1;
}

sub mysplit($$)
{
    my $sign = shift;
    my $str = shift;
    my @tmp = split /$sign/,$str;
    my @res = ();
    my $flag=0;
    my $str_add="";
    foreach my $el (@tmp)
    {
        $flag++ if $el =~ /"/;
        push @res, $el if !$flag;
        if ($flag>0 && $flag<2){$str_add.=$el.", "}
        elsif($flag==2)
        {
            $str_add.=$el;
            push @res,$str_add;
            $flag=0;
            $str_add="";
        }
    }
    return @res;
}