use DBI;

#get_alias(player_id), returns alias of player_id
sub get_alias {
	if (@_[0] == 0) {
		return '!NEWPLAYER!';
	}
	$alias_query = "SELECT alias FROM PLAYER WHERE id=@_[0]";
	$query = $db->prepare($alias_query);
	$query->execute;
	@query_result = $query->fetchrow_array;
	$query->finish;
	return @query_result[0];
}

#get_participation(player_id, event_id), returns true if player_id participated in event_id
sub get_participation {
	$participation_query = "SELECT event_id FROM playerevent WHERE player_id=@_[0] AND event_id=@_[1]";
	$query = $db->prepare($participation_query);
	$query->execute;
	if (@query_result = $query->fetchrow_array) {
		return 1;
	}
	return 0;
}

#get_state(player_id), returns state of player_id
sub get_state {
	if (@_[0] == 0) {
		return 0;
	}
	$state_query = "SELECT state_id FROM playerstate WHERE player_id=@_[0]";
	$query = $db->prepare($state_query);
	$query->execute;
	@query_result = $query->fetchrow_array;
	$query->finish;
	return @query_result[0];
}

#get_teamrank(player_id, nationals_event_id), returns rank of the team that player_id played for at nationals_event_id
sub get_teamrank {
	$team_query = "SELECT teamranks.rank FROM playerteam INNER JOIN teamevent ON playerteam.team_id=teamevent.team_id INNER JOIN teamranks ON playerteam.team_id=teamranks.fkey_team_id WHERE playerteam.player_id=$_[0] AND teamevent.event_id=@_[1]";
	$query = $db->prepare($team_query);
	$query->execute;
	@query_result = $query->fetchrow_array;
	$query->finish;
	return @query_result[0];
}

#Set up connection to auszone database
$database = LOL YOU WISH GRUG
$hostname = LOL YOU WISH GRUG
$username = LOL YOU WISH GRUG
$password = LOL YOU WISH GRUG
$dsn = "DBI:mysql:database=$database;host=$hostname";
$db = DBI->connect($dsn, $username, $password);

#Read teams and players from text file
open(TEAMS_FILE, "teams.txt");
@teams_file_data = <TEAMS_FILE>;
close(TEAMS_FILE);

#set up data for each nationals: event id, host state, max teams
 @nationals = (
				[52, 3, 20, 'Darwin'],
				[45, 6, 22, 'Sunshine Coast'],
				[38, 7, 18, 'Willetton'],
				[32, 5, 21, 'Bendigo'],
				[26, 2, 18, 'Campbelltown'],
				[20, 6, 20, 'Rockhampton']
 );

foreach $line (@teams_file_data) {

	chomp($line);
	@split_line = split(':',$line);
	$team_name = @split_line[0];
	@players = split(',', @split_line[1]);
	@aliases = ();
	@ratings = ();

	foreach $player (@players) {

		@aliases[$#aliases + 1] = get_alias($player);

		$nationals_counted = 0;
		$nationals_current = 0;
		$player_rating = 0;

		while ($nationals_counted < 2 and $nationals_current < $#nationals+1) {
			if ((get_state($player) != $nationals[$nationals_current][1]) and (get_participation($player,$nationals[$nationals_current][0]))) {
				$player_rating = $player_rating + (get_teamrank($player,$nationals[$nationals_current][0]) / $nationals[$nationals_current][2] );
				$nationals_counted = $nationals_counted + 1;
			}
			$nationals_current = $nationals_current + 1;
		}

		if ($nationals_counted != 0) {
			$final_rating = $player_rating / $nationals_counted;
		} else {
			$final_rating = 0;
		}

		@ratings[$#ratings + 1] = $final_rating;

	}

	print "[$team_name]\n";
	$i = 0;
	$total_team_rating = 0;
	$rated_players = 0;
	$final_team_rating = 0;

	while ($i < ($#aliases + 1)) {
		print "\t\t@aliases[$i]: @ratings[$i]\n";
		if (@ratings[$i] > 0) {
			$total_team_rating = $total_team_rating + @ratings[$i];
			$rated_players = $rated_players + 1;
		}
		$i = $i + 1;
	}

	if ($rated_players > 0) {
		$final_team_rating = $total_team_rating / $rated_players;
	}

	print "Team Rating: $final_team_rating\n\n\n";

}

$db->disconnect;