#!/usr/bin/perl -w
# Bifröst - A music player metadata synchronizer
# Attempts a multi-directional meta-data synchronization between
# Amarok, Exaile and Rhythmbox.
#
# (c) 2009 - Dominik Schulz <lkml@ds.gauner.org>
# 
use XML::DOM;
use XML::Simple;
use DBI;
use Getopt::Long;

# Cmdline options
my $verbose = 0;
my $dry = 0;
my $do_rbox = 0;
my $do_amarok = 0;
my $do_exaile = 0;

# Statevariables
my $dba = undef; # Amarok DB handle
my $dbe = undef; # Exaile DB handle
my $num_update_amarok = 0;
my $num_update_exaile = 0;
my $num_update_rbox = 0;
my $rboxdbxmlfile = "$ENV{'HOME'}/.local/share/rhythmbox/rhythmdb.xml";
my $exailedbfile = "$ENV{'HOME'}/.exaile/music.db";
my $amarokdir = "$ENV{'HOME'}/.kde/share/apps/amarok";
my $t = time();
my $doc = undef; # Rhythmbox-DB-XML Handle

GetOptions(
	"rhythmbox|rbox|r"	=> \$do_rbox,
	"amarok|a"			=> \$do_amarok,
	"exaile|e"			=> \$do_exaile,
	"verbose|v"			=> \$verbose,
	"dry|d"				=> \$dry,
	"help|h"			=> sub { print "bifroest - Synchronize music player metadata\n\t-a: Enable Amarok\n\t-e: Enable Exaile\n\t-r: Enable Rhythmbox\n\t-v: Verbose\n\t-d: Dry\n"; exit 0; },
);

# Data - Multi-dimensional hash
# files
# -> key (normalized file location)
#    -> {amarok,exaile,rbox,...}
#       -> location - original location - for update
#       -> rating - this DBs rating for the file key 
#       -> atime - the last access time for this file
my %files = ();

# Disable Amarok support if the dbdir was not found
if($do_amarok && !-d $amarokdir) {
	$do_amarok = 0;
	print "Disabled Amarok support since $amarokdir was not found.\n";
}
# Disable Exaile support if the SQLite file was not found
if($do_exaile && !-e $exailedbfile) {
	$do_exaile = 0;
	print "Disabled Exaile support since $exailedbfile was not found.\n";
}
# Disable Rbox support if the DB was not found
if($do_rbox && !-e $rboxdbxmlfile) {
	$do_rbox = 0;
	print "Disabled Rhythmbox support since $rboxdbxmlfile was not found.\n";
}

# Read Rhythmbox DB
if($do_rbox) {
	print "Rhythmbox: Reading XML. This will take some time ...\n" if $verbose;
	$doc = XMLin($rboxdbxmlfile, ForceArray => 1);

	my $xmlcount = 0;
	foreach my $entry (@{$doc->{entry}}) {
		if($entry->{location}[0] && $entry->{location}[0] =~ m/^file:\/\//) {
			my $key = &normalize($entry->{location}[0]);
			$files{$key}{rbox}{'location'} = $entry->{location}[0];
			$files{$key}{rbox}{'rating'} = ($entry->{rating}[0] ? $entry->{rating}[0] : 0);
			# Limit to max. rating = 5
			$files{$key}{rbox}{'rating'} = ($files{$key}{rbox}{rating} > 5 ? 5 : $files{$key}{rbox}{rating});
			$files{$key}{rbox}{'atime'} = ($entry->{mtime}[0] ? $entry->{mtime}[0] : 0);
			$xmlcount++;
		}
	}

	print "Rhythmbox: Parsed XML in ".(time()-$t)."s ($xmlcount entires)\n" if $verbose;
}
if($do_amarok) {
	# Read Amarok-DB
	$t = time();
	my $sleep = 5;
	# Start Amarok-MySQLe-Instance
	my $cmd = "cd $amarokdir && /usr/sbin/mysqld --defaults-file=`pwd`/my.cnf --default-storage-engine=MyISAM --datadir=`pwd`/mysqle --socket=`pwd`/sock --bind-address=127.0.0.1 --port=3307 --skip-grant-tables --skip-innodb &>/tmp/bifroest-amarok-mysql.log &";
	print "CMD: $cmd\n" if $verbose;
	# start mysql
	system($cmd) >> 8 and warn("Could not start MySQLe!");
	print "Amarok: Sleeping $sleep s to let MySQLe start ...\n" if $verbose;
	sleep($sleep);
	print "Amarok: Reading MySQL Tables. This should be fast ...\n" if $verbose;
	# Database Variables
	my $query = 0;            # Hold query strings
	my $prepq = 0;            # Hold prepared query
	my $dsn = "DBI:mysql:host=127.0.0.1;port=3307;database=amarok";
	$dba = DBI->connect( $dsn, "root", "root" )
		or warn("Could not connect to DBMS: " . DBI->errstr);
	$query = "SELECT u.rpath,s.rating,s.accessdate FROM urls AS u, statistics AS s WHERE u.id = s.url";
	$prepq = $dba->prepare($query);
	$prepq->execute();
	my $sqlcount = 0;
	while(my @row = $prepq->fetchrow_array()) {
		my $key = &normalize($row[0]);
		$files{$key}{amarok}{'location'} = $row[0];
		$files{$key}{amarok}{'rating'} = ($row[1] > 5 ? 5 : $row[1]);
		$files{$key}{amarok}{'atime'} = $row[2] || 0;
		$sqlcount++;
	}
	$prepq->finish;
	print "Amarok: Parsed MySQL in ".(time()-$t-$sleep)."s ($sqlcount entries)\n" if $verbose;
}
# Read Exaile SQLite
if($do_exaile) {
	$t = time();
	my $dbargs = {
		AutoCommit	=> 0,
		PrintError	=> 1,
	};
	my $dsn = "DBI:SQLite:dbname=$exailedbfile"; # TODO pfad dyn. ermitteln
	$dbe = DBI->connect($dsn,"","",$dbargs)
		or warn("Could not connect to DBMS: " . DBI->errstr);
	my $query = "SELECT p.name,t.user_rating,t.modified FROM tracks AS t, paths AS p WHERE t.path = p.id";
	my $prepq = $dbe->prepare($query);
	$prepq->execute();
	my $sqlitecount = 0;
	while(my @row = $prepq->fetchrow_array()) {
		my $key = &normalize($row[0]);
		$files{$key}{exaile}{'location'} = $row[0];
		$files{$key}{exaile}{'rating'} = ($row[1] > 5 ? 5 : $row[1]);
		$files{$key}{exaile}{'atime'} = $row[2] || 0;
		$sqlitecount++; 
	}
	$prepq->finish;
	print "Exaile: Parsed SQLite in ".(time()-$t)."s. ($sqlitecount entries)\n" if $verbose;
}

# Process Data
foreach my $key (keys %files) {
	# Select the entry which was last modified, i.e. the most recent one
	my $latest = "";
	my $atime = 0;
	foreach my $k (keys %{$files{$key}}) {
		if($files{$key}{$k}{'atime'} > $atime && $files{$key}{$k}{'rating'} > 0) {
			$atime = $files{$key}{$k}{'atime'};
			$latest = $k;
		}
	}
	if($latest) {
		print "Key: $key - " if $verbose;
		print "rb_location: $files{$key}{rbox}{location} - " if $verbose && $files{$key}{rbox}{location};
		print "rb_rating: $files{$key}{rbox}{rating} - " if $verbose && $files{$key}{rbox}{rating}; 
		print "rb_atime: $files{$key}{rbox}{atime} - " if $verbose && $files{$key}{rbox}{'atime'};
		print "am_location: $files{$key}{amarok}{location} - " if $verbose && $files{$key}{amarok}{location};
		print "am_rating: $files{$key}{amarok}{rating} - " if $verbose && $files{$key}{amarok}{rating};
		print "am_atime: $files{$key}{amarok}{atime} - " if $verbose && $files{$key}{amarok}{'atime'};
		print "ex_location: $files{$key}{exaile}{location} - " if $verbose && $files{$key}{exaile}{location};
		print "ex_rating: $files{$key}{exaile}{rating} - " if $verbose && $files{$key}{exaile}{rating};
		print "ex_atime: $files{$key}{exaile}{atime} - " if $verbose && $files{$key}{exaile}{'atime'};
		print "Last Modified: $latest" if $verbose;
		print "\n" if $verbose;
		# Amarok
		if($do_amarok && $files{$key}{amarok}{location} && $files{$key}{amarok}{rating} != $files{$key}{$latest}{rating}) {
			print "\tWould update AmarokDB\n" if $verbose;
			&update_adb($dba,$files{$key}{amarok}{location},$files{$key}{$latest}{rating});
		}
		# Exaile
		if($do_exaile && $files{$key}{exaile}{location} && $files{$key}{exaile}{rating} != $files{$key}{$latest}{rating}) {
			print "\tWould update ExaileDB\n" if $verbose;
			&update_edb($dbe,$files{$key}{exaile}{location},$files{$key}{$latest}{rating});
		}
		# Rhythmbox
		if($do_rbox && $files{$key}{rbox}{location} && $files{$key}{rbox}{rating} != $files{$key}{$latest}{rating}) {
			print "\tWould update RhythmboxDB\n" if $verbose;
			&update_rdb($doc,$files{$key}{rbox}{location},$files{$key}{$latest}{rating});
		}
	}
}

if(!$dry) {
	open(XML, ">", "$rboxdbxmlfile.bf");
	print XML XMLout($doc, RootName => 'rhythmdb', XMLDecl => 1);
	close(XML);
}

if($do_amarok) {
	$dba->disconnect if $dba;
	# shutdown MySQLe
	$cmd = "mysqladmin -S $amarokdir/sock shutdown";
	print "CMD: $cmd\n" if $verbose;
	system($cmd) >> 8 and warn("Could not shutdown MySQLe!");
}

if($do_rbox) {
	# move old xmlfile to xmlfile.backup and xmlfile.bf to xmlfile
	$cmd = "mv $rboxdbxmlfile $rboxdbxmlfile.backup";
	print "CMD: $cmd\n" if $verbose;
	system($cmd) unless $dry;
	$cmd = "mv $rboxdbxmlfile.bf $rboxdbxmlfile";
	print "CMD: $cmd\n" if $verbose;
	system($cmd) unless $dry;
}

if($do_exaile) {
	$dbe->commit() if $dbe;
	$dbe->disconnect if $dbe;
}

print "Finished. Updated $num_update_amarok AmarokDB, $num_update_exaile ExaileDB, $num_update_rbox RhythmboxDB Entries.\n";

###########################################################################
# Subs
###########################################################################
# Update rating in the Rhythmbox-DB
sub update_rdb {
	my $doc = shift;
	my $key = shift;
	my $rating = shift;
	$num_update_rbox++;
	return if $dry;
	foreach my $entry (@{$doc->{entry}}) {
		if($entry->{location}[0] && $entry->{location}[0] eq $key) {
			print "\t\tUpdate Rating for $key to $rating\n" if $verbose;
			$entry->{rating}[0] = $rating;
		}
	}
}
# Update rating in the Amarok-DB
sub update_adb {
	my $dba = shift;
	my $key = shift;
	my $rating = shift;
	$num_update_amarok++;
	return if $dry;
	$rating = ($rating > 5 ? 5 : $rating);
	my $query = "REPLACE INTO statistics (url,rating) SELECT id,? FROM urls WHERE rpath = ?";
	print "\t\tUpdate Rating for $key to $rating (Query: $query)\n" if $verbose;
	my $prepq = $dba->prepare($query);
	$prepq->execute($rating,$key);
	$prepq->finish();
}
# Update rating in the Exaile DB
sub update_edb {
	my $dbe = shift;
	my $key = shift;
	my $rating = shift;
	$num_update_exaile++;
	return if $dry;
	my $query = "UPDATE tracks SET user_rating = ? WHERE path = (SELECT id FROM paths WHERE name = ?)";
	print "\t\tUpdate Rating for $key to $rating (Query: $query)\n" if $verbose;
	my $prepq = $dbe->prepare($query);
	$prepq->execute($rating,$key);
	$prepq->finish();
}
# Normalize the Location for use as key
sub normalize
{
	my $path = shift;
	$path =~ s/^file:\/\///;
	$path =~ s/^\.//;
	$path =~ s/\%([A-Fa-f0-9]{2})/pack('C', hex($1))/seg;
	return $path;
}