#!/usr/bin/perl

use Martian::Toolkit;
use Martian::Countries;
use DBI;

$database="leadcapture";
$hostname="localhost";
$user="root";
$password="##########";
$options="";

$tab = "\t";


$driver = "mysql";
$dsn = "DBI:$driver:database=$database;$options";
$dbh = DBI->connect($dsn, $user, $password) || die "Can't connect";

$lput = $dbh->prepare("INSERT IGNORE INTO leads(firsthit,lastname,firstname,email,title,phone,company,addr,addr2,city,state,postal,country,hitcount) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,1);");
$lfind = $dbh->prepare("SELECT leadid FROM leads WHERE email = ?;");
$lgetbyemail = $dbh->prepare("SELECT * FROM leads WHERE email = ?;");
$lgetbyid = $dbh->prepare("SELECT * FROM leads WHERE leadid = ?;");
$lfind = $dbh->prepare("SELECT leadid FROM leads WHERE email = ?;");
$lgetsources = $dbh->prepare("SELECT sourceids FROM leads WHERE leadid = ?;");
$lsetsources = $dbh->prepare("UPDATE leads SET sourceids=? WHERE leadid = ?;");
$lsetlast = $dbh->prepare("UPDATE leads SET lasthit=? WHERE leadid = ?;");

$qput = $dbh->prepare("INSERT IGNORE INTO qualifications(leadid,sourceid,submission,industry,revenue,website,mediatype,using_how,timeline,objective,interest) VALUES(?,?,?,?,?,?,?,?,?,?,?);");
$oput = $dbh->prepare("INSERT IGNORE INTO orders(leadid,whatordered,sourceid,submission,fulfilled) VALUES(?,?,?,?,\"no\");");

foreach $file (...) {

open(DATAIN, "ssh -i /home/cms/.ssh/identity -l ##### ############### 'cat /www/###############/#########/$file.csv'|") or die "Can't open SSH tunnel: $!";
$header = <DATAIN>;
$fields=();
@fields = &mysplit(',', $header);
$numfields = scalar(@fields);
for($i=0; $i<@fields; $i++) {
  $fields[$i] =~ s|\"(.*)\"|$1|;
  $fields{$fields[$i]} = $i;
}

while(<DATAIN>) {
  @f = &mysplit(',', $_);
  for($i=0; $i<@f; $i++) {
    $f[$i] =~ s|\"(.*)\"|$1|;
  }
  splice(@f, $fields{country}, 0, ("US")) unless scalar(@f) == $numfields;
  $r = $lput->execute($f[$fields{timestamp}], $f[$fields{lastname}], $f[$fields{firstname}], $f[$fields{email}], $f[$fields{title}], $f[$fields{phone}], $f[$fields{company}], $f[$fields{addr}], $f[$fields{addr2}], $f[$fields{city}], $f[$fields{state}], $f[$fields{postal}], $f[$fields{country}]);
  if($r > 0) { 
    print "Added $f[$fields{firstname}] $f[$fields{lastname}]\n";
  }
  $lfind->execute($f[$fields{email}]);
  $leadid = ($lfind->fetchrow_array())[0];
  $lfind->finish();
  print STDERR "Lead ID: $leadid\n";
  $lsetlast->execute($f[$fields{timestamp}], $leadid);
  $lgetsources->execute($leadid);
  $sources = ($lgetsources->fetchrow_array())[0];
  $lgetsources->finish();
  $sources = $sources ? ($sources !~ m/$f[$fields{sourceid}]/ ? "$sources, $f[$fields{sourceid}]" : $sources) : $f[$fields{sourceid}];
  $lsetsources->execute($sources, $leadid);

  $qput->execute($leadid, $f[$fields{sourceid}], $f[$fields{timestamp}], $f[$fields{industry}], $f[$fields{revenue}], $f[$fields{website}], $f[$fields{mediatype}], $f[$fields{usage}], $f[$fields{timeline}], $f[$fields{objective}], $f[$fields{interest}]);
  $whatordered = ($f[$fields{sourceid}] =~ m/^######/ ? (($f[$fields{receive}] eq "Demo CD") ? "CD" : "Paper") : (($f[$fields{sourceid}] =~ m/^cd/) ? "CD" : "Paper"));
  $oput->execute($leadid, $whatordered, $f[$fields{sourceid}], $f[$fields{timestamp}]);
}
close(DATAIN);
}

$dbh->disconnect();