#!/usr/bin/perl

use strict;

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

use Common::Util qw(clean norm scrub mphon);
use Common::RSDB;
use Getopt::Std;

use constant ARTIST_NAME_INDEX => 7;
use constant ALBUM_NAME_INDEX  => 10;
use constant TRACK_NAME_INDEX  => 25;

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

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

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

unless ( $client_id =~ /^\d+$/ && $client_id > 0 ) {
    die usage("client_id '$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 $dbObj = new Common::RSDB( client_id => $client_id, dbi_attr => { RaiseError => 1 } );
my $dbh = $dbObj->DBH();

#printf("operating on %s\n", $dbObj->DBName());

my $and_where;
if ($product_id) {
    my $product_ids_joined = join( ',', $product_id );
    $and_where = " AND P.product_id in ($product_ids_joined)";
    $dbh->do("DELETE FROM product_catalog_extract WHERE product_id in ($product_ids_joined)");
} elsif ($album_id) {
    my $album_ids_joined = join( ',', $album_id );
    $and_where = " AND A.album_id in ($album_ids_joined)";
    $dbh->do("DELETE FROM product_catalog_extract WHERE album_id in ($album_ids_joined)");
}

#!!!!!!!!!!!!!!!!!
#! if you're thinking of changing this sql statement, pay attention to:
#!	1) the indexes ARTIST_NAME_INDEX, ALBUM_NAME_INDEX, TRACK_NAME_INDEX
#!	2) $num_bind_params (probably the only thing that really stands out)
#!	3) the rows specifed in the $sth_insert->execute() statement
#! thankfully, mysql will complain if you miss the last two, but if you
#! ignore the first thing, then your clean, scrub, mphon names could be screwed up
#!!!!!!!!!!!!!!!!!

my $sql_select = <<"ENDOFTEXT";
          SELECT
             P.product_id, P.product_type, 
             L.label_id, L.client_label_id, L.label_name,
             R.artist_id, R.client_artist_id, R.artist_name,
             A.album_id, A.client_album_id, A.album_name, A.release_date, A.upc, A.upc_alt, 
             A.catalog_id, A.company_id, A.vendor_id, A.location_id, A.number_of_discs,
             AD.disc_id, AD.disc_name, AD.disc_number, AD.number_of_tracks,
             T.track_id, T.client_track_id, T.track_name, T.track_number, T.duration, T.isrc, T.genre_name, T.custom_1, T.custom_2, T.custom_3
          FROM
             product P, track T, album A, album_disc AD, artist R, label L
          WHERE
             P.product_type = 'T'
             AND P.owner_id = T.track_id
             AND T.artist_id = R.artist_id
             AND T.label_id = L.label_id
             AND T.disc_id = AD.disc_id
             AND AD.album_id = A.album_id
             $and_where
    UNION ALL
    SELECT
             P.product_id, P.product_type, 
             L.label_id, L.client_label_id, L.label_name,
             R.artist_id, R.client_artist_id, R.artist_name,
             A.album_id, A.client_album_id, A.album_name, A.release_date, A.upc, A.upc_alt,
             A.catalog_id, A.company_id, A.vendor_id, A.location_id, A.number_of_discs,
             NULL, NULL, NULL, NULL,
             NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
    FROM
       product P, album A, artist R, label L
    WHERE
       P.product_type IN ('A','2','1','4','3','9')
       AND P.owner_id = A.album_id
       AND A.artist_id = R.artist_id
       AND A.label_id = L.label_id
       $and_where
ENDOFTEXT

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

my $num_bind_params          = 54;
my $bind_params_placeholders = '?,' x $num_bind_params;

my $sql_insert = <<"ENDOFSQL";
   INSERT INTO $table
      (
       product_id, product_type, 
       label_id, client_label_id, label_name,
       artist_id, client_artist_id, artist_name,
       album_id, client_album_id, album_name, release_date, upc, upc_alt, catalog_id, company_id, vendor_id, location_id, number_of_discs,
       disc_id, disc_name, disc_number, number_of_tracks,
       track_id, client_track_id, track_name, track_number, duration, isrc, genre_name, track_custom_1, track_custom_2, track_custom_3,
       artist_clean, album_clean, track_clean, 
       artist_norm, album_norm, track_norm, 
       artist_scrub, album_scrub, track_scrub,
       artist_mphon, album_mphon, track_mphon,
       artist_clean_mphon, album_clean_mphon, track_clean_mphon, 
       artist_norm_mphon, album_norm_mphon, track_norm_mphon, 
       artist_scrub_mphon, album_scrub_mphon, track_scrub_mphon,
       date_created
      )
   VALUES 
      ($bind_params_placeholders now())
ENDOFSQL

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

# unless we've specified an album_id or product_id
# truncate the table so that we can reload it from scratch
unless ( $album_id || $product_id ) {
    $dbh->do("truncate table $table");
}

while ( my (@row) = $sth_select->fetchrow_array() ) {

    my @normalized_stuff;
    for my $item (
        clean( $row[ARTIST_NAME_INDEX] ), clean( $row[ALBUM_NAME_INDEX] ), clean( $row[TRACK_NAME_INDEX] ),
        norm( $row[ARTIST_NAME_INDEX] ),  norm( $row[ALBUM_NAME_INDEX] ),  norm( $row[TRACK_NAME_INDEX] ),
        scrub( $row[ARTIST_NAME_INDEX] ), scrub( $row[ALBUM_NAME_INDEX] ), scrub( $row[TRACK_NAME_INDEX] ),
        mphon( $row[ARTIST_NAME_INDEX] ), mphon( $row[ALBUM_NAME_INDEX] ), mphon( $row[TRACK_NAME_INDEX] ),

        mphon( clean( $row[ARTIST_NAME_INDEX] ) ), mphon( clean( $row[ALBUM_NAME_INDEX] ) ), mphon( clean( $row[TRACK_NAME_INDEX] ) ),
        mphon( norm( $row[ARTIST_NAME_INDEX] ) ),  mphon( norm( $row[ALBUM_NAME_INDEX] ) ),  mphon( norm( $row[TRACK_NAME_INDEX] ) ),
        mphon( scrub( $row[ARTIST_NAME_INDEX] ) ), mphon( scrub( $row[ALBUM_NAME_INDEX] ) ), mphon( scrub( $row[TRACK_NAME_INDEX] ) )
      ) {
        if ( $item eq '' ) {
            $item = undef;
        }
        push @normalized_stuff, $item;
    }

    $sth_insert->execute( @row[ 0 .. 32 ], @normalized_stuff );
}

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