#!/usr/bin/perl
#
# m3u2myth.pl
#
# m3u <==> MythMusic DB
# created: 08/25/2003
#
# Copyright (C) 2005 Joseph N. Mondloch
# #
# # This program is free software; you can redistribute it and/or modify
# # it under the terms of the GNU General Public License version 2,
# # as published by the Free Software Foundation
# #
# # 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.
# #
# # http://www.gnu.org/licenses/gpl.txt
# #
#
# insert m3u playlist files into MythMusic DB
# build m3u playlist files from MythMusic DB
#
#
# Version: 0.02162005

use Getopt::Long;
use DBI;
use MP3::Info;
                                                                                                                                                
my $DBuser = 'mythtv';
my $DBpass = 'mythtv';
my $DBhost = 'localhost';
my $FEhost = 'myth';
my %playlist;
my %playlistid;
my $playlistname;
my $filename;
my $mode = 0;
my $force = 0;
my $dbh;

print "\n---------------------------------------\n";
print "m3u <==> MythMusic DB Conversion Script\n";
print "---------------------------------------\n\n";
GetOptions (
   'file=s'    => \$filename,
   'name=s'    => \$playlistname,
   'id=i'      => \$playlistid,
   'user=s'    => \$DBuser,
   'pass=s'    => \$DBpass,
   'host=s'    => \$DBhost,
   'fehost=s'  => \$FEhost,
   'list'      => sub { $mode = 1; },
   'toDB'      => sub { $mode = 2; },
   'toM3U'     => sub { $mode = 3; },
   'force'     => sub { $force = 1; },
);

if ($mode == 1) { 
   $dbh = DBI->connect("dbi:mysql:database=mythconverg;host=$DBhost;user=$DBuser;password=$DBpass") || die "$DBI::errstr";
   &listDB();
} elsif ($mode == 2) { 
   $dbh = DBI->connect("dbi:mysql:database=mythconverg;host=$DBhost;user=$DBuser;password=$DBpass") || die "$DBI::errstr";
   if (defined($filename) && defined($playlistname)) {   
      &readM3U();
      &writeDB();
   } else {
      print "ERROR: --toDB requires: --file & --name\n";
   }
} elsif ($mode == 3) { 
   $dbh = DBI->connect("dbi:mysql:database=mythconverg;host=$DBhost;user=$DBuser;password=$DBpass") || die "$DBI::errstr";
   if (defined($filename) && defined($playlistid)) {   
      &readDB($playlistid);
      &writeM3U($playlistid);
   } else {
      print "ERROR: --toM3U requires: --file & --id\n";
   }
} else { &showHelp(); }
exit(0);

sub showHelp {
   print "Usage:\n";
   print " $0\n";
   print "   --list\t\t\t [list Playlists in DB]\n";
   print "   --toDB\t\t\t [Load m3u file into DB]\n";
   print "   --toM3U\t\t\t [Build m3u file from DB]\n";
   print "   --file <file.m3u>\t\t [m3u playlist file]\n";
   print "   --id 'playlist id'\t\t [DB playlist id number]\n";
   print "   --force\t\t\t [Override File/DB entry exists errors]\n";
   print "   --name 'playlist name'\t [name for playlist (db/file)]\n";
   print "   --fehost 'hostname'\t\t [MythTV Frontend Hostname]\n";
   print "   --user 'username'\t\t [DB user -- Default: $DBuser]\n";
   print "   --pass 'password'\t\t [DB password -- Default: $DBpass]\n";
   print "   --host 'hostname'\t\t [DB host -- Default: $DBhost]\n";
}

sub listDB {
   $sth = $dbh->prepare("SELECT playlistid,name,hostname FROM musicplaylist;");
   $sth->execute || die "Unable to execute query: $dbh->errstr\n";
                 
   print "Current playlist(s) in DB:\n";                                                                                                                               
   while($row = $sth->fetchrow_arrayref)
   {
      print "ID: $row->[0]\t Name: $row->[1]\t Host: $row->[2]\n";
   }
}

sub readDB {
   my $playlistid = $_[0];
   my @songids;

   print "Retrieving playlist: $playlistid...\n";
   $sth = $dbh->prepare("SELECT name,songlist,hostname FROM musicplaylist WHERE playlistid=$playlistid;");
   $sth->execute || die "Unable to execute query: $dbh->errstr\n";
                                                                                                                                                
   while($row = $sth->fetchrow_arrayref)
   {
      $playlist{$playlistid}{'name'} = $row->[0];
      @songids = split /,/, $row->[1];
      $playlist{$playlistid}{'hostname'} = $row->[2];
   }

   foreach(@songids) {
      if ($_ < 0) {
         print "Playlist contains sub-playlist ($_)...\n";
         $playlist{$playlistid}{'songlist'} = $playlist{$playlistid}{'songlist'} . &readDB(0 - $_) . ",";
      } else {
         $playlist{$playlistid}{'songlist'} = $playlist{$playlistid}{'songlist'} . $_ . ",";
      }
   }
   chop($playlist{$playlistid}{'songlist'});

   return($playlist{$playlistid}{'songlist'});
}


sub writeDB {
   my $songlist;

   $sth = $dbh->prepare("SELECT * FROM musicplaylist WHERE name=\"$playlistname\" AND hostname=\"$FEhost\";");
   $sth->execute || die "Unable to execute query: $dbh->errstr\n";
   if ($sth->rows() > 0) {
      if ($force == 0) {
         print "DB: $playlistname already exists. Use --force to override.\n";
         exit(1);
      }
      else {
         $sth = $dbh->prepare("DELETE FROM musicplaylist WHERE name=\"$playlistname\" AND hostname=\"$FEhost\";");
         $sth->execute || die "Unable to execute query: $dbh->errstr\n";
      }
   }

   foreach $file (@{ $playlist{'m3u'} }) {
      $sth = $dbh->prepare("SELECT intid FROM musicmetadata WHERE filename=\"$file\";");
      $sth->execute || die "Unable to execute query: $dbh->errstr\n";
 
      while($row = $sth->fetchrow_arrayref)
      {
         $songlist = $songlist . $row->[0] . ",";
      }
   }
   chop($songlist);

   print "Inserting new playlist to DB: $playlistname:$songlist\n";
   $sth = $dbh->prepare("INSERT INTO musicplaylist (name,songlist,hostname) VALUES (\"$playlistname\",\"$songlist\",\"$FEhost\");");
   $sth->execute || die "Unable to execute query: $dbh->errstr\n";
}

sub readM3U {
   print "Reading data from m3u file...\n";
   open(HAND, "$filename") || die("unable to open m3u file: $!");
   while(<HAND>) {
      chomp;
      next if /^#EXT/;
      push @{ $playlist{'m3u'} }, $_;
   }
   close(HAND);
}

sub writeM3U {
   my $playlistid = $_[0];

   print "Writing playlist ($playlist{$playlistid}{'name'}) to m3u file...\n";   
   my @songids = split /,/, $playlist{$playlistid}{'songlist'};

   if ((-e $filename) && ($force == 0)) {   
      print "FILE: $filename already exists. Use --force to override.\n";
      exit(1);
   }
   open(HAND, ">$filename") || die("unable to open m3u output file: $!");
   print HAND "#EXTM3U\n"; 
   foreach(@songids) {
      $sth = $dbh->prepare("SELECT filename FROM musicmetadata WHERE intid=\"$_\";");
      $sth->execute || die "Unable to execute query: $dbh->errstr\n";
            
      while($row = $sth->fetchrow_arrayref)
      {
         my $mp3Tag = get_mp3tag($row->[0],2,1);
         my $mp3Info = get_mp3info($row->[0],2,1);
         $mp3Tag->{'TPE1'} =~ tr/\000-\010//d;
         $mp3Tag->{'TIT2'} =~ tr/\000-\010//d;
         print HAND "#EXTINF:" . int($mp3Info->{'SECS'}) . "," . $mp3Tag->{'TPE1'} . " - $mp3Tag->{'TIT2'}\n";
         print HAND "$row->[0]\n";
      }
   }
   close(HAND);
}