#! /usr/bin/perl

# DATABASE TO CONNECT TO
$DATABASE = "stats_hockey";

# INITIALIZE DATABASE CONNECTION
&connect;

# SET GLOBAL ARRAYS FOR FIELDS
@players = ("gp", "g", "a", "pts", "pm", "pim", "pp", "sh", "ppa", "sha", "ppp", "shp", "gw", "fg", "sog", "soa", "ot", "shots", "pct");
@goal = ("gpi", "min_h", "min_s", "avg", "w", "l", "sw", "sl", "ow", "ol", "en", "sho", "sog", "soa", "ga", "saves", "spct");

# Load the teams and their player URLs
&sql2("SELECT team_id, team_url FROM `ho_teams` WHERE league_id='1' AND team_url<>''");
while (my @team = $sth2->fetchrow_array) {
	$team_id = $team[0];
	$team_url = $team[1];

	# Form the cURL request
	my $req = "/data/www/dev/lib/share/howegrabber/bin/howegrabber -u 'Augusta echl' -p 'HCK-531' -l '$team_url'";

	# Run cURL
	my $res = `$req`;
	$fullText = $res;

	# flush the database
	&sql("delete from ho_player_stats WHERE team_id='$team_id';");
	&sql("delete from ho_goal_stats WHERE team_id='$team_id';");

	# PARSE TEXT
	@fullText = split(/\n/, $fullText);
	foreach $line (@fullText) {
		if ($playerFlag) {
			if ($line =~ /BENCH/) { $playerFlag = 0; }
			else {
				&parsePlayerLine($line);
				}
			}
		if ($goalFlag) {
			if ($line =~ /TEAM/) { $goalFlag = 0; }
			else {
				&parseGoalLine($line);
				}
			}
		if ($line =~ /PLAYER/) { $playerFlag = 1; }
		if ($line =~ /SPCT/) { $goalFlag = 1; }
	}
}

###############################################################
# SUBS
###############################################################

sub parseGoalLine() {
	$line = $_[0];
	chop($line);

	if ($line !~ /TOTALS/) {

		$number = substr($line, 0, 3);
		$number = despace($number);

		$player = substr($line, 4, 21);
		$player = despace($player);

		$pos = "G";

		if ($player) {

			&sql("select id from ho_players where name='$player' AND team_id='$team_id'");
			$id = $sth->fetchrow_array;

			if (!$id) {
				&sql("insert into ho_players (id, team_id, name, pos) values ('', '$team_id', '$player', '$pos');");
				$id = $sth->{mysql_insertid};
				}

			@fields = split(/ /, substr($line, 25, length($line)));
			$values = "'', '$team_id', '$id'";
			foreach $field (@fields) {
				if ($field || $field eq "0") {
					if ($field =~ /:/) {
						@min = split(/:/, $field);
						$values .= ", '$min[0]', '$min[1]'";
						}
					else {
						$values .= ", '$field'";
						}
					}
				}
			print "insert into goal_stats (id, team_id, player_id, ".join(", ", @goal).") values ($values);\n\n";
			&sql("insert into ho_goal_stats (id, team_id, player_id, ".join(", ", @goal).") values ($values);");
			}	
		}
	}

sub parsePlayerLine() {
	$line = $_[0];
	chop($line);

	if ($line !~ /TOTALS/) {

		$number = substr($line, 0, 3);
		$number = despace($number);

		$player = substr($line, 4, 21);
		$player = despace($player);

		$pos = substr($line, 25, 2);
		$pos = despace($pos);

		if ($player) {

			&sql("select id from ho_players where name='$player' AND team_id='$team_id'");
			$id = $sth->fetchrow_array;

			if ($id) {
				}
			else {
				&sql("insert into ho_players (id, team_id, name, pos) values ('', '$team_id', '$player', '$pos');");
				$id = $sth->{mysql_insertid};
				}
	
			@fields = split(/ /, substr($line, 29, length($line)));
			$values = "'', '$id', '$team_id'";
			foreach $field (@fields) {
				if ($field || $field eq "0") {
					$values .= ", '$field'";
					}
				}
			print "insert into player_stats (id, player_id, ".join(", ", @players).") values ($values);\n\n";
			&sql("insert into ho_player_stats (id, player_id, team_id, ".join(", ", @players).") values ($values);");
			}	
		}
	}


# despace stuff
sub despace() {
        $_[0] =~ s/^\s+//;
        $_[0] =~ s/\s+$//;
        return $_[0];
        }

################################################
# DATABASE CONNECT METHODS for MySQL           #
# -                                            #
# Used as a "require" script, not a standalone #
################################################

# connect to the database
sub connect() {
	use DBI;
	$dbh = DBI->connect("DBI:mysql:$DATABASE:db1.dfw1.infinityprosports.com","hockeystats", "kuf532ls") || die "Connect failed.\nDBI::errstr\n" unless $dbh;
	}

# disconnects from db
sub disconnect() {
	$dbh->disconnect;
	}

# perform an SQL command
sub sql2() {
	eval {
		$sth2 = $dbh->prepare($_[0]);
		};
	if ($@) {
		&disconnect();
		print "An error occured while accessing the database.";
		exit;
		}
	else {
		$sth2->execute;
		}
	return ($sth2);
	}


# perform an SQL command
sub sql() {
	eval {
		$sth = $dbh->prepare($_[0]);
		};
	if ($@) {
		&disconnect();
		print "An error occured while accessing the database.";
		exit;
		}
	else {
		$sth->execute;
		}
	return ($sth);
	}