#!/usr/local/bin/perl -w
#######################################################################
#
# addProduct.pl
#
# by Jacob Turino.
#
# See documentation for description and version history.
#
#######################################################################
use strict;
use DBI;
use Net::Amazon;
use Encode qw(from_to decode encode);
use Test::Simple 'no_plan';

my $VERSION = "0.1";
my $DEBUG = 1;

my $key = '1DJNSB32RY4CN5WK6AR2'; # enter Amazon Subscription ID here

test() if $DEBUG;

# if program is run from Access we're going to connect to a different db and we'll have to declare the path of all our files
my $runFromAccess = @ARGV; # equals 0 if no args

my $driver            = "ODBC";
my $db                = "TIU";
my $user              = "";
my $pass              = "";
my $output            = 'c:\perl\prog\results.txt';
my $towerSearchScript = 'c:\perl\prog\towerSearch.pl';

unless ($runFromAccess) {
	$driver            = "SQLite";
	$db                = "products";
	$output            = 'results.txt';
	$towerSearchScript = 'towerSearch.pl'
}

# $0 is what you'd expect $ARGV[0] to be (program name)
print "\n This is *** $0 *** (version $VERSION)." unless $runFromAccess;

my $dbh = DBI->connect( "dbi:$driver:$db", $user, $pass, {RaiseError => 1} );
my $ua = Net::Amazon->new (token => $key);

# statement handle for update query that will write the results back to the database(used in sub writeToDB())
my $updStmt = "UPDATE tblProducts SET ArtistID = ?, ProductName = ?, ListPrice = ? "
            . "WHERE UPC= ?";
my $updSth = $dbh->prepare( $updStmt );

#my $upc =  shift @ARGV or die "Usage: $0 <UPC> <GenreID>\n";
#my $genreID =  shift @ARGV or die "Usage: $0 <UPC> <GenreID>\n";

print "\n\nUPC: " unless $runFromAccess;
chomp( my $upc = shift || <> );
my $genreID = shift || 25;
print "\n" unless $runFromAccess;

my ($artist, $album, $listPrice, $usedPrice, $newPrice) = getAlbumInfo($upc);

unless ($album) { # if amazon cannot find the upc
	print "$artist\n";
	writeToFile( "UPC not found", $upc, $genreID );
}

print "\tArtist: $artist\n\tAlbum: $album\n\n";

# check if the artist name matches one in our list of AmazonNames
# 	(the AmazonName field stores all known names that amazon uses for a given artist
#	 for example, the AmazonName field for the artist we call "CARS" says "<THE CARS><CARS>")
my $artistID = findArtistID("AmazonName", "%<$artist>%", $artist) || 0;
# if not, test to see if it matches one of our TIU names (this should never match if the last one didn't, but it's important that we check)
unless ($artistID) {
	# if we do match here, we have to update our AmazonName field to include this name
	updateAmazonName( $artistID, uc($artist) ) and print "\n\n\t\tBAM!!!\n\n" if 
		$artistID = findArtistID("ArtistName", $artist, $artist );
}

# if we got a match then we write our information to the db and exit (writeToFile exits)
if ($artistID) {
	writeToDB( $upc, $artistID, $album, $listPrice );
	writeToFile( "Artist Matched", $upc, $genreID, $artist, $usedPrice, $newPrice );
}

# if we're still going it then we didn't get a match.
# but we can at least write the album info to the database
writeToDB( $upc, 3672, $album, $listPrice ); # 3672 is the ArtistID for ArtistName "N/A"

# run towerSearch.pl to try and match the artist	
print "Searching Tower Records...\n\n";
my @towerResults = `perl $towerSearchScript $upc $artist $album`;
chomp(@towerResults);
my ($sortAs, $others) = @towerResults;

# print tower results
print "\tBest Match = \t$sortAs\n\n" if $sortAs;
if ($others) {
	my @otherResults = split( '><', $others );
	print "\tOther results:\t";
	foreach (@otherResults) {print "$_\n\t\t\t" if $_};
	print "\n";
}

# if we didn't find a match on tower, we're done
writeToFile( "No Match" , $upc, $genreID, $artist, $usedPrice, $newPrice) unless $sortAs;

# otherwise, we should try and match the tower name against our list of names.
# this probably won't ever match, but it's important to check (if we have to create a new artist, we may use this name,
# so we have to first check that this name isn't already used)
$artistID = findArtistID("ArtistName", $sortAs, $artist);

# if that didn't work check to see if we can remove ", The" from the end of the name (as in "Beatles, The") and then try again.
unless ($artistID) {
	if ($sortAs =~ s/, The//i) {
		$artistID = findArtistID("ArtistName", $sortAs, $artist);
	}
}

if ($artistID) {
	writeToFile( "No Match", $upc, $genreID, $artist, $usedPrice, 
				 $newPrice, $sortAs, $artistID );
}

writeToFile( "No Match" , $upc, $genreID, $artist, $usedPrice,
			 $newPrice, $sortAs );

#---------------- Subroutines -------------------------------------------------------------------------------------------------------------------
# writeToDB( $upc, $artistID, $albumName, $listPrice ): write information to db for product where UPC = $upc
# returns: nothing
sub writeToDB {
	my ( $targetUPC, $artistID, $productName, $listPrice ) = @_;
	
	my $x;
	while (1) {
		# check that this album name isn't already being used for this artist
		my $stmt = "SELECT ProductName FROM tblProducts "
				 . "WHERE ArtistID =  $artistID AND ProductName = "
				 . $dbh->quote($productName);
		
		last unless $dbh->selectrow_array( $stmt );
		$x++;
		$productName .= " [Amazon $x]";
	}
	
	open(RED,">red.txt") or die "Can't open output: $!";
	print RED $productName;
	
	print "Writing to tblProducts where UPC = $targetUPC:\n\n";
	print "\t$artistID, $productName, $listPrice\n\n";
	# execute the query that was prepared at the start of the program with the terms that we want to write
	my $rows = 
		$updSth->execute( $artistID, $productName, $listPrice, $targetUPC );
	print " Updated $rows rows where UPC = $targetUPC.\n\n";
}
# updateAmazonName( $artistID, $amazonName ): add $amazonName to AmazonName field in tblArtists 
#	where ArtistID = $artistID
# returns: nothing
sub updateAmazonName {
	my ($artistID, $artist) = @_;
	print "Updating AmazonName for ArtistID: $artistID\n";
	my $stmt = "UPDATE tblArtists SET AmazonName = [AmazonName] & '<$artist>' "
             . "WHERE ArtistID=$artistID";
	$dbh->do( $stmt );
}
# getAlbumInfo( $upc ): search $upc on amazon
# returns: $artist, $album, $listPrice
sub getAlbumInfo {
	my ($upc) = @_;

	# Get a request object
	my $response = $ua->search(upc => "$upc", mode => "music") ;

	unless ($response->is_success()) {
		return "Error: " . $response->message() . "\n";
	}
	
	my %results = %{$response->properties()};
	
	# foreach my $key (keys %results) {
		# print "$key:\t$results{$key}\n"
	# }
	
	my ($artistsRef,$album,$listPrice,$usedPrice,$newPrice) = 
			@results{'artists','ProductName','ListPrice','UsedPrice','ThirdPartyNewPrice'};
			
	my @artists = @{$artistsRef};
	
	# Sometimes a cd will have multiple artists, so amazon returns a list. 
	# We don't want more than one artist, so we set artist to "Various Artists"
	my $artist;
	if (scalar(@artists) == 1) {
		($artist) = @artists;
	} else {
		$artist = "Various Artists";
	}
	
	# amazon uses charset iso-8859-1, so we need to change it to utf8, which perl uses
	$artist = decode("iso-8859-1", $artist);
	# $artist = encode("cp1252", $artist);
	# $artist = from_to($octets, "iso-8859-1", "cp1250");
	$album = decode("iso-8859-1", $album);
	
	return ($artist, $album, $listPrice, $usedPrice, $newPrice);
}
# findArtistID( $fieldToSearch, $patternToMatch, $artistName ): finds an ArtistID in the db
# returns: $artistID
sub findArtistID {
	my ($fieldName, $searchTerm, $artist) = @_;
	my $id = 0;
	
	my %possibleMatches;
	
	# First look for exact match
	my $stmt = "SELECT ArtistID, ArtistName FROM tblArtists "
	         . "WHERE $fieldName Like " . $dbh->quote($searchTerm);
	
	if ($artist eq "Various Artists") {
		$stmt = "SELECT ArtistID FROM tblArtists "
			  . "WHERE ArtistName Like 'V/A%' AND GenreID=$genreID";
	}
	
	my $sth = $dbh->prepare( $stmt );
	
	$sth->execute();
	
	while ( my ( $artistID, $artistName ) = $sth->fetchrow_array ) {
		$possibleMatches{$artistID} = $artistName;
	}
	my $numMatches = scalar( keys(%possibleMatches) );
	if ($numMatches == 1) {
		($id) = keys(%possibleMatches);
	} elsif ($numMatches > 1) {
		open(OUTPUT,">$output") or die "Can't open output '$output': $!";
		print OUTPUT "0\tM:$artist\n";
		foreach my $id ( keys %possibleMatches ) {
			print OUTPUT "$id\t$possibleMatches{$id}\n";
		}
		close OUTPUT;
		system( 'C:\perl\prog\frmSearchResults.MAF' );
		die "Multiple Artists Matched\n";
	}
	print "ArtistID = $id\n\n" if $id;
	return $id if $id;
}
# writeToFile( $messageText, $upc, $genreID, $amazonName, $usedPrice, $newPrice, $towerName, $artistID ):  writes the results
		# of our search to a text file that will be read by the Access database, then exits. Args are all optional, but must appear in
		# the order shown ($amazonName must be the 4th arg if it appears at all).
# returns: nothing
sub writeToFile {
	my @args = @_;
	open(OUTPUT,">$output") or die "Can't open output '$output': $!";
	my $x = 0;
	# output file should look like this:
		#  0	Message
		# -1	UPC
		# -2	GenreID
		# -3	AmazonName
		# -4	Used Price
		# -5	New Price
		# -6	TowerName
		# -7	ArtistID (for best match)
	# the left column will be used as an ID field when we need to look up values in Access.
	# We use negative numbers b/c it may be set up later to include other possible ArtistID matches and ArtistIDs are positive numbers
	map { tr/"/'/ if $_; print OUTPUT $x-- . "\t$_\n" } ( @args );
	close OUTPUT;
	system( 'C:\perl\prog\frmSearchResults.MAF' ) if $runFromAccess;
	# until (<>){};
	exit;
}
# test(): I honestly have no idea what this sub does, I just found it on the web and I thought it would be cool to include it.
# returns: ???
sub test {
	print "\nTime for some tests!!!\n\n";
	
	my %dbs =  ( Access => { driver => 'ODBC',
	                         db     => 'TIU',
	                         user   => '',
	                         pass   => '',
                             },
	             SQLite => { driver => 'SQLite',
	                         db     => 'products',
	                         user   => '',
	                         pass   => '',
                           },
	           );
	
	foreach my $key ( sort keys %dbs ) {
		my $driver = $dbs{$key}{driver};
		my $db     = $dbs{$key}{db};
		my $user   = $dbs{$key}{user};
		my $pass   = $dbs{$key}{pass};
	
		my $dbh = DBI->connect( "dbi:$driver:$db", $user, $pass );
		ok( defined $dbh, "connected to $key");
		
		next unless $dbh;
		
		foreach my $table ( 'tblProducts', 'tblArtists' ) {
			my $rows = $dbh->do( "SELECT * FROM $table" );
			ok( $rows, "found $table" );
		}
	}
	
	my $ua = Net::Amazon->new (token => $key);
	ok( defined $ua, 'connected to Amazon');
	
	foreach my $towerSearchScript ('c:\perl\prog\towerSearch.pl', 'towerSearch.pl') {
		my @towerResults = `perl $towerSearchScript 0 "teh beatles" "abbey road"`;
		ok( @towerResults, "towerSearchScript works at $towerSearchScript");
	}
	exit;
}
__END__

=head1 NAME

  addProduct.pl

=head1 SYNOPSIS

  Just run the program using perl. It'll do its thing.
  (it's actually going to be called from a MS Access database)
  

=head1 DESCRIPTION



=head1 VERSION HISTORY

 * 0.1  3/12  

 

=head1 SEE ALSO




=head1 AUTHOR

Jacob Turino

Copyright 2006

=cut
