#! /usr/bin/perl -w

use strict;
use DBI;
use File::Find;
use Image::ExifTool qw(:Public);
use Time::Local;
use Getopt::Long;
use File::Basename;

my $dbargs;
my $dbh;
my $top_roll_id;
my $photos_dir;

##########################
#
#getTime: get time from exif data
#
##########################
sub getTime($)
{
    my $file = shift;
    my $time;
    my $createDate;
    my $exifTool = new Image::ExifTool;
    my $info = $exifTool->ImageInfo("$file");
    if (defined($$info{"CreateDate"}))
    {
	$createDate=$$info{"CreateDate"};
	my ($year,$mon,$day,$hour,$min,$sec) = ($createDate =~ m/(\d\d\d\d)\:(\d\d)\:(\d\d)\s(\d\d)\:(\d\d)\:(\d\d)/);
	if ($day > 0 && $mon > 0  && $year > 0)
	{
	    $mon--;
	    $time=timegm($sec,$min,$hour,$day,$mon,$year);
	}
	else
	{
	    $time=0;
	}
    }
    else
    {
	$time=0;	
    }
    return $time;
}

sub tagPhoto($)
{
    my $tag=shift;
    my $tagId;
    my @rows = $dbh->selectrow_array("select id from tags where name like \"".$tag."\"");
    if (defined($rows[0]))
    {
	$tagId=$rows[0];
    }
    else
    {
	$dbh->do("insert into tags (name,category_id,is_category) values (\"".$tag."\",".$top_roll_id.",0)");
	$tagId = $dbh->func('last_insert_rowid');	     
    }
    return $tagId;
}


##########################
#
#search for jpg
#
##########################
sub search 
{
    my $file = $File::Find::name;
    if (-f $file && $_ =~ /^.*\.jpg$/i)
    {
	my ($roll) = ($File::Find::dir =~ m/.*\/(.*?)$/);
	my $roll_id;
	my $time = getTime($file);
	print "Importing $file ";	
	if (!$dbh->selectrow_array("select id from photos where base_uri like 'file://".dirname($file)."/' and filename like '".basename($file)."'"))
	{
	    $dbh->do("insert into photos (time,base_uri,filename,description,roll_id,default_version_id) values(".$time.",'file://".dirname($file)."/','".basename($file)."','',1,1)");
	    print "OK\n";
	    my $photoId=$dbh->func('last_insert_rowid');
	    my $exiftool = new Image::ExifTool;
	    $exiftool->ExtractInfo($file);
	    my $fileKeywords = $exiftool->GetValue("Keywords");
	    if (defined($fileKeywords))
	    {
		
		my @keywords = split(/\,/,$fileKeywords);
		foreach my $tag (@keywords)
		{
		    $tag =~ s/^ //;
		    print "Adding keyword *$tag*\n";
		    my $tagId=&tagPhoto($tag);
		    $dbh->do("insert into photo_tags (photo_id,tag_id )values (".$photoId.",".$tagId.")");
		}
	    }
	}
	else
	{
	    print "KO\n";
	}
    }
}

##########################
#
#MAIN
#
##########################

GetOptions ('path=s' => \$photos_dir);

if (!defined($photos_dir))
{
    die "Usage import2fspot.pl --path /xxx/yyy\n";
}


if ( ! -d $photos_dir)
{
    die "Error $photos_dir is not a directory\n";
}

$dbargs = {AutoCommit => 0,PrintError => 1};
$dbh = DBI->connect("dbi:SQLite:dbname=$ENV{HOME}/.config/f-spot/photos.db","","",$dbargs);
#$dbh->func('last_insert_rowid')
if ($dbh->err()) 
{ 
    die "$DBI::errstr\n";
}
print "DB Ok\n";

my @rows = $dbh->selectrow_array("select id from tags where name like 'roll'");
if (defined($rows[0]))
{
    print "Category \"Roll\" already in DB\n";
    $top_roll_id=$rows[0];
}
else
{
    print "Adding ROLL category\n";
    $dbh->do("insert into tags (name,category_id,is_category) values ('Roll',0,1)");
    $top_roll_id = $dbh->func('last_insert_rowid');	     
}

find(\&search, $photos_dir);



$dbh->commit();
$dbh->disconnect();

