#!/usr/bin/perl -w
#
# add-photos.pl: add each image named in @ARGV in the database, recurse if -r is specified
#
# usage e.g.: 
#	add-photos.pl /tmp/*.jpg
#	add-photos.pl -r /tmp/
#
# Matthieu Fuzellier, 05 Dec 2006
#
# source: http://www.mattiouz.com/software/photo/add-photos.pl
#

use strict;
use Image::ExifTool;
use DBI;
use Cwd;

my $verbose		= 0;
my $recurse		= 1;
my $server		= 'localhost';
my $db			= 'mattiouz';
my $table		= 'photos';
my $exifTool	= new Image::ExifTool;

# Connect to the db.
my $dbh = DBI->connect("DBI:mysql:$db:$server", undef, undef);

# If $dbh does not exist, the attempt to connect to the
# database server failed. The server may not be running,
# or the given database may not exist.
if (not $dbh) {
	print STDERR "The connection attempt failed for the following reason:\n $DBI::errstr";
	exit;
}

#call addPhotos()
addPhotos(@ARGV);

sub addPhotos {
    
	my @files = @_;
	my $currWorkDir	= &Cwd::cwd();

	foreach my $file (@files) {			# remaining args

		# if directory and -r -> recurse
		if ($recurse and -d $file) {
			print STDERR "$file is a directory\n";
			chdir $file;
			my @filesList = <*>;
			addPhotos(@filesList);
			chdir '..';
		}
		
		# if JPG add photo to db
	    elsif ($file =~ /\.jpg$/ || $file =~ /\.JPG$/) {
			next if $file =~ /-(sq|tn|sm|med)\.jpg$/;	# avoid adding variants
			next if $file =~ /-(sq|tn|sm|med)\.JPG$/;	# avoid adding variants
			
			if ( ! -r $file ) {
				print STDERR "Can't read $file! skipping it...\n";
				next;
			}
			
			# grab metadata
			$exifTool->Options(DateFormat => '%Y-%m-%d %H:%M:%S');
			$exifTool->Options(CoordFormat => q{%d deg %d min %.2f sec});
			my $info 		= $exifTool->ImageInfo($file, 'ImageDescription', 'DateTimeOriginal', 'UserComment', 'IPTC:City', 'IPTC:Province-State', 'IPTC:Country-PrimaryLocationName', 'GPSPosition');
			my $desc 		= $$info{'ImageDescription'};
			my $date 		= $$info{'DateTimeOriginal'};
			my $visibility		= $$info{'UserComment'};
			my $city		= $$info{'City'};
			my $state		= $$info{'Province-State'};
			my $country		= $$info{'Country-PrimaryLocationName'};
			my $position		= $$info{'GPSPosition'};
			
			# format visibility
			if ($visibility =~ m/visibility:public/) { $visibility = 'public'; }
			elsif ($visibility =~ m/visibility:private/) {$visibility = 'private'; }
			else {$visibility = 'friends'; }
			
			# remove quotes from description
			$desc =~ s/'/ /g if ($desc);
			$city =~ s/'/ /g if ($city);
			$state =~ s/'/ /g if ($state);
			$country =~ s/'/ /g if ($country);
			
			# insert in db
			my $queryUpdateString = "Insert into $table values (0, '$file', '$currWorkDir', '$desc', '$date', '$visibility', '$city', '$state', '$country', '$position')";

			# check if already a record for that image
			my $queryCheckString = "select id from $table where filename like '$file' and path like '$currWorkDir'";
			print $queryCheckString . "\n" if $verbose;
			my $queryCheck = $dbh->prepare($queryCheckString);
			$queryCheck->execute();
                        my @row = $queryCheck->fetchrow();
			my $result = scalar @row;
			if ($result > 0){
				#need to update not add
				print "Updating photo $file (id=$row[0])\n";
				$queryUpdateString = "Update $table set filename='$file', path='$currWorkDir', description='$desc', date='$date', ";
				$queryUpdateString .= "visibility='$visibility', city='$city', state='$state', country='$country', position='$position'";
				$queryUpdateString .= "where id=$row[0]";
			} else {
				print "Inserting photo $file\n";
			}

			print $queryUpdateString . "\n" if $verbose;
			my $queryUpdate = $dbh->prepare($queryUpdateString);
			$queryUpdate->execute();
			#my @row = $query->fetchrow();
			#print "@row\n";

			print "Done $currWorkDir/$file\n" if $verbose;
		}
	}
}

exit;