#!/usr/bin/perl
use strict;
use warnings;

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

# 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';

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

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

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

# store info on all mp3s into sqlite db
opendir(DIR,$gogear_base) || die "Cannot read $gogear_base: $!";
while (my $f=readdir DIR) {
    next unless $f=~/\.mp3$/;
    my $info=MP3::Info->new(catfile($gogear_base,$f));
    print '+'.$info->title.' ('.$info->artist.")\n";
    
    $dbh->do("insert into songTable (cSongTitle,iArtistId,iAlbumId,ITrackNr,iTrackLength,cFileName,iDirId,iGenreId,iBitRate,iFileSize,iMediaType) values (?,?,?,?,?,?,?,?,?,?,?)",undef,
        $info->title,find_or_create('artist','name',$info->artist),find_or_create('album','title',$info->album),$info->tracknum,int($info->secs),$f,4,12,$info->bitrate,$info->size,4);
}

# move local copy back to GoGear 
move($local_db,$gogear_db) || 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';
    
    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');
    }
}