#!/usr/bin/perl

use strict;

use Getopt::Std;

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

$| = 1;

my %opt;
getopts( 'c:u', \%opt );
my $client_id = $opt{c};
my $EXE = $opt{u} ? 1 : 0;
die "usage: $0 -c <client_id> [-u (update)]\n" unless ( $client_id =~ /^\d+$/ && $client_id > 0 );

my $dbObj = new Common::RSDB( client_id => $client_id, dbi_attr => { RaiseError => 1 } );

#printf("operating on %s\n", $dbObj->DBName());
my $DBH   = $dbObj->DBH();
my %STH   = _init_sth();
my %CACHE = ();
my $count = 0;

print $EXE ? "apply changes\n\n" : "test changes\n\n";

print "update albums...";
$STH{metaUpdatedAlbums}->execute();
while ( my $album = $STH{metaUpdatedAlbums}->fetchrow_hashref() ) {
    my $label  = _lookup( 'label',  $album->{label_id} );
    my $artist = _lookup( 'artist', $album->{artist_id} );

    my %alt_names = (
        artist => _get_alt_names( $artist->{artist_name} ),
        album  => _get_alt_names( $album->{album_name} ),
    );

    # update the single album product
    $EXE && $STH{pceAlbumUpdate}->execute(
        $label->{label_id},          $label->{client_label_id}, $label->{label_name},      $artist->{artist_id},
        $artist->{client_artist_id}, $artist->{artist_name},    $album->{album_name},      $album->{release_date},
        $album->{upc},               $album->{upc_alt},         $album->{catalog_id},      $album->{company_id},
        $album->{vendor_id},         $album->{location_id},     $album->{number_of_discs}, $alt_names{artist}{clean},
        $alt_names{album}{clean},    $alt_names{artist}{norm},  $alt_names{album}{norm},   $alt_names{artist}{scrub},
        $alt_names{album}{scrub},    $album->{product_id}
    );

    # update the album info for any tracks associated with this album
    $EXE && $STH{pceAlbumTracksUpdate}->execute(
        $album->{client_album_id}, $album->{album_name},      $album->{release_date},   $album->{upc},
        $album->{upc_alt},         $album->{catalog_id},      $album->{company_id},     $album->{vendor_id},
        $album->{location_id},     $album->{number_of_discs}, $alt_names{album}{clean}, $alt_names{album}{norm},
        $alt_names{album}{scrub},  $album->{album_id}
    );
    $count++;
}
print "$count\n";
$count = 0;

print "insert albums...";
$STH{metaInsertedAlbums}->execute();
while ( my $album = $STH{metaInsertedAlbums}->fetchrow_hashref() ) {
    my $label  = _lookup( 'label',  $album->{label_id} );
    my $artist = _lookup( 'artist', $album->{artist_id} );

    my %alt_names = (
        artist => _get_alt_names( $artist->{artist_name} ),
        album  => _get_alt_names( $album->{album_name} ),
    );

    $EXE && $STH{pceAlbumInsert}->execute(
        $album->{product_id},     'A',                       $label->{label_id},          $label->{client_label_id},
        $label->{label_name},     $artist->{artist_id},      $artist->{client_artist_id}, $artist->{artist_name},
        $album->{album_id},       $album->{client_album_id}, $album->{album_name},        $album->{release_date},
        $album->{upc},            $album->{upc_alt},         $album->{catalog_id},        $album->{company_id},
        $album->{vendor_id},      $album->{location_id},     $album->{number_of_discs},   $alt_names{artist}{clean},
        $alt_names{album}{clean}, $alt_names{artist}{norm},  $alt_names{album}{norm},     $alt_names{artist}{scrub},
        $alt_names{album}{scrub}
    );
    $count++;
}
print "$count\n";
$count = 0;

print "update tracks...";
$STH{metaUpdatedTracks}->execute();
while ( my $track = $STH{metaUpdatedTracks}->fetchrow_hashref() ) {
    my $label  = _lookup( 'label',  $track->{label_id} );
    my $artist = _lookup( 'artist', $track->{artist_id} );
    my $album  = _lookup( 'album',  $track->{disc_id} );

    my %alt_names = (
        artist    => _get_alt_names( $artist->{artist_name} ),
        album     => _get_alt_names( $album->{album_name} ),
        track     => _get_alt_names( $track->{track_name} ),
        track_alt => _get_alt_names( $track->{custom_2} ),
    );

    # update the single track product
    $EXE && $STH{pceTrackUpdate}->execute(
        $label->{label_id},          $label->{client_label_id}, $label->{label_name},         $artist->{artist_id},
        $artist->{client_artist_id}, $artist->{artist_name},    $album->{album_id},           $album->{client_album_id},
        $album->{album_name},        $album->{release_date},    $album->{upc},                $album->{upc_alt},
        $album->{catalog_id},        $album->{company_id},      $album->{vendor_id},          $album->{location_id},
        $album->{number_of_discs},   $track->{disc_id},         $track->{disc_name},          $track->{disc_number},
        $track->{number_of_tracks},  $track->{track_id},        $track->{client_track_id},    $track->{track_name},
        $track->{track_number},      $track->{duration},        $track->{isrc},               $track->{genre_name},
        $track->{custom_1},          $track->{custom_2},        $track->{custom_3},           $alt_names{artist}{clean},
        $alt_names{album}{clean},    $alt_names{artist}{norm},  $alt_names{album}{norm},      $alt_names{artist}{scrub},
        $alt_names{album}{scrub},    $alt_names{track}{clean},  $alt_names{track_alt}{clean}, $alt_names{track}{norm},
        $alt_names{track}{scrub},    $track->{product_id}
    );
    $count++;
}
print "$count\n";
$count = 0;

print "insert tracks...";
$STH{metaInsertedTracks}->execute();
while ( my $track = $STH{metaInsertedTracks}->fetchrow_hashref() ) {
    my $label  = _lookup( 'label',  $track->{label_id} );
    my $artist = _lookup( 'artist', $track->{artist_id} );
    my $album  = _lookup( 'album',  $track->{disc_id} );

    my %alt_names = (
        artist    => _get_alt_names( $artist->{artist_name} ),
        album     => _get_alt_names( $album->{album_name} ),
        track     => _get_alt_names( $track->{track_name} ),
        track_alt => _get_alt_names( $track->{custom_2} ),
    );

    $EXE && $STH{pceTrackInsert}->execute(
        $track->{product_id},         'T',                       $label->{label_id},          $label->{client_label_id},
        $label->{label_name},         $artist->{artist_id},      $artist->{client_artist_id}, $artist->{artist_name},
        $album->{album_id},           $album->{client_album_id}, $album->{album_name},        $album->{release_date},
        $album->{upc},                $album->{upc_alt},         $album->{catalog_id},        $album->{company_id},
        $album->{vendor_id},          $album->{location_id},     $album->{number_of_discs},   $track->{disc_id},
        $track->{disc_name},          $track->{disc_number},     $track->{number_of_tracks},  $track->{track_id},
        $track->{client_track_id},    $track->{track_name},      $track->{track_number},      $track->{duration},
        $track->{isrc},               $track->{genre_name},      $track->{custom_1},          $track->{custom_2},
        $track->{custom_3},           $alt_names{artist}{clean}, $alt_names{album}{clean},    $alt_names{artist}{norm},
        $alt_names{album}{norm},      $alt_names{artist}{scrub}, $alt_names{album}{scrub},    $alt_names{track}{clean},
        $alt_names{track_alt}{clean}, $alt_names{track}{norm},   $alt_names{track}{scrub}
    );
    $count++;
}
print "$count\n";

$DBH->disconnect();

#/////////////////////////////////////////////////

sub _init_sth {
    my %sth = ();

    $sth{metaUpdatedAlbums} = $DBH->prepare(
        'SELECT p.product_id, a.*
FROM product p, album a, meta_update m
WHERE m.owner_id=p.owner_id AND p.owner_id=a.album_id AND m.product_type="A" AND p.product_type="A" AND m.action="U"'
    );
    $sth{metaUpdatedTracks} = $DBH->prepare(
        'SELECT p.product_id, t.*
FROM product p, track t, meta_update m
WHERE m.owner_id=p.owner_id AND p.owner_id=t.track_id AND m.product_type="T" AND p.product_type="T" AND m.action="U"'
    );
    $sth{metaInsertedAlbums} = $DBH->prepare(
        'SELECT p.product_id, a.*
FROM product p, album a, meta_update m
WHERE m.owner_id=p.owner_id AND p.owner_id=a.album_id AND m.product_type="A" AND p.product_type="A" AND m.action="I"'
    );
    $sth{metaInsertedTracks} = $DBH->prepare(
        'SELECT p.product_id, t.*
FROM product p, track t, meta_update m
WHERE m.owner_id=p.owner_id AND p.owner_id=t.track_id AND m.product_type="T" AND p.product_type="T" AND m.action="I"'
    );

    $sth{labelLookup}  = $DBH->prepare('SELECT * FROM label WHERE label_id=?');
    $sth{artistLookup} = $DBH->prepare('SELECT * FROM artist WHERE artist_id=?');
    $sth{albumLookup}  = $DBH->prepare('SELECT a.*, d.* FROM album_disc d JOIN album a USING (album_id) WHERE d.disc_id=?');

    my $bind_params_placeholders = '?,' x 25;
    $sth{pceAlbumInsert} = $DBH->prepare( "
INSERT INTO product_catalog_extract (
    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,
    artist_clean, album_clean,
    artist_norm, album_norm,
    artist_scrub, album_scrub,
    date_created
    )
VALUES 
    ($bind_params_placeholders now())" );

    $bind_params_placeholders = '?,' x 43;
    $sth{pceTrackInsert} = $DBH->prepare( "
INSERT INTO product_catalog_extract (
    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,
    artist_norm, album_norm,
    artist_scrub, album_scrub,
    track_clean, track_alt_clean, track_norm, track_scrub,
    date_created
    )
VALUES 
    ($bind_params_placeholders now())" );

    $sth{pceAlbumUpdate} = $DBH->prepare( "
UPDATE product_catalog_extract
SET label_id=?, client_label_id=?, label_name=?,
    artist_id=?, client_artist_id=?, artist_name=?,
    album_name=?, release_date=?, upc=?, upc_alt=?, catalog_id=?,
    company_id=?, vendor_id=?, location_id=?, number_of_discs=?,
    artist_clean=?, album_clean=?,
    artist_norm=?, album_norm=?,
    artist_scrub=?, album_scrub=?
WHERE product_id=?" );

    $sth{pceAlbumTracksUpdate} = $DBH->prepare( "
UPDATE product_catalog_extract
SET client_album_id=?, album_name=?, release_date=?, upc=?, upc_alt=?, catalog_id=?,
    company_id=?, vendor_id=?, location_id=?, number_of_discs=?,
    album_clean=?, album_norm=?, album_scrub=?
WHERE product_type='T' AND album_id=?" );

    $sth{pceTrackUpdate} = $DBH->prepare( "
UPDATE product_catalog_extract
SET 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=?,
    artist_norm=?, album_norm=?,
    artist_scrub=?, album_scrub=?,
    track_clean=?, track_alt_clean=?, track_norm=?, track_scrub=?
WHERE product_id=?" );

    return %sth;
}

sub _lookup {
    my ( $type, $key ) = @_;

    my $sth =
        $type eq 'label'  ? $STH{labelLookup}
      : $type eq 'artist' ? $STH{artistLookup}
      : $type eq 'album'  ? $STH{albumLookup}
      :                     '';

    unless ( $CACHE{$type}{$key} ) {
        $sth->execute($key);
        return undef unless $sth->rows();

        if ( $sth->rows() > 1 ) {
            print "multiple $type matches for $key\n";
            return undef;
        }

        $CACHE{$type}{$key} = $sth->fetchrow_hashref();
        $sth->finish();
    }

    #else { print STDERR "cache hit\n"; }

    return $CACHE{$type}{$key};
}

sub _get_alt_names {
    my $name = shift;
    return {
        clean => clean($name),
        norm  => norm($name),
        scrub => scrub($name),
    };
}