#!/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($!);

my @stmts = (
    "CREATE TABLE IF NOT EXISTS zipcodes (
        zipcode text NOT NULL,
        cityname text NOT NULL,
        CONSTRAINT zipcodes_pk PRIMARY KEY(zipcode)
     )",
    "CREATE TABLE IF NOT EXISTS censusdata (
        census_id bigserial,
        zipcode text,
        cityname text NOT NULL,
        peoplecount bigint NOT NULL,
        CONSTRAINT census_pk PRIMARY KEY(census_id)
     )",
    
     # Empty tables if they already have data
     "TRUNCATE zipcodes",
     "TRUNCATE censusdata",
);

foreach my $stmt (@stmts) {
    print "Running $stmt...\n";
    $dbh->do($stmt) or croak($dbh->errstr);
}   
    
    
print "Loading zipcodes into database...\n";
my $zipsth = $dbh->prepare("INSERT INTO zipcodes (zipcode, cityname) VALUES (?, ?)")
        or croak($dbh->errstr);
open(my $zipfh, '<', 'zipcodes.txt') or croak($!);
while((my $line = <$zipfh>)) {
    chomp $line;
    my ($zipcode, $cityname) = split/\;/, $line;
    if(!$zipsth->execute($zipcode, $cityname)) {
        croak($dbh->errstr);
    }
}
close $zipfh;

print "Loading census data into database...\n";
my $censussth = $dbh->prepare("INSERT INTO censusdata (cityname, peoplecount) VALUES (?, ?)")
        or croak($dbh->errstr);
open(my $censusfh, '<', 'census.txt') or croak($!);
while((my $line = <$censusfh>)) {
    chomp $line;
    my ($cityname, $population) = split/\;/, $line;
    if(!$censussth->execute($cityname, $population)) {
        croak($dbh->errstr);
    }
}
close $censusfh;

print "Adding missing zipcodes to census data...\n";
$dbh->do("UPDATE censusdata c SET zipcode = z.zipcode FROM zipcodes z WHERE c.cityname = z.cityname")
        or croak($dbh->errstr);


$dbh->commit;

print "Done\n";