#! /usr/bin/perl

# This script is made freely available for non-commerical use by Mike Fast
# February 2008
# http://fastballs.wordpress.com/
# Attribution is appreciated but not required.

# MySQL database connection statement
use DBI;
$dbh = DBI->connect("DBI:mysql:database=pbp2;host=HOSTNAME", 'USERNAME', 'PASSWORD') 
or die $DBI::errstr;

# Get all pitch info from database
$all_pitches_query = 'SELECT pitch_id, ab_id, des, type FROM pitches WHERE (ball IS NULL) ORDER BY pitch_id ASC';
$sth= $dbh->prepare($all_pitches_query) or die $DBI::errstr;
$sth->execute();

# Process each pitch and store result in an array
$old_ab_id = 0;
$ball = 0;
$strike = 0;
while ( ($pitch, $ab, $des, $type) = $sth->fetchrow_array( ) )  {
    # Store count from previous pitch into ball/strike hashes for this pitch
    if ($ab > $old_ab_id || $ab < $old_ab_id) {
	$ball = 0;
	$strike = 0;
    } else {
    }
    $store_ball{$pitch} = $ball;
    $store_strike{$pitch} = $strike;

    # Process ball, strike, or foul (no strike) for new pitch
    if ("B" eq $type) {
	$ball++;
    }
    if ("S" eq $type) {
	# Do not count a strike if a pitch is fouled off with two strikes in the count
	if (2 == $strike && ("Foul" eq $des || "Foul (Runner Going)" eq $des)) {
	    # don't increment
	} else {
	    $strike++;
	}
    }

    $old_ab_id = $ab;
}
$sth->finish();

print "\nFinished accounting for balls and strikes.  Beginning database update...\n";

for my $pitch_id (keys %store_ball) {
    my $update_ball = $store_ball{$pitch_id};
    my $update_strike = $store_strike{$pitch_id};
#    print "$pitch_id: $update_ball-$update_strike.\n";
    $update_count_query = 'UPDATE pitches SET ball = ' . $update_ball . ', strike = ' . $update_strike . ' WHERE pitch_id = ' . $pitch_id;
#    print "yo\n";
    $sth= $dbh->prepare($update_count_query) or die $DBI::errstr;
    $sth->execute();
}