#------------------------------------------------------------
# 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;