#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;

use Spreadsheet::ParseExcel;

my $abbr = {};
while (<DATA>) {
  chomp;
  my ($state, $ab) = split(/\t/);
  $abbr->{$state} = $ab;
}

my $mode = "All";

my $file = "/Users/paulmison/Desktop/ghackday1/US\ results\ by\ county.xls";
my $max  = {};
my $min  = {};

# get the raw data
my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file);
SHEET: foreach my $sheet (@{$excel->{Worksheet}}) {
  next SHEET if ($sheet->{Name} eq 'Total results');
  next SHEET if ($sheet->{Name} eq 'Sheet45');

  my $state = $sheet->{Name};

  my $svg = 0;
  $svg = 1 if (-e "/Users/paulmison/Desktop/ghackday1/$state.svg");

  printf("State: %s\n", $state) if ($svg and ($mode eq 'All' or $mode = "statesvg"));
  
  $sheet->{MaxRow} ||= $sheet->{MinRow};
  ROW: foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
      $sheet->{MaxCol} ||= $sheet->{MinCol};

#     foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
    next ROW if (!defined $sheet->{Cells}[$row][0]->{Val});
    next ROW if (!defined $sheet->{Cells}[$row][3]->{Val});
    next ROW if (!defined $sheet->{Cells}[$row][4]->{Val});
    next ROW if (!defined $sheet->{Cells}[$row][5]->{Val});
    next ROW if ($sheet->{Cells}[$row][0]->{Val} =~ /^County/);

    my $county = $sheet->{Cells}[$row][0]->{Val};
    $county =~ s/\s+$//;
    $county =~ s/ /_/g;
  
    # get hex colour (from red to blue?)
    my $obama = $sheet->{Cells}[$row][3]->{Val};
    $obama =~ s/,//g;
    my $mccain = $sheet->{Cells}[$row][4]->{Val};
    $mccain =~ s/,//g;
    my $other = $sheet->{Cells}[$row][5]->{Val};
    $other =~ s/,//g;
    
    my $total = $obama+$mccain+$other;
    
    my $ob_per = 100*$obama/$total;
    my $mc_per = 100*$mccain/$total;
    my $ot_per = 100*$other/$total;
  
    # do maxima per state
    {
      no warnings "uninitialized";

      if ($ob_per > $max->{$state}{'obama'}{'per'}) {
        $max->{$state}{'obama'}{'per'} = $ob_per;
        $max->{$state}{'obama'}{'county'} = $county;
      }

      if ($ob_per > $max->{'Overall'}{'obama'}{'per'}) {
        $max->{'Overall'}{'obama'}{'per'} = $ob_per;
        $max->{'Overall'}{'obama'}{'county'} = $state."/".$county;
      }

      if (!defined $min->{$state}{'obama'}{'per'} || $ob_per < $min->{$state}{'obama'}{'per'}) {
        $min->{$state}{'obama'}{'per'} = $ob_per;
        $min->{$state}{'obama'}{'county'} = $county;
      }

      if (!defined $min->{'Overall'}{'obama'}{'per'} || $ob_per < $min->{'Overall'}{'obama'}{'per'}) {
        $min->{'Overall'}{'obama'}{'per'} = $ob_per;
        $min->{'Overall'}{'obama'}{'county'} = $state."/".$county;
      }

      if ($ob_per > $max->{$state}{'mc'}{'per'}) {
        $max->{$state}{'mc'}{'per'} = $mc_per;
        $max->{$state}{'mc'}{'county'} = $county;
      }

      if ($mc_per > $max->{'Overall'}{'mc'}{'per'}) {
        $max->{'Overall'}{'mc'}{'per'} = $mc_per;
        $max->{'Overall'}{'mc'}{'county'} = $state."/".$county;
      }

      if (!defined $min->{$state}{'mc'}{'per'} || $mc_per < $min->{$state}{'mc'}{'per'}) {
        $min->{$state}{'mc'}{'per'} = $mc_per;
        $min->{$state}{'mc'}{'county'} = $county;
      }

      if (!defined $min->{'Overall'}{'mc'}{'per'} || $mc_per < $min->{'Overall'}{'mc'}{'per'}) {
        $min->{'Overall'}{'mc'}{'per'} = $mc_per;
        $min->{'Overall'}{'mc'}{'county'} = $state."/".$county;
      }

      if ($ot_per > $max->{$state}{'ot'}{'per'}) {
        $max->{$state}{'ot'}{'per'} = $ot_per;
        $max->{$state}{'ot'}{'county'} = $county;
      }

      if ($ot_per > $max->{'Overall'}{'ot'}{'per'}) {
        $max->{'Overall'}{'ot'}{'per'} = $ot_per;
        $max->{'Overall'}{'ot'}{'county'} = $state."/".$county;
      }

      if (!defined($min->{$state}{'ot'}{'per'}) || $ot_per < $min->{$state}{'ot'}{'per'}) {
        $min->{$state}{'ot'}{'per'} = $ot_per;
        $min->{$state}{'ot'}{'county'} = $county;
      }

      if (!defined($min->{'Overall'}{'ot'}{'per'}) || $ot_per < $min->{'Overall'}{'ot'}{'per'}) {
        $min->{'Overall'}{'ot'}{'per'} = $ot_per;
        $min->{'Overall'}{'ot'}{'county'} = $state."/".$county;
      }
    }
    my $ob_col = int(2.55*$ob_per);
    my $mc_col = int(2.55*$mc_per);
    my $ot_col = int(2.55*$ot_per);
    
    my $blue = sprintf("%x", $ob_col);
    my $red  = sprintf("%x", $mc_col);
    
    my $col = $red."00".$blue;
    
    # print colour
    print qq(<use xlink:href="#$county" stroke="none" fill="#$col" />\n) if ($svg and ($mode eq 'All' or $mode = "statesvg"));
  }
}

# get the diff data

my $diff = {};

foreach my $state (keys %{ $max }) {
  $diff->{$state} = ($max->{$state}{'obama'}{'per'})-($min->{$state}{'obama'}{'per'});
}

my $states = [ sort { $diff->{$b} <=> $diff->{$a} } keys %{ $diff } ];

if ($mode eq 'All' or $mode eq 'Diffs') {
  # print out human readable diffs
  foreach my $state (@{ $states }) {
    printf "%s: difference of %.2f%%\n", $state, $diff->{$state};
  
    printf "\t%s County, %.2f%% vs ", $max->{$state}{'obama'}{'county'}, $max->{$state}{'obama'}{'per'};
    printf "\t%s County, %.2f%%\n", $min->{$state}{'obama'}{'county'}, $min->{$state}{'obama'}{'per'};
  }
}

if ($mode eq 'All' or $mode eq 'ussvg') {
  # build CSS for all STATES
  foreach my $state (@{ $states }) {
    next if $state eq "Overall";
    my $ab = $abbr->{uc($state)};
    
    #  my $color = sprintf("%x", int(2.55*($diff->{$state})));
    my $invcolor = sprintf("%x", int(2*(100-($diff->{$state}))));
    
    print "\t\t#$ab { fill: #ff${invcolor}${invcolor}; }\n";
  }
}

if ($mode eq 'All' or $mode eq 'chart') {
  # build chart URLs for top 10 states
  
  my @states = splice (@{ $states }, 0, 15);
  
  # print Dumper(@states);
  
  my $img = "http://chart.apis.google.com/chart?cht=bhs&chs=500x440&chco=4d89f9,c6d9fd&chd=t:";
  
  my (@mins, @maxs);
  
  foreach my $state (@states) {
    push @mins, int($min->{$state}{'obama'}{'per'});
    push @maxs, int($max->{$state}{'obama'}{'per'}-$min->{$state}{'obama'}{'per'});
  }
  
  $img .= join(',', @mins);
  $img .= "|";
  $img .= join(',', @maxs);
  
  $img .= "&chxt=y&chxl=0:|".join("|", reverse @states)."|";
  
  print $img;
}

__DATA__
ALABAMA	AL
ALASKA	AK
AMERICAN SAMOA	AS
ARIZONA	AZ
ARKANSAS	AR
CALIFORNIA	CA
COLORADO	CO
CONNECTICUT	CT
DELAWARE	DE
DISTRICT OF COLUMBIA	DC
FEDERATED STATES OF MICRONESIA	FM
FLORIDA	FL
GEORGIA	GA
GUAM	GU
HAWAII	HI
IDAHO	ID
ILLINOIS	IL
INDIANA	IN
IOWA	IA
KANSAS	KS
KENTUCKY	KY
LOUISIANA	LA
MAINE	ME
MARSHALL ISLANDS	MH
MARYLAND	MD
MASSACHUSETTS	MA
MICHIGAN	MI
MINNESOTA	MN
MISSISSIPPI	MS
MISSOURI	MO
MONTANA	MT
NEBRASKA	NE
NEVADA	NV
NEW HAMPSHIRE	NH
NEW JERSEY	NJ
NEW MEXICO	NM
NEW YORK	NY
NORTH CAROLINA	NC
NORTH DAKOTA	ND
NORTHERN MARIANA ISLANDS	MP
OHIO	OH
OKLAHOMA	OK
OREGON	OR
PALAU	PW
PENNSYLVANIA	PA
PUERTO RICO	PR
RHODE ISLAND	RI
SOUTH CAROLINA	SC
SOUTH DAKOTA	SD
TENNESSEE	TN
TEXAS	TX
UTAH	UT
VERMONT	VT
VIRGIN ISLANDS	VI
VIRGINIA	VA
WASHINGTON	WA
WEST VIRGINIA	WV
WISCONSIN	WI
WYOMING	WY