#! /usr/bin/perl
use strict;
use warnings;
no warnings qw(once);
use CGI;
use CGI::Carp qw( fatalsToBrowser );
use DBI;
use Encode;
use POSIX;
use HTML::Entities;
use utf8;
use locale;
use lib qw(../include);
require 'config.pm';
require 'common.pm';

my $dbh = DBI->connect($config::local_connstr, $config::local_username, $config::local_password)
	or die "connect: " . $DBI::errstr;
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;

binmode STDOUT, ':utf8';

my $cgi = CGI->new;
my $locale = wloh_common::get_locale($cgi);

my $aux_parms = wloh_common::get_auxillary_parameters($dbh, $locale);

wloh_common::set_locale($locale);

my $season = wloh_common::get_max_season($dbh, $locale);

# Pick up all the subdivisions' ratings.
my %subdivision_ratings = ();
my $q = $dbh->prepare('SELECT divisjon, avdeling, serie_id, AVG(rating) AS avg_rating FROM ratings NATURAL JOIN siste_divisjon NATURAL JOIN spiller_kultur WHERE kultur=? AND sesong=? GROUP BY divisjon, avdeling, serie_id ORDER BY divisjon, avdeling');
$q->execute($locale, $season);

while (my $ref = $q->fetchrow_hashref) {
	my $division = $ref->{'divisjon'};
	my $rating = $ref->{'avg_rating'};
	my $id = $ref->{'serie_id'};

	push @{$subdivision_ratings{$division}}, [ $id, $rating ];
}

$q = $dbh->prepare('SELECT divisjon,AVG(rating) AS avg_rating,STDDEV(rating) AS stddev_rating FROM ratings NATURAL JOIN siste_divisjon NATURAL JOIN spiller_kultur WHERE kultur=? AND sesong=? GROUP BY divisjon ORDER BY divisjon');
$q->execute($locale, $season);

my @divisions = ();

my $i = 0;
while (my $ref = $q->fetchrow_hashref) {
	my %division = ();

	$division{'#rank'} = wloh_common::get_ordinal($ref->{'divisjon'}, $locale);
	$division{'#average'} = sprintf "%.1f", $ref->{'avg_rating'};
	$division{'#stddev'} = sprintf "%.1f", $ref->{'stddev_rating'};

	my @subdivisions = ();
	for my $arr (@{$subdivision_ratings{$ref->{'divisjon'}}}) {
		my ($id, $rating) = @$arr;
		push @subdivisions, {
			'#divlink' => sprintf("%.1f", $rating),
			'#divlink/href' => sprintf("http://wordfeud.aasmul.net/serie-%d", $id)
		};
	}

	$division{'subdivisions'} = \@subdivisions;

	push @divisions, \%division;
}

$q = $dbh->prepare('
SELECT *
FROM ratings
  NATURAL JOIN kanonisk_navn
  NATURAL JOIN siste_divisjon
  NATURAL JOIN spiller_kultur
WHERE kultur=?
ORDER BY rating DESC');
$q->execute($locale);

my @players = ();

$i = 0;
while (my $ref = $q->fetchrow_hashref) {
	my %player = ();

	$player{'#rank'} = wloh_common::get_ordinal(++$i, $locale);
	$player{'#user'} = Encode::decode_utf8($ref->{'navn'});
	$player{'#user/href'} = sprintf "http://wordfeud.aasmul.net/bruker-%d", $ref->{'id'};
	$player{'#rating'} = sprintf "%.1f", $ref->{'rating'};
	$player{'#stddev'} = sprintf "%.1f", $ref->{'rating_stddev'};
	$player{'#divlink'} = $ref->{'serie_navn'};
	$player{'#divlink/href'} = sprintf "http://wordfeud.aasmul.net/serie-%d", $ref->{'serie_id'};

	push @players, \%player;
}

print CGI->header(-type=>'text/html; charset=utf-8', -expires=>'+5m');
wloh_common::process_template('rating', $locale, {
	'#navbar' => wloh_common::get_navbar($cgi, $dbh, $locale),
	'iterations' => $aux_parms->{'num_iterations'},
	'rating-prior-stddev' => sprintf("%.1f", $aux_parms->{'rating_prior_stddev'}),
	'match-stddev' => sprintf("%.1f", $aux_parms->{'score_stddev'} * sqrt(2.0)),
	'#divisions' => XML::Template::alternate('tr/class', \@divisions, 'even', 'odd'),
	'#players' => XML::Template::alternate('tr/class', \@players, 'even', 'odd'),
	'last-sync' => wloh_common::get_last_sync($dbh)
});

$dbh->rollback;
