#!/bin/perl -w

# makedb.pl
# NDC Code Release 1
#
# Read preprocessed GS-Logs from STDIN, and flush them into a SQL-Database
#
#   Copyright (C) 2001 Andreas Ulbrich, Ralf Ertzinger (ndccode@ndc.sh)
#
#   This program is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation; either version 2 of the License, or
#   (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA


use DBI;

# Define SQL-Variables

$sql_database = "stats";
$sql_db_table = "PlayerStats";
$sql_datasource = "DBI:mysql:$sql_database";
$sql_username = "";
$sql_password = "";
$sql_db_handle = DBI->connect($sql_datasource, $sql_username, $sql_password) or die "Could not connect to database.\n";

# Load player data

@gslog = <STDIN>;

# Load a list of players who are _not_ updated.
if ( -r "noupdate") {
	open(NOUPDATE,"noupdate") or die "Could not open noupdate: $!\n";
	@noupdate = <NOUPDATE>;
	close(NOUPDATE);
	chomp(@noupdate);
	print "List of players not being updated by specific request:\n";
	print join("\n", @noupdate);
	print "\n";
	%noupdate = ();
	foreach $nick (@noupdate) { $noupdate{$nick} = 1 }
}

# First, delete all players who didn't play within the last 100 days

print "Deleting players...\n";

$sql_query = $sql_db_handle->prepare("DELETE from $sql_db_table WHERE TO_DAYS(NOW()) - TO_DAYS(LastSeen) > 100");
$sql_query->execute or die "Could not delete datasets.";
$sql_query->finish;

# Flush data from previous day (in fact, flush only 'mapsplayed'. The
# DailyStats.pl script will ignore these datasets for table generation.

print "Flushing datasets...\n";

$sql_query = $sql_db_handle->prepare("UPDATE $sql_db_table SET MapsPlayed=0, fe_rank=0");
$sql_query->execute or die "Could not flush database.\n";
$sql_query->finish;

# Process data

print "Processing...\n";

LOOP: while ($gslog_line = shift(@gslog)) {

  ($pl_nick,$pl_kills,$pl_victims,$pl_suicides,$pl_maps_played,$pl_maps_won,$pl_time) = split(":",$gslog_line);

  # Check if the player is on the "noupdate" list. Skip it then.
  if (defined($noupdate{$pl_nick})) {
	print "Skipping $pl_nick due to noupdate.";
	shift @gslog;
	next LOOP;
  }

  if (($pl_nick ne "") & ($pl_time > 60) & ($pl_maps_played != 0)) {

    # Check if there is already a dataset for this player.
    # If not, init all values, and create an entry.

    $sql_query = $sql_db_handle->prepare("SELECT nick FROM $sql_db_table WHERE nick=?");
    $sql_query->execute($pl_nick);
    $sql_result = $sql_query->fetchrow_hashref;
    $sql_query->finish;

    unless (defined($sql_result)) {
      $sql_query = $sql_db_handle->prepare("INSERT INTO $sql_db_table (Nick, Email, Picture, Homepage, Skill, Skill_top, Kills, Victims, Suicides, MapsPlayed, MapsWon, Time, FPH, fe_Skill, fe_Kills, fe_Victims, fe_Suicides, fe_MapsPlayed, fe_MapsWon, fe_Time, fe_FPH, LastSeen) VALUES (?, '', 'empty.jpg', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', NOW())");
      $sql_query->execute($pl_nick) or die "Could not insert dataset for $pl_nick\n";
      $sql_query->finish;
    }

    # Load the dataset

    $sql_query = $sql_db_handle->prepare("SELECT Skill, Skill_top, Kills, Victims, Suicides, MapsPlayed, MapsWon, Time, FPH, fe_Skill, fe_Kills, fe_Victims, fe_Suicides, fe_MapsPlayed, fe_MapsWon, fe_Time, fe_FPH FROM $sql_db_table WHERE nick=?");
    $sql_query->execute($pl_nick);
    $sql_result = $sql_query->fetchrow_hashref;

    $pl_sql_skill = $sql_result->{Skill};
    $pl_sql_skill_top = $sql_result->{Skill_top};
    $pl_sql_kills = $sql_result->{Kills};
    $pl_sql_victims = $sql_result->{Victims};
    $pl_sql_suicides = $sql_result->{Suicides};
    $pl_sql_mapsplayed = $sql_result->{MapsPlayed};
    $pl_sql_mapswon = $sql_result->{MapsWon};
    $pl_sql_time = $sql_result->{"Time"};
    $pl_sql_fph = $sql_result->{FPH};
    $pl_sql_fe_skill = $sql_result->{fe_Skill};
    $pl_sql_fe_kills = $sql_result->{fe_Kills};
    $pl_sql_fe_victims = $sql_result->{fe_Victims};
    $pl_sql_fe_suicides = $sql_result->{fe_Suicides};
    $pl_sql_fe_mapsplayed = $sql_result->{fe_MapsPlayed};
    $pl_sql_fe_mapswon = $sql_result->{fe_MapsWon};
    $pl_sql_fe_time = $sql_result->{fe_Time};
    $pl_sql_fe_fph = $sql_result->{fe_FPH};

    $sql_query->finish; 

    $pl_skill = ($pl_kills / ($pl_victims + $pl_suicides + 1)) * (1 + ($pl_maps_won / $pl_maps_played));
    $pl_fph = ($pl_kills * 3600) / $pl_time; 
    $pl_time = $pl_time / 60;

    # Flush the data into the fields

    $pl_sql_skill = $pl_skill;
    $pl_sql_kills = $pl_kills;
    $pl_sql_victims = $pl_victims;
    $pl_sql_suicides = $pl_suicides;
    $pl_sql_mapsplayed = $pl_maps_played;
    $pl_sql_mapswon = $pl_maps_won;
    $pl_sql_time = $pl_time;
    $pl_sql_fph = $pl_fph;

    $pl_sql_fe_kills += $pl_kills;
    $pl_sql_fe_victims += $pl_victims;
    $pl_sql_fe_suicides += $pl_suicides;
    $pl_sql_fe_mapsplayed += $pl_maps_played;
    $pl_sql_fe_mapswon += $pl_maps_won;
    $pl_sql_fe_time += $pl_time;
    $pl_sql_fe_fph = ($pl_sql_fe_kills * 60) / $pl_sql_fe_time;
    $pl_sql_fe_skill = ($pl_sql_fe_kills / ($pl_sql_fe_victims + $pl_sql_fe_suicides + 1)) * (1 + ($pl_sql_fe_mapswon / $pl_sql_fe_mapsplayed));
    if ($pl_sql_fe_mapsplayed < 10) {
      $pl_sql_fe_skill = 0;
    }
    if ($pl_sql_fe_skill > $pl_sql_skill_top) {
      $pl_sql_skill_top = $pl_sql_fe_skill;
    }

    $sql_query = $sql_db_handle->prepare("UPDATE $sql_db_table SET Skill='$pl_sql_skill', Skill_top='$pl_sql_skill_top', Kills='$pl_sql_kills', Victims='$pl_sql_victims', Suicides='$pl_sql_suicides', MapsPlayed='$pl_sql_mapsplayed', MapsWon='$pl_sql_mapswon', Time='$pl_sql_time', FPH='$pl_sql_fph', fe_Skill='$pl_sql_fe_skill', fe_kills='$pl_sql_fe_kills', fe_Victims='$pl_sql_fe_victims', fe_Suicides='$pl_sql_fe_suicides', fe_MapsPlayed='$pl_sql_fe_mapsplayed', fe_MapsWon='$pl_sql_fe_mapswon', fe_Time='$pl_sql_fe_time', fe_FPH='$pl_sql_fe_fph', LastSeen=NOW() WHERE nick=?");
    $sql_query->execute($pl_nick) or die "Could not update entry: $pl_nick\n";
    $sql_query->finish;
  }

  shift @gslog;

}

$sql_db_handle->disconnect;

print "Finished.\n";

exit(0);

