#!/usr/bin/perl -w

#################################################################
##
## Script to get Audio CD information from the CDDB 
## and push them in the MySQL DB
##
## Uses code from cddb.pl script by Armin Obersteiner (the trick
## with getting the track times)
##     
##     (c) Tobias Cremer 2009
##	
##     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 2 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, write to the Free Software
##     Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
##

use strict; 
use CDDB_get qw( get_cddb ); 
use Data::Dumper; 
use Getopt::Long;
use DBI; 

use vars qw( $title $artist $verbose $query $verbose $multiple_art $silent $compilation $ask ); 
use vars qw( $dsn $dbh $user $password ); 
use vars qw( %cd ); 

GetOptions ('t' => \$title,
			'a' => \$artist,
			'v' => \$verbose,
			'q' => \$query,
            'm' => \$multiple_art, 
            's' => \$silent, 
            'y' => \$ask); 

### Set compilation according to $multiple_art; 
if( defined( $multiple_art )){

    $compilation = "1"; 

}else{

    $compilation = "0"; 

}

### Stop when $multiple_art is called until implemented
die "Not implemented yet!\n" if (defined( $multiple_art )); 

my %config;
# following variables just need to be declared if different from defaults

$config{CDDB_HOST}="freedb.freedb.org";        # set cddb host
$config{CDDB_PORT}=8880;                       # set cddb port
$config{CDDB_MODE}="cddb";                     # set cddb mode: cddb or http
$config{CD_DEVICE}="/dev/cdrom";               # set cd device

### INTERACTIVITY
$config{input}=1 unless( defined( $ask ));

%cd=get_cddb(\%config);

die "no cddb entry found" unless(defined $cd{title});

## Just show what was returned from freedb
&query() if( defined( $query ));

## connect to the mySQL DB
$dsn =  "DBI:mysql:database=audio;host=localhost";


$user = 'root';
# chop $password = $ENV{'audio_password'};
$password="password"; 
die "\$audio_password nicht gesetzt!" if( $password eq '' ); 


## Connect to DB first
$dbh =  DBI->connect($dsn, $user, $password,
        { RaiseError => 1, AutoCommit => 0 }) || die ("Can't connect to DB");


#############################################################
##
## Checkis we have the CD already in the database
## by comparing the cdid hex with the ones in the DB
##
my $cd_check = &check_cdid(); 
print "CHECK: $cd_check\n\n"; 

die "CD is already in the database!\n" if( $cd_check gt 0); 

##
## check if the artist already exist in the DB
##
my $artist_id = &artist(); 

##
## check if the publishing year already exist in the DB
##
my $year = &year(); 

##
## Add Album name with compilation flag and pub. year as arguments
##
my $album_id = &album( $compilation, $year ); 

##
## add tracks to DB aithz album and artist_id as arguments
##
my $tracks =&tracks( $album_id, $artist_id ); 

##
## finally, add the CD-Id to DB referencing the album
##
my $cd_id = &add_cdid( $album_id ); 

##
## Commit the stuff or roll back!
##

if( $tracks eq 1 ){

    $dbh->commit; 

}else{

    $dbh->rollback; 

}

###############################################
##
##
##     ALL SUBS BELOW
##
##
###############################################

##################################################
##
## Check if the artist exists, if not create it
##

sub artist {

my $query; 
my @insert_id;
my @artist;
my $rows; 
my $last_insert_file;

$query = $dbh->prepare( "SELECT art_id FROM artist WHERE `art_name`=\"$cd{artist}\"" ); 
$query->execute(); 

@artist  = $query->fetchrow_array;

print "Artist: " . $artist[0] . "\n" if( defined( $verbose )); 

$rows = $query->rows; 

if( $rows == 0 ){

    $query = $dbh->prepare( "INSERT INTO artist ( `art_name` ) VALUES ( '$cd{artist}' )" ); 
    $query->execute() || die  $dbh->error;


    $query = $dbh->prepare( "SELECT LAST_INSERT_ID()" ); 
    $query->execute();

    @insert_id  = $query->fetchrow_array;

    return $insert_id[0];

}else{

    print "$rows returned!\n"; 
    return $artist[0];


}

}

##################################################
##
## Check if a year exists, if not create it
##

sub year {

my $query; 
my @insert_id;
my @year;
my $rows; 
my $last_insert_file;

$query = $dbh->prepare( "SELECT `y_id` FROM year WHERE `y_year`=\"$cd{year}\"" ); 
$query->execute(); 

@year  = $query->fetchrow_array;

$rows = $query->rows; 

if( $rows == 0 ){

    $query = $dbh->prepare( "INSERT INTO year ( `y_year` ) VALUES ( '$cd{year}' )" ); 
    $query->execute() || die  $dbh->error;


    $query = $dbh->prepare( "SELECT LAST_INSERT_ID()" ); 
    $query->execute();

    @insert_id  = $query->fetchrow_array;

    return $insert_id[0];

}else{

    print "$rows returned!\n"; 
    return $year[0];


}

}

##############################################
##
## Create album title
##

sub album {

my $compilation = $_[0]; 
my $year = $_[1]; 
my $query; 
my @insert_id;
my $title; 

print "title: " . $cd{title} . "\n" if( defined( $verbose )); 
print "id: " . $cd{id} . "\n"  if( defined( $verbose )); 

$cd{title} =~ s/\s*$//; 

$title = $dbh->quote( $cd{title} ); 

$query = $dbh->prepare( "INSERT INTO album ( `a_name` , `a_compilation`, `a_year` ) VALUES ( $title, '$compilation', '$year' )" ); 

$query->execute() || die  $dbh->error;


    $query = $dbh->prepare( "SELECT LAST_INSERT_ID()" ); 
    $query->execute();

    @insert_id  = $query->fetchrow_array;

    return $insert_id[0];

}

##############################################
##
## Check CDID entry
##

sub check_cdid {

my $query;
my $result;
my $row; 
my @album_id;
my $cdid; 

$cdid = $dbh->quote( $cd{id} ); 

$query = $dbh->prepare(  qq( SELECT cdid_album FROM `cdid` WHERE `cdid_id` = "$cdid" ) ); 
$query->execute; 

$row = $dbh->rows; 



@album_id = $query->fetchrow_array;

if( defined( $album_id[0] ) && $album_id[0] gt 0 ){

print "Album ID: " . $album_id[0] . "\n" if( defined( $verbose )); 
print "CD ID: " . $cd{id} . "\n" if( defined( $verbose )); 

    return $album_id[0]; 

}else{

    return -1; 

}
}

##############################################
##
## Create CDID entry
##


sub add_cdid {

my $a_id = $_[0];
my $query;
my $result;

$query = $dbh->prepare( qq( INSERT INTO cdid ( `cdid_id`, `cdid_album` ) VALUES ( "$cd{id}", '$a_id' ) ) ); 
$result = $query->execute() || die $dbh->error; 

return 1;

}


##############################################
##
## Create CD tracks
##

sub tracks {

my $a_id = $_[0];
my $art_id = $_[1];
my $query; 
my $query_str; 

$query = $dbh->prepare( "INSERT INTO title ( `t_title`, `t_album`, `t_number`, `t_time`, `t_artist` ) VALUES ( ?, '$a_id', ?, ?, '$art_id' )" ); 

### Calculate times and get track names

my $n=1;
foreach my $i ( @{$cd{track}} ) {

## Calculate Track times
my $from=$cd{frames}[$n-1];
my $to=$cd{frames}[$n]-1;
my $dur=$to-$from;
my $min=int($dur/75/60);
my $sec=int($dur/75)-$min*60;
  $sec = "0" . $sec if( $sec < 10 );
my $hour = int(00); 

#if( $min gt 60 ){

#    $hour = int(1); 
#    $min = $min - 60; 

#}

$query->execute( $i, $n, $hour . ":" . $min . ":" . $sec ) || die $dbh->error; 

$n++;

}

return 1

}

sub query {

print "Artist: " . $cd{artist} . "\n"; 
print "Title: " . $cd{title} . "\n"; 

 	my $n=1;
 
 	foreach my $i ( @{$cd{track}} ) {
 
 	## Calculate Track times
 	my $from=$cd{frames}[$n-1];
 	my $to=$cd{frames}[$n]-1;
 	my $dur=$to-$from;
 	my $min=int($dur/75/60);
 	my $sec=int($dur/75)-$min*60;
     $sec = "0" . $sec if( $sec < 10 );
 
 
 
 	  print "$n: "; 
      print " " if( $n < 10 ); 
      print "$i ($min:$sec)";
 	  print "\n";
 	  $n++;
 	}

exit(127)
    
}