#! /usr/bin/perl
use strict;
use warnings;
use lib qw(/srv/bzr.sesse.net/www/xml-template/perl/);
use XML::Template;
use CGI;
require '../foosball.pm',

my $dbh = foosball::db_connect();

# Single score board (whoa, inefficient)
my @single_top = ();
my $q = $dbh->prepare('select username from users');
$q->execute();
while (my $ref = $q->fetchrow_hashref) {
	my $username = $ref->{'username'};
	my ($rating, $rd) = foosball::find_single_rating($dbh, $username);
	my ($oldrating) = foosball::find_single_rating($dbh, $username, 'AND ratetime::date < current_date ');

	my $trend = "";
	if (defined($oldrating)) {
		$trend = (sprintf "%+d", int($rating-$oldrating+0.5));
	}

	push @single_top, {
		'username' => $username,
		'rating' => int($rating+0.5),
		'rd' => int($rd+0.5),
		'lowerbound' => int($rating - 3.0*$rd + 0.5),
		'trend' => $trend,
	};
}
@single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;

# Double score board
my @double_top = ();
$q = $dbh->prepare('select username from users');
$q->execute();
while (my $ref = $q->fetchrow_hashref) {
	my $username = $ref->{'username'};
	my ($rating, $rd) = foosball::find_double_rating($dbh, $username);
	my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');

	my $trend = "";
	if (defined($oldrating)) {
		$trend = (sprintf "%+d", int($rating-$oldrating+0.5));
	}

	push @double_top, {
		'username' => $username,
		'rating' => int($rating+0.5),
		'rd' => int($rd+0.5),
		'lowerbound' => int($rating - 3.0*$rd + 0.5),
		'trend' => $trend,
	};
}
@double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;

# Last games
my @last_games = ();
$q = $dbh->prepare('select * from ( select to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,\'Double\' as type,team1_username1 || \' / \' || team1_username2 as username1, team2_username1 || \' / \' || team2_username2 as username2,score1,score2 from double_results union all select to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,\'Single\' as type,username1,username2,score1,score2 from single_results ) t1 order by gametime desc limit 10');
$q->execute();
while (my $ref = $q->fetchrow_hashref) {
	push @last_games, $ref;
}

$dbh->disconnect;

print CGI->header(-type=>'application/xhtml+xml');

my $doc = XML::Template::process_file('index.xml', {
	'#singletop' => \@single_top,
	'#doubletop' => \@double_top,
	'#lastgames' => \@last_games,
});
print $doc->toString;
