#!/usr/bin/env perl

use strict;
use warnings;

use Carp;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=TropicoDB;host=/var/run/postgresql", 'ElPresidente', 'WorldDomination', {AutoCommit => 0})
        or croak($!);

generateReport($dbh, "Most populated zip codes", "SELECT zipcode, sum(peoplecount) FROM censusdata 
                                                  GROUP BY zipcode
                                                  ORDER BY sum(peoplecount) desc 
                                                  LIMIT 10");
     
generateReport($dbh, "Least populated zip codes", "SELECT zipcode, sum(peoplecount) FROM censusdata 
                                                  GROUP BY zipcode
                                                  ORDER BY sum(peoplecount) 
                                                  LIMIT 10");

generateReport($dbh, "Zip codes without census", "SELECT z.zipcode, z.cityname FROM zipcodes z
                                                  WHERE NOT EXISTS (
                                                    SELECT 1 FROM censusdata c
                                                    WHERE c.zipcode = z.zipcode
                                                  )");
                                            

$dbh->commit;

sub generateReport {
    my ($dbh, $title, $stmt) = @_;
    
    print $title, "\n";
    print "=" x length($title), "\n";
    my $sth = $dbh->prepare($stmt) or croak($dbh->errstr);
    $sth->execute or croak($dbh->errstr);

    while((my @line = $sth->fetchrow_array)) {
        print join(';', @line), "\n";
    } 
    $sth->finish;
    print "\n\n";
}