#---------------------------------------------------------------
# ____                   _ _         ____  _                    
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___ 
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/                            
#
# 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;