#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect('DBI:mysql:obs','obs')
    or die "Couldn't connect to database: " . DBI->errstr;

my ($pathname, $filename, $artist, $sortname, $album, $track, $tracknum, $modtime, $tmp);
my @values;


my $trans = 0;

while (<>) {

    $pathname = $_; #this is our pathname 
    chomp($pathname); #can't hurt
    @values = split m#/#, $pathname; #split at each slash
    $filename = $values[$#values]; #get the last value
    ($sortname, $tmp) = split / - /, $filename, 2; #get the sortname
    ($tracknum, $track) = split / /, $tmp, 2; #get the track number and track name
    $artist = $sortname; #check for comma and the to re-order for artist
    if ($artist =~ /, the/) {
	$artist =~ s/^/the /;
	$artist =~ s/,.*//;
    }
    $track =~ s#.mp3##; #soundtracks don't have artist!
    if ($values[$#values-2] eq 'soundtracks') {
	$album = $values[$#values-1];
    } else {
	($tmp, $album) = split / - /, $values[$#values-1], 2;
    }
    chomp($artist);
    chomp($sortname);
    chomp($sortname);
    chomp($album);
    chomp($track);
    if (!defined $artist || !defined $sortname || !defined $album || !defined $track || !defined $pathname) {
	print "there's an error in one of the files in the \usr\local\share\mp3 path:\n";
	print "artist: $artist\n";
	print "display: $sortname\n";
	print "album: $album\n";
	print "track: $track\n";
	print "pathname: $pathname\n";
	exit (0);
    }
    $pathname =~ s#^#obsvol://hdvol01:#; #prepend url values
    ($sortname, $album, $track) = check_caps ($sortname, $album, $track); #capitalize for nice display
    $artist = $sortname; #redundant loop can probably get rid of one of these
    if ($artist =~ /, The/) {
	$artist =~ s/^/The /;
	$artist =~ s#, The##;
    }
    $trans = add_record ($artist, $sortname, $album, $tracknum, $track, $modtime, $pathname, $trans); #do the add
}


sub check_caps {
    my @tmp = @_;
    my @lcwords = qw(Of The A And With);
    my $lcwords;
   
    foreach $_ (@tmp) {
    $_ =~ s/ ((^\w)|(\s\w))/\U$1/xg;
    $_ =~ s/([\w']+)/\u\L$1/g;
    foreach $lcwords (@lcwords) {
        $_ =~ s/(\s$lcwords\s)/\u\L$1/g; 
    }
    $_ =~ s/(^\w)/\U$1/xg;
    $_ =~ s/(,\s\w)/\U$1/xg;
    $_ =~ s/(-\s\w)/\U$1/xg;
    }
    return @tmp;
}

sub add_record {
    my ($artist, $sortname, $album, $tracknum, $track, $modtime, $pathname, $update) = @_;
    my ($rows);
    

    my $sth1 = $dbh->prepare('SELECT id from Artist where name = ?')
	or die "Couldn't prepare statement: " . $dbh->errstr;
    my $sth2 = $dbh->prepare('SELECT id from Album where name = ?')
	or die "Couldn't prepare statement: " . $dbh->errstr;
    my $sth3 = $dbh->prepare('SELECT id from Track where name = ? and album = ?  and artist = ?')
	or die "Couldn't prepare statement: " . $dbh->errstr;

    $sth1->execute($artist); 
    my $artistid = $sth1->fetchrow_array();

    if ($sth1->rows == 0) {
	my $artistload = $dbh->prepare('INSERT INTO Artist (Name, Sortname) values (?, ?)');
	$artistload->execute($artist, $sortname);
	print "added new artist: $artist\n";
	$update++;
	$sth1->execute($artist);
	$artistid = $sth1->fetchrow_array();
    }
  
    $sth2->execute($album);
    my $albumid = $sth2->fetchrow_array();

    if ($sth2->rows == 0) {
	my $albumload = $dbh->prepare('INSERT INTO Album (Name, Artist) values (?, ?)');
	$albumload->execute($album, $artistid);
	$sth2->execute($album);
	print "added new album: $album\n";
	$update++;
	$albumid = $sth2->fetchrow_array();
    }


    $sth3->execute($track, $albumid, $artistid);
    my $trackid = $sth3->fetchrow_array();

    if ($sth3->rows == 0) {
	my $trackload = $dbh->prepare('INSERT INTO Track (Name, Artist, Album, TrackNum, Url, LastChanged) values (?, ?, ?, ?, ?, Now())');
	$trackload->execute($track, $artistid, $albumid, $tracknum, $pathname);
	$sth3->execute($track, $albumid, $artistid);
	print "added new track: $artist -  $album -  $tracknum $track\n";
	$update++;
	$trackid = $sth3->fetchrow_array();
    }

    return ($update);
}


if ($trans > 0) {
    print "$trans total updates\n";
}