#!/usr/bin/perl
# This is a heavily hacked script originally found at
# svn.zsi.at:1000/repos/perl-scripts/trunk/gogear.pl
use strict;
use warnings;

use MP3::Info ();
use DBI;
use File::Spec::Functions;
use File::Copy;

# device uses utf8 encoding for file names, so we must utf8ify files in msdosfs
use Encode;

# setup
my $gogear_mount = $ARGV[0] || '/mnt/gogear';
my $gogear_base = catdir $gogear_mount, qw(_system media audio);
my $gogear_db = catfile $gogear_base, 'MyDb';
my $local_db = './MyDb';

# working with a file on harddisk (as opposed to via usb) is /so/ much faster
copy $gogear_db, $local_db or die "Cannot copy $gogear_db to $local_db";

my $dbh = DBI->connect("dbi:SQLite2:$local_db", { RaiseError => 1 });

# delete old entries
$dbh->do("delete from artistTable");
$dbh->do("delete from albumTable");
$dbh->do("delete from songTable");
$dbh->do("delete from genreTable");

$dbh->do("delete from dirTable");
$dbh->do("insert into dirTable values(1, 'C:', -559030611)");

sub walkdir;

walkdir 1, $gogear_mount;

sub walkdir {
	my $parent = shift;
	my $dir = shift;

	opendir DIR, $dir or die "Cannot read $dir: $!";
	@_ = readdir DIR;
	closedir DIR;
	my @subdirs = sort grep { ! /^\./ && -d catdir $dir, $_ } @_;

	foreach (sort grep { /\.mp3$/i && -f catdir $dir, $_ } @_) {
		my $info = MP3::Info->new(catfile $dir, $_);
		print $info->artist . " / " . $info->album . " / " . $info->title . "\n";

		$dbh->do("insert into songTable (cSongTitle, iArtistId, iAlbumId, ITrackNr, iTrackLength, cFileName, iDirId, iGenreId, iBitRate, iFileSize, iMediaType) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
			undef,
			# Actually should be 100 in MyDb but the gogear firmware
			# crashes with long titles that long. So trim at 64 for
			# the moment. Same for artist and album.
			substr($info->title, 0, 64),
			find_or_create('artist', 'name', substr($info->artist, 0, 64)),
			find_or_create('album', 'title', substr($info->album, 0, 64)),
			$info->tracknum,
			int($info->secs),
			encode("utf8", $_),
			$parent,
			find_or_create('genre', 'name', substr($info->genre, 0, 50)),
			$info->bitrate,
			$info->size,
			1);
	}

	map {
		$dbh->do("insert into dirTable (cDirName, iParentDirId) values (?, ?)", undef, encode("utf8", $_), $parent);
		my $nextparent = $dbh->func('last_insert_rowid');

		walkdir $nextparent, catdir $dir, $_;
	} @subdirs;
}

# move local copy back to GoGear
move $local_db, $gogear_db or die "Cannot move local copy $local_db back to GoGear ($gogear_db)";

# find or create an artist or album
sub find_or_create {
	my ($table, $fld, $value) = @_;
	my $TableId = 'i' . ucfirst($table) . 'Id';
	my $TableFld = 'c' . ucfirst($table) . ucfirst($fld);
	$table .= 'Table';
	$value = "Unknown" unless $value;

	if (my $id = $dbh->selectrow_array("select $TableId from $table where $TableFld = ?", undef, $value)) {
		return $id;
	} else {
		$dbh->do("insert into $table ($TableFld) values (?)", undef, $value);
		return $dbh->func('last_insert_rowid');
	}
}