#!/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);
}