#!/usr/bin/perl

use strict;
use Getopt::Std;
use IO::File;
use Date::Calc;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::Util qw(decodeDateMysql trimspaces clean normalize_upc normalize_isrc);
use Common::RSDB;
use Common::RSApp;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::XMLObject;
use RPS::DB::Item::Track;
use RPS::DB::Item::Album;
use RPS::DB::Item::Product;
use RPS::DB::Item::Master;
use RPS::DB::Item::Publisher;
use RPS::DB::Item::TrackLicense;


# reporting flags and variable
#
use constant kQuiet => 0;
use constant kNormal => 1;
use constant kDebug => 3;
my $gReportLevel = kNormal;

use constant kProgressQuanta => 200;

# if two tracks on the same album have the same title,
# duration is the only distinguishing property. In this
# case the import data might be slightly different from the
# database data so we'll allow a +/- tolerance in matching.
use constant kDurationTolerance => 5;

# we're skipping some publishers
#   - ABCKO MUSIC INC (P11300)
#   - ABKCO MUSIC INC PETE TOWNSHEND CATALOG (P20061)
my @skipPublishers = qw(P11300 P20061);


# Parse the command-line.
#
my ($clientID, $inputFile) =  parseCommandLine();

$| = 1;

# Instantiate the application singleton object.
#
my $appSingleton = Common::RSApp->new(clientID => $clientID);


# Scan the input file, and build the data structure of updates.
#
my %updates;
scanInputFileForUpdates($inputFile, \%updates);



# Commit any necessary updates.
#
processUpdates(\%updates);


# report unmatched licenses
#
# reportErrors(\%updates);


#
# --- Subroutines
#


sub parseCommandLine
{
    my %opt;
    getopts('c:f:p', \%opt);

    my $inputFile = $opt{f};
    my $clientID = $opt{c};

    unless ($clientID =~ /^\d+$/ && $clientID > 0)
    {
	    die usage("You must specify a valid client ID");
    }

    unless ($inputFile)
    {
	    die usage("You must specify a catalog file to import");
    }

    unless (-e $inputFile)
    {
	    die usage("The specified catalog file does not exist");
    }

    return ($clientID, $inputFile);
}


sub usage
{
	my $errstr = shift;
	my $text = ($errstr) ? "ERROR: $errstr\n" : '';
	$text .= "Usage: $0 -c <clientID> -f <inputFile>\n";
	return $text;
}


sub scanInputFileForUpdates
{
    my ($inputFilePath, $updates) = @_;
    assert($inputFilePath);
    assert($updates);


    # Open the input file for reading.
    #
    my $inputFile = IO::File->new($inputFile) || die "Can't open input file '$inputFile': $!\n";

	readTabInputFile($inputFile, $updates);

    # Done with the input file, close it.
    #
    $inputFile->close();
}


sub readTabInputFile
{
    my ($file, $updates) = @_;
    assert($file);
    assert($updates);

    report("\nReading input file...");

    my $recordCount = 0;
	my $skipCount = 0;

    # Process the file line by line
    #
    while (my $record = <$file>) 
    {
	    my @fields;
	    my $licenseType;
	    my $artistName;
	    my $albumTitle;
	    my $catalogNumber;
	    my $upc;
	    my $isrc;
	    my $songCode;
	    my $duration;
	    my $songTitle;
	    my $dateSent;
	    my $dateReceived;
	    my $topPublisherNumber;
	    my $topPublisherName;
	    my $publisherNumber;
	    my $publisherName;
	    my $publisherShare;
	    my $licenseNumber;
	    my $rateType;
    
        $recordCount++;

        if (0 == $recordCount % kProgressQuanta)
        {
            report("  record $recordCount ... ");
        }

        # Split the line up into fields.
        #
    	chomp $record;
    	@fields = split("\t", $record);
    	@fields = trimquotes(@fields);
    	@fields = escapequotes(@fields);


		# Note - clientAlbumProductID is coming from the 'albumID' field,
		# and clientTrackProductID is coming from the 'trackID' field.
		# I'm not positive that this mapping is correct, or even
		# meaningful, but I do believe that the original values _are_ just
		# client-specified meta-data... so, it may make sense.
		# In any event, we need _something_ to put in the
		# 'product_code' column in the RPS product table, so this is
		# as good as anything...
		#
		(undef,$licenseType,undef,undef,$artistName,$albumTitle,$catalogNumber,$upc,$isrc,undef,undef,$duration,$songTitle,undef,$songCode,$dateSent,$dateReceived,undef,$topPublisherNumber,$topPublisherName,$publisherNumber,$publisherName,$publisherShare,undef,$licenseNumber,undef,$rateType,undef,undef) = undefspaces(@fields);

		# SKIP these publishers:
		#
		if(grep(/^$publisherNumber$/, @skipPublishers) || grep(/^$topPublisherNumber$/, @skipPublishers))
		{
			report("!Warning (#".$recordCount."): Skipping publisher=$publisherName ($publisherNumber)", kDebug);
			$skipCount++;
			next;
		}

		$isrc = normalize_isrc($isrc);

		# trim leading zeros
		$upc = _myNormalizeUPC($upc);
		my $altUPC = _myNormalizeUPC($catalogNumber);
		$publisherShare =~ s/%//;

		# we aren't saving licenses with share of 0.00%.
		#
		if(abs($publisherShare) && ($altUPC || $upc || $isrc) && $songCode && $publisherNumber)
		{
				my $newLicense = {};
				$newLicense->{trackLicenseID}		= undef;
				$newLicense->{lineNum}				= $recordCount;
				$newLicense->{songCode}				= $songCode;
				$newLicense->{licenseType}			= $licenseType;
				$newLicense->{artistName}			= $artistName;
				$newLicense->{albumTitle}			= $albumTitle;
				$newLicense->{catalogNumber}		= $catalogNumber;
				$newLicense->{altUPC}				= $altUPC;
				$newLicense->{upc}					= $upc;
				$newLicense->{isrc}					= $isrc;
				$newLicense->{duration}				= $duration;
				$newLicense->{songTitle}			= $songTitle;
				$newLicense->{dateSent}				= _convertDate($dateSent);
				$newLicense->{dateReceived}			= _convertDate($dateReceived);
				$newLicense->{topPublisherNumber}	= $topPublisherNumber;
                $newLicense->{publisherNumber}		= $publisherNumber;
                $newLicense->{publisherShare}		= $publisherShare;
                $newLicense->{licenseNumber}		= $licenseNumber;
                $newLicense->{rateType}				= $rateType;

				push @{$updates->{licenses}}, $newLicense;

				# -jff- I forget why this line is here
				#
				# report(join('\t', sort keys %$newLicense)) if($recordCount == 2);
		}
		else
		{
			report("!Warning (#".$recordCount."): Incomplete data for song=$songTitle, code=$songCode, publisher#=$publisherNumber");
			$skipCount++;
		}

		# regular publisher
		#
		if($publisherNumber && $publisherName)
		{
			if(!exists($updates->{publishers}->{$publisherNumber}))
			{
				# $updates->{publishers}->{$publisherNumber}->{publisherID} = "";
				# $updates->{publishers}->{$publisherNumber}->{isAdmin} = "";
				# $updates->{publishers}->{$publisherNumber}->{adminNumber} = "";
				$updates->{publishers}->{$publisherNumber}->{publisher_name} = $publisherName;
			}
		}
		else
		{
			report("!Warning (#".$recordCount."): Incomplete publisher", kDebug);
		}

		# top (admin) publisher
		#
		if($topPublisherNumber && $topPublisherName)
		{
			if(!exists($updates->{publishers}->{$topPublisherNumber}))
			{
				$updates->{publishers}->{$topPublisherNumber}->{publisher_name} = $topPublisherName;
			}

			if($publisherName && ($publisherName ne $topPublisherName))
			{
				# mark this publisher as an admin
				#
				$updates->{publishers}->{$topPublisherNumber}->{isAdmin} = 1;

				# set the regular publisher's admin as this publisher
				#
				$updates->{publishers}->{$publisherNumber}->{adminNumber} = $topPublisherNumber;
			}
		}
		else
		{
			report("!Warning (#".$recordCount."): Incomplete top publisher", kDebug);
		}

	}
    report("  Records processed : $recordCount");
    report("  Records skipped   : $skipCount");
}


sub processUpdates
{
    my ($updates) = @_;

	my $publishers = $updates->{publishers};
	# Create publisher entries
	#
	report("\nCreating Publishers...");
	my $count = 0;

	# we need to create an entry for Harry Fox first
	#
	# my $hfa = RPS::DB::Item::Publisher->Create(publisher_name => "HARRY FOX AGENCY",
											   # is_agency => 1,
											   # is_admin => 0);
	# $hfa->save();
	# my $hfaID = $hfa->publisher_id;

	# HFA has publisher_id=1, we don't need to create it again. -jff-
	#
	my $hfaID = 1;

	my $pubCreateCount = 0;
	my $pubMatchCount = 0;
	foreach my $publisherNumber (sort { $publishers->{$b}->{isAdmin} <=> $publishers->{$a}->{isAdmin} } keys %$publishers)
	{
		$count++;
		if (0 == $count % kProgressQuanta)
		{
			report("  publisher $count ...");
		}

		my $publisher = $publishers->{$publisherNumber};

		# have we already created/matched this publisher?
		#
		if(! $publisher->{publisherID})
		{
			my $publisherID;
			$publisherID = lookupPublisher($publisher->{publisher_name});
			if($publisherID)
			{
				$publisher->{publisherID} = $publisherID;
				$pubMatchCount++;
				next;
			}

			my %pubParams =
			(
				publisher_name => $publisher->{publisher_name},
				is_agency => 0,
				is_admin => 0,
				agent_id => $hfaID,
			);

			if($publisher->{adminNumber})
			{
				# does this publisher have an admin?
				#
				$pubParams{admin_id} = $publishers->{$publisher->{adminNumber}}->{publisherID};
			}
			elsif($publisher->{isAdmin} == 1)
			{
				# is this an admin?
				#
				$pubParams{is_admin} = 1;
			}

			# report("Creating publisher: ".Dumper(\%pubParams), kDebug);

			my $dbItem = RPS::DB::Item::Publisher->Create(%pubParams);
			$dbItem->save();
			$publisher->{publisherID} = $dbItem->publisher_id;

			$pubCreateCount++;
		}
	} # END - publishers loop

	report("  Publishers created : $pubCreateCount");
	report("  Publishers matched : $pubMatchCount");

    # Create license entries
    #
	my $tracks = {};
    my $licenses = $updates->{licenses};
	my %seenLicense = ();

	report("\nCreating Licenses...");

	my $matchCount = 0;
    $count = 0;
    foreach my $license (@$licenses)
    {
        $count++;
        if (0 == $count % kProgressQuanta)
        {
            report("  license $count ...");
        }

		# did we already create this license?
		#

		if(!$license->{trackLicenseID})
		{
			#
			# We need to identify this track before we can assign
			# a license to it.
			#
			my $trackID;
			my $trackKey = join('-', $license->{albumTitle},
								$license->{upc},
								$license->{songCode},
								$license->{songTitle},
								$license->{isrc},
								$license->{duration},
								);

			if(!exists($tracks->{$trackKey}))
			{
				$trackID = _deriveTrackIDFromLicense($license);
			}
			else
			{
				$trackID = $tracks->{$trackKey};
				report("TrackID already found for trackKey = $trackKey", kDebug);
			}
			
			if($trackID)
			{
				report("Song matched!", kDebug);

				# store this for future reference
				#
				$tracks->{$trackKey} = $trackID;

				my %licenseParams = 
				(
					track_id => $trackID,
					date_sent => $license->{dateSent},
					date_received => $license->{dateReceived},
					date_issued => $license->{dateReceived},
					issuer_license_id => $license->{licenseNumber},
					type => 1,
					rate_basis => 1,
					percentage_of_sales => 100,
				);

				my $productType;
				if($license->{licenseType} eq 'DIGITAL')
				{
					$licenseParams{product_type_id} = 3;
				}

				my $share;
				if($license->{publisherShare})
				{
					$licenseParams{share} = $license->{publisherShare};
				}
				else
				{
					report("!Warning (#".$license->{lineNum}."): missing share for song = ".$license->{songTitle});
					next;
				}

				my $publisherID;
				my $pubNum = $license->{publisherNumber};
				if($pubNum && $publishers->{$pubNum} && $publishers->{$pubNum}->{publisherID})
				{
					$licenseParams{publisher_id} = $publishers->{$pubNum}->{publisherID};
				}
				else
				{
					report("!Warning (#".$license->{lineNum}."): missing publisher for song = ".$license->{songTitle});
					next;
				}

				my $rateType;
				if($license->{rateType} =~ /min(imum)?/i)
				{
					$licenseParams{rate_type} = 2;
				}
				elsif($license->{rateType} =~ /stat(utory)?/i)
				{
					$licenseParams{rate_type} = 1;
				}

				# let's make sure we didn't already import this license
				#
				my $licenseKey = join('-', $trackID, $licenseParams{publisher_id}, $licenseParams{share});
				if(!$seenLicense{$licenseKey})
				{
					$seenLicense{$licenseKey} = 1;

					# report("Creating track_license with: ".Dumper(\%licenseParams), kDebug);

					my $dbItem = RPS::DB::Item::TrackLicense->Create(%licenseParams);
					$dbItem->save();
					$license->{trackLicenseID} = $dbItem->track_license_id;
					$matchCount++;
				}
				else
				{
					report("!Warning (#".$license->{lineNum}."): License already created: song = ".$license->{songTitle});
				}
			}
			else
			{
				# ok, we want to output the whole line here
				my @values;
				foreach my $key (sort keys %$license)
				{
					push @values, $license->{$key};
				}

				# report(join('\t', @values));
				report("!Warning (#".$license->{lineNum}."): Song not found: song = ".$license->{songTitle});
			}
		}
		else
		{
			report("!Warning (#".$license->{lineNum}."): I guess we already processed this license?");
		}

	} # END - licenses loop

    report("  Total licenses processed : $count");
    report("  Licenses created         : $matchCount");
}

sub _deriveTrackIDFromLicense
{
	my $license = shift;

	my $trackID;

	# The Sanctuary matching rule is this:
	# 1. We must be able to match the catalog number or UPC to a release in our catalog.
	#
	if($license->{catalogNumber} || $license->{upc})
	{
		$trackID = _getTrackIDFromCatalogNumber($license);
	}

	if(!$trackID && $license->{upc} || $license->{altUPC})
	{
		$trackID = _getTrackIDFromUPC($license);
	}

	# other lookups are not reliable, so we won't even bother
	#
	return $trackID;

	# let's try the isrc
	#
#	if($license->{isrc})
#	{
#		my $masterColl = RPS::DB::Item::Master->GetByISRC($license->{isrc});
#		if($masterColl->size == 1)
#		{
#			my $masterItem = $masterColl->next();
#			my $trackColl = RPS::DB::Item::Track::GetTracksByMasterID($masterItem->master_id);
#
#			if($trackColl->hasNext())
#			{
#				if($trackColl->size == 1)
#				{
#					my $trackItem = $trackColl->next();
#					$trackID = $trackItem->track_id;
#					report("TrackID found from isrc = ".$license->{isrc}, kDebug);
#				}
#				else
#				{
#					report("!Warning: more than one track for master_id = ".$masterItem->master_id, kDebug);
#				}
#			}
#			else
#			{
#				report("!Warning: no tracks found for master_id = ".$masterItem->master_id, kDebug);
#			}
#		}
#		else
#		{
#			report("!Warning: more than one master for isrc = ".$license->{isrc}, kDebug);
#		}
#	}
#
#	if(!$trackID && $license->{upc})
#	{
#		$trackID = _getTrackIDFromUPC($license->{upc}, $license);
#	}
#
#	if(!$trackID && $license->{altUPC} && $license->{altUPC} ne $license->{upc})
#	{
#		$trackID = _getTrackIDFromUPC($license->{altUPC}, $license);
#	}

}


sub lookupPublisher
{
	my ($name) = @_;

	my $publisherID;

	# Does this publisher already exist?
	#
	my $coll = RPS::DB::Item::Publisher->Match($name);
	if(defined $coll && $coll->size == 1)
	{
		my $dbItem = $coll->next();
		$publisherID = $dbItem->publisher_id;
	}

	return $publisherID;
}

sub reportErrors
{
    my ($updates) = @_;

    report("Reporting errors...");

    my $count = 0;
    my $licenses = $updates->{licenses};

	my $header;
	my @fields;
	foreach my $license (@$licenses)
	{
		next if($license->{trackLicenseID} > 0);

		if(!$header)
		{
			@fields = sort keys %$license;
			$header = join('\t', @fields);
			print $header . "\n";
		}

		my @values;
		foreach my $key (@fields)
		{
			push @values, $license->{$key};
		}
		print join('\t', @values) . "\n";
	}
}

sub _normalizeCatalogNumber
{
	my $catalogNumber = shift;

	$catalogNumber =~ s/\D//g;
	if(length($catalogNumber) >= 10)
	{
		$catalogNumber = substr($catalogNumber, 5, 10);
		if(length($catalogNumber) == 5)
		{
			$catalogNumber .= "-2";
		}
		else
		{
			$catalogNumber =~ s/2$/-2/;
		}
		return $catalogNumber;
	}
	else
	{
		return undef;
	}
}

sub _getTrackIDFromCatalogNumber
{
	my ($license) = @_;

	my $trackID;
	my $matchedTracks;

	# the order here is important, we always want to use the catalogNumber if we can.
	#
	my $catalogNumber = _normalizeCatalogNumber($license->{catalogNumber}) || _normalizeCatalogNumber($license->{upc});
	if($catalogNumber)
	{
		report("Looking up CatalogNumber = $catalogNumber", kDebug);
		my $coll = RPS::DB::Item::Album->GetByCatalogNumber($catalogNumber);

		if($coll->hasNext())
		{
			my $albumItem = $coll->next();

			$matchedTracks = MatchTracksFromAlbumID($albumItem->album_id, $license);
			$trackID = FindTrackFromMatchedTracks($matchedTracks, $license);
		}
		else
		{
			report("!Warning: CatalogNumber not found", kDebug);
		}
	}

	return $trackID;
}


sub _getTrackIDFromUPC
{
	my ($license) = @_;

	my $trackID;
	my $upc = $license->{upc} || $license->{altUPC};

	if($upc)
	{
		report("Looking up UPC = $upc", kDebug);
		my $productColl = RPS::DB::Item::Product->GetByUPC($upc);
		if($productColl->hasNext())
		{
			my $prodItem = $productColl->next();
			my $matchedTracks = MatchTracksFromAlbumID($prodItem->album_id, $license);
			$trackID = FindTrackFromMatchedTracks($matchedTracks, $license);
		}
		else
		{
			report("!Warning: UPC not found", kDebug);
		}
	}

	return $trackID;
}

sub FindTrackFromMatchedTracks
{
	my ($matchedTracks, $license) = @_;

	my $trackID;

	if(@$matchedTracks == 1)
	{
		$trackID = $matchedTracks->[0]->track_id;
	}
	elsif(@$matchedTracks > 1)
	{
		report("Checking duration...", kDebug);
		foreach my $track (@$matchedTracks)
		{
			# let's compare duration
			#
			my $masterItem = RPS::DB::Item::Master->Lookup(master_id => $track->master_id);
			my $duration = RPS::XMLObject::_MMSSToSeconds($license->{duration});

			if($duration == $masterItem->duration || abs($duration - $masterItem->duration) <= kDurationTolerance)
			{
				$trackID = $track->track_id;
			}
		}

		if(!$trackID)
		{
			report("!Warning (#".$license->{lineNum}."): duplicate song, no match on duration = ".$license->{songTitle}, kDebug);
		}
	}
	
	return $trackID;
}


sub MatchTracksFromAlbumID
{
	my ($albumID, $license) = @_;

	my @matchedTracks = ();

	# normalize the song title we are trying to match
	#
	my $licenseTrack = lc($license->{songTitle});
	$licenseTrack =~ s/\s+//g;
	my $licenseTrackClean = clean($licenseTrack);

	my $trackColl = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
	while($trackColl->hasNext())
	{
		my $trackItem = $trackColl->next();

		if ($trackItem->title)
		{
			# normalize the song title from the database
			#
			my $prodTrack = lc($trackItem->title);
			$prodTrack =~ s/\s+//g;
			my $prodTrackClean = clean($prodTrack);
			$prodTrackClean =~ s/_//g;

			# match a short title
			if (length($prodTrack) < 3)
			{
				if ($licenseTrack eq $prodTrack || $licenseTrackClean eq $prodTrackClean)
				{
					push @matchedTracks, $trackItem;
					# $trackID = $trackItem->track_id;
				}
			}
			# does the dbTrack contain the title? or the other way around?
			# This works because we've already narrowed it down to the 
			# correct album.
			elsif ($licenseTrack eq $prodTrack ||
				   index($licenseTrack, $prodTrack) > -1 ||
				   index($prodTrack, $licenseTrack) > -1 ||
				   $licenseTrackClean eq $prodTrackClean ||
				   index($licenseTrackClean, $prodTrackClean) > -1 ||
				   index($prodTrackClean, $licenseTrackClean) > -1
				   )
			{
				push @matchedTracks, $trackItem;
				# $trackID = $trackItem->track_id;
			}
		}
	}

	return \@matchedTracks;
}


sub escapequotes
{
	my @string = @_;

	for (@string)
	{
		s/\"\"/"/g;
	}

	return wantarray ? @string : $string[0];
}

sub trimquotes
{
	my @string = trimspaces(@_);

	for (@string)
	{
		if (m/^\".*\"$/)
		{
			s/^\"//;
			s/\"$//;
		}
	}

	return wantarray ? @string : $string[0];
}

sub undefspaces
{
	my @string = trimspaces(@_);

	for (@string)
	{
		if ($_ eq '')
		{
			$_ = undef;
		}
	}

	return wantarray ? @string : $string[0];
}

sub report
{
    my ($text, $level) = @_;

    $level = kNormal unless $level;

    if ($level <= $gReportLevel)
    {
        print $text . "\n";
    }
}

sub _convertDate
{
	my $date = shift;
	my $newValue;

	my ($year, $month, $day);
	if ((($year, $month, $day) = Date::Calc::Decode_Date_US($date))
	 || (($year, $month, $day) = Date::Calc::Decode_Date_EU($date))
	 || (($year, $month, $day) = decodeDateMysql($date)))
	{
		$newValue = sprintf("%4d-%02d-%02d", $year, $month, $day);
	}

	return $newValue;
}

sub _myNormalizeUPC
{
	my $upc = shift;

	if(length($upc) > 10)
	{
		$upc =~ s/^0+//;
	}

	if(length($upc) == 10)
	{
		# assume it needs the "format" digit on the end (2)
		$upc = $upc."2";
	}

	return normalize_upc($upc);
}