#------------------------------------------------------------
# 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 = " . $class->quote($albumID);

    if ($productType) {
        $sql .= " AND product_type_id = " . $class->quote($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 = " . $class->quote($trackID);

    if ($productType) {
        $sql .= " AND product_type_id = " . $class->quote($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 = "  . $class->quote($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 = " . $class->quote($trackID) . " AND product_type_id=" . kProductTypeDigitalTrack;
    $sql .= " AND parent_product_id = " . $class->quote($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 = " . $class->quote($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 = " . $class->quote($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 = " . $class->quote($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 $productID = join ',', map { $class->quote($_) } grep {defined $_} @productID;

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

    return $count ? 0 : 1;
}

sub GetChildProductsByProductID {
    my ($class, $productID) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
          "SELECT product_id FROM "
        . kTable
        . " WHERE parent_product_id = "
        . $dbo->DBQuote($productID);

    my $sth = $dbo->DoCmd($sql);
    my @id;
    while ( my $aRes = $sth->fetchrow_arrayref ) {
        push @id, @$aRes;
     }

    return wantarray ? @id : \@id;
}

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 $albumID         = $dbo->DBQuote( $args{album_id} );

    my $sql = "SELECT product_id FROM " . kTable . " WHERE " . "asset_id = $albumID AND 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 = " . $dbo->DBQuote($albumID);

    if ($productID) {
        $sql .= "AND product_id != " . $dbo->DBQuote($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;