#! /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,count(*) as num_games from single_rating group by username');
$q->execute();
while (my $ref = $q->fetchrow_hashref) {
	my $username = $ref->{'username'};
	my ($rating, $rd) = foosball::find_single_rating($dbh, $username);
	next if ($rating == 0 || $rating == 1500);

	my ($oldrating) = foosball::find_single_rating($dbh, $username, 'AND ratetime::date < current_date ');

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

	push @single_top, {
		'username' => $username,
		'rating' => foosball::round($rating),
		'rd' => foosball::round($rd),
		'lowerbound' => foosball::round($rating - 3.0*$rd),
		'trend' => $trend,
		'numgames' => $ref->{'num_games'},
	};
}
@single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;

# Double score board
my @double_top = ();
$q = $dbh->prepare('select username,count(*) as num_games from double_rating group by username');
$q->execute();
while (my $ref = $q->fetchrow_hashref) {
	my $username = $ref->{'username'};
	my ($rating, $rd) = foosball::find_double_rating($dbh, $username);
	next if ($rating == 0 || $rating == 1500);

	my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');

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

	push @double_top, {
		'username' => $username,
		'rating' => foosball::round($rating),
		'rd' => foosball::round($rd),
		'lowerbound' => foosball::round($rating - 3.0*$rd),
		'trend' => $trend,
		'numgames' => $ref->{'num_games'},
	};
}
@double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;

# Last games
my @last_games = ();
$q = $dbh->prepare('
select * from (
    select
        gametime as sort_gametime,
        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,
	ra1.rating_diff as diff1, 
	ra2.rating_diff as diff2, 
	ra3.rating_diff as diff3, 
	ra4.rating_diff as diff4 
    from
        double_results re
        join double_rating ra1
            on re.gametime=ra1.ratetime
	    and re.team1_username1=ra1.username
	join double_rating ra2
	    on re.gametime=ra2.ratetime
	    and re.team1_username2=ra2.username
	join double_rating ra3
	    on re.gametime=ra3.ratetime
	    and re.team2_username1=ra3.username
	join double_rating ra4
	    on re.gametime=ra4.ratetime
	    and re.team2_username2=ra4.username
    union all
    select
        gametime as sort_gametime,
        to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
	\'Single\' as type,
	username1,
	username2,
	score1,
	score2,
	ra1.rating_diff as diff1, 
	null as diff2, 
	ra2.rating_diff as diff3,
	null as diff4 
    from
        single_results re
        join single_rating ra1
            on re.gametime=ra1.ratetime
	    and re.username1=ra1.username
	join single_rating ra2
	    on re.gametime=ra2.ratetime
	    and re.username2=ra2.username
) t1
order by sort_gametime desc limit 10');
$q->execute();
while (my $ref = $q->fetchrow_hashref) {
	if (defined($ref->{'diff2'})) {
		$ref->{'diff1'} = sprintf "%+d / %+d",
			foosball::round($ref->{'diff1'}),
			foosball::round($ref->{'diff2'});
		$ref->{'diff2'} = sprintf "%+d / %+d",
			foosball::round($ref->{'diff3'}),
			foosball::round($ref->{'diff4'});
	} else {
		$ref->{'diff1'} = sprintf "%+d", foosball::round($ref->{'diff1'});
		$ref->{'diff2'} = sprintf "%+d", foosball::round($ref->{'diff3'});
	}
	push @last_games, $ref;
}

$dbh->disconnect;

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

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