#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------

package RPS::ArtistRoyalty::Fast::Static::ProductPrices;

use strict;
use Data::Dumper;
use File::Path;

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';
use lib '/app/tools/raptor/lib';
use Common::RSApp;

use Data::Dumper;
use DB_File;

use Common::Log;

use base 'RPS::ArtistRoyalty::Fast::Static';

use constant kProductID    => 0;
use constant kPriceLevelID => 1;
use constant kPriceID      => 2;
use constant kRetail       => 3;
use constant kWholesale    => 4;
use constant kPPD          => 5;

sub FileName            { 'product_prices' }
sub CacheQueryTableList { "'track','product','product_price','price'" }

sub _Create {
    my ( $class, $filename, $payorID ) = @_;

    # Going to use hash of hashes.

    my %hash;
    $DB_BTREE->{'flags'} = R_DUP;
    tie %hash, "DB_File", $filename, O_RDWR | O_CREAT, 0666, $DB_HASH or die "Error opening $filename: $!\n";

    # This configures Dumper to use a format that can be eval'd directly into a variable.
    # Otherwise it lards it up with additional notation.
    #
    $Data::Dumper::Terse = 1;

    my $dbo = Common::RSApp::GetClientDB();

    my $tempMapSql = qq/
    CREATE TEMPORARY TABLE zzProductPriceMap (
    track_product_id int(10) unsigned,
    album_product_id int(10) unsigned,
    KEY `track_product_id` (`track_product_id`),
    KEY `album_product_id` (`album_product_id`)
    ) 
    AS 
    SELECT track_product.product_id as track_product_id, album_product.product_id as album_product_id
    FROM track
    JOIN product as track_product ON (track_product.product_type_id = 4 AND track_product.asset_id=track.track_id)
    JOIN product as album_product ON (album_product.product_type_id = 3 AND album_product.product_id=track_product.parent_product_id)
    /;
    $dbo->DoCmd($tempMapSql);

    my $trackSql = qq/
    SELECT 
    product.product_id,
    product_price.price_level_id,
    product_price.price_id,
    price.retail,
    price.wholesale,
    price.ppd
    FROM product
    JOIN zzProductPriceMap ON (zzProductPriceMap.track_product_id = product.product_id)
    JOIN product_price ON (product_price.product_id = zzProductPriceMap.album_product_id)
    JOIN price USING (price_id)
    WHERE product_type_id=4
    /;

    my $albumSql = qq/
    SELECT 
    product.product_id,
    product_price.price_level_id,
    product_price.price_id,
    price.retail,
    price.wholesale,
    price.ppd
    FROM product
    JOIN product_price USING (product_id)
    JOIN price USING (price_id)
    WHERE product_type_id<>4
    /;

    my $bigSql = "SELECT * FROM ( $trackSql UNION $albumSql ) AS subq ORDER BY 1,2";
    my $sth    = $dbo->DoCmd($bigSql);

    # Note that fetchrow_array ref returns a reference
    # to an array that the DBI layer _reuses_.  So you cannot
    # simply stash that away for later reference.
    # That's why we call fetchrow_array, which gives us our
    # own array.
    #
    my $lastProductID = undef;
    my $hashRef       = undef;
    while ( my @array = $sth->fetchrow_array() ) {
        if ( $lastProductID != $array[kProductID] ) {
            if ($lastProductID) {
                $hash{$lastProductID} = Dumper($hashRef);
            }
            $hashRef       = {};
            $lastProductID = $array[kProductID];
        }

        $hashRef->{ $array[kPriceLevelID] } = \@array;
    }
    if ($lastProductID) {
        $hash{$lastProductID} = Dumper($hashRef);
    }

    untie %hash;
}

sub GetProductPriceData {
    my ( $class, $dataPath ) = @_;

    my $filename = "$dataPath/" . $class->FileName();

    my %hash;
    my $dbObj = tie %hash, "DB_File", $filename, O_RDONLY, 0666, $DB_HASH or die "Error opening $filename: $!\n";

    return \%hash;
}

1;