#!/usr/bin/perl

use warnings;
use strict;

use FindBin;
use lib "$FindBin::Bin/../perllib";
use lib "$FindBin::Bin/../commonlib/perllib";

use POSIX qw(strcoll);
use mySociety::DBHandle qw(dbh);

use mySociety::MaPit;
use FixMyStreet::Geocode::OSM;

BEGIN {
    mySociety::Config::set_file("$FindBin::Bin/../conf/general");
    mySociety::DBHandle::configure(
        Name => mySociety::Config::get('FMS_DB_NAME'),
        User => mySociety::Config::get('FMS_DB_USER'),
        Password => mySociety::Config::get('FMS_DB_PASS'),
        Host => mySociety::Config::get('FMS_DB_HOST', undef),
        Port => mySociety::Config::get('FMS_DB_PORT', undef)
    );
}

my $query = "SELECT council, COUNT(*) AS total, SUM(fixed) AS fixed
  FROM (SELECT council,
        CASE WHEN state in ('fixed','fixed - user', 'fixed - council')
             THEN 1 ELSE 0 END AS fixed
        FROM problem WHERE confirmed IS NOT NULL AND
        state IN ('fixed', 'fixed - user', 'fixed - council', 'confirmed') AND
        whensent < NOW() - INTERVAL '4 weeks') AS a
  GROUP BY council";

my $stats = dbh()->selectall_arrayref($query, { Slice => {} });

my @councils;
foreach my $row (@$stats) {
    if ($row->{council}) {
        $row->{council} =~ s/\|.*//g;
        my @council_ids = split(/,/, $row->{council});
        push(@councils, @council_ids);
        $row->{council} = \@council_ids;
    }
}
my $areas_info = mySociety::MaPit::call('areas', \@councils);
my %adminsum;
my %adminfixed;
foreach my $row (@$stats){
    if ($row->{council}) {
        for my $councilid (@{$row->{council}}) {
            $adminsum{$councilid} += $row->{total};
            $adminfixed{$councilid} += $row->{fixed};
        }
    }
}

foreach my $councilid (sort sort_councils keys %adminsum) {
    my $council = $areas_info->{$councilid}->{name};
    my $total = $adminsum{$councilid};
    my $fixed = $adminfixed{$councilid};
    printf("%5.1f %4d %s (%d)\n", 100 * $fixed / $total, $total,
           $council, $councilid);
}

sub sort_councils {
    my $retval = ($adminfixed{$b} / $adminsum{$b}) <=>
        ($adminfixed{$a} / $adminsum{$a});
    $retval = $adminsum{$b} <=> $adminsum{$a} unless $retval;
    $retval = strcoll($areas_info->{$a}->{name},
                      $areas_info->{$b}->{name}) unless $retval;
    return $retval;
}