package NPEMap::Postcodes;

use vars qw(@ISA @EXPORT);
use warnings;
use strict;

use vars qw($tt_config);

use Template;
use NPEMap::Config;
use Date::Format;

require Exporter;
@ISA    = qw(Exporter);
@EXPORT = qw(build_home_stats missing_district_stats);

sub _getAllStats {
    my $dbh = shift;
    my $sql = <<"HERE";
SELECT COUNT(*) AS all,
       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
       COUNT(DISTINCT outward) AS all_out,
       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out
FROM postcodes WHERE deleted = 'f';
HERE

    my $sth = $dbh->prepare($sql);

    $sth->execute();

    return $sth->fetchrow_hashref;
}

sub _getDateStats {
    my $dbh = shift;
    my $sql = <<"HERE";
SELECT TO_CHAR(DATE_TRUNC('day', created_at), 'YYYY-MM-DD') AS day,
       COUNT(*) AS all,
       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS us
FROM postcodes WHERE deleted ='f'
GROUP BY  date_trunc('day', created_at)
ORDER BY  date_trunc('day', created_at);

HERE

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    return $sth->fetchall_arrayref({});
}

sub _getLastPostcodes {
    my $dbh = shift;
    my $num = shift;
    my $sql = <<"HERE";
SELECT easting, northing, outward, inward, TO_CHAR(created_at, 'DDth Mon HH24:MI')AS date
FROM postcodes
WHERE NOT deleted
ORDER BY created_at DESC LIMIT ? 
HERE

    my $sth = $dbh->prepare($sql);
    $sth->execute($num);
    return $sth->fetchall_arrayref({});
}

=pod

=head build_home_stats

build_home_stats takes a database handle as a parameter and builds the
HTML stats for inclusion into the home page

=cut

sub build_home_stats {
    my $dbh = shift;
    my $template = Template->new($tt_config);

    my $allStats = _getAllStats($dbh);
    my $lastPostcodes = _getLastPostcodes($dbh, 10);

    my $vars = {
        allstats => $allStats,
        lastpostcodes => $lastPostcodes
    };
    
    $template->process('home_stats.tt', $vars, $staticdir . '/stats.html')
        or warn $template->error();
}

sub missing_district_stats {
    my $dbh = shift;
    my $template = Template->new($tt_config);

    my $sql = <<EOF;
SELECT   d.outward, d.district, d.county FROM districts AS d LEFT OUTER JOIN postcodes AS p ON (d.outward = p.outward)
WHERE    p.id IS NULL
ORDER BY d.outward
EOF

    my $sth = $dbh->prepare($sql); 
    $sth->execute or die $dbh->errstr;
    my $districts = $sth->fetchall_arrayref({});

    my $vars = {
        districts => $districts,
        updated => ctime(time)
    };

    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
        or warn $template->error();
}

1;