#!/usr/bin/perl -w
#
# $Date$
# $Revision$
# $Author$
#
#  musicdirs.pl
#
#

my $settings_dir = "$ENV{'HOME'}/.musicdirs";

my @dir_formats = ('genre/%G/%P/%B/%N. %T', 'artist/%p/%P/%B/%N. %T', 'year/%Y/%P/%B/%N. %T');

# Libraries
    use DBI;
    use Encode;
    use File::Find;
    use File::Path;
    use File::Basename;
    use Getopt::Long;

# Auto flush buffers
    $|++;

# Check for necessary programs
    # check for id3info
    # check for vorbiscomment

# Load cli arguments
    my ($scan, $calc, $stats, $create);
    GetOptions('scan'     => \$scan,
               'calc'     => \$calc,
               'stats'    => \$stats,
               'create'   => \$create
              );

# these are used several places -- might as well define th here for clarity
    my ($sh, $count);

# Create the settings directory
    mkdir $settings_dir, 0700
        or chmod 0700, $settings_dir;

# Connect to the database
    my $dbh = DBI->connect("dbi:SQLite:dbname=$settings_dir/musicdirs.db",'','');
    if (!$dbh || $DBI::errstr) {
        die "Couldn't open/create $settings_dir/musicdirs.db SQLite database.\n";
    }
    END {
        no warnings;
        $dbh->disconnect if ($dbh);
    }

# Make sure the database is up to date
    db_update();

# Need to know how many tracks there are so we can report progress
    $sh = $dbh->prepare('SELECT COUNT(id) FROM tracks');
    $sh->execute();
    our ($num_tracks) = $sh->fetchrow_array();
    $sh->finish;

# Where are the files
    my $music_dir = setting('music_dir', '/pub/music/all_music');
    if (!$music_dir) {
    }
    elsif (! -e $music_dir) {
    }
    my $parent_dir = $music_dir;
    $parent_dir =~ s#/[^/]+/?$##;

# Load the genres
    load_mp3_genres();

# Scan?
    if ($scan) {
        print "Scanning files...\n";
    # Set up the statement handles that will be used by parse_music
        our $mtime_h = $dbh->prepare('SELECT id, inode, mtime FROM tracks WHERE filename=? AND path_id=?');
        our $ins_h   = $dbh->prepare('INSERT INTO tracks (id,filename,path_id,inode,mtime,type,title,artist_id,album_id,genre_id,year,tracknum,flag) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,2)');
        our $upd_h   = $dbh->prepare('UPDATE tracks SET inode=?,mtime=?,type=?,title=?,artist_id=?,album_id=?,genre_id=?,year=?,tracknum=?,flag=2 WHERE id=?');
        our $flag_h  = $dbh->prepare('UPDATE tracks SET flag=1 WHERE id=?');

    # Flag all files before we start
        $dbh->do('UPDATE tracks SET flag=0');

    # Scan
        chdir($music_dir);
        our $files_parsed  = 0;
        our $files_updated = 0;
        find({ wanted => \&parse_music, no_chdir => 1 }, '.');
        print "\r    files scanned:  $files_updated / $files_parsed \n";

    # Finish out the statement handles
        $mtime_h->finish;
        $ins_h->finish;
        $upd_h->finish;
        $flag_h->finish;

    # Delete any duplicate entries belonging to deleted tracks
        $dbh->do('DELETE FROM duplicates WHERE track_id1 IN (SELECT id FROM tracks WHERE flag=0) OR track_id2 IN (SELECT id FROM tracks WHERE flag=0)');

    # Alert the user about files in the db that are no longer on disk
        $sh = $dbh->prepare('SELECT COUNT(flag) FROM tracks WHERE flag=0');
        $sh->execute();
        my ($rows) = $sh->fetchrow_array();
        $sh->finish();
        $dbh->do('DELETE FROM tracks WHERE flag=0');
        if ($rows > 0) {
            print "Removed $rows tracks because they no longer exist on disk.\n";
        }

    # Calculate "various" albums
        print "Calculating multi-artist albums...\n";

    # Reset the primary_artist field
        $dbh->do('UPDATE tracks SET primary_artist=NULL');
    # Scan
        my (%albums, $last_pair);
        $sh = $dbh->prepare('SELECT path_id, album_id, artist_id, COUNT(artist_id) AS artist_score
                             FROM tracks
                             GROUP BY path_id, album_id, artist_id');
        $sh->execute();
        while ($row = $sh->fetchrow_hashref) {
            my $key = join('-', $row->{'path_id'}, $row->{'album_id'});
            $albums{$key}{'path_id'}     = $row->{'path_id'};
            $albums{$key}{'album_id'}    = $row->{'album_id'};
            $albums{$key}{'num_tracks'} += $row->{'artist_score'};
            $albums{$key}{'artists'}{$row->{'artist_id'}} = $row->{'artist_score'};
            $albums{$key}{'num_artists'}++;
        }
        $sh->finish;
        $sh = $dbh->prepare('UPDATE tracks SET primary_artist=? WHERE path_id=? AND album_id=?');
        $count = 0;
        foreach my $album (keys %albums) {
            $album = $albums{$album};
            $count++;
        # Single artist
            next if ($album->{'num_artists'} < 2);
        # Calculate multi-artist
            my $primary = undef;
            foreach my $artist (keys %{$album->{'artists'}}) {
                next unless ($album->{'artists'}{$artist} / $album->{'num_tracks'} >= .75);     # Single-artist if 75% or more tracks attributed to a single artist
                $primary = $artist;
                last;
            }
            $primary ||= get_field_id('artist', 'Various');
            $sh->execute($primary,
                         $album->{'path_id'},
                         $album->{'album_id'});
        # Progress
            print "\r    processed:  $count / $files_parsed " if ($count % 100 == 0);
        }
        $sh->finish;
        print "\r    processed:  $count / $files_parsed \n";

    }

    if ($calc) {

    # Scan for duplicates
        print "Scanning for duplicate title+artist+album+tracknum...\n";

    # Delete any old duplicates
        $dbh->do('DELETE FROM duplicates');

    # Set up the statement handles
        my $dupc_h  = $dbh->prepare('SELECT id FROM tracks WHERE id!=? AND title=? AND artist_id=? AND album_id=? AND tracknum=?');
        my $dup_h   = $dbh->prepare('INSERT INTO duplicates (track_id1, track_id2) VALUES (?,?)');

    # Query
        my (%dupes, $num_dupes);
        $sh = $dbh->prepare('SELECT id, title, artist_id, album_id, tracknum FROM tracks ORDER BY path_id');
        $sh->execute();
        $count = $num_dupes = 0;
        while (my $track = $sh->fetchrow_hashref()) {
            $count++;
        # Already know this one is a dup?
            next if ($dupes{$track->{'id'}});
        # Find a match (or more)
            $dupc_h->execute($track->{'id'}, $track->{'title'}, $track->{'artist_id'}, $track->{'album_id'}, $track->{'tracknum'});
            while (my ($track_id) = $dupc_h->fetchrow_array()) {
                last unless ($track_id);
            # Match!
                $num_dupes++;
                $dupes{$track_id} = 1;
                $dup_h->execute($track->{'id'}, $track_id);
            }
        # Progress
            print "\r    processed:  $count / $num_tracks ($num_dupes duplicates) " if ($count % 100 == 0);
        }
        print "\r    processed:  $count / $num_tracks ($num_dupes duplicates) \n";
        $sh->finish;

    # Finish
        $dupc_h->finish;
        $dup_h->finish;

    }

# Stats
    if ($stats) {
    # Show the duplicates?
    #   SELECT p1.path, t1.filename, p2.path, t2.filename FROM duplicates, tracks t1, tracks t2, paths p1, paths p2 WHERE duplicates.track_id1=t1.track_id AND duplicates.track_id2=t2.track_id AND t1.path_id=p1.id AND t2.path_id=p2.id ORDER BY p1.path, t1.filename
    # No artist
    #   SELECT paths.path, tracks.filename FROM tracks, paths, artists WHERE tracks.path_id=paths.id AND tracks.artist_id=artists.id AND artists.artist="" ORDER BY path, filename
    # No album
    #   SELECT paths.path, tracks.filename FROM tracks, paths, albums WHERE tracks.path_id=paths.id AND tracks.album_id=albums.id AND albums.album="" ORDER BY path, filename
    # No genre
    #   SELECT paths.path, tracks.filename FROM tracks, paths, genres WHERE tracks.path_id=paths.id AND tracks.genre_id=genres.id AND genres.genre="" ORDER BY path, filename
    }

# Create the links
    if ($create) {
    # Create a genre directory
        mkpath "$parent_dir/genre";

    # Query all files and figure out where to put them
        my $linkp_h = $dbh->prepare('SELECT link_path FROM links WHERE track_id=?');
        my $linkd_h = $dbh->prepare('DELETE FROM links WHERE track_id=?');
        my $link_h  = $dbh->prepare('INSERT INTO links (track_id, link_path) VALUES (?,?)');
        $sh         = $dbh->prepare('SELECT tracks.*,
                                         paths.path,
                                         genres.genre,
                                         a1.artist,
                                         a2.artist AS primary_artist,
                                         albums.album
                                     FROM tracks, paths, genres, artists AS a1, albums
                                         LEFT JOIN artists AS a2
                                             ON tracks.primary_artist=a2.id
                                     WHERE
                                         tracks.path_id=paths.id
                                         AND tracks.genre_id=genres.id
                                         AND tracks.artist_id=a1.id
                                         AND tracks.album_id=albums.id
                                     ORDER BY paths.path, tracks.filename');
        $sh->execute();
        $count = 0;
        while (my $row = $sh->fetchrow_hashref) {
            my $link_path;
        # Load the current links (if any)
            $linkp_h->execute($row->{'id'});
            while (($link_path) = $linkp_h->fetchrow_array) {
                $row->{'links'}{$link_path} = 1;
            }
        # Delete the links for this track
            $linkd_h->execute($row->{'id'});
        # No concat in SQLite
            $row->{'path'} = $row->{'path'}.'/'.$row->{'filename'};
        # Parse the various paths
            foreach my $format (@dir_formats) {
            # Build a path
                my $short_path = fix_utf8(build_path($format, $row).'.'.$row->{'type'});
                my $path       = "$parent_dir/$short_path";
            # Known link -- remove it from the hash so we don't delete it later
                if ($row->{'links'}{$short_path}) {
                    delete $row->{'links'}{$short_path};
                }
            # Insert the row
                else {
                    $link_h->execute($row->{'id'}, $short_path);
                }
            # Compare the inode, and delete outdated links
                if (-e $path) {
                    my $inode = (stat($path))[1];
                    if ($inode != $row->{'inode'}) {
                        #print "stale inode or duplicate filename:  $short_path\n";
                        unlink $path
                            or die "Can't remove outdated inode $path:  $!\n\n";
                    }
                    else {
                        next;
                    }
                }
            # Link doesn't exist, make the path
                my ($parent) = (fileparse($path))[1];
                if (!-e $parent) {
                    mkpath($parent) or die "Couldn't mkpath $parent\n   $!\n";
                }
            # Create the link
                link "$music_dir/$row->{'path'}", $path
                    or die "couldn't create hard link $path\n    $!\n";
            }
        # Delete any remaining links
            foreach $link_path (keys %{$row->{'links'}}) {
                next unless (-e "$parent_dir/$link_path");
                next if ("$parent_dir/$link_path" =~ /^$music_dir/);
                #print "unlink:  $parent_dir/$link_path\n";
                unlink "$parent_dir/$link_path"
                    or die "Can't remove $parent_dir/$link_path:  $!\n\n";
            }
        # Status update
            print "\r    processed:  $count " if (++$count % 100 == 0);
            #print "$path\n";
        }
        print "\n";
        $sh->finish;
        $link_h->finish;
        $linkd_h->finish;
        $linkp_h->finish;
        ######
    }

print "done\n";


#####################################################
    sub build_path {
        my $format = shift;
        my $row    = shift;
        my $path   = '';
    # No format
        return undef unless ($format =~ /%\w/);
    # Set up the parsing variables
        my %parse;
        $parse{'F'} = clean_path($row->{'filename'});
        $parse{'G'} = clean_path($row->{'genre'}          or '_no_genre');
        $parse{'A'} = clean_path($row->{'artist'}         or '_no_artist');
        $parse{'P'} = clean_path($row->{'primary_artist'} or $row->{'artist'});
        $parse{'B'} = clean_path($row->{'album'}          or '_no_album');
        $parse{'Y'} = clean_path($row->{'year'}           or '_no_year');
        $parse{'T'} = clean_path($row->{'title'}          or '_no_title');
        $parse{'N'} = clean_path($row->{'tracknum'}       or '');
    # Short variables
        $parse{'g'} = $parse{'G'} eq '_no_genre'  ? $parse{'G'} : substr($parse{'G'}, 0, 1);
        $parse{'a'} = $parse{'A'} eq '_no_artist' ? $parse{'A'} : substr($parse{'A'}, 0, 1);
        $parse{'p'} = $parse{'P'} eq '_no_artist' ? $parse{'P'} : substr($parse{'P'}, 0, 1);
        $parse{'b'} = $parse{'B'} eq '_no_album'  ? $parse{'B'} : substr($parse{'B'}, 0, 1);
        $parse{'y'} = $parse{'Y'} eq '_no_year'   ? $parse{'Y'} : substr($parse{'Y'}, 0, 2);
        $parse{'t'} = $parse{'T'} eq '_no_title'  ? $parse{'T'} : substr($parse{'T'}, 0, 1);
        $parse{'n'} = $parse{'N'};
    # Matches
        my $keys = join('', keys %parse);
    # Parse
        $format =~ s/(?<!\\)\%([$keys])/$parse{$1}/sg;
        $format =~ s/(\s(\W)\s)(\2\s)+/$1/sg;
        $format =~ s/[\s\.\-]*\/[\s\.\-]*/\//sg;
        $format =~ s/\.$//sg;
    # Return
        return $format;
    }

    sub clean_path {
        my $word = shift;
        $word =~ tr/\/\-\\:/-/s;
        return $word;
    }

    sub parse_music {
        my $file = $File::Find::name;
        $file =~ s/^\.\///;
    # Keep track of the highest track id
        local $max_id;
    # Ignore non-music files
        return unless ($file =~ /\.(ogg|mp3)/i);
    # Increment the counters
        if (($files_updated > 0 && $files_updated % 10 == 0 && $files_parsed % 10 == 0) || $files_parsed % 250 == 0) {
            print "\r    files scanned:  $files_updated / $files_parsed ";
        }
        $files_parsed++;
    # Extract the path and filename
        my %info;
        ($info{'filename'}, $info{'path'}) = fileparse($file);
        $info{'path'} = get_field_id('path', $info{'path'});
    # Stat
        ($info{'inode'}, $info{'mtime'}) = (stat($file))[1,9];
    # Do we need to (re)scan this file?  If not, just update its flag
        my ($db_inode, $db_mtime);
        $mtime_h->execute($info{'filename'}, $info{'path'});
        ($info{'track_id'}, $db_inode, $db_mtime) = $mtime_h->fetchrow_array();
        if ($db_mtime && $info{'mtime'} == $db_mtime
                && $db_inode && $info{'inode'} == $db_inode) {
            $flag_h->execute($info{'track_id'});
            return;
        }
    # Get a shell-safe filename
        my $safe_file = shell_safe($file);
    # Handle mp3
        if ($file =~ /\.mp3$/i) {
            my $out = `id3info $safe_file`;
            $info{'type'} = 'mp3';
            ($info{'album'})    = $out =~ m/^===[\ \t]*TALB.+?:[\ \t]*(.*?)\s*?$/m;
            ($info{'genre'})    = $out =~ m/^===[\ \t]*TCON.+?:[\ \t]*(.*?)\s*?$/m;
            ($info{'title'})    = $out =~ m/^===[\ \t]*TIT2.+?:[\ \t]*(.*?)\s*?$/m;
            ($info{'artist'})   = $out =~ m/^===[\ \t]*TPE1.+?:[\ \t]*(.*?)\s*?$/m;
            ($info{'tracknum'}) = $out =~ m/^===[\ \t]*TRCK.+?:[\ \t]*(\d+)/m;
            ($info{'year'})     = $out =~ m/^===[\ \t]*TYER.+?:[\ \t]*(\d+)/m;
        # Old-style genre identifier
            if ($info{'genre'} && $info{'genre'} =~ /\((\d+)\)/) {
                $info{'genre'} = $mp3_genres[$1];
            }
        }
    # Handle ogg
        elsif ($file =~ /\.ogg$/i) {
            $out = `vorbiscomment $safe_file`;
            $info{'type'} = 'ogg';
            ($info{'album'})    = $out =~ m/^album=[\ \t]*(.*?)\s*?$/mi;
            ($info{'genre'})    = $out =~ m/^genre=[\ \t]*(.*?)\s*?$/mi;
            ($info{'title'})    = $out =~ m/^title=[\ \t]*(.*?)\s*?$/mi;
            ($info{'artist'})   = $out =~ m/^artist=[\ \t]*(.*?)\s*?$/mi;
            ($info{'tracknum'}) = $out =~ m/^tracknumber=[\ \t]*(\d+)/mi;
            ($info{'year'})     = $out =~ m/^date=[\ \t]*(\d+)/mi;
        }
    # No title, skip ahead
        #print STDERR "\nNo title:   $file\n" unless ($info{'title'}  && $info{'title'}  =~ /\w/);
        #print STDERR "\nNo genre:   $file\n" unless ($info{'genre'}  && $info{'genre'}  =~ /\w/);
        #print STDERR "\nNo artist:  $file\n" unless ($info{'artist'} && $info{'artist'} =~ /\w/);
        #print STDERR "\nNo album:   $file\n" unless ($info{'album'}  && $info{'album'}  =~ /\w/);
    # Clean up
        if ($info{'title'}) {
            $info{'title'}  =~ s/\s*\(.*?\)//sg;
        }
        if ($info{'artist'}) {
            $info{'artist'} =~ s/\s*\(.*?\)//sg;
            $info{'artist'} =~ s/\s+(featuring|with)\W.*$//s;
        # "the"
            $info{'artist'} =~ s/^(the|los)\s+(.+)$/$2, $1/si;
        # Various variations of "Various"
            $info{'artist'} = 'Various' if ($info{'artist'} =~ /^variou?s(?:\s*artist)?/i);
        }
    # Load keys for certain fields
        $info{'genre'}  = get_field_id('genre',  $info{'genre'});
        $info{'artist'} = get_field_id('artist', $info{'artist'});
        $info{'album'}  = get_field_id('album',  $info{'album'});
    # If there is a db_mtime, we just need to update
        $files_updated++;
        if ($info{'track_id'}) {
            $upd_h->execute($info{'inode'},
                            $info{'mtime'},     $info{'type'},
                            $info{'title'},     $info{'artist'},
                            $info{'album'},     $info{'genre'},
                            $info{'year'},      $info{'tracknum'},
                            $info{'track_id'});
        }
        else {
        # No max id on file
            if (!$max_id || $max_id < 1) {
                my $sh = $dbh->prepare('SELECT MAX(id) FROM tracks');
                $sh->execute();
                ($max_id) = $sh->fetchrow_array();
                $sh->finish;
                $max_id ||= 0;
            }
            $max_id++;
        # Insert
            $ins_h->execute($max_id,
                            $info{'filename'},  $info{'path'},
                            $info{'inode'},
                            $info{'mtime'},     $info{'type'},
                            $info{'title'},     $info{'artist'},
                            $info{'album'},     $info{'genre'},
                            $info{'year'},      $info{'tracknum'},
                           );
        }
    }

    sub shell_safe {
        my $str = shift;
        $str =~  s/'/'\\''/sg;
        return "'$str'";
    }

    sub setting {
        my $field = shift;
        my $value = undef;
        if (@_) {
            $value = shift;
            $dbh->do('DELETE FROM settings WHERE field=?', undef, $field);
            $dbh->do('INSERT INTO settings (field, value) VALUES (?,?)', undef, $field, $value);
            return $value;
        }
        my $sh = $dbh->prepare('SELECT value FROM settings WHERE field=?');
        if ($sh) {
            $sh->execute($field);
            ($value) = $sh->fetchrow_array();
            $sh->finish;
            return $value;
        }
        return undef;
    }

    sub get_field_id {
        my $field = shift;
        my $value = (shift or '');
    # Cache
        local %cache;
        local %max_cache;
    # Look it up?
        if (!$cache{$field}{$value} || $cache{$field}{$value} < 1) {
            my $sh = $dbh->prepare("SELECT id FROM ${field}s WHERE $field=?");
            $sh->execute($value);
            ($cache{$field}{$value}) = $sh->fetchrow_array();
            $sh->finish;
        # Need to create a new entry?
            if (!$cache{$field}{$value} || $cache{$field}{$value} < 1) {
                if (!$max_cache{$field} || $max_cache{$field} < 1) {
                    $sh = $dbh->prepare("SELECT MAX(id) FROM ${field}s");
                    $sh->execute();
                    ($max_cache{$field}) = $sh->fetchrow_array();
                    $sh->finish;
                    $max_cache{$field} ||= 0;
                }
                $max_cache{$field}++;
                $dbh->do("INSERT INTO ${field}s (id, $field) VALUES (?,?)", undef,
                         $max_cache{$field},
                         $value);
                $cache{$field}{$value} = $max_cache{$field};
            }
        }
    # Return
        return $cache{$field}{$value};
    }

    sub load_mp3_genres {
        @mp3_genres = (
            'Blues'                 ,# => 0,
            'Classic Rock'          ,# => 1,
            'Country'               ,# => 2,
            'Dance'                 ,# => 3,
            'Disco'                 ,# => 4,
            'Funk'                  ,# => 5,
            'Grunge'                ,# => 6,
            'Hip-Hop'               ,# => 7,
            'Jazz'                  ,# => 8,
            'Metal'                 ,# => 9,
            'New Age'               ,# => 10,
            'Oldies'                ,# => 11,
            'Other'                 ,# => 12,
            'Pop'                   ,# => 13,
            'R&B'                   ,# => 14,
            'Rap'                   ,# => 15,
            'Reggae'                ,# => 16,
            'Rock'                  ,# => 17,
            'Techno'                ,# => 18,
            'Industrial'            ,# => 19,
            'Alternative'           ,# => 20,
            'Ska'                   ,# => 21,
            'Death Metal'           ,# => 22,
            'Pranks'                ,# => 23,
            'Soundtrack'            ,# => 24,
            'Euro-Techno'           ,# => 25,
            'Ambient'               ,# => 26,
            'Trip-Hop'              ,# => 27,
            'Vocal'                 ,# => 28,
            'Jazz+Funk'             ,# => 29,
            'Fusion'                ,# => 30,
            'Trance'                ,# => 31,
            'Classical'             ,# => 32,
            'Instrumental'          ,# => 33,
            'Acid'                  ,# => 34,
            'House'                 ,# => 35,
            'Game'                  ,# => 36,
            'Sound Clip'            ,# => 37,
            'Gospel'                ,# => 38,
            'Noise'                 ,# => 39,
            'Alt. Rock'             ,# => 40,
            'Bass'                  ,# => 41,
            'Soul'                  ,# => 42,
            'Punk'                  ,# => 43,
            'Space'                 ,# => 44,
            'Meditative'            ,# => 45,
            'Instrumental Pop'      ,# => 46,
            'Instrumental Rock'     ,# => 47,
            'Ethnic'                ,# => 48,
            'Gothic'                ,# => 49,
            'Darkwave'              ,# => 50,
            'Techno-Industrial'     ,# => 51,
            'Electronic'            ,# => 52,
            'Pop-Folk'              ,# => 53,
            'Eurodance'             ,# => 54,
            'Dream'                 ,# => 55,
            'Southern Rock'         ,# => 56,
            'Comedy'                ,# => 57,
            'Cult'                  ,# => 58,
            'Gangsta Rap'           ,# => 59,
            'Top 40'                ,# => 60,
            'Christian Rap'         ,# => 61,
            'Pop/Funk'              ,# => 62,
            'Jungle'                ,# => 63,
            'Native American'       ,# => 64,
            'Cabaret'               ,# => 65,
            'New Wave'              ,# => 66,
            'Psychedelic'           ,# => 67,
            'Rave'                  ,# => 68,
            'Showtunes'             ,# => 69,
            'Trailer'               ,# => 70,
            'Lo-Fi'                 ,# => 71,
            'Tribal'                ,# => 72,
            'Acid Punk'             ,# => 73,
            'Acid Jazz'             ,# => 74,
            'Polka'                 ,# => 75,
            'Retro'                 ,# => 76,
            'Musical'               ,# => 77,
            'Rock & Roll'           ,# => 78,
            'Hard Rock'             ,# => 79,
            'Folk'                  ,# => 80,
            'Folk Rock'             ,# => 81,
            'National Folk'         ,# => 82,
            'Swing'                 ,# => 83,
            'Fast-Fusion'           ,# => 84,
            'Bebob'                 ,# => 85,
            'Latin'                 ,# => 86,
            'Revival'               ,# => 87,
            'Celtic'                ,# => 88,
            'Bluegrass'             ,# => 89,
            'Avantgarde'            ,# => 90,
            'Gothic Rock'           ,# => 91,
            'Progressive Rock'      ,# => 92,
            'Psychedelic Rock'      ,# => 93,
            'Symphonic Rock'        ,# => 94,
            'Slow Rock'             ,# => 95,
            'Big Band'              ,# => 96,
            'Chorus'                ,# => 97,
            'Easy Listening'        ,# => 98,
            'Acoustic'              ,# => 99,
            'Humour'                ,# => 100,
            'Speech'                ,# => 101,
            'Chanson'               ,# => 102,
            'Opera'                 ,# => 103,
            'Chamber Music'         ,# => 104,
            'Sonata'                ,# => 105,
            'Symphony'              ,# => 106,
            'Booty Bass'            ,# => 107,
            'Primus'                ,# => 108,
            'Porn Groove'           ,# => 109,
            'Satire'                ,# => 110,
            'Slow Jam'              ,# => 111,
            'Club'                  ,# => 112,
            'Tango'                 ,# => 113,
            'Samba'                 ,# => 114,
            'Folklore'              ,# => 115,
            'Ballad'                ,# => 116,
            'Power Ballad'          ,# => 117,
            'Rhythmic Soul'         ,# => 118,
            'Freestyle'             ,# => 119,
            'Duet'                  ,# => 120,
            'Punk Rock'             ,# => 121,
            'Drum Solo'             ,# => 122,
            'A Cappella'            ,# => 123,
            'Euro-House'            ,# => 124,
            'Dance Hall'            ,# => 125,
            'Goa'                   ,# => 126,
            'Drum & Bass'           ,# => 127,
            'Club-House'            ,# => 128,
            'Hardcore'              ,# => 129,
            'Terror'                ,# => 130,
            'Indie'                 ,# => 131,
            'BritPop'               ,# => 132,
            'Negerpunk'             ,# => 133,
            'Polsk Punk'            ,# => 134,
            'Beat'                  ,# => 135,
            'Christian Gangsta Rap' ,# => 136,
            'Heavy Metal'           ,# => 137,
            'Black Metal'           ,# => 138,
            'Crossover'             ,# => 139,
            'Contemporary Christian',# => 140,
            'Christian Rock'        ,# => 141,
            'Merengue'              ,# => 142,
            'Salsa'                 ,# => 143,
            'Thrash Metal'          ,# => 144,
            'Anime'                 ,# => 145,
            'JPop'                  ,# => 146,
            'Synthpop'              ,# => 147
        );
    }

    sub fix_utf8 {
        my $val  = shift;
        my $str  = ref $val ? $val : \$val;
        my $undo = shift;
    # Return Early?
        return '' unless ($$str and length($$str));
    # Get a temp var so we don't actually modify $$str
        my $tmp = $$str;
    # Decode the string to UTF-8 and check for malformed characters - if there are some, this isn't already UTF-8
        Encode::_utf8_on($tmp);
        my $is_utf8 = Encode::is_utf8($tmp, Encode::FB_QUIET);
    # Undoing utf-8?
        if ($undo) {
        # Malformed utf-8 characters, this is probably NOT utf-8
            return $$str if (!$is_utf8);
        # Now we convert back to iso-8859-1
            Encode::from_to($$str, 'utf-8', 'iso-8859-1');
            return $$str;
        }
    # No malformed characters - this is already UTF-8 - convert it back to latin1 check again to make sure that it's encoded properly
        if ($is_utf8) {
            Encode::from_to($$str, 'utf-8', 'iso-8859-1');
        # Check again to see if it wasn't just a malformed string
            $tmp = $$str;
            Encode::_utf8_on($tmp);
            $is_utf8 = Encode::is_utf8($tmp, Encode::FB_QUIET);
            if ($is_utf8) {
                Encode::from_to($$str, 'utf-8', 'iso-8859-1');
            }
        }
    # Now we decode from iso-8859-1
        Encode::from_to($$str, 'iso-8859-1', 'utf-8');
        return $$str;
    }

    sub db_update {
        my $db_vers = 0;
        $db_vers = setting('db_vers') if (-s "$settings_dir/musicdirs.db" > 0);
    # Leave early if we're up to date
        return if ($db_vers == 1);
    # No database
        if ($db_vers < 1) {
            $dbh->do('CREATE TABLE settings (
                        field   TEXT PRIMARY KEY,
                        value   TEXT
                      )');
            $dbh->do('CREATE TABLE genres (
                        id          INTEGER PRIMARY KEY,
                        genre       TEXT NOT NULL
                      )');
            $dbh->do('CREATE TABLE artists (
                        id          INTEGER PRIMARY KEY,
                        artist      TEXT NOT NULL
                      )');
            $dbh->do('CREATE TABLE albums (
                        id          INTEGER PRIMARY KEY,
                        album       TEXT NOT NULL
                      )');
            $dbh->do('CREATE TABLE paths (
                        id          INTEGER PRIMARY KEY,
                        path        BLOB NOT NULL
                      )');
            $dbh->do('CREATE TABLE tracks (
                        id              INTEGER PRIMARY KEY,
                        filename        BLOB NOT NULL,
                        path_id         INTEGER,
                        inode           INTEGER,
                        mtime           INTEGER,
                        type            TEXT,
                        title           TEXT,
                        year            INTEGER,
                        tracknum        INTEGER,
                        artist_id       INTEGER,
                        album_id        INTEGER,
                        genre_id        INTEGER,
                        flag            INTEGER DEFAULT 0,
                        primary_artist  INTEGER,

                        UNIQUE (filename, path_id)
                      )');
            $dbh->do('CREATE INDEX dupcheck ON tracks (id, title, artist_id, album_id, tracknum)');
            $dbh->do('CREATE INDEX flag ON tracks (flag)');
            $dbh->do('CREATE TABLE links (
                        track_id    INTEGER,
                        link_path   TEXT,
                        PRIMARY KEY (track_id, link_path)
                      )');
            $dbh->do('CREATE TABLE duplicates (
                        track_id1      INTEGER,
                        track_id2      INTEGER,
                        PRIMARY KEY (track_id1, track_id2)
                      )');
            $db_vers = setting('db_vers', $db_vers + 1);
        }
    }