#!/usr/bin/perl
use DBI;
use Getopt::Long;
use Pod::Usage;
use MP3::Tag;
use MP3::Info qw(:all);
use Ogg::Vorbis::Header;
use POSIX qw(strftime);
use Cwd qw(abs_path realpath);
use strict;
use utf8;
use open ':std', ':encoding(UTF-8)';


my $cfgdir		= sprintf('%s/.pjam', $ENV{HOME});
my $cfgfile		= sprintf('%s/config', $cfgdir);
my $pjam_version	= "1.3.1";
my $pjam_desc		= "\npjam-import version $pjam_version for PerlJammer\n";
my (%opts, $DBH);

Getopt::Long::Configure('bundling');

if (scalar @ARGV == 0)
{
    pod2usage(-message => "$pjam_desc\nInsufficient arguments specified\n",
              -exitstatus => -1,
              -verbose => 0);
}
elsif (-d $cfgdir && -f $cfgfile)
{
    %opts = get_prefs($cfgfile);

    if (GetOptions(\%opts,
                   'find|f',
                   'help|usage|?|h|u',
                   'man|m',
                   'show|s',
                   'version|v'))
    {
        if ($opts{version})
        {
            pod2usage(-message => $pjam_desc,
                      -exitstatus => 0,
                      -verbose => 0);
        }
        elsif ($opts{help})
        {
            pod2usage(-message => $pjam_desc,
                      -exitstatus => 0,
                      -verbose => 1);
        }
        elsif ($opts{man})
        {
            pod2usage(-exitstatus => 0,
                      -verbose => 2);
        }
        else
        {
            import_files();
        }
    }
    else
    {
        pod2usage(-message => $pjam_desc,
                  -exitstatus => -1,
                  -verbose => 0);
    }
}
else
{
    print "The config file ~/.pjam/config was not found.  Please set up PerlJammer\n",
          "and its database correctly before running pjam-import.\n";
}

exit (0);


sub get_prefs
{
    my %o;

    open(PREFS, $_[0]);
    while (<PREFS>)
    {
        next if (/^#/);
        if (/^(\S+)\s*[:=]\s+(yes|true)$/i)
        {
            $o{$1} = 1;
        }
        elsif (/^(\S+)\s*[:=]\s+(no|false)$/i)
        {
            $o{$1} = 0;
        }
        elsif (/^(\S+)\s*[:=]\s+"?([^"]+)"\s*$/)        # " reset
        {
            $o{$1} = $2;
        }
        elsif (/^(\S+)\s*[:=]\s+(\S.*\S)\s*$/)
        {
            $o{$1} = $2;
        }
        elsif (/^(\S+)\s*[:=]\s+(\d+)\s*$/)
        {
            $o{$1} = $2;
        }
    }

    return(%o);
}


sub open_db
{
    my ($dsn, $dbh, $user, $password);

    $dsn = "DBI:mysql:%s;"
         . "mysql_read_default_group=%s;"
         . "mysql_read_default_file=%s;"
         . "mysql_mysql_client_found_rows=TRUE;"
         . "mysql_mysql_ssl=TRUE";


    $dsn = sprintf($dsn,
                   $opts{sqldb},
                   $opts{sqlgroup},
                   $opts{sqlcfg});

    $dbh = DBI->connect($dsn, undef, undef,
                        {RaiseError => 1, AutoCommit => 1, mysql_enable_utf8 => 1});

    my $sth = $dbh->prepare('SET NAMES utf8mb4') || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    return ($dbh);
}


sub import_files
{
    my @audio_files;

    MP3::Tag->config(write_v24 => 1, prohibit_v24 => 0);
    use_winamp_genres();
   
    select((select(STDOUT), $| = 1)[0]);
    $DBH = open_db() || die "Could not open the database";

    print "Collecting new files ...";

    push (@ARGV, '.') unless (scalar @ARGV);
    foreach my $path (@ARGV)
    {
        find_files($DBH, realpath($path), \@audio_files);
    }
    print " Done.\n";

    foreach my $file (sort @audio_files)
    {
        if ($file =~ /\.m3u$/)
        {
            next;
        }
        elsif ($file =~ /\.mp3$/)
        {
            my ($mp3, $title, $track, $artist, $disc, $comment, $year, $genreID, $genre, $info);

            $mp3 = MP3::Tag->new($file);
            $info = get_mp3info($file);
            ($title, $track, $artist, $disc, $comment, $year, $genre) = $mp3->autoinfo;
            $mp3->get_tags;
            $genreID = $mp3->genres($genre);

            if ($opts{find} || $opts{show})
            {
                print "$file\n";
                if ($opts{show})
                {
                    printf("\t%s :: %s :: %s [%02d] (%02d:%02d, %s, %d)\n",
                           $artist, $disc, $title, $track, $info->{MM}, $info->{SS}, $genre, $year);
                }
            }
            else
            {
                mp3insert($DBH,
                          $file, $title, $artist, $disc, $track, $year, $genre,
                          sprintf('%02d:%02d', $info->{MM}, $info->{SS}));
            }
        }
        elsif ($file =~ /\.ogg$/)
        {
            my ($ogg, $title, $track, $artist, $disc, $comment, $year, $genre, $length);

            $ogg = Ogg::Vorbis::Header->new($file);
            $length = $ogg->info('length');
            
            if ($opts{find} || $opts{show})
            {
                print "File:\t$file\n";
                if ($opts{show})
                {
                    printf("\t%s :: %s :: %s [%02d] (%02d:%02d, %s, %d)\n",
                           $ogg->comment('artist'),
                           $ogg->comment('album'),
                           $ogg->comment('title'),
                           $ogg->comment('tracknumber'),
                           $length / 60,
                           $length % 60,
                           $ogg->comment('genre'),
                           $ogg->comment('date'));
                }
            }
            else
            {
                mp3insert($DBH,
                          $file,
                          $ogg->comment('title'),
                          $ogg->comment('artist'),
                          $ogg->comment('album'),
                          $ogg->comment('tracknumber'),
                          $ogg->comment('date'),
                          $ogg->comment('genre'),
                          sprintf('%02d:%02d',
                                  $length / 60,
                                  $length % 60));
            }
                
#               foreach my $com ($ogg->comment_tags)
#               {
#                   print "$com: $_\n" foreach $ogg->comment($com);
#               }

#            while (my ($k, $v) = each %{$ogg->info})
#            {
#                print "$k: $v\n";
#            }
        }
    }

    $DBH->disconnect();
}


sub find_files
{
    my ($dbh, $path, $filelist) = @_;
    my @dir;

    if (-d $path)
    {
        opendir (DIR, $path);
        @dir = grep(!/^\.+$/, readdir(DIR));
        closedir (DIR);

        foreach my $entry (@dir)
        {
            my $newpath = $path.'/'.$entry;
            if (-d $newpath)
            {
                print ".";
                find_files($dbh, $newpath, $filelist);
            }
            elsif (-f $newpath)
            {
                check_file($dbh, $newpath, $filelist);
            }
        }
    }
    elsif (-f $path)
    {
        check_file($dbh, $path, $filelist);
    }
}


sub check_file
{
    my ($dbh, $file, $filelist) = @_;

    if ($file =~ /\.(mp3|ogg)$/i)
    {
        my $query = sprintf('SELECT COUNT(id) FROM song WHERE filename = "%s"',
                            $file);
        my $sth = $dbh->prepare($query) || die "Error:" . $DBH->errstr . "\n";
        $sth->execute() || die "Error:" . $DBH->errstr . "\n";
        my $ref = $sth->fetchrow_arrayref;
        if ($$ref[0] == 0)				# new file
        {
            push(@$filelist, $file);
        }
        $sth->finish();
    }
}


sub mp3insert
{
    my ($dbh, $file, $title, $artist, $disc, $track, $year, $genre, $time) = @_;
    my ($sth, $query, $db_version, $artistid, $discid, $id);

    # sanitize quotes in data
    $artist =~ s/"/\\"/g;
    $title =~ s/"/\\"/g;
    $disc =~ s/"/\\"/g;
    
    # Force these to INTs to protect against stupid people tagging stupid values
    $year = int($year);
    $track = int($track);


    # Retrieve the artistid, inserting the artist if necessary
    
    $query = sprintf('SELECT id FROM artist WHERE artist = "%s"',
                     $artist);
    $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    if (my $ref = $sth->fetchrow_arrayref)
    {
        $artistid = $$ref[0];
        $sth->finish();
    }
    else
    {
        $sth->finish();
        $query = sprintf('INSERT INTO artist (artist, date_added)
                          VALUES ("%s", curdate())',
                         $artist);
        $dbh->do($query) || die "Error:" . $dbh->errstr . "\n";

        $query = sprintf('SELECT id FROM artist WHERE artist = "%s"',
                         $artist);
        $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
        $sth->execute || die "Error:" . $dbh->errstr . "\n";
        my $ref = $sth->fetchrow_arrayref;
        $artistid = $$ref[0];
        $sth->finish();
    }

    # Retrieve the discid, inserting the disc if necessary
    # We create a special-case disc here named "(single)"
    # with no artist, date, or genre, for singles.

    $query = ($disc eq '(single)')
           ? 'SELECT id FROM disc WHERE disc = "(single)"'
           : sprintf('SELECT id FROM disc WHERE artistid = "%s" AND disc = "%s"',
                     $artistid,
                     $disc);
    $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    if (my $ref = $sth->fetchrow_arrayref)
    {
        $discid = $$ref[0];
        $sth->finish();
    }
    else
    {
        $sth->finish();

        # We also need to special-case the disc here,
        # if the '(single)' disc has not already been created.

        $query = ($disc eq '(single)')
               ? sprintf('INSERT INTO disc (disc, artistid, date_added)
                          VALUES ("(single)", 0, curdate())')
               : sprintf('INSERT INTO disc (disc, artistid, genre, year, date_added)
                          VALUES ("%s", "%s", "%s", "%s", curdate())',
                         $disc,
                         $artistid,
                         $genre,
                         $year);
        $dbh->do($query) || die "Error:" . $dbh->errstr . "\n";

        $query = ($disc eq '(single)')
               ? sprintf('SELECT id FROM disc WHERE disc = "%s"',
                         $disc)
               : sprintf('SELECT id FROM disc WHERE artistid = "%s" AND disc = "%s"',
                         $artistid,
                         $disc);
        $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
        $sth->execute || die "Error:" . $dbh->errstr . "\n";
        my $ref = $sth->fetchrow_arrayref;
        $discid = $$ref[0];
        $sth->finish();
    }

    # Check to see whether this track already exists as determined by a filename/artist/disc
    # triple.  We will treat duplicate tracks with the same filename/artist/disc triple but
    # with different paths as distinct tracks.  We will also treat duplicate tracks with the
    # same filename and artist but different disc as distinct tracks, thus allowing the
    # construction of virtual discs from existing tracks.  It is the caller's responsibility
    # to provide a valid and correct path in each case.  The problem remains of tracks having
    # the same filename but different artist metadata; we assume this to be the result of an
    # error at some point and leave it to the user to resolve manually.

    $query = sprintf('SELECT id FROM song
                      WHERE filename = "%s" AND artistid = "%s" AND discid = "%s"',
                     $file,
                     $artistid,
                     $discid);
    $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    if (my $ref = $sth->fetchrow_arrayref)
    {
        $id = $$ref[0];
    }
    $sth->finish();

    # If it already exists, we will assume this is a rescan and update the song's metadata.
    # Otherwise, we will insert a new entry.

    if ($id)
    {
        $query = sprintf('UPDATE song
                          SET title="%s", discid="%s", genre="%s", track_num="%s", play_time="%s", year="%s", gain_adj="%s"
                          WHERE id="%s" AND filename="%s" AND artistid="%s"',
                          $title,
                          $discid,
                          $genre,
                          $track,
                          $time,
                          $year,
                          $id,
                          $file,
                          $artistid);
    }
    else
    {
        $query = sprintf('INSERT INTO song (filename, title, artistid, discid, genre, track_num, play_time, year, date_added, gain_adj)
                          VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", curdate(), "%s")',
                          $file,
                          $title,
                          $artistid,
                          $discid,
                          $genre,
                          $track,
                          $time,
                          $year,
                          0);
    }
    $dbh->do($query) || die "Error:" . $dbh->errstr . "\n";

    $query = sprintf('SELECT s.id, a.artist, d.disc, s.title, s.track_num, s.play_time, s.genre, s.year
                      FROM song as s, disc as d, artist as a
                      WHERE s.filename = "%s" AND s.artistid = a.id AND s.discid = d.id',
                     $file);
    $sth = $dbh->prepare($query) || die "Error:" . $dbh->errstr . "\n";
    $sth->execute || die "Error:" . $dbh->errstr . "\n";

    if (my $ref = $sth->fetchrow_arrayref)
    {
       printf("%s: %s -- %s -- %s [%02d] (%s, %s, %s)\n", @$ref);
    }
    $sth->finish();
    
}


__END__

=head1 NAME

B<pjam-import> - Import MP3 and Ogg Vorbis files into the PerlJammer database

=head1 VERSION

Version 1.3.1

=head1 SYNOPSIS

pjam-import [options] path [path [...]]

  Options:
    --find
    --show
    --help, --usage, -?
    --man
    --version

=head1 OPTIONS

=over 4

=item B<-f, --find>

Do not insert anything into the database; find un-added files only.

=item B<-s, --show>

Show metadata on found files (implies B<-find>)

=item B<-h, -u, -?, --help, --usage>

List command-line options and usage, then exit

=item B<-m, --man>

Display full documentation and exit

=item B<-v, --version>

Display version string and exit

=back

=head1 DESCRIPTION

B<pjam-import> scans the specified directory path to find audio files which are
not already contained in the PerlJammer database.  It will then import those files
into the database, using file metadata extracted from the tags in the files.

B<IT IS YOUR RESPONSIBILITY TO ENSURE THAT THESE TAGS CONTAIN THE CORRECT METADATA
BEFORE RUNNING PJAM-IMPORT!>  Verifying correctness of tag metadata is a complex
problem that frequently requires human judgement, and is beyond the scope of
B<pjam-import>.

If you are uncertain whether your metadata is correct, use B<pjam-import -find> to
see what would be added.  You can use B<pjam-import -show> (which implies B<-find>)
to have pjam-import show you the metadata it finds in each file.

If you need to correct file metadata before running B<pjam-import>, we recommend
B<MusicBrainz Picard>, available from http://musicbrainz.org/ if your OS distribution
does not already provide it as a package.

=head1 SINGLES

B<pjam-import> handles singles by means of a special meta-disc named '(single)',
which has no associated artist, genre or date.  If it does not already exist, this
meta-disc will be created the first time it is needed.  It is ASSUMED that in the
case of a single, the track number will be either 0 (i.e, left blank), 1 (A-side),
or 2 (B-side), but this is neither checked nor enforced by B<pjam-import>.

=head1 REPORTING BUGS

Please send all bug reports to the author.

=head1 LICENSE

B<pjam-import>, part of the B<PerlJammer> package, is copyright (C) 2003-2023 Phil Stracchino.

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.

=head1 OBTAINING PerlJammer

B<PerlJammer> can be downloaded from http://co.ordinate.org/perljammer/.

=head1 AUTHOR

B<PerlJammer> and its supporting tools are written and maintained by Phil Stracchino
(phil@co.ordinate.org).

=cut

