#!/usr/bin/env perl

use warnings;
use strict;

use DBI;
use LWP::UserAgent;
use XML::Simple;

######## EDIT HERE ###########
my $dbh = DBI->connect ('dbi:Pg:dbname=george', '', '', {RaiseError=>1, AutoCommit=>0, PrintError=>1});
my $username = "username";
my $password = "Password123";
###############################

my $client_id      = "i2pi";      
my $client_version = "3.14e9";             
my $api_version    = "1.0.0";          
my $user_agent     = "$client_id/$client_version ($^O) Wesabe-API/$api_version";
my $base_url = 'https://www.wesabe.com/';

my $agent    = new LWP::UserAgent;
$agent->agent($user_agent);

getAccounts();
getTransactions();

1;

sub getXML 
{
	my ($page) = @_;
	$page = $base_url . $page;

	my $request  = new HTTP::Request('GET', $page);
	$request->authorization_basic($username, $password);
	return $agent->request($request)->content;
}

sub getAccounts 
{
	my $xml = getXML ("accounts.xml");
	my $parser = new XML::Simple(KeyAttr=>[]);
	my $data = $parser->XMLin($xml);

	my $upd = qq{
		UPDATE wesabe.account 
		SET balance = ?, last_uploaded = ?
		WHERE account_id = ?
	};
	my $ins = qq{
		INSERT INTO wesabe.account
			(account_id, name, institution, type, balance, last_uploaded)
		VALUES
			(?, ?, ?, ?, ?, ?)
	};

	foreach my $acc (@{$data->{"account"}})
	{
		my $account_id = $acc->{id};
		my $name = $acc->{name};
		my $institution = $acc->{"financial-institution"}->{name};
		my $type = $acc->{"account-type"};
		my $balance = $acc->{"current-balance"}->{content};	
		my $uploaded = $acc->{"last-uploaded-at"}->{content};	

		eval {
			my ($exists) = $dbh->selectrow_array("SELECT COUNT(*) FROM wesabe.account WHERE account_id = ?", undef, $account_id);
			if (0 == $exists)
			{
				$dbh->do($ins, undef, $account_id, $name, $institution, $type, $balance, $uploaded);
			} else
			{
				$dbh->do($upd, undef, $balance, $uploaded, $account_id);
			}
			$dbh->commit();
		};
		if ($@)
		{
			print "Woops: $@\n";
			$dbh->rollback();
		}
		
	}
}

sub checkMerchant
{
	my ($id, $name) = @_;

	my ($exists) = $dbh->selectrow_array ("SELECT COUNT(*) FROM wesabe.merchant WHERE merchant_id = ?", undef, $id);
	$dbh->do("INSERT INTO wesabe.merchant (merchant_id, name) VALUES (?, ?)", undef, $id, $name) if (0 == $exists)
}

sub getTagID
{
	my ($tag) = @_;

	my ($tag_id) = $dbh->selectrow_array ("SELECT tag_id FROM wesabe.tag WHERE tag = ?", undef, $tag);
	if (!defined $tag_id)
	{
		$dbh->do("INSERT INTO wesabe.tag (tag) VALUES (?)", undef, $tag);
		my ($tag_id) = $dbh->selectrow_array ("SELECT currval('wesabe.tag_tag_id_seq')");
	}

	return $tag_id;
}

sub tagTx
{
	my ($tag, $tx_id) = @_;

	my $tag_id = getTagID ($tag);

	eval {
		$dbh->do("INSERT INTO wesabe.tx_tag (tx_id, tag_id) VALUES (?,?)", undef, $tx_id, $tag_id);
		$dbh->commit();
	};
	if ($@)
	{
		if ($@ !~ /duplicate key/) {
			die("Woops: $@");
		}
		$dbh->rollback();
	}
}

sub getTransactions
{	
	my $xml = getXML ("transactions.xml");
	my $parser = new XML::Simple(KeyAttr=>[]);
	my $data = $parser->XMLin($xml);

	my $ins = qq{
		INSERT INTO wesabe.tx
			(guid, date, amount, account_id, raw_name, raw_type, memo, check_number, merchant_id)
		VALUES
			(?, ?, ?, ?, ?, ?, ?, ?, ?)
	};

	foreach my $tx (@{$data->{"txaction"}})
	{
		my $guid = $tx->{guid};
		my $date = $tx->{date};
		my $amount = $tx->{amount}->{content};
		my $account_id = $tx->{"account-id"};
		my $raw_name = $tx->{"raw-name"};
		my $raw_type = $tx->{"raw-txntype"};
		my $memo = $tx->{memo};
		my $check_number = $tx->{"check-number"};
		my $merchant_id = $tx->{merchant}->{id};
		my $merchant_name = $tx->{merchant}->{name};
		#my @tags = map {$_->{name}} $tx->{tags}->{tag};

		my @tags;
		my $tag = $tx->{tags}->{tag};
		if (defined $tag)
		{
			if (ref($tag) eq "HASH") 
			{
				push (@tags, $tag->{name});
			} else
			{
				@tags = map {$_->{name}} @$tag;
			}
		}


		my ($exists) = $dbh->selectrow_array("SELECT COUNT(*) FROM wesabe.tx WHERE guid = ?", undef, $guid);
		if (0 == $exists)
		{
			eval {
				checkMerchant ($merchant_id, $merchant_name) if (defined $merchant_id);
				$dbh->do($ins, undef, $guid, $date, $amount, $account_id, $raw_name, $raw_type, $memo, $check_number, $merchant_id);	
				my ($id) = $dbh->selectrow_array("SELECT currval('wesabe.tx_tx_id_seq')");
				for $tag (@tags)
				{
					tagTx($tag, $id);
				}
				$dbh->commit();	
			};
			if ($@)
			{
				print "Woops: $@\n";
				$dbh->rollback();
			}
		}
	}
}