use strict;
use lib '/app/tools/common/lib';
use Common::RSApp;
use Common::RSDB;
use Common::DB::Item::ClientCMIDMap;

use Data::Dumper;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Album;

use AllMetadata;
use constant kCMClientID => 112;

use constant client_id          => 0;
use constant album_name         => 1;
use constant album_artist       => 2;
use constant album_artist_id    => 3;
use constant label_name	        => 4;
use constant label_id           => 5;
use constant catalog_id	        => 6;
use constant upc                => 7;
use constant album_id           => 8;
use constant client_album_id    => 9;
use constant release_date       => 10;
use constant upc_alt            => 11;
use constant album_custom_1	    => 12;
use constant album_custom_2	    => 13;
use constant album_custom_3	    => 14;
use constant track_name	        => 15;
use constant disc_no            => 16;
use constant disc_name          => 17;
use constant track_no           => 18;
use constant track_minutes      => 19;
use constant track_seconds      => 20;
use constant track_artist       => 21;
use constant track_artist_id    => 22;
use constant isrc               => 23;
use constant track_id           => 24;
use constant client_track_id    => 25;
use constant genre              => 26;
use constant track_custom_1     => 27;
use constant track_custom_2	    => 28;
use constant track_custom_3	    => 29;
use constant master_id          => 30;
use constant song_id            => 31;
use constant song_title         => 32;
use constant composer_id        => 33;
use constant composer_name      => 34;
use constant kNumFields         => 35;

my %gIndexToFieldMap = 
(
client_id()          => 'client_id',
album_name()         => 'album_name',
album_artist()       => 'album_artist',
album_artist_id()    => 'album_artist_id',
label_name()	     => 'label_name',
label_id()           => 'label_id',
catalog_id()	     => 'catalog_id',
upc()                => 'upc',
album_id()           => 'album_id',
client_album_id()    => 'client_album_id',
release_date()       => 'release_date',
upc_alt()            => 'upc_alt',
album_custom_1()	 => 'album_custom_1',
album_custom_2()	 => 'album_custom_2',
album_custom_3()	 => 'album_custom_3',
track_name()	     => 'track_name',
disc_no()            => 'disc_no',
disc_name()          => 'disc_name',
track_no()           => 'track_no',
track_minutes()      => 'track_minutes',
track_seconds()      => 'track_seconds',
track_artist()       => 'track_artist',
track_artist_id()    => 'track_artist_id',
isrc()               => 'isrc',
track_id()           => 'track_id',
client_track_id()    => 'client_track_id',
genre()              => 'genre',
track_custom_1()     => 'track_custom_1',
track_custom_2()	 => 'track_custom_2',
track_custom_3()	 => 'track_custom_3',
master_id()          => 'master_id',
song_id()            => 'song_id',
song_title()         => 'song_title',
composer_id()        => 'composer_id',
composer_name()      => 'composer_name',
);

my $singleton = Common::RSApp->new(clientID => kCMClientID);


# Go through all the metadata in the dump table, and
# see if I can find matches.
#

my $dbo = Common::RSApp::GetClientDB();
my $dbh = $dbo->DBH;

my @match;
my @nomatch;
my @multiple;

my $linenum = 1;
while (my $line = <STDIN>)
{
    print STDERR "$linenum\n";
    chop $line;

    # Parse the line into the proper chunks.
    #
    my @bits = split(/\t/, $line);


    # See if I can find all the matching stuff in one shot.
    # !!! What I'll do is add _all_ the fields to the @where array, then
    # !!! try to match that.  If I don't get a match, I'll start removing
    # !!! fields until I do, or until I run out of fields that I feel comfortable
    # !!! removing, or until we hit a multi-match.
    #
    my @where;
    push @where, 'album_name = '    . $dbo->DBQuote($bits[album_name]);
    push @where, 'album_artist = '  . $dbo->DBQuote($bits[album_artist]);
    push @where, 'track_name = '    . $dbo->DBQuote($bits[track_name]);
    push @where, 'track_artist = '  . $dbo->DBQuote($bits[track_artist]);
    push @where, 'release_date= '   . $dbo->DBQuote($bits[release_date]);
    push @where, 'catalog_id = '    . $dbo->DBQuote($bits[catalog_id]);
    push @where, 'upc = '           . $dbo->DBQuote($bits[upc]);
    push @where, 'isrc = '          . $dbo->DBQuote($bits[isrc]);
    push @where, 'genre= '          . $dbo->DBQuote($bits[genre]);
    push @where, 'label_name = '    . $dbo->DBQuote($bits[label_name]);
    push @where, 'client_album_id= '. $dbo->DBQuote($bits[client_album_id]);
    push @where, 'upc_alt = '       . $dbo->DBQuote($bits[upc_alt]);
    push @where, 'song_title = '    . $dbo->DBQuote($bits[song_title]);
    push @where, 'composer_name= '  . $dbo->DBQuote($bits[composer_name]);
    push @where, 'track_minutes= '  . $dbo->DBQuote($bits[track_minutes]);
    push @where, 'track_seconds= '  . $dbo->DBQuote($bits[track_seconds]);
    push @where, 'disc_no = '       . $dbo->DBQuote($bits[disc_no]);
    push @where, 'disc_name = '     . $dbo->DBQuote($bits[disc_name]);
    push @where, 'track_no= '       . $dbo->DBQuote($bits[track_no]);
    push @where, 'album_custom_1= ' . $dbo->DBQuote($bits[album_custom_1]);
    push @where, 'album_custom_2= ' . $dbo->DBQuote($bits[album_custom_2]);
    push @where, 'album_custom_3= ' . $dbo->DBQuote($bits[album_custom_3]);
    push @where, 'track_custom_1= ' . $dbo->DBQuote($bits[track_custom_1]);
    push @where, 'track_custom_2= ' . $dbo->DBQuote($bits[track_custom_2]);
    push @where, 'track_custom_3= ' . $dbo->DBQuote($bits[track_custom_3]);

    # Keep trying to match until we succeed, removing fields from the back of the array.
    # If we only have the first 4 fields, left, then we've failed.
    while (scalar @where > 1)
    {
        my $sql = "SELECT * FROM zzall_metadata WHERE " . join(' AND ', @where);
        my $collection = AllMetadata->GetAll($sql);
        if (1 == $collection->size())
        {
            # !!! What do I really need to make the entry?
            push @match, $linenum;
            my $matchedItem = $collection->next();
            makeMapEntry($matchedItem, \@bits);
            last;
        }
        elsif (1 < $collection->size())
        {
            # !!! Let's see if I can winnow this down.
            #
            my @possible;
            my $bestSoFar = 0;
            my $bestIndex = undef;
            my $i = 0;
            while (my $matched = $collection->next())
            {
                # Count up how many fields actually match.
                #
                my $count = 0;
                for (my $f = 0; $f < kNumFields; $f++)
                {
                    # Skip some of these fields.
                    #
                    next if (album_id == $f || track_id == $f || album_artist_id == $f || track_artist_id == $f || label_id == $f 
                     || song_id == $f || composer_id == $f || master_id == $f);

                    my $fieldname = $gIndexToFieldMap{$f};
                    if ($matched->$fieldname eq $bits[$f])
                    {
                        $count++;
                    }
                }

                if ($count > $bestSoFar)
                {
                    $bestSoFar = $count;
                    $bestIndex = $i;
                }
                elsif ($count == $bestSoFar)
                {
                    # if two have identical counts, then we don't have a winner...
                    #
                    $bestIndex = undef;
                }
                push @possible, $matched;
                $i++;
            }

            if (defined $bestIndex)
            {
                # We have a winner!
                # !!! This will change to actually make the map entry.
                # 
                my $matchedItem = $possible[$bestIndex];
                makeMapEntry($matchedItem, \@bits);
                push @match, $linenum;
                last;
            }
            my $matchline = "$linenum\t$line\n---possibles---\n";
            foreach my $matched (@possible)
            {
                $matchline .= "\t" . $matched->client_id;
                $matchline .= "\t" . $matched->album_name;
                $matchline .= "\t" . $matched->album_artist;
                $matchline .= "\t" . $matched->album_artist_id;
                $matchline .= "\t" . $matched->label_name;
                $matchline .= "\t" . $matched->label_id;
                $matchline .= "\t" . $matched->catalog_id;
                $matchline .= "\t" . $matched->upc;
                $matchline .= "\t" . $matched->album_id;
                $matchline .= "\t" . $matched->client_album_id;
                $matchline .= "\t" . $matched->release_date;
                $matchline .= "\t" . $matched->upc_alt;
                $matchline .= "\t" . $matched->album_custom_1;
                $matchline .= "\t" . $matched->album_custom_2;
                $matchline .= "\t" . $matched->album_custom_3;
                $matchline .= "\t" . $matched->track_name;
                $matchline .= "\t" . $matched->disc_no;
                $matchline .= "\t" . $matched->disc_name;
                $matchline .= "\t" . $matched->track_no;
                $matchline .= "\t" . $matched->track_minutes;
                $matchline .= "\t" . $matched->track_seconds;
                $matchline .= "\t" . $matched->track_artist;
                $matchline .= "\t" . $matched->track_artist_id;
                $matchline .= "\t" . $matched->isrc;
                $matchline .= "\t" . $matched->track_id;
                $matchline .= "\t" . $matched->client_track_id;
                $matchline .= "\t" . $matched->genre;
                $matchline .= "\t" . $matched->track_custom_1;
                $matchline .= "\t" . $matched->track_custom_2;
                $matchline .= "\t" . $matched->track_custom_3;
                $matchline .= "\t" . $matched->master_id;
                $matchline .= "\t" . $matched->song_id;
                $matchline .= "\t" . $matched->song_title;
                $matchline .= "\t" . $matched->composer_id;
                $matchline .= "\t" . $matched->composer_name;
                $matchline .= "\n";
            }
            push @multiple, $matchline;
            last;
        }

        # We didn't match any - take away a field from the where clause and try again.
        #
        pop @where;
    }

    if (scalar @where < 2)
    {
        push @nomatch, "$linenum\t$line";
    }

    $linenum++;
}

print "match: " . scalar @match . "\n";
print "\nno match: " . scalar @nomatch . "\n";
foreach my $notmatched (@nomatch)
{
    print "$notmatched\n";
}
print "\nmultiple: " . scalar @multiple . "\n";
foreach my $multimatch (@multiple)
{
    print "$multimatch\n";
}


sub makeMapEntry
{
    my ($matchedItem, $bits) = @_;

    # bits is the parsed line with the cm data
    # matchedItem is the db object with the client data
    #
    my $clientID = $matchedItem->client_id;
    
    addFieldToMap($clientID, $bits->[album_id], $matchedItem->album_id, 'album_id');
    addFieldToMap($clientID, $bits->[track_id], $matchedItem->track_id, 'track_id');
    addFieldToMap($clientID, $bits->[album_artist_id], $matchedItem->album_artist_id, 'artist_id');
    addFieldToMap($clientID, $bits->[track_artist_id], $matchedItem->track_artist_id, 'artist_id');
    addFieldToMap($clientID, $bits->[label_id], $matchedItem->label_id, 'label_id');
    addFieldToMap($clientID, $bits->[master_id], $matchedItem->master_id, 'master_id');
    addFieldToMap($clientID, $bits->[song_id], $matchedItem->song_id, 'song_id');
    addFieldToMap($clientID, $bits->[composer_id], $matchedItem->composer_id, 'composer_id');
}

sub addFieldToMap
{
    my ($clientID, $cmSiteID, $clientSiteID, $idType) = @_;

    return if (! $cmSiteID && ! $clientSiteID);

    # We'll do this initial lookup with just the client site id.
    # That way we can make sure we don't have wonky data
    #
    my $currentItem = Common::DB::Item::ClientCMIDMap->Lookup
    (
        id_type => $idType,
        client_id => $clientID,
        client_site_id => $clientSiteID,
#        cm_site_id => $cmAlbumID,
    );
    if ($currentItem)
    {
        if ($currentItem->cm_site_id != $cmSiteID)
        {
            die "ERROR! Already have a map entry!: client $clientID, $idType client site id $clientSiteID, current cm id " . $currentItem->cm_site_id . " != $cmSiteID\n";
        }
    }
    else
    {
        # Make a new entry
        #
        my $newItem = Common::DB::Item::ClientCMIDMap->Create
        (
            client_id => $clientID,
            client_site_id => $clientSiteID,
            cm_site_id => $cmSiteID,
            id_type => $idType,
        );
        $newItem->save();
    }
}