#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# 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::DB::Item::ClientOptions;
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;
use constant kGrossRevenue    => 17;

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

    # Get the artist run options, if any
    my $options = RPS::DB::Item::ClientOptions->Lookup( name => 'artist_category_service' );
    Log->warn("Option artist_category_service: " . $options->value ) if( $options );

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

    my $sql = <<ENDSQL;
        SELECT
            sale_id,
            file_id,
            product_id,
            country_code,
            channel,
            price_level,
            format_type,
            product_type,
            sales,
            returns,
            units,
            total_revenue,
            conversion_rate,
            price,
            average_price,
            retail_price,
            wholesale_price,
            date_end,
            gross_revenue
        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'))
ENDSQL

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

        if( $options && _userSkip($options->value, $sale) ) {
            Log->info("  skipping category sale ". $sale->{sale_id} );
            next;
        }

        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},
            $sale->{gross_revenue},
        ) . "\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;
}

# _userSkip: returns true if the specified option(s) match the format type
sub _userSkip {
    my($optionString, $sale) = @_;
    return 1 if( $optionString =~ /w/i &&  # WEBCAST
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_WEBCAST        eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_WEBCAST    eq $sale->{format_type} ));
    return 1 if( $optionString =~ /s/i &&  # SDARS
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_SDARS          eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_SDARS      eq $sale->{format_type} ));
    return 1 if( $optionString =~ /b/i &&  # BES
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_BES            eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_BES        eq $sale->{format_type} ));
    return 1 if( $optionString =~ /c/i &&  # CABLERAD
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_CABLERAD       eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_CABLERAD   eq $sale->{format_type} ));
    return 1 if( $optionString =~ /p/i &&  # PES
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_PES            eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_PES        eq $sale->{format_type} ));
    return 1 if( $optionString =~ /a/i &&  # ADMIN TRUE UP
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_ATU            eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_ATU        eq $sale->{format_type} ));
    return 1 if( $optionString =~ /i/i &&  # INTERNATIONAL PROXIES
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_IP             eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_IP         eq $sale->{format_type} ));
    return 1 if( $optionString =~ /e/i &&  # SETTLEMENT
                 ( RPS::File::Sale::FORMAT_EPHEMERAL_SETTLEMENT     eq $sale->{format_type} ||
                   RPS::File::Sale::FORMAT_NON_EPHEMERAL_SETTLEMENT eq $sale->{format_type} ));
    return 0;
}

1;