#!/usr/bin/perl

use lib '/app/tools/sale_import/lib';
use lib '/app/tools/common/lib';

use Sale::Match;
use Getopt::Std;

my %opt;
getopts( 'c:t:p:a:i', \%opt );

my $client_id  = $opt{c};
my $table      = $opt{t} || 'product_pattern_match';
my $product_id = $opt{p};
my $album_id   = $opt{a};
my $do_insert  = $opt{i};

unless ( $table eq 'product_pattern_match_new' || $table eq 'product_pattern_match' ) {
    die usage("table $table is not valid!");
}

unless ( $client_id =~ /^\d+$/ && $client_id > 0 ) {
    die usage("-c <client_id>  must be a number greater than zero");
}

if ( $product_id && $album_id ) {
    die usage("You cannot specify both a product_id and album_id (choose one or the other)");
} elsif ( $product_id && $product_id !~ /^\d+(,\d+)*$/ ) {
    die usage("-p <product_id> must be a number (separate multiple product_ids with a comma -- no spaces)");
} elsif ( $album_id && $album_id !~ /^\d+(,\d+)*$/ ) {
    die usage("-p <album_id> must be a number (separate multiple album_ids with a comma -- no spaces)");
}

sub usage {
    my $errstr = shift;
    my $text = ($errstr) ? "ERROR: $errstr\n" : '';
    $text .= "Usage: $0 -c <client_id> [-a <album_ids> -p <product_ids> -t <table_name>]\n";
    return $text;
}

my $match = new Sale::Match( client_id => $client_id );

my $pattern_level = $match->pattern_level();

my $sql_select = <<"ENDOFSQL";
SELECT 
  product_id, product_type, 
  UPPER(upc) as upc, UPPER(isrc) as isrc, 
  artist_norm, album_norm, track_norm,
  artist_scrub, album_scrub, track_scrub, 
  artist_norm_mphon, album_norm_mphon, track_norm_mphon,
  artist_scrub_mphon, album_scrub_mphon, track_scrub_mphon
FROM
  product_catalog_extract
ENDOFSQL

if ($product_id) {
    my $product_ids_joined = join( ',', $product_id );
    $sql_select .= " WHERE product_id in ($product_ids_joined)";

    # since you want me to do the inserts and you specified product_id(s),
    # you probably want me to automatically delete those product_id(s). Right?
    if ($do_insert) {
        $match->dbh->do("DELETE FROM product_pattern_match WHERE product_id in ($product_ids_joined)");
    }

} elsif ($album_id) {
    my $album_ids_joined = join( ',', $album_id );
    $sql_select .= " WHERE album_id in ($album_ids_joined)";

    # since you want me to do the inserts and you specified album_id(s),
    # you probably want me to automatically delete the associatedproduct_id(s). Right?
    if ($do_insert) {
        my $sql = qq{
						 DELETE FROM product_pattern_match 
						 WHERE product_id in 
						 (
						  SELECT product_id
						  FROM product_catalog_extract
						  WHERE album_id in ($album_ids_joined)
						 )
						};

        $match->dbh->do($sql);
    }

}

my $sth_select = $match->dbh->prepare($sql_select);
$sth_select->execute();

my $sql_insert = <<"ENDOFSQL";
INSERT INTO $table
  (product_id, product_type, match_md5, pattern, level, date_created) 
VALUES 
  (?, ?, ?, ?, ?, now())
ENDOFSQL

my $sth_insert = $match->dbh->prepare($sql_insert);

#$match->dbh->do("truncate table $table");

while ( my $data_href = $sth_select->fetchrow_hashref() ) {

    my $match_patterns_href = $match->GetMatchPatterns(
        data      => $data_href,
        min_level => 70
    ) || die $match->errstr;

    while ( my ( $match_md5, $pattern ) = each %{$match_patterns_href} ) {

        my @data = (
            $data_href->{product_id},
            $data_href->{product_type},
            $match_md5, $pattern, $pattern_level->{ $data_href->{product_type} }->{$pattern}
        );

        if ($do_insert) {
            $sth_insert->execute(@data) || die $sth_insert->errstr;
        } else {
            print join( "\t", @data, '\N' ), "\n";
        }

    }
}

$sth_select->finish();
$sth_insert->finish();
$match->dbh->disconnect();