#!/usr/bin/perl

# This script imports freedb files into a mysql database.
# Start this in the directory containing the freedb directories.
# Copyright (C) 2002 Lars Bohn

# 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 Cwd;
use DBD::mysql;

$database = 'freedb';

%dirnames = qw(blues 1 classical 2 country 3 data 4 folk 5 jazz 6 misc 7 newage 8 reggae 9 rock 10 soundtrack 11);

# Find all cddb-directories in current directory.
@dirlist = ();
foreach $i (keys(%dirnames)) {
	if (-d $i) {
		if ($first == undef) {$first = 1; print "Found";}
		print" $i";
		push(@dirlist,$i);
	}
}
print ".\n";

if (@dirlist == 0) {
	die "No cddb directories in current directory. Exiting.\n";
}

$dirname = "classical";
$dir_id = 2;

# Connect to the db. $database should be defined.
# $hostname and $port default to localhost and 3306.
# $user defaults to DBI_USER and $pass to DBI_PASS.

$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port", $user, $password);

# Lock the tables for faster import (still have to test this).
#my $statement = "LOCK TABLES album WRITE, artist WRITE, firstline WRITE, genre WRITE, processedbyline WRITE, song WRITE, submittedvialine WRITE";
	
#$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
#$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
#$sth->finish;

$dir = cwd();
foreach $i (@dirlist) {
	chdir("$i") or die "Can't cd into $i...\n";
	@allfiles = glob("*");
	$numfiles = @allfiles;
	print "Processing $i. Found $numfiles files...\n";
	foreach $thefile (@allfiles) {
		parse_file($thefile, $i, $dirnames{$i});
	}
	chdir("$dir") or die "Could not get back into parent...\n";
}
print"Done.\n";

# Unlock the album, song table.
#my $statement = "UNLOCK TABLES";

#$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
#$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
#$sth->finish;

$rc = $dbh->disconnect;

sub parse_file {
	my($filename, $dirname, $dirindex) = @_; #the dirname is the dirname stored in the db.
	
	open (THEFILE, $filename);

	#print "Processing $filename.\n";

	# Handle firstline. -------------------------------------------------
	# Get the first line, see if it already exists in the db, add if it does not.
	# Save the index of the firstline in $firstlineindex.
	my $firstlineindex = 0;
	my $firstline = <THEFILE>;
	chomp($firstline);

	my $sql = $dbh->quote($firstline);
	
	my $statement = "SELECT id from firstline where firstlinetext = $sql";
	
	$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
	$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
	
	if ($rv == 0) {
		# Not in the db. Add entry...

		if ((length $firstline) > 255 ) {
			printf "WARNING: In $dirname/$filename: Firstline is longer than 255! Will not fit.\n";
		}

		$statement = "INSERT INTO firstline VALUES(0, $sql)";
		$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
		
		# This did not work.
		#$firstlineindex = $sth->{insertid};

		# But this did.
		$firstlineindex = $dbh->{'mysql_insertid'};

	} else {
		# Already in the db. Get the index.
		my @row = $sth->fetchrow_array;
		$firstlineindex = $row[0];
		$sth->finish;
	}
	# Completed firstline. ----------------------------------------------

	# Handle Track frame offsets. ---------------------------------------
	my $theoffsets = <THEFILE>;
	chomp($theoffsets);
	
	# Find the occurrence.
	while ( !($theoffsets =~ /^# Track frame offsets:/) ) {
		$theoffsets = <THEFILE>;
		chomp($theoffsets);
	}
	# Track offsets start in the next line, so read again.
	$theoffsets = <THEFILE>;
	chomp($theoffsets);
	# No get only the number in the line.
	$theoffsets =~ s/\D*(\d+)\D*/$1/;

	# Read all offset entries. This stops at the "one blank comment line"
	# at the end of the list of offsets, or if the line contains letters.
	my $aline = <THEFILE>;
	chomp($aline);
	while ($aline =~ /^#[ ]+\d+[ ]*$/ ) {
		$aline =~ s/\D*(\d+)\D*/$1/;
		$theoffsets = $theoffsets . "," . $aline;
		$aline = <THEFILE>;
		chomp($aline);
	}

	# Completed Track frame offsets. ....................................

	# Handle Disc length. -----------------------------------------------

	# Find the occurrence.
	while ( !($aline =~ /^# Disc length:/) ) {
		$aline = <THEFILE>;
		chomp($aline);
	}

	my $thedisclength = $aline;
	$thedisclength =~ s/\D*(\d+)\D*/$1/;

	# Completed Disc length. ............................................

	# Handle Revision, SubmittedVia, and ProcessedBy. -------------------
	# The correct order is
	# Revision
	# Processed by
	# Submitted via
	# Unfortunately, a lot of entries have
	# Submitted via
	# Revision
	# Processed by.
	
	my $foundrev;
	my $foundsub;
	my $foundpro;
	my $submittedViaLineIndex = 0;
	my $processedByLineIndex = 0;
	my $therevision;

	while (!($foundrev && $foundsub && $foundpro)) {
		# Find the occurrence.
		while ( !(($aline =~ /^# Revision:/) || ($aline =~ /^# Submitted via:/) ||
						($aline =~ /^# Processed by:/))) {
			$aline = <THEFILE>;
			chomp($aline);
		}
		if ($aline =~ /^# Revision:/) {
			$foundrev = true;
			# Handle Revision. --------------------------------------------------
			
			$therevision = $aline;
			$therevision =~ s/\D*(\d+)\D*/$1/;
		
			#print "Revision: $therevision\n";
		
			# Completed Revision. ...............................................
			$aline = <THEFILE>;
			chomp($aline);
			next; # Jumps to while.
		}
		if ($aline =~ /^# Submitted via:/) {
			$foundsub = true;
			# Handle Submitted via line. ----------------------------------------
			# Get the line, see if it already exists in the db, add if it does not.
			# Save the index of the Submitted via line in $submittedViaLineIndex.
		
			my $sql = $dbh->quote($aline);
			
			my $statement = "SELECT id from submittedvialine where submittedvialinetext = $sql";
			
			$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
			$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
			
			if ($rv == 0) {
				# Not in the db. Add entry...
				if ((length $aline) > 255 ) {
					printf "WARNING: In $dirname/$filename: SubmittedVia is longer than 255! Will not fit.\n";
				}
				$statement = "INSERT INTO submittedvialine VALUES(0, $sql)";
				$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
				
				# This did not work.
				#$submittedViaLineIndex = $sth->{insertid};
		
				# But this did.
				$submittedViaLineIndex = $dbh->{'mysql_insertid'};
		
				#print "Added a submittedViaLine with index $submittedViaLineIndex\n";
			} else {
				# Already in the db. Get the index.
				my @row = $sth->fetchrow_array;
				$submittedViaLineIndex = $row[0];
				#print "Got a submittedViaLineIndex $submittedViaLineIndex\n";
				$sth->finish;
			}
			#print"Submitted: $aline\n";
			# Completed Submitted via line. .....................................
			$aline = <THEFILE>;
			chomp($aline);
			next; # Jumps to while.
		}
		if ($aline =~ /^# Processed by:/) {
			$foundpro = true;
			# Handle Processed by line. -----------------------------------------
			# Get the line, see if it already exists in the db, add if it does not.
			# Save the index of the processed by line in $processedByLineIndex.
		
			my $sql = $dbh->quote($aline);
			
			my $statement = "SELECT id from processedbyline where processedbylinetext = $sql";
			
			$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
			$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
			
			if ($rv == 0) {
				# Not in the db. Add entry...
				if ((length $aline) > 255 ) {
					printf "WARNING: In $dirname/$filename: ProcessedBy is longer than 255! Will not fit.\n";
				}

				$statement = "INSERT INTO processedbyline VALUES(0, $sql)";
				$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
				
				# This did not work.
				#$processedByLineIndex = $sth->{insertid};
		
				# But this did.
				$processedByLineIndex = $dbh->{'mysql_insertid'};
		
				#print "Added a processedByLine with index $processedByLineIndex\n";
			} else {
				# Already in the db. Get the index.
				my @row = $sth->fetchrow_array;
				$processedByLineIndex = $row[0];
				#print "Got a processedByLineIndex $processedByLineIndex\n";
				$sth->finish;
			}
			#print"Processed: $aline\n";
			# Completed Processed by line. ......................................

			$aline = <THEFILE>;
			chomp($aline);
			next; # Jumps to while.
		}
	}
	#print "Rev: $therevision, Submitted: $submittedViaLineIndex,\n Processed: $processedByLineIndex.\n";
	# Completed Revision, SubmittedVia, and ProcessedBy. ................

	# Handle DISCID. ----------------------------------------------------
	# Find first occurrence of DISCID.
	while ( !($aline =~ /^DISCID=/) ) {
		$aline = <THEFILE>;
		chomp($aline);
	}
	# Remove a trailing "," if present.
	$aline =~ s/,$//;
	my $thediscid = $aline;
	$thediscid =~ s/^DISCID=//;
	# See if next lines are DISCID too. Concat if necessary.
	$aline = <THEFILE>;
	chomp ($aline);
	
	while ($aline =~ /^DISCID=/) {
		$aline =~ s/^DISCID=//;
		# Remove a trailing "," if present.
		$aline =~ s/,$//;
		$thediscid = $thediscid . "," . $aline;
		$aline = <THEFILE>;
		chomp ($aline);
	}
	if ((length $thediscid) > 255 ) {
		printf "WARNING: In $dirname/$filename: Discids is longer than 255! Will not fit.\n";
	}

	# Completed DISCID. .................................................

	# Handle DTITLE. ----------------------------------------------------
	# Find DTITLE.
	my $thetitle = $aline;
	
	# Find first occurrence of DTITLE.
	while ( !($thetitle =~ /^DTITLE=/) ) {
		$thetitle = <THEFILE>;
		chomp($thetitle);
	}
	$thetitle =~ s/^DTITLE=//;
	# See if next lines are DTITLE too. Concat if necessary.
	$aline = <THEFILE>;
	chomp ($aline);
	
	while ($aline =~ /^DTITLE=/) {
		$aline =~ s/^DTITLE=//;
		$thetitle = $thetitle . $aline;
		$aline = <THEFILE>;
		chomp ($aline);
	}
	
	my @fields = split(/ \/ /, $thetitle);
	my $theartist = $fields[0];
	$thetitle = $fields[1];
	if ($thetitle eq "") {
#		print"ERROR\n";
		$thetitle = $theartist;
	}

	# Add the artist. ----------
	$sql = $dbh->quote($theartist);
	
	$statement = "SELECT id from artist where name = $sql";
	
	$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
	$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
	
	$artistindex = 0;
	if ($rv == 0) {
		# Not in the db. Add entry...
		$statement = "INSERT INTO artist VALUES(0, $sql)";
		$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
		
		# This did not work.
		#$artistindex = $sth->{insertid};

		# But this did.
		$artistindex = $dbh->{'mysql_insertid'};

#		print "Added an artist with index $artistindex\n";
	} else {
		# Already in the db. Get the index.
		my @row = $sth->fetchrow_array;
		$artistindex = $row[0];
#		print "Got a artistindex $artistindex\n";
		$sth->finish;
	}
	# End adding the artist. ----------

#	print "artist: $theartist\n";
#	print "title: $thetitle\n";

	# Handle DYEAR. --------------------------------------------------
	
	# Find the occurrence.
	while ( !($aline =~ /^DYEAR=/) ) {
		$aline = <THEFILE>;
		chomp($aline);
	}

	my $theyear = $aline;
	$theyear =~ s/\D*(\d+)\D*/$1/;
	if ($theyear eq "DYEAR=") { $theyear = 0; }

#	print "Year: $theyear\n";

	# Completed DYEAR ...............................................

	# Handle DGENRE. -----------------------------------------
	# Get the line, see if it already exists in the db, add if it does not.
	# Save the index of the processed by line in $genreIndex.
	my $genreIndex = 0;

	# Find the occurrence.
	while ( !($aline =~ /^DGENRE=/) ) {
		$aline = <THEFILE>;
		chomp($aline);
	}

	$aline =~s/^DGENRE=//;

	my $sql = $dbh->quote($aline);
	
	$statement = "SELECT id from genre where genretext = $sql";
	
	$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
	$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";

	if ($rv == 0) {
		# Not in the db. Add entry...
		if ((length $aline) > 255 ) {
			printf "WARNING: In $dirname/$filename: Genretext is longer than 255! Will not fit.\n";
		}
		$statement = "INSERT INTO genre VALUES(0, $sql)";
		$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
		
		# This did not work.
		#$genreIndex = $sth->{insertid};

		# But this did.
		$genreIndex = $dbh->{'mysql_insertid'};

#		print "Added a genre with index $genreIndex\n";
	} else {
		# Already in the db. Get the index.
		my @row = $sth->fetchrow_array;
		$genreIndex = $row[0];
#		print "Got a genreIndex $genreIndex\n";
		$sth->finish;
	}
	# Completed DGENRE. ......................................

# Add the album. -----------------------------------------------

	# Just insert the album. Assume the db is empty.
	my $theAlbumIndex = 0;

#	$statement = "SELECT id from album where filename = '$filename' and filedir_id = $dirindex";

#	$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
#	$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";

#	if ($rv == 0) {
		# Not in the db. Add entry...
		my $sql_title = $dbh->quote($thetitle);
		my $sql_discids = $dbh->quote($thediscid);
		my $sql_offsets = $dbh->quote($theoffsets);
		
		$statement = "INSERT INTO album VALUES(0, $artistindex, $sql_title, $dirindex, '$filename',
											$sql_discids, $thedisclength, $sql_offsets, $theyear,
											$genreIndex, $therevision, $firstlineindex,
											$processedByLineIndex, $submittedViaLineIndex)";
		$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
		
		# This did not work.
		#$theAlbumIndex = $sth->{insertid};

		# But this did.
		$theAlbumIndex = $dbh->{'mysql_insertid'};

#		print "Added an album with index $theAlbumIndex\n";
#	} else {
#		printf"WARNING: Would overwrite file $dirname / $filename in db.\n";
#		# Already in the db. Get the index.
#		my @row = $sth->fetchrow_array;
#		$theAlbumIndex = $row[0];
#		print "Got a theAlbumIndex $theAlbumIndex\n";
#		$sth->finish;
#	}

	# Completed adding the album. ...............................................

	# Handle TTITLEs. --------------------------------------------------
	
	$aline = <THEFILE>;
	chomp ($aline);

	my $numtitle = 0;
	my $atitle = "";
	my $anartist = "";
	my $theInsertString = "INSERT DELAYED INTO song VALUES "; # delayed works here.
	
	while ($aline =~ /^TTITLE/) { # while in the TTITLE block.
		while ($aline =~ /^TTITLE$numtitle=/) { # while in the same title.
			$aline =~ s/^TTITLE$numtitle=//;
			$atitle = $atitle . $aline;
			$aline = <THEFILE>;
			chomp ($aline);
		}
		if ((length $atitle) == 0) {
			#print "ERROR 10: in $dirname/$filename empty title: $numtitle.\n";
			$atitle = "";
		}

		# We have a title. Does it contain " / "?
		if ($atitle =~ / \/ /) { # We have an artist / title-combination. Split.
			my @fields = split(/ \/ /, $atitle);
			$anartist = $fields[0];
			$atitle = $fields[1];
			if ((length $anartist) == 0) {
				$anartist = "";
			}
			if ((length $atitle) == 0) {
				#print "WARNING: in $dirname/$filename empty title at : $numtitle\n";
				#print "         artist: $anartist\n";
				$atitle = "";
			}

			# Add the artist. ----------
			$sql = $dbh->quote($anartist);
			
			$statement = "SELECT id from artist where name = $sql";

			$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
			$rv = $sth->execute or die "can't execute the query: $sth->errstr\n";
			
			$artistindex = 0;
			if ($rv == 0) {
				# Not in the db. Add entry...
				$statement = "INSERT INTO artist VALUES(0, $sql)";
				$sth = $dbh->do($statement) or die "Can't do $statement: $dbh->errstr\n";
				
				# This did not work.
				#$artistindex = $sth->{insertid};
		
				# But this did.
				$artistindex = $dbh->{'mysql_insertid'};
		
		#		print "Added an artist with index $artistindex\n";
			} else {
				# Already in the db. Get the index.
				my @row = $sth->fetchrow_array;
				$artistindex = $row[0];
		#		print "Got a artistindex $artistindex\n";
				$sth->finish;
			}
			# End adding the artist. ----------

		} # ^ This block may have modified $atitle!!!
		
		# Now add the song to the db.
		# Add the song. -----------------------------------------------
		
		my $sql_title = $dbh->quote($atitle);

		$theInsertString = $theInsertString . "(0, $artistindex,  $theAlbumIndex, $sql_title, $numtitle),";
	
		# Completed adding the song. ...............................................
		
		# Continue to next title.
		$numtitle++;
		$atitle = "";
		$anartist = "";
	} # while TTITLE block
	
	# Now add all songs.
	# chop the last "," away...
	chop($theInsertString);
	
	$sth = $dbh->do($theInsertString) or die "Can't do $theInsertString: $dbh->errstr\n";
	
	# Completed TTITLEs. ......................................

	close(THEFILE) or die "Could not close a file: $!";
}