#!/usr/bin/perl

# This script imports tag association data from the output generated by PSATool
# and modifies a properly configured Adobe Lightroom SQLite database.
# This code is Copyright (C) Pablo Averbuj, 2006.

use strict;
use DBI;
use Data::Dumper;
use Data::GUID;
use YAML;


# This is your Lightroom library typically located in:
# My Documents\My Pictures\Lightroom . The filename is longer by default but I shortened mine
my $dbfile = "Library.aglib";

# This is the file you generated using PSATool
my $psafile = "test.txt";

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
my %tag_cache;

my $localid = $dbh->selectrow_array("select value from Adobe_variablesTable where  name = 'Adobe_entityIDCounter'"); 
print "Initial ID: $localid\n";


open(PSA, "<$psafile");
while (<PSA>) {
	tr/\n\r//d;
	my @fields = split(/;/, $_, 7);
	next unless ($fields[6]); # Only process images with tags

	my $file = $fields[0];
	my $tags = $fields[6];

	next if ($tags eq 'Folders');
	print STDERR "Processing file: $file [$tags]\n";

	my $fileid = resolve_file("\\$file");
	if (!$fileid) {
		print STDERR "Couldn't resolve file to fileid [$file]\n";
		next;
	}

	my @tags = split(/;/, $tags);
	foreach my $tag (@tags) {
		my $tagid = resolve_tag($tag);

		if (!$tagid) {
			print STDERR "Couldn't resolve tag to tagid [$tag] (file: $file)\n";
			next;
		}

		associate($fileid, $tagid);
	}
}
close(PSA);


print "Final ID: $localid\n";
$dbh->do("update Adobe_variablesTable set value = ? where  name = 'Adobe_entityIDCounter'", undef, $localid); 
$dbh->do("update AgLibraryImageSearchIndex set dirty = 1");

exit;


sub resolve_tag {
	my $tag = shift;
#	print STDERR "  Processing tag: $tag\n";
	return undef unless ($tag);
	if (exists $tag_cache{$tag}) {
		return $tag_cache{$tag};
	}

	my @parts = split(/\\/, $tag);
	my $local = pop(@parts);
	my $prefix = join('\\', @parts);
	$local =~ tr/"//d;

	my $parent = resolve_tag($prefix);

	my $id;
	if ($parent) {
		$id = $dbh->selectrow_array('SELECT id_local FROM AgLibraryTag where name = ? and parent = ?',
			undef, $local, $parent);
	} else {
		$id = $dbh->selectrow_array('SELECT id_local FROM AgLibraryTag where name = ? and parent = (select id_local from AgLibraryTag where parent is null and kindName = ?)',
			undef, $local, 'AgKeywordTagKind');
	}


#	print STDERR "Resolving $tag ($parent -> $local) [$id]\n";

	$tag_cache{$tag} = $id;
	return $id;
}

sub resolve_file {
	my $file = shift;
	my $id = $dbh->selectrow_array('SELECT image from Adobe_imageFiles where lower(absolutePath) = lower(?)', undef, $file);
	return $id;
}

sub associate {
	my ($fileid, $tagid) = @_;
	if ($fileid and $tagid) {
		my $id = $dbh->selectrow_array('select id_local from AgLibraryTagImage where image = ? and tag = ?', undef, $fileid, $tagid);
		if ($id) {
			print STDERR "Association exists ($id) between fileid [$fileid] and tagid [$tagid]\n";
			return $id;

		}

		print STDERR "Creating association: $fileid -> $tagid\n";

		$dbh->do("INSERT INTO AgLIbraryTagImage (id_local, id_global, image, tag, positionInCollection, tagKind) values (?, ?, ?, ?, ?, ?)",
			undef, ++$localid, Data::GUID->new()->as_string, $fileid, $tagid, 'z', 'AgKeywordTagKind');
	} else {
		print STDERR "Did not get either fileid [$fileid] or tagid [$tagid] so not associating\n";
	}
}