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


# This package will just contain some static methods to create and read the sales file.
# And it will provide constants to use as array indexes.
# But that's about it...

package RPS::ArtistRoyalty::Fast::Static::Sales;

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 DB_File;

use Common::Log;
use Common::Util;

use Raptor::DB::Item::Sale;
use RPS::DB::Item::Product;
use RPS::File::Sale;
use RPS::DB::Item::DistributionFee;


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

use constant kSaleID                => 0;
use constant kProductID             => 1;
use constant kCountryCode           => 2;
use constant kChannel               => 3;
use constant kPriceLevel            => 4;
use constant kProductType           => 5;
use constant kFormatType            => 6;
use constant kSales                 => 7;
use constant kReturns               => 8;
use constant kTotalRevenue          => 9;
use constant kConversionRate        => 10;
use constant kPrice                 => 11;
use constant kAveragePrice          => 12;
use constant kRetailPrice           => 13;
use constant kWholesalePrice        => 14;
use constant kDistributionFee       => 15;
use constant kDateEnd               => 16;

sub FileName { 'sales' }
sub CacheQueryTableList { "'sale','product','user_input_dist_fee','file'"};

# select GROUP_CONCAT(UPDATE_TIME) FROM information_schema.tables WHERE TABLE_SCHEMA='C_CMH_RECORDS' AND TABLE_NAME IN ('sale', 'sale_run_map');
#select MD5(GROUP_CONCAT(UPDATE_TIME)) FROM information_schema.tables WHERE TABLE_SCHEMA='C_CMH_RECORDS' AND TABLE_NAME IN ('sale', 'sale_run_map');

# Let's just pass endDate to everybody...
# Moving this to the base class.
#
#sub Create
#{
#    my ($class, $dataPath, $payorID, $endDate) = @_;
#
#
#    mkpath($dataPath) unless -d $dataPath;
#    my $path = "$dataPath/".$class->FileName();
#
#    $class->_Create($path, $payorID, $endDate);
#}

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

    open SALEFILE, "> $filename" or die "ERROR: Unable to open $filename for writing: $!";

    
    my %productType;

Log->warn("... fetching all products");
    my $allProducts = RPS::DB::Item::Product->GetAll();
    while (my $product = $allProducts->next())
    {
        $productType{$product->product_id()} = _translate_product_type_id_to_product_type($product->product_type_id());
    }
    
Log->warn("... fetching distribution fee");
    my %fileFormatDistFee;
    my $allFees = RPS::DB::Item::DistributionFee->GetAll();
    while (my $fee = $allFees->next())
    {
        $fileFormatDistFee{$fee->file_id()}{$fee->associated_format()} = $fee->dist_fee_pct();
    }
    
    # I ought to just left join to the user_input_dist_fee table, but instead for now
    # I'll query it here, and fill it in as we go.

    #
    # !!! I need to get the correct price_level.
    # !!! Sometimes the sale has it, sometimes I need the product's default, and
    # !!! sometimes I will need to hard-code it.
    #
    # !!! I think I'll need to join product on product_id so I can get the product_type_id and the default_price_level_id.
    # !!! Then I can take care of getting it right up front.
    #
    # !!! Well, this query is taking a ton of time, since I added that join.
    # !!! So, forget that, we'll do it later.
    #
    # !!! But, I still kinda want to join product on product_id, because I want to translate the type.
    my %args;
    $args{sortByProductID} = 1;
    if ($endDate)
    {
        $args{ending_sale_date} = $endDate;
    }


    # JPK - Rather than use the Raptor::DB::Item::Sale interface, we currently are forced
    # to use DBI directly.  Here's why...
    # This query is obviously going to have a massive result set (sale is a big table).
    # It turns out that the DBD::mysql driver (which is the final interface between perl and the mysql C api)
    # is set up by _default_ to use 'mysql_store_result()' to retrieve the data from the database.
    # This means that the ENTIRE RESULT SET is read straight into memory, and subsequent calls to fetch_row simply
    # return data from this memory buffer.
    # 
    # This is a problem if the size of the result set exceeds the amount of RAM on your box...
    #
    # There is, luckily, another option.  We can tell the driver to use 'mysql_use_result()', which will instead
    # do no buffering and fetch each row as requested.   This is actually slightly faster (since the driver doesn't have
    # to allocate any memory), but there is a catch - While the request is 'active', the table(s) being queried are locked.
    # So you don't want to waste a lot of time when reading the data, or allow execution to pause.
    # 
    # In this particular use, we're streaming the data straight out to a file, so it's pretty much ideal.
    #
    # Now, it would be nice if we could use the Common::RSDB / DB::Item interface here.  However, we currently do not
    # have the proper interface in place to allow us to pass these sort of configuration parameters through.
    # Yes, we can fetch the Client DB object, and dereference the database handle, but I have been having a tough time
    # getting the _timing_ correct to allow it to actually work.
    #
    # So I'm coding around that issue by stepping down a level and just using the DBO directly.
    #
    # SAH - Update 9/28/2015, this is now activated via new Common::RSDB::DirectIO class

    my $dbo = Common::RSApp::GetClientDB();
    my $direct = $dbo->DirectIO();

	my $sql = "SELECT * from sale WHERE artist_royalty_status<>2"
	 . " AND file_id in (select file_id from file where period_id > 0) "
	 . " AND free<>1"
     . " AND product_type not in ('L')"
	 . " AND ( price <> 0 or product_type not in ('T', 'A'))";

    if( $endDate ) {
		$sql .= " AND date_end <= " . $dbo->DBQuote($endDate);
	}

    $sql .= " ORDER BY product_id";


    my $sth = $dbo->DoCmd($sql);

    while (my $sale = $sth->fetchrow_hashref())
    {
        my $sales = $sale->{sales};
        my $returns = $sale->{returns};
        my $units = $sale->{units};

        if (_isDigital($sale))
        {
            $sales = $units;
        }
        elsif ($units < 0)
        {
            $returns = -1 * $units;
        }

        my $fee = $fileFormatDistFee{$sale->{file_id}}{$sale->{format_type}};

        # To make date comparison fast, convert the date_end to epoch seconds.
        # !!! If we use 'raw' queries, we can get the database to return it in this format,
        # !!! rather than having to spend zorch converting it here...
        # !!! Using UNIX_TIMESTAMP() ...

        print SALEFILE join("\t", 
            $sale->{sale_id}, 
            $sale->{product_id}, 
            uc $sale->{country_code}, # Force country codes to uppercase; see FB16152.
            $sale->{channel}, 
            $sale->{price_level},
            $productType{$sale->{product_id}},
            $sale->{format_type}, 
            $sales,
            $returns, 
            $sale->{total_revenue}, 
            $sale->{conversion_rate}, 
            $sale->{price}, 
            $sale->{average_price},
            $sale->{retail_price},
            $sale->{wholesale_price},
            $fee,
            $sale->{date_end},
            ) . "\n";
    }
}


sub GetSales
{
    my ($class, $dataPath, $firstSale, $lastSale) = @_;


    # Currently the sales file is a text file.
    # We'll use DB_RECNO, which allows us to access the file through
    # a tied array ref.
    #
    my $filename = "$dataPath/".$class->FileName();

    my @array;
    tie @array, "DB_File", $filename, O_RDONLY, 0666, $DB_RECNO or die "Error opening $filename: $!\n";

    return \@array;
}

sub _translate_product_type_id_to_product_type
{
    my ($productTypeID) = @_;

    return
    $productTypeID == RPS::DB::Item::Product::kProductTypeDigital       ? RPS::File::Sale::TYPE_ALBUM :
    $productTypeID == RPS::DB::Item::Product::kProductTypeDigitalTrack  ? RPS::File::Sale::TYPE_TRACK :
    $productTypeID == RPS::DB::Item::Product::kProductTypeLP            ? RPS::File::Sale::TYPE_LP :
    $productTypeID == RPS::DB::Item::Product::kProductTypeLP5           ? RPS::File::Sale::TYPE_LP5 :
    $productTypeID == RPS::DB::Item::Product::kProductTypeCD            ? RPS::File::Sale::TYPE_CD :
    $productTypeID == RPS::DB::Item::Product::kProductTypeVHS           ? RPS::File::Sale::TYPE_VHS :
    $productTypeID == RPS::DB::Item::Product::kProductTypeCass          ? RPS::File::Sale::TYPE_CASS :
    $productTypeID == RPS::DB::Item::Product::kProductTypeEP            ? RPS::File::Sale::TYPE_EP :
    $productTypeID == RPS::DB::Item::Product::kProductTypeDVD           ? RPS::File::Sale::TYPE_DVD :
    $productTypeID == RPS::DB::Item::Product::kProductTypeBluRay        ? RPS::File::Sale::TYPE_BLURAY :
    $productTypeID == RPS::DB::Item::Product::kProductTypeCassSingle    ? RPS::File::Sale::TYPE_CAS_SIN :
    $productTypeID == RPS::DB::Item::Product::kProductTypeCDSingle      ? RPS::File::Sale::TYPE_CD_SIN :
    $productTypeID == RPS::DB::Item::Product::kProductTypeDVDCDSet      ? RPS::File::Sale::TYPE_DVD_CD_SET :
    $productTypeID == RPS::DB::Item::Product::kProductTypeDblCD         ? RPS::File::Sale::TYPE_DBL_CD :
    0;
}

sub _isDigital
{
    my ($sale) = @_;


    if (RPS::File::Sale::TYPE_ALBUM eq $sale->{product_type}
     || RPS::File::Sale::TYPE_TRACK eq $sale->{product_type})
    {
        return 1;
    }

    return 0;
}


1;