#------------------------------------------------------------
# Copyright (C) 2007 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------

package RPS::DB::Item::ReportQueries::ProductWithoutPrices;

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::ReportQueries;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::Product;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::ProductPrice;
use RPS::DB::Item::Album;
use RPS::DB::Item::Label;
use RPS::DB::Item::Artist;
use RPS::DB::Item::PriceLevel;
use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::ContractRateType;

use base 'RPS::DB::Item::ReportQueries';


sub _config
{
    return 
    {
        'label_name'        => {},
        'catalog_number'    => {},
        'album_title'       => {},
        'artist_name'       => {},
        'product_type'      => {},
        'product_id'        => {},
        'product_code'        => {},
        'upc_ean'               => {},
        'album_id'          => {},
        'missing_price_level' => {},
    };
}


sub GetProductsWithoutPrices
{
    my ($class) = @_;

    # First, create a temporary table for this report.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('DROP TABLE IF EXISTS zzproducts_no_prices');
    $dbo->DoCmd('CREATE TEMPORARY TABLE zzproducts_no_prices ('
    . '`label_name` varchar(255),'
    . '`catalog_number` varchar(255),'
    . '`album_title` varchar(255),'
    . '`artist_name` varchar(255),'
    . '`product_type` varchar(25),'
    . '`product_id` int(10) unsigned,'
    . '`product_code` varchar(25),'
    . '`upc_ean` varchar(25),'
    . '`album_id` int(10) unsigned,'
    . '`missing_price_level` varchar(25) )');



    # Go through every non-track product.
    #

    my $allProducts = RPS::DB::Item::Product->GetAll();
    while (my $product = $allProducts->next())
    {
        next if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id);


        # Skip inactive products
        #
        next if (RPS::DB::Item::Product::kProductStatusInactive == $product->product_status_id);


        my $productID = $product->product_id;
        my $albumID = $product->asset_id;


        # Skip this album if it is inactive
        #
        my $album = RPS::DB::Item::Album->Lookup(album_id => $albumID);
        next if (! $album);
        next if ($album->inactive());
        next if (! $album->artist_id());

        my $label = RPS::DB::Item::Label->Lookup(label_id => $album->label_id);
        my $artist = RPS::DB::Item::Artist->Lookup(artist_id => $album->artist_id);

        # Is there a contract attached?
        #
        my $albumContracts = RPS::DB::Item::AlbumContract->GetByAlbumID($albumID);
        while (my $albumContract = $albumContracts->next())
        {
            # Now we'll run through the actual contract terms.
            # For each term that matches this product and is a %retail or %wholesale term,
            # we'll make sure the price level exists.
            #
            my $artistContractID = $albumContract->artist_contract_id;
            my $terms = RPS::DB::Item::NewArtistContractTerm->GetByArtistContractID($artistContractID);
            while (my $term = $terms->next())
            {
                my $rateType = $term->contract_rate_type_id;

                next unless (RPS::DB::Item::ContractRateType::kRateTypePercentRetail == $rateType
                 || RPS::DB::Item::ContractRateType::kRateTypePercentWholesale == $rateType);

                next unless _termMatchesProduct($term, $product);

                
                # Figure out which price levels to check for.
                # 0 == all.
                #
                my $priceLevelID = $term->price_level_id;
                my @priceLevelIDs;
                if (! $priceLevelID)
                {
                    if (RPS::DB::Item::Product::kProductTypeDigital() == $product->product_type_id)
                    {
                        push @priceLevelIDs, 
                        (
                            RPS::DB::Item::PriceLevel::kPriceLevelAlbumDownload,
                            RPS::DB::Item::PriceLevel::kPriceLevelTrackDownload,
                        );
                    }
                    else
                    {
                        push @priceLevelIDs,
                        (
                            RPS::DB::Item::PriceLevel::kPriceLevelFull,
                            RPS::DB::Item::PriceLevel::kPriceLevelMid,
                            RPS::DB::Item::PriceLevel::kPriceLevelBudget,
                        );
                    }
                }
                else
                {
                    push @priceLevelIDs, $priceLevelID;
                }


                # All these price levels should, in fact, exist.
                #
                foreach my $testPriceLevelID (@priceLevelIDs)
                {
                    if (! RPS::DB::Item::ProductPrice->GetByProductIDPriceLevelID($productID, $testPriceLevelID))
                    {
                        my $productType = _idToProductType($product->product_type_id);
                        my $missingPriceLevel = _idToPriceLevel($testPriceLevelID);


                        my $sth = $dbo->DoCmdWithPlaceholders('SELECT COUNT(*) FROM zzproducts_no_prices WHERE'
                            . " label_name= ?"
                            . " AND catalog_number= ?"
                            . " AND album_title= ?"
                            . " AND artist_name= ?"
                            . " AND product_type= ?"
                            . " AND product_id= ?"
                            . " AND product_code= ?"
                            . " AND upc_ean= ?"
                            . " AND album_id= ?"
                            . " AND missing_price_level= ?",
                            [
                                $label->label_name,
                                $album->catalog_number,
                                $product->title,
                                $artist->name,
                                $productType,
                                $productID,
                                $product->product_code,
                                $product->upc_ean,
                                $album->album_id,
                                $missingPriceLevel,
                            ]
                        );

                        my $hr = $sth->fetchrow_hashref();
                        next unless (! $hr->{'COUNT(*)'});

                        # Missing!   You're on report, mister.
                        #
                        $dbo->DoCmdWithPlaceholders('INSERT INTO zzproducts_no_prices SET'
                        . " label_name= ?,"
                        . " catalog_number= ?,"
                        . " album_title= ?,"
                        . " artist_name= ?,"
                        . " product_type= ?,"
                        . " product_id= ?,"
                        . " product_code= ?,"
                        . " upc_ean= ?,"
                        . " album_id= ?,"
                        . " missing_price_level= ?",
                        [
                            $label->label_name,
                            $album->catalog_number,
                            $product->title,
                            $artist->name,
                            $productType,
                            $productID,
                            $product->product_code,
                            $product->upc_ean,
                            $album->album_id,
                            $missingPriceLevel,
                        ]
                        );
                    }
                }
            }
        }
    }

    # Now return a collection object to iterate over the new temporary table
    #
    my $sql = "SELECT * FROM zzproducts_no_prices ORDER BY label_name,album_title,artist_name,product_id";
    return $class->SUPER::GetAll($sql);
}


sub _idToProductType
{
    my ($id) = @_;

    # We have a table for this, might as well use it.
    #
    my $productTypeEntry = RPS::DB::Item::ProductType->Lookup(product_type_id => $id);
    if (! $productTypeEntry)
    {
        return '???';
    }
    return $productTypeEntry->description();
}


sub _idToPriceLevel
{
    my ($id) = @_;

    my $priceLevelEntry = RPS::DB::Item::PriceLevel->Lookup(price_level_id => $id);
    if (! $priceLevelEntry)
    {
        return '???';
    }
    return $priceLevelEntry->name();
}


# Going to create a hash table that maps product id and income source id.
# It isn't really more efficient than a big freakin' if/then/elsif code block,
# but for some reason I find it less irritating.
#
my %gProductIncomeSourceMap = 
(
    RPS::DB::Item::Product::kProductTypeLP() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceLP() => 1,
    },
    RPS::DB::Item::Product::kProductTypeLP5() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceLP5() => 1,
    },    
    RPS::DB::Item::Product::kProductTypeCD() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceCD() => 1,
    },
    RPS::DB::Item::Product::kProductTypeDigital() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbum() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrack() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalStream() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTethered() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceRingtone() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceMasterLicense() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceSyncLicense() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceJukebox() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDualDownload() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceBackground() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceVPD() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumPremium() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrackPremium() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumUpgrade() => 1,
        RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrackUpgrade() => 1,
    },
    RPS::DB::Item::Product::kProductTypeCass() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceCassette() => 1,
    },
    RPS::DB::Item::Product::kProductTypeCDSingle() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceCDSingle() => 1,
    },
    RPS::DB::Item::Product::kProductTypeDVDCDSet() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceDVDCDSet() => 1,
    },
    RPS::DB::Item::Product::kProductTypeDblCD() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceDblCD() => 1,
    },
    RPS::DB::Item::Product::kProductTypeBluRay() =>
    {
        RPS::DB::Item::IncomeSource::kIncomeSourceBluRay() => 1,
    },
#    RPS::DB::Item::Product::kProductTypeVHS() =>
#    {
#    },
#    RPS::DB::Item::Product::kProductTypeEP() =>
#    {
#    },
#    RPS::DB::Item::Product::kProductTypeDVD() =>
#    {
#    },
#    RPS::DB::Item::Product::kProductTypeCassSingle() =>
#    {
#    },
);



# This is where it gets a bit fuzzy....
#
sub _termMatchesProduct
{
    my ($term, $product) = @_;

    my $incomeSourceID = $term->income_source_id;
    
    # A NULL income source id can only belong to the default term.
    # Which will always be % Net Revenue.  Which we don't care about.
    return undef unless $incomeSourceID;
    

    my $productTypeID = $product->product_type_id;

    if ($gProductIncomeSourceMap{$productTypeID}
     && $gProductIncomeSourceMap{$productTypeID}{$incomeSourceID})
    {
        return 1;
    }

    return undef;
}



1;