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