#!/usr/bin/perl
#
# Copyright (c) 2006-2009 Dominic Hargreaves
# Permission is hereby granted, free of charge, to any person obtaining a
# copy of this software and associated documentation files (the "Software"),
# to deal in the Software without restriction, including without limitation
# the rights to use, copy, modify, merge, publish, distribute, sublicense,
# and/or sell copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
# IN THE SOFTWARE.
#
use strict;
use warnings;

use FindBin;
use AppConfig qw(:argcount :expand);
use MIME::Lite;

# Find our private perl libraries
use lib "$FindBin::Bin/../perllib";
use NPEMap;
use NPEMap::Config;

# Get the postcodes
my $dbh = setup_dbh() or die $!;

my $sql = <<EOF;
SELECT id,outward||' '||inward AS postcode,coalesce(to_char(easting,'000000'),''),coalesce(to_char(northing,'000000'),''),coalesce(to_char(ie_easting,'000000'),''),coalesce(to_char(ie_northing,'000000'),''),coalesce(reason,'') from postcodes p inner join bad_postcodes b on (p.id=b.postcode) where source=? and delete_reason=1 and deleted='t' order by id;
EOF

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

my $source_sth = $dbh->prepare('SELECT id,name,contact FROM sources WHERE contact IS NOT NULL');
$source_sth->execute or die $dbh->errstr;

my $source;
while ($source = $source_sth->fetchrow_hashref) {
    $sth->execute($source->{id});
    
    my @problems = @{$sth->fetchall_arrayref};

    if (@problems) {

        my $body = <<EOF;
Some postcodes that we got from $source->{name}
have been reported as bad and deleted on npemap.org.uk:

NPEmap ID,postcode,easting,northing,ie_easting_ie_northing,reason
EOF

        foreach my $problem (@problems) {
            $body .= join(',', @{$problem});
            $body .= "\n";
        }

        my $msg = MIME::Lite->new(
            From    => $adminemail,
            To      => $source->{contact},
            Subject => 'Problem postcodes reported on npemap.org.uk',
            Data    => $body
        );
        $msg->send;
    }
}

$dbh->disconnect;