#!/usr/bin/perl
use DBI;
use Getopt::Long;
use Pod::Usage;
use POSIX qw(strftime);
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.1.0';
my $pjam_desc		= "\nmp3insert version $pjam_version for PerlJammer\n";
my %opts;


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

    if (GetOptions(\%opts,
                   'path|p=s',
                   'title|t=s',
                   'artist|a=s',
                   'disc|d=s',
                   'genre|g=s',
                   'year|y=s',
                   'track|n=i',
                   'frame|f=s',
                   'length|l=s',
                   'time|m=s',
                   'bpm|b=s',
                   'gain|replay|r=i',
                   'no_grip_fix' => sub { $opts{grip_track_fix} = 0 },
                   'help|usage|?',
                   'man',
                   'version'))
    {
        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
        {
            mp3insert();
        }
    }
    else
    {
        pod2usage(-message => $pjam_desc,
                  -exitstatus => -1,
                  -verbose => 1);
    }
}
else
{
    print "The config file ~/.pjam/config was not found.  Please run PerlJammer\n",
          "to create it.  Preferably, you should edit it to set the sqlconfigfile,\n",
          "sqldb, and sqlgroup preferences.  You will then not need to use -cnffile\n",
          "-dbname, or -group here.\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{sqlconfigfile});

    $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 mp3insert
{
    my ($sth, $dbh, $query, $db_version, $artistid, $discid, $id);

    # sanitize quotes in data
    $opts{artist} =~ s/"/\\"/g;
    $opts{title} =~ s/"/\\"/g;
    $opts{disc} =~ s/"/\\"/g;

    # fix the time value, which Grip sends mis-formatted
    $opts{time} = sprintf('%02d:%02d', split(/:/, $opts{time}));

    $dbh = open_db() || die "Failed to open the database";

    # Retrieve the artistid, inserting the artist if necessary

    $query = sprintf('SELECT id FROM artist WHERE artist = "%s"',
                     $opts{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())',
                         $opts{artist});
        $dbh->do($query) || die "Error:" . $dbh->errstr . "\n";

        $query = sprintf('SELECT id FROM artist WHERE artist = "%s"',
                         $opts{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

    $query = sprintf('SELECT id FROM disc WHERE artistid = "%s" AND disc = "%s"',
                     $artistid,
                     $opts{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();
        $query = sprintf('INSERT INTO disc (disc, artistid, genre, year, date_added)
                          VALUES ("%s", "%s", "%s", "%s", curdate())',
                         $opts{disc},
                         $artistid,
                         $opts{genre},
                         $opts{year});
        $dbh->do($query) || die "Error:" . $dbh->errstr . "\n";

        $query = sprintf('SELECT id FROM disc WHERE disc = "%s" AND artistid = "%s"',
                         $opts{disc},
                         $artistid);
        $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"',
                     $opts{path},
                     $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.  We increment the track number because we need
    # to be compatible with Grip, which starts track numbers from 0.

    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"',
                          $opts{title},
                          $discid,
                          $opts{genre},
                          $opts{track} + $opts{grip_track_fix},
                          $opts{time},
                          $opts{year},
                          $opts{gain} || 0,
                          $id,
                          $opts{path},
                          $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")',
                          $opts{path},
                          $opts{title},
                          $artistid,
                          $discid,
                          $opts{genre},
                          $opts{track} + $opts{grip_track_fix},
                          $opts{time},
                          $opts{year},
                          $opts{gain} || 0);
    }
    $dbh->do($query) || die "Error:" . $dbh->errstr . "\n";

    $dbh->disconnect();
}




__END__

=head1 NAME

B<mp3insert> - Inserts MP3 tracks into B<PerlJammer>'s music database

=head1 VERSION

Version 1.1.0

=head1 SYNOPSIS

mp3insert [options]

  Options:
    -path
    -title
    -artist
    -disc
    -genre
    -year
    -track (-n)
    -frame      [ignored; for compatibility only]
    -length     [ignored; for compatibility only]
    -time (-m)
    -bpm        [ignored; for compatibility only]
    -replay, -gain (-r)
    -help, -usage, -?
    -man
    -version

=head1 OPTIONS

=over 4

=item B<-path>

The full path to the MP3 file.  MP3insert will resolve symbolic links.

=item B<-title>

The title of the track.

=item B<-artist>

The artist for this track.

=item B<-disc>

The disc containing this track.

=item B<-genre>

The ID3 genre name for this track.

=item B<-year>

The year of release for this track.

=item B<-track>

The track number of this track on the disc.

=item B<-frame>

Ignored; present for compatibility only.

=item B<-length>

Ignored; present for compatibility only.

=item B<-time>

The running time of this track in minutes and seconds (mm:ss).

=item B<-bpm>

Ignored; present for compatibility only.

=item B<-replay>, B<-gain>

Desired replay gain to be applied to this track.

=item B<-no_grip_fix>

Do not increment track numbers (see below)

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

List command-line options and usage, then exit

=item B<-man>

Display full documentation and exit

=item B<-version>

Display version string and exit

=back

=head1 DESCRIPTION

B<mp3insert>, as its name suggests, inserts MP3 music tracks (or, more accurately, their
metadata) into B<PerlJammer>'s music database.  It should be automatically used by B<Grip>
for this purpose if the option is checked in B<Grip>'s configuration.  All of the above
options may be abbreviated to their initial letters EXCEPT as indicated in the usage summary.

It should be noted that three of the possible command-line options are present ONLY for
compatibility with B<Grip> and B<DigitalDJ>, and of those three, two (start frame and length
in frames) were never used even by B<DigitalDJ>.  At present, these three options are accepted
by B<PerlJammer>, but are ignored and are not written to the database.  (In fact, if the
database has been updated to B<PerlJammer>'s version 3 schema, the applicable fields no
longer even exist.)

=head1 IMPORTANT USAGE NOTE:

B<mp3insert> is not intended to be used directly by the user.  It is intended to be called
either by B<Grip> or by B<pjam-import>.  Accordingly, IT DOES NOT CHECK COMMAND-LINE
ARGUMENTS.  It assumes that they have been supplied correctly.  If you choose to use
mp3insert manually, it is your responsibility to verify that all of the key required arguments
(path, title, artist, disc, genre, year, track, time) are correct.

B<Grip> calls B<mp3insert> with track numbers starting from 0.  (Don't ask me why; ask Mike
Oliphant.)  B<PerlJammer>'s B<mp3insert> corrects for this by incrementing track numbers by
1.  If calling B<mp3insert> manually, it is probably desirable that this not happen.  The
B<-no_grip_fix> option is provided for this purpose; it simply tells B<mp3insert> to assume
that track numbers are correct in the first place and B<NOT> increment them.

=head1 REPORTING BUGS

Please send all bug reports to the author.

=head1 LICENSE

B<mp3insert>, part of the B<PerlJammer> package, is copyright (C) 2003 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
