#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::Master;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Util qw(escape_mysql_regexp escape_mysql_like);
use Common::DB::ItemCollection;

use lib '/app/tools/rps/lib';
use base 'Common::DB::Item';

use constant kTable => 'master';
use constant kDB    => Common::DB::Item::kClientDB();

sub Get {
    my ( $class, %args ) = @_;

    my $order;
    if ( $args{order} ) {
        $order = $args{order};
    } else {
        $order = "master.title, artist.name";
    }

    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT master.* FROM master LEFT JOIN artist ON artist.artist_id=master.artist_id ORDER BY $order";
    return $class->GetAll($sql);
}

sub Search {
    my ( $class, $searchTerm ) = @_;

    return undef if ( $searchTerm eq '' );

    my $dbo = Common::RSApp::GetClientDB();
    my $where;

    # the new way
    #
    $where =
        $class->SearchSyntax( field => "master.title", value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "master.isrc",  value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "artist.name",  value => $searchTerm );

    my $sql = "SELECT master.* FROM master LEFT JOIN artist ON artist.artist_id=master.artist_id WHERE $where ORDER BY title";

    return $class->GetAll($sql);
}

sub Match {
    my ( $class, $searchTerm ) = @_;

    return undef if ( $searchTerm eq '' );

    my $dbo = Common::RSApp::GetClientDB();
    my $where;

    $where = "WHERE master.title = " . $dbo->DBQuote($searchTerm);
    $where .= " OR master.isrc = " . $dbo->DBQuote($searchTerm);
    $where .= " OR artist.name = " . $dbo->DBQuote($searchTerm);

    my $sql = "SELECT master.* FROM master LEFT JOIN artist ON artist.artist_id=master.artist_id $where ORDER BY title";
    return $class->GetAll($sql);
}

sub GetMastersBySongID {
    my ( $class, $songID ) = @_;
    return undef if ( !defined $songID );

    my $sql = "SELECT * FROM master WHERE song_id=$songID";
    return $class->GetAll($sql);
}

sub GetByISRC {
    my ( $class, $isrc ) = @_;
    return undef if ( !defined $isrc );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM master WHERE isrc=" . $dbo->DBQuote($isrc);

    return $class->GetAll($sql);
}

sub HasDigitalProduct {
    my ($class) = shift;
    my %args = @_;

    return undef unless ( defined( $args{isrc} ) );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT * FROM master "
      . "INNER JOIN track USING (master_id) "
      . "INNER JOIN album USING (album_id) "
      . "LEFT JOIN product AS album_product ON "
      . "( album_product.asset_id = album.album_id AND album_product.product_type_id = 3 ) "
      . "LEFT JOIN product AS track_product ON "
      . "( track_product.asset_id = track.track_id AND track_product.product_type_id = 4 ) "
      . "WHERE isrc = 'USACX0690600' AND ( track_product.product_id OR album_product.product_id )";

    Common::Log::Debug("QUERY: $sql");

    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return defined($count) && $count > 0;
}

sub GetTableStatus {
    my ($class) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SHOW TABLE STATUS LIKE '" . kTable . "'";

    my $sth = $dbo->DoCmd($sql);

    my $result;
    my @results;
    while ( $result = $sth->fetchrow_arrayref ) {
        push @results, @{$result};
    }
    return @results;
}

1;