#!/usr/bin/perl

acquire('itunes.conf');
$insert_count = 0;
use DBI;
$dbhi=DBI->connect('dbi:mysql:',$mysqlusername,$mysqlpassword, {RaiseError=>1}) or die "Couldn't connect:".DBI->errstr();
$dbhi->do("use $idbName");
$dbhm=DBI->connect('dbi:mysql:',$mysqlusername,$mysqlpassword, {RaiseError=>1}) or die "Couldn't connect:".DBI->errstr();
$dbhm->do("use $mdbName");
my $ith= $dbhi->prepare("select * from Track")
	or die "Couldn't prepare statement: " . $dbhi->errstr;
$ith->execute()
	or die "Couldn't execute statement: " . $ith->errstr;

while (@trackData = $ith->fetchrow_array()){
	$itunesID = @trackData[0];
	$filename = @trackData[4];
	#we hae to shove a \ in front of the ' things
	$filename =~ s/\'/\\\'/gi;
	$select_statement = "select intid, filename from musicmetadata where (filename = '$filename')";
	my $mth = $dbhm->prepare($select_statement)
		or die "Couldn't prepare statement: " . $dbhm->errstr;
	$mth->execute()
		or die "Couldn't execute statement: " . $mth->errstr;
	#now to update the mythintid field in the Track table
	while (@mythmusicData = $mth->fetchrow_array()){
		$intid = @mythmusicData[0];
		$update_string =
          "UPDATE Track set mythintid = $intid where ID = $itunesID";
        my $update_handle =
          $dbhi->prepare_cached($update_string);
        $update_handle->execute();
	}
}

#now to go through the itunes playlists and copy them to myth
my $ith= $dbhi->prepare("select * from Playlist")
	or die "Couldn't prepare statement: " . $dbhi->errstr;
$ith->execute()
	or die "Couldn't execute statement: " . $ith->errstr;
deleteExistingitunesPlaylists(); #delete existing itunes playlist (they will be recreated)
while (@playlistData = $ith->fetchrow_array()){
	$plID = @playlistData[0];
	$plName = @playlistData[1];
	$plItems = @playlistData[2];
	#prepare a mythplaylist
	$mythpl = '';
	#plItems has a , at the start. Lets loose it
	$plItems = substr($plItems,1);
	$comma_pos = '1';
	while (length($plItems) > 1){
		$i = $i + 1;
		$plItems =~ m/,/g;
		$comma_pos = pos($plItems);
		
		if ($comma_pos == '')
		{
			$song_ID = $plItems;
			$plItems = '';
		} else {
			$song_ID = substr($plItems,0,$comma_pos - 1);
			$plItems = substr($plItems,$comma_pos);
		}
		#$song_ID is the itunes ID. Now to convert it to the MythID
		my $i2th= $dbhi->prepare("select * from Track where (ID = $song_ID)")
			or die "Couldn't prepare statement: " . $dbhi->errstr;
		$i2th->execute()
			or die "Couldn't execute statement: " . $i2th->errstr;
		while (@trackData = $i2th->fetchrow_array()){
			$mythIntID = @trackData[5];
			if ($mythIntID != ''){
				if ($mythpl == ''){
					$mythpl = $mythIntID;
				} else{
					$mythpl = $mythpl . ',' . $mythIntID;
				}
			}
		}
		

	}
	#$mythpl now contains the playlist in myth format. Now to insert a new record into the musicplaylist table
	$insert_string = "insert into musicplaylist (name, hostname,songlist) values ('$plName (i)','$hostname','$mythpl')";
	my $insert_handle = $dbhm->prepare_cached($insert_string);
	$insert_handle->execute();
	$insert_count += 1;
	
	
}
print "Inserted $insert_count playlists.\n";
sub deleteExistingitunesPlaylists
{
	$delete_string = "delete from musicplaylist where (name LIKE '%(i)%')";
	my $delete_handle = $dbhm->prepare_cached($delete_string);
	$delete_handle->execute();
}

sub acquire { 	
	my($file) = @_; 	
	delete($INC{$file}); 	
	eval('require("$file")'); 	
	die "*** Failed to eval() file $file:\n$@\n" if ($@);
}