#!/usr/bin/perl 

if ($#ARGV != 0)  {
    print "\n==> USAGE: zipfix input_file\n\n";
    exit 1;
}

$input = $ARGV[0];

open(INPUT, $input) or die "==> ERROR: Couldn't open the input file: $!\n";
open(OUTPUT, ">$input.out") or die "==> ERROR: Couldn't open output file: $!\n";

### define the states
%states = ('AL','01', 'AK','02', 'AZ','04', 'AR','05', 'CA','06', 'CO','08',
 'CT','09', 'DE','10', 'DC','11', 'FL','12', 'GA','13', 'HI','15', 'ID','16',
 'IL','17', 'IN','18', 'IA','19', 'KS','20', 'KY','21', 'LA','22', 'ME','23',
 'MD','24', 'MA','25', 'MI','26', 'MN','27', 'MS','28', 'MO','29', 'MT','30',
 'NE','31', 'NV','32', 'NH','33', 'NJ','34', 'NM','35', 'NY','36', 'NC','37',
 'ND','38', 'OH','39', 'OK','40', 'OR','41', 'PA','42', 'RI','44', 'SC','45',
 'SD','46', 'TN','47', 'TX','48', 'UT','49', 'VT','50', 'VA','51', 'WA','53',
 'WV','54', 'WI','55', 'WY','56', 'AS','60', 'GU','66', 'MP','69', 'PR','72',
 'VI','78', 'FM','64', 'UM','74', '67','67', 'MH','68', 'PW','70', '71','71',
 '76','76', '79','79', '81','81', '84','84', '86','86', '89','89', '95','95');

#print "$states{'AL'}\n";

while($line = <INPUT>) {
    $abbrev = substr $line, 0, 2;
    $zip = substr $line, 2, 5;
    $lat = substr $line, 136, 10;
    $long = substr $line, 146, 11;
    $fips = $states{$abbrev};
    #print (OUTPUT "$state $zip $lat $long\n");
    print (OUTPUT "update us_zipcodes set latitude = $lat, longitude = $long\nwhere zipcode = '$zip' and fips_state_code = '$fips'\;\n");

}

close(INPUT);
close(OUTPUT);