#!/usr/bin/perl -w

# NO WARRANTY
#
# This script can extract the tags from an f-spot database and
# create some .pal files for Picasa that will make Picasa see
# albums corresponding to the tags.  You will need to blow away
# the Picasa2 directory (its database) for this to work, so use
# with care and take backups!
#
# NOTE: this likely has lots of bugs.  For example, any use with
# tags containing "&" is likely to be interesting at least.
#
# 20090601 -- Morten Welinder (terra@gnome.org)

use strict;

my $fspotdb = $ARGV[0];
my $verbose = 0;
my $HOME = $ENV{'HOME'};
my $DBID = "6967bf2742dd2e5a2d9733ce891d77a7";

my %tag_id_to_name;
open (FIL, "sqlite3 '$fspotdb' 'select id,name from tags;' |");
while (<FIL>) {
    chomp;
    my ($id,$name) = split ('\|',$_,2);
    print STDERR "TAG $id is $name\n" if $verbose;
    $tag_id_to_name{$id} = $name;
}
close (FIL);

my %photo_id_to_name;
open (FIL, "sqlite3 '$fspotdb' 'select id,uri from photos;' |");
while (<FIL>) {
    chomp;
    my ($id,$uri) = split ('\|',$_,2);
    print STDERR "PHOTO $id is $uri\n" if $verbose;
    my $name = &mangle_uri ($uri);
    next unless defined $name;
    $photo_id_to_name{$id} = $name;
}
close (FIL);

my %tag_id_to_photo_ids;
open (FIL, "sqlite3 '$fspotdb' 'select photo_id,tag_id from photo_tags;' |");
while (<FIL>) {
    chomp;
    my ($photo,$tag) = split ('\|',$_,2);
    print STDERR "PHOTO $photo has TAG $tag\n" if $verbose;
    if (!exists $photo_id_to_name{$photo}) {
	print STDERR "PHOTO $photo does not exist.\n";
	next;
    }
    if (!exists $tag_id_to_name{$tag}) {
	print STDERR "TAG $tag does not exist.\n";
	next;
    }
    $tag_id_to_photo_ids{$tag} ||= [];
    push @{$tag_id_to_photo_ids{$tag}}, $photo;
}
close (FIL);

foreach my $tag (sort { $a <=> $b } keys %tag_id_to_photo_ids) {
    my $tagname = $tag_id_to_name{$tag};
    local (*XML);

    my $albumid = substr (unpack ("H32", $tagname) . "00"x16, 0, 32);

    my $filename = "/tmp/$albumid.pal";
    open (XML, "> $filename") or die "$0: Cannot create $filename: $!\n";

    print STDERR "Creating \"$filename\"...\n";
    print XML "<picasa2album>\n";
    print XML " <DBID>$DBID</DBID>\n";
    print XML " <AlbumID>$albumid</AlbumID>\n";
    print XML " <property name=\"unread\" type=\"flag\" value=\"0\"/>\n";
    print XML " <property name=\"hascollage\" type=\"flag\" value=\"0\"/>\n";
    print XML " <property name=\"uid\" type=\"string\" value=\"$albumid\"/>\n";
    print XML " <property name=\"category\" type=\"num\" value=\"0\"/>\n";
    print XML " <property name=\"date\" type=\"real64\" value=\"37651.432002\"/>\n";
    print XML " <property name=\"token\" type=\"string\" value=\"]album:$albumid\"/>\n";
    print XML " <property name=\"name\" type=\"string\" value=\"$tagname\"/>\n";
    print XML " <files>\n";
    foreach my $photo (sort { $a <=> $b } @{$tag_id_to_photo_ids{$tag}}) {
	my $photo_file = $photo_id_to_name{$photo};
	print XML "  <filename>$photo_file</filename>\n";
    }
    print XML " </files>\n";
    print XML "</picasa2album>\n";
    close (XML);
}



# -----------------------------------------------------------------------------

sub mangle_uri {
    my ($uri) = @_;
    my $file = $uri;
    return undef unless $file =~ s|^file://||;
    if (!-f $file) {
	print STDERR "Warning: $file is not a regular file.\n";
	return undef;
    }
    return undef unless $file =~ s|^${HOME}/||;
    $file =~ s|/|\\|g;
    return "\$My Documents\\$file";
}