#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::Product;
use strict;
use warnings;
use Data::Dumper;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Track;

use lib '/app/tools/common/lib';
use Common::Timer;

use base 'Common::DB::Item';


use constant kProductTypeLP             => 1;
use constant kProductTypeCD             => 2;
use constant kProductTypeDigital        => 3;
use constant kProductTypeDigitalTrack   => 4;
use constant kProductTypeVHS            => 5;
use constant kProductTypeCass           => 6;
use constant kProductTypeEP             => 7;
use constant kProductTypeDVD            => 9;
use constant kProductTypeCDSingle       => 11;
use constant kProductTypeCassSingle     => 12;
use constant kProductTypeDVDCDSet       => 13;
use constant kProductTypeDblCD          => 14;
use constant kProductTypeRingtone       => 15;
use constant kProductTypeLP5            => 16;
use constant kProductTypeBluRay         => 17;

# The following are license configurations and are not in the product table
#
use constant kAllProducts               => '';
use constant kAllPhysicalProducts       => 255;
use constant kAllDigitalProducts        => 254;

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

use constant kProductStatusActive   => 1;
use constant kProductStatusInactive => 2;


# This is NOT THE SAME as the 'product_format' table.  This just maps the product_format_id to a human-readable string
#

my %gProductTypeIDStrings = (
    kProductTypeLP()             => 'LP',
    kProductTypeCD()             => 'CD',
    kProductTypeDigital()        => 'Digital Album',
    kProductTypeDigitalTrack()   => 'Digital Track',
    kProductTypeVHS()            => 'VHS',
    kProductTypeCass()           => 'Cassette',
    kProductTypeEP()             => 'EP',
    kProductTypeDVD()            => 'DVD',
    kProductTypeCDSingle()       => 'CD Single',
    kProductTypeCassSingle()     => 'Cassette Single',
    kProductTypeDVDCDSet()       => 'DVD CD Set',
    kProductTypeDblCD()          => 'Double CD',
    kProductTypeRingtone()       => 'Ringtone',
    kProductTypeLP5()            => 'LP5',
    kProductTypeBluRay()         => 'BluRay',
    kAllPhysicalProducts()       => 'All Physical',
    kAllDigitalProducts()        => 'All Digital',
    kAllProducts()               => 'All',
);

sub ProductTypeIDToString
{
    my ($class, $productTypeID) = @_;
    return $gProductTypeIDStrings{$productTypeID || ""};
}

sub GetProductsByAlbumID
{
	my ($class, $albumID, $productType) = @_;
    my $t = Common::Timer->new();

	my $sql = "SELECT * FROM ".kTable." WHERE asset_id=$albumID";

    if ($productType)
    {
        $sql .= " AND product_type_id=$productType";
    }
    else
    {
        $sql .= " AND product_type_id <> " . kProductTypeDigitalTrack;
    }
    
    $sql .= " ORDER BY date_created";

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

sub GetProductsByTrackID
{
	my ($class, $trackID, $productType) = @_;

	my $sql = "SELECT * FROM ".kTable." INNER JOIN product_track USING (product_id) " .
	          "WHERE track_id=$trackID";

    if ($productType)
    {
        $sql .= " AND product_type_id=$productType";
    }
    else
    {
        $sql .= " AND product_type_id <> " . kProductTypeDigitalTrack;
    }

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

sub GetProductTypesByAlbumID
{
	my ($class, $albumID) = @_;

	my $sql = "SELECT product_type_id FROM ".kTable." WHERE asset_id=$albumID";
	my $dbo = Common::RSApp::GetClientDB();
	my $sth = $dbo->DoCmd($sql);
	my @results;
	while (my ($id) = $sth->fetchrow_array())
	{
		push @results, $id;
	}
	return @results;
}

sub GetDigitalTrackProduct
{
    my ($class, $trackID, $parentProductID) = @_;
    return undef if(!$trackID);
    return undef if(!$parentProductID);
    
    my $sql = "SELECT * from ".kTable." WHERE asset_id=$trackID AND product_type_id=".kProductTypeDigitalTrack;
    $sql .=  " AND parent_product_id = $parentProductID";
    
    my $collection = $class->SUPER::GetAll($sql);
    
    return ($collection->hasNext()) ? $collection->next() : undef;
}

sub GetAllDigitalTrackProducts
{
    my ($class, $trackID) = @_;
    return undef if(!$trackID);
    
    my $sql = "SELECT * from ".kTable." WHERE asset_id=$trackID AND product_type_id=".kProductTypeDigitalTrack;
    
    return $class->SUPER::GetAll($sql);
}

# Leaving this here for DA clients who will continue to only have 1 digital album product per album.
# Everyone else should be using GetAllDigitalAlbumProducts.
sub GetDigitalAlbumProduct
{
	my ($class, $albumID) = @_;
	return undef if(!$albumID);

    my $sql = "SELECT * from ".kTable." WHERE asset_id=$albumID AND product_type_id=".kProductTypeDigital;

	my $collection = $class->SUPER::GetAll($sql);

	return ($collection->hasNext()) ? $collection->next() : undef;
}

sub GetAllDigitalAlbumProducts
{
    my ($class, $albumID) = @_;
    return undef if(!$albumID);
    
    my $sql = "SELECT * from ".kTable." WHERE asset_id=$albumID AND product_type_id=".kProductTypeDigital;
    
    return $class->SUPER::GetAll($sql);
}


sub GetByUPC
{
	my ($class, $upc) = @_;

	my $dbo = Common::RSApp::GetClientDB();
	my $sql = "SELECT * FROM ".kTable." WHERE upc_ean=".$dbo->DBQuote($upc);
    return $class->SUPER::GetAll($sql);
}


# for now, only check if any sales records are tied to this product
sub CanDelete
{
    my ($class, $productID) = @_;

    my $sql = "SELECT count(*) FROM sale WHERE product_id=$productID";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my $count = $sth->fetchrow_arrayref()->[0] if ($sth);

    return $count ? 0 : 1;
}

sub GetDigitalUPCByAlbumID
{
    my ($album_id) = @_;
	my $dbo = Common::RSApp::GetClientDB();

    my $sql = " SELECT upc_ean FROM " . kTable .
	          " WHERE asset_id = " . $dbo->DBQuote($album_id) . " AND " .
			        "product_type_id = " . kProductTypeDigital;

    Common::Log::Debug( "QUERY: $sql" );
    my $sth = $dbo->DoCmd( $sql );
    my ($upc) = $sth->fetchrow_array();
    return $upc;
}

sub GetDACountByAlbumID
{
    my ($class, $album_id, $product_id) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = " SELECT COUNT(*) FROM " . kTable .
	          " WHERE asset_id = " . $dbo->DBQuote($album_id) . " AND " .
			        "product_type_id = " . kProductTypeDigital;
			      
    if ($product_id) {
        $sql .= " AND product_id != " . $dbo->DBQuote($product_id);
    }

    Common::Log::Debug( "QUERY: $sql" );
    
    my $sth = $dbo->DoCmd( $sql );
    my ($count) = $sth->fetchrow_array();
    return $count;
}


sub ProductTypeExistsOnAlbum
{
    my ($class, %args) = @_;
    my $dbo = Common::RSApp::GetClientDB();
    
    my $albumID = $dbo->DBQuote($args{albumID});
    my $productTypeID = $dbo->DBQuote($args{productTypeID});  
    my $productID = $dbo->DBQuote($args{productID});    

    my $sql = " SELECT COUNT(*) FROM " . kTable .
	          " WHERE asset_id = " . $albumID . " AND " .
			        "product_type_id = " . $productTypeID;

    # If we were passed a product ID, then we need to exclude that from the query.
    if ($productID && $productID ne "NULL")
    {
        $sql .= " AND product_id != " . $productID;
    }    
    
    Common::Log::Debug( "QUERY: $sql" );
    
    my $sth = $dbo->DoCmd( $sql );
    my ($count) = $sth->fetchrow_array();
    return $count;
}

# Check to see if a UPC code has been used.
sub GetProductIDByUPC
{
    my ($class, %args) = @_;
	my $dbo = Common::RSApp::GetClientDB();

	my $upc = $dbo->DBQuote($args{upc});
	my $product_type_id = $dbo->DBQuote($args{product_type_id});

    my $sql = "SELECT product_id FROM " . kTable . " WHERE " .
              "upc_ean = $upc AND product_type_id = $product_type_id";

    Common::Log::Debug( "QUERY: $sql" );
    my $sth = $dbo->DoCmd( $sql );
    my ($id) = $sth->fetchrow_array();
    return $id;
}


sub GetProductIDByAlbumID
{
    my ($class, %args) = @_;
	my $dbo = Common::RSApp::GetClientDB();

	my $album_id = $dbo->DBQuote($args{album_id});
	my $product_type_id = $dbo->DBQuote($args{product_type_id});

    my $sql = "SELECT product_id FROM " . kTable . " WHERE " .
              "asset_id = $album_id AND product_type_id = $product_type_id";

    Common::Log::Debug( "QUERY: $sql" );
    my $sth = $dbo->DoCmd( $sql );
    my ($id) = $sth->fetchrow_array();
    return $id;
}


# Check to see if a UPC code has been used.
sub UPCExists
{
    my ($class, %args) = @_;
	my $dbo = Common::RSApp::GetClientDB();

	my $upc = $dbo->DBQuote($args{upc});
	my $albumID;

    if( $args{albumID} ) {
		$albumID = $args{albumID};
	} elsif ($args{trackID}) {
		my $track = RPS::DB::Item::Track->Lookup(track_id => $args{trackID});
		$albumID = $track->AlbumID;
	}

	my $dt_product_id = kProductTypeDigitalTrack;
#	my $sql = <<SQL;
#SELECT count(product.product_id)
#FROM album
#	LEFT JOIN product ON ( album.album_id = product.asset_id )
#	LEFT JOIN track ON ( track.album_id = album.album_id AND
#	                     product.product_type_id <> $dt_product_id )
#	LEFT JOIN product as track_product ON (
#		track.track_id = track_product.asset_id AND
#		track_product.product_type_id = $dt_product_id )
#	WHERE product.product_type_id <> $dt_product_id AND
#	    ( product.upc_ean = $upc
#          OR track_product.upc_ean = $upc
#          OR product.upc_alt = $upc
#          OR track_product.upc_alt = $upc )
#SQL
#
#	$sql .= sprintf( " AND album.album_id <> %s ", $dbo->DBQuote($albumID) )
#	    if( $albumID );

    my $sql = "SELECT count(product_id) " .
             "FROM product " .
             "WHERE ( upc_ean = $upc OR upc_alt = $upc ) ";

    if( $albumID ) {
       my $albumSQL = $dbo->DBQuote($albumID);
       $sql .= "AND ( " .
                 "( product_type_id <> 4 AND asset_id <> $albumSQL ) OR " .
                 "( product_type_id  = 4 AND asset_id NOT IN ( " .
                 "SELECT track_id FROM track WHERE album_id = $albumSQL ) )) ";
    }

	Common::Log::Debug( "QUERY: $sql" );
	my $sth = $dbo->DoCmd($sql);
	my ($count) = $sth->fetchrow_array();
	Common::Log::Debug( "COUNT: $count" );

	return $count;
}


# Check to see if a UPC code has been used by another DA product for this album.
sub UPCExistsDA
{
    my ($class, %args) = @_;
    
    my $dbo = Common::RSApp::GetClientDB();
    
    my $upc = $dbo->DBQuote($args{upc});
    my $albumID = $args{albumID};
    my $productID = $args{productID};

    my $sql = "SELECT count(product_id) " .
             "FROM product " .
             "WHERE ( upc_ean = $upc OR upc_alt = $upc ) " .
             "AND product_type_id = " . kProductTypeDigital . " " .
             "AND asset_id = $albumID ";
    
    if ($productID) 
    {
        $sql .= "AND product_id != $productID";  
    }
    
    Common::Log::Debug( "QUERY: $sql" );
    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();
    Common::Log::Debug( "COUNT: $count" );
    
    return $count;
}


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


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

    my $where;

    if($searchTerm)
    {
        $where = "WHERE product_type_id != " . RPS::DB::Item::Product::kProductTypeDigitalTrack 
            . " AND ( " . $class->SearchSyntax(field => "product.title", value => $searchTerm)
            . " OR ". $class->SearchSyntax(field => "upc_ean", value => $searchTerm)
            . " OR ". $class->SearchSyntax(field => "artist.name", value => $searchTerm) . " )";
    }
    else
    {
        die "no valid param passed to album search";
    }

    my $sql = "SELECT product.* FROM product LEFT JOIN album ON album_id = asset_id LEFT JOIN artist ON artist.artist_id=album.artist_id $where ORDER BY title";

    Common::Log::Debug( "QUERY: $sql" );
    return $class->GetAll($sql);
}


sub GetAllExceptDigitalTrackProducts
{
    my ($class) = @_;
    
    my $sql = "SELECT * from ".kTable." WHERE product_type_id!=".kProductTypeDigitalTrack;
    
    return $class->SUPER::GetAll($sql);
}

1;