#------------------------------------------------------------
# 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::ContractTermSource;
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 contract term source id.
#
my %gProductContractTermSourceMap = (
    RPS::DB::Item::Product::kProductTypeDigital() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceAllDigital()          => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalAlbum()        => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalTrack()        => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalStream()       => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalTethered()     => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceRingtone()            => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceMasterLicense()       => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceSyncLicense()         => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceJukebox()             => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDualDownload()        => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceBackground()          => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceVPD()                 => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalAlbumPremium() => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalTrackPremium() => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalAlbumUpgrade() => 1,
        RPS::DB::Item::ContractTermSource::kContractTermSourceDigitalTrackUpgrade() => 1,
    },
    RPS::DB::Item::Product::kProductTypeBluRay() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceBluRay() => 1,
    },
    RPS::DB::Item::Product::kProductTypeCass() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceCassette() => 1,
    },
    RPS::DB::Item::Product::kProductTypeCassSingle() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceCassetteSingle() => 1,
    },
    RPS::DB::Item::Product::kProductTypeCD() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceCD() => 1,
    },
    RPS::DB::Item::Product::kProductTypeDblCD() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceDblCD() => 1,
    },
    RPS::DB::Item::Product::kProductTypeCDSingle() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceCDSingle() => 1,
    },
    RPS::DB::Item::Product::kProductTypeDVD() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceDVD() => 1,
    },
    RPS::DB::Item::Product::kProductTypeDVDCDSet() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceDVDCDSet() => 1,
    },
    RPS::DB::Item::Product::kProductTypeEP() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourcePerformanceIncome() => 1,
    },
    RPS::DB::Item::Product::kProductTypeLP() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceLP() => 1,
    },
    RPS::DB::Item::Product::kProductTypeLP5() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceLP5() => 1,
    },
    RPS::DB::Item::Product::kProductTypeVHS() => {
        RPS::DB::Item::ContractTermSource::kContractTermSourceVHS() => 1,
    }
);

sub _termMatchesProduct {
    my ( $term, $product ) = @_;

    my $contractTermSourceID = $term->contract_term_source_id;
    my $productTypeID = $product->product_type_id;

    return undef unless $contractTermSourceID && $productTypeID;

    # Checking if contract term source is 'All Physical' or 'All'
    if (($contractTermSourceID == RPS::DB::Item::ContractTermSource::kContractTermSourceAllPhysical() && ($productTypeID != RPS::DB::Item::ContractTermSource::kContractTermSourceAllDigital())) ||
         $contractTermSourceID == RPS::DB::Item::ContractTermSource::kContractTermSourceAll()) {
        return 1;
    }
    #otherwise checking if product is digital or exact match our term source
    elsif ( $gProductContractTermSourceMap{$productTypeID} && $gProductContractTermSourceMap{$productTypeID}{$contractTermSourceID} ) {
        return 1;
    }

    return undef;
}

1;