#!/usr/bin/perl
use strict;

# -----------------------------------------
# input params: all optional
#
# 	-f <file_id>
# 	-c check only - no updates performed
# 	-v verbose
#
# -----------------------------------------
# what it does:
#
#   - for every napster file with open status
# 	  - get unmatched sale records from db
#     - use (artist/norm|album/norm) along with upc isrc track/norm to match
#
# -----------------------------------------

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::Util qw(clean norm);

use lib '/app/tools/rps/lib';
use RPS::File::File;
use RPS::Sale::File;
use RPS::Sale::Match;

use constant CLIENT_ID  => 31;
use constant CLIENT_DIR => 'sanctuary_records_group';
use constant SERVICE_ID => 10;
use constant LOG        => 'automap_napster.log';

use Getopt::Std;

# -----------------------------------------

unless ( -t STDIN ) {
    my $log = join( '/', '/app/shared/sale_import', CLIENT_DIR, LOG );
    select STDERR if open( STDERR, ">>$log" );
}

print "running with args '@ARGV'\n";

my %opt;
getopts( 'vcf:', \%opt );

my $UPDATE = $opt{c} ? 0 : 1;
my $DEBUG  = $opt{v} ? 1 : 0;

my $cnt_map = 0;
my $summary = map_unmatched( $opt{f} );

map {
    print "matched $summary->{$_} for file $_\n" if $summary->{$_};
    $cnt_map += $summary->{$_};
} keys %$summary;

print scalar( keys(%$summary) ) . " files analyzed\n";
print "$cnt_map total records mapped\n";

# -----------------------------------------

sub map_unmatched {
    my $file_id_single = shift;
    my %summary        = ();

    my $sql = 'SELECT file_id from file where service_id=' . SERVICE_ID;
    $sql .= ' and file_status=' . File::File::STATUS_OPEN;
    $sql .= ' and remaining_exceptions > 0';
    $sql .= " and file_id=$file_id_single" if $file_id_single;

    my $files = RPS::File::Files->new( client_id => CLIENT_ID );
    return unless $files->getByQuery($sql);

    my $dbo = Common::RSDB->new( client_id => CLIENT_ID );

    while ( my $fileObj = $files->GetNext ) {
        my $file_id = $fileObj->FileID;
        $DEBUG && print "checking $file_id\n";
        $summary{$file_id} = 0;

        my $sales = File::Sales->new( client_id => CLIENT_ID );
        $sales->GetUnmatchedByFile( file_id => $file_id );

        while ( my $sale = $sales->GetNext() ) {
            my $upc  = $sale->UPC;
            my $isrc = $sale->ISRC;

            my $artist     = $sale->ArtistName;
            my $artistNorm = norm($artist);

            my $album     = $sale->AlbumName;
            my $albumNorm = norm($album);

            my $track     = $sale->TrackName;
            my $trackNorm = norm($track);

            next unless ( $upc && $isrc && $track && ( $album || $artist ) );
            my $product_id;

            if ($artist)    # exclude album
            {
                my $sql =
'select product_id from product_catalog_extract where upc=? and isrc=? and product_type="T" and (artist_name=? or artist_norm=?) and (track_name=? or track_norm=?)';

                my $sth = $dbo->DBH->prepare($sql);
                $sth->execute( $upc, $isrc, $artist, $artistNorm, $track, $trackNorm );

                $product_id = $sth->fetchrow_arrayref()->[0] if ( $sth->rows == 1 );
            }

            if ( !$product_id and $album )    # exclude artist
            {
                my $sql =
'select product_id from product_catalog_extract where upc=? and isrc=? and product_type="T" and (album_name=? or album_norm=?) and (track_name=? or track_norm=?)';

                my $sth = $dbo->DBH->prepare($sql);
                $sth->execute( $upc, $isrc, $album, $albumNorm, $track, $trackNorm );

                $product_id = $sth->fetchrow_arrayref()->[0] if ( $sth->rows == 1 );
            }

            # now try the same 2 combinations above only without upc
            if ( !$product_id and $artist )    # exclude album and upc
            {
                my $sql =
'select product_id from product_catalog_extract where isrc=? and product_type="T" and (artist_name=? or artist_norm=?) and (track_name=? or track_norm=?)';

                my $sth = $dbo->DBH->prepare($sql);
                $sth->execute( $isrc, $artist, $artistNorm, $track, $trackNorm );

                $product_id = $sth->fetchrow_arrayref()->[0] if ( $sth->rows == 1 );
            }

            if ( !$product_id and $album )     # exclude artist and upc
            {
                my $sql =
'select product_id from product_catalog_extract where isrc=? and product_type="T" and (album_name=? or album_norm=?) and (track_name=? or track_norm=?)';

                my $sth = $dbo->DBH->prepare($sql);
                $sth->execute( $isrc, $album, $albumNorm, $track, $trackNorm );

                $product_id = $sth->fetchrow_arrayref()->[0] if ( $sth->rows == 1 );
            }

            next unless $product_id;
            $DEBUG && print "match $product_id with $upc $isrc $artist $album $track\n";

            if ($UPDATE) {
                $sale->ImportStatus(File::Sale::STATUS_BATCH_MAPPED);
                $sale->ProductID($product_id);
                $sale->Save();
            }

            $summary{$file_id}++;
        }

        # oh what the heck, go ahead and make sure the metrics are accurate
        $fileObj->UpdateSummary() if $summary{$file_id};

    }
    return \%summary;
}

sub usage {
    my $err = shift;

    print "ERROR: $err\n";
    print "Usage: $0 [-f FILE_ID -c COUNT]\n";
    exit();
}