#!/usr/bin/perl -w
use strict;
use warnings;
use Data::Dumper;
use DBI;

my $file = '/home/hesco/lists/voters/ca/psr00693.txt';

my $db = 'voters';
my $host = '127.0.0.1';
my $user = 'vtr_user';
my $pw = 'secret';
my $dsn = "dbi:Pg:database=$db;host=$host";
my $dbh = DBI->connect($dsn, $user, $pw,
              { RaiseError => 0, PrintError => 0, AutoCommit => 0 }) or die $DBI::errstr;

my $precinct_sql =<<EOQ;
SELECT precinct_ndx
FROM precinct
WHERE precinct = ? AND precinct_part = ?;
EOQ

my @fields = ();
my $counter = 0;
my $field_names;
my ($insert_reg,$sth,$prec_id);
my ($insert_address,$phone,$insert_hy,$t);
my ($m,$d,$y);
my $restart = 0;
open ('VOTERS','<',$file);
while (<VOTERS>){
  $counter++;
  @fields = split ",",$_; # for csv
  # @fields = split "\t",$_; # for tab delimited
  my($ndx);
  if($counter == 1) {
    foreach my $ndx (0 .. (0 + @fields)){
      $fields[$ndx] =~ s/\ *$//;
      $fields[$ndx] =~ s/\s*$//;
      $fields[$ndx] =~ s/'/\\'/;
    }
    my $field_names_tmp = \@fields;
    # print STDERR "\$field_names_tmp is: ", Dumper($field_names_tmp);
    foreach my $i (0 .. (0 + @fields -1)){
      $field_names->{'ndx'}->{$i} = $fields[$i];
      $field_names->{'field_name'}->{$fields[$i]} = $i;
    }
    # print "\$field_names is: \n", Dumper(\$field_names);
    foreach my $i (0 .. (0 + @fields -1)){
      print "$i -- $fields[$i]\n";
    }
    next;
  }

  # registrants table
  my @reg_indexes = (0,1,25,2,3,4,5,5,26,27,28,29,30,31,34,35,36,39);
  my @hy_indexes = (1,58,59,60,61,62,63,64,65);
  my @addr_indexes = (1,6,7,8,9,10,11,12,13,14,15,16);
  # define the indexes for any other tables 
  # into which you want to normalize this data

  $sth = $dbh->prepare($precinct_sql);
  $sth->execute($fields[32],$fields[33]);
  ($prec_id) = $sth->fetchrow_array();
  if(!defined($prec_id)){ $prec_id = -1; }

  $insert_reg = "INSERT INTO registrant VALUES(";
  $insert_reg = _build_insert(\@fields,$insert_reg,@reg_indexes);
  $insert_reg =~ s/,$/,$prec_id);/;
  # print "\$insert_reg is: " . $insert_reg . "\n";
  # print "registrant_id is: " . $fields[1] . ":";
  $sth = $dbh->prepare($insert_reg);
  $sth->execute() or warn "\$sql is: $insert_reg, error is: " . $dbh->errstr;


  print "\$registrant_id is: " . $fields[1] . "\t" . $phone . "\n"; # , $insert_hy,"\n";
  $dbh->commit;
}

1;

sub _build_insert {
  my($fields, $insert, @indexes) = @_;

  foreach my $field (@indexes){
    unless(defined($fields[$field])){
      $insert .= "'',";
      next;
    }
    $fields[$field] =~ s/\ *$//;
    $fields[$field] =~ s/\s*$//;
    $fields[$field] =~ s/'/\\'/;
    $insert .= "'$fields[$field]',";
  }

  return $insert;
}