#---------------------------------------------------------------
# ____                   _ _         ____  _                    
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___ 
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/                            
#
# 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::LabelRoyalty::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 RPS::DB::Item::RegionCountryMap;


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

# !!! Figure out which sale columns we will actually need...
# sale_id
# product_id
# service_id  - we may want to bake in some joins to get service/distributor ids...
# file_id
# units
# price
# date_begin
# date_end
# conversion_rate
# country_code
# product_type
# format_type
# currency_code
# media_type
# free

# more about service_id:
# If the sale has a service_id, then use that for the service_id, and the file's service_id as the distributor_id.
# If the sale doesn't have a service_id, then use the file's service_id as the service_id, and there is no distributor_id.
# -> We can probably bake this into the query, eliminating a step later.



# !!! Artist Royalty version grabs the distribution fee - That's probably not a bad idea.


use constant kSaleID                => 0;
use constant kProductID             => 1;
use constant kServiceID             => 2;
use constant kDistributorID         => 3;
use constant kFileID                => 4;
use constant kUnits                 => 5;
use constant kPrice                 => 6;
use constant kDateBegin             => 7;
use constant kDateEnd               => 8;
use constant kConversionRate        => 9;
use constant kCountryCode           => 10;
use constant kCurrencyCode          => 11;
use constant kProductType           => 12;
use constant kFormatType            => 13;
use constant kMediaType             => 14;
use constant kFree                  => 15;


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


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

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

    my $runData = RPS::DB::Item::LabelRoyaltyRun->Lookup(label_royalty_run_id => $runID);
    my $endingSaleDate = $runData->ending_sale_date();
    
    # !!! Don't know if I need this bit of code, really.
    #
#    my %productType;
#    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());
#    }

    
    # !!! This does look like code I want to keep.
    #
    # !!! No, we get the fee from the label contract terms.
    #
#    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 also would like to translate the country code into a region id - Might as well do that here.
    #
#    my %countryRegion;
#    my $mapItems = RPS::DB::Item::RegionCountryMap->GetAll();
#    while (my $mapItem = $mapItems->next())
#    {
#        $countryRegion{ $mapItem->country_code() } = $mapItem->region_id;
#    }

    

    # JPK - Going to need to take the end date into account.
    # Also, we'll need to sort the sales by product_id...
    #
    # !!! I think I'll convert this to a DBI call, rather than use DB::Item.

    my $dbo = Common::RSApp::GetClientDB();
	my $sql = "SELECT "
     . " sale.sale_id,"
     . " sale.product_id,"
     . " IF(sale.service_id,sale.service_id,file.service_id),"
     . " file.service_id,"
     . " file.file_id,"
     . " sale.units,"
     . " sale.price,"
     . " sale.date_begin,"
     . " sale.date_end,"
     . " sale.conversion_rate,"
     . " sale.country_code,"
     . " sale.currency_code,"
     . " sale.product_type,"
     . " sale.format_type,"
     . " sale.media_type,"
     . " sale.free"
     . " FROM sale JOIN file USING (file_id) WHERE label_royalty_status<>2"
     . " AND file.period_id > 0"
	 . " AND free<>1";

    if ($endingSaleDate)
    {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }
    my $sth = $dbo->DoCmd($sql);
    while (my $row = $sth->fetchrow_arrayref())
#    while (my $sale = $allSales->next())
    {
        my $fileID = $row->[kFileID];
        my $formatType = $row->[kFormatType];

#        my $fee = $fileFormatDistFee{$fileID}{$formatType};

        # Swap out country code for region id
        #
#        $row->[kRegionID] = $countryRegion{ $row->[kRegionID] };
#        $row->[kRegionID] = 0 unless $row->[kRegionID];

        # 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", @$row, $fee) . "\n";
        print SALEFILE join("\t", @$row) . "\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;
}


1;