#!/usr/bin/perl

use strict;
use Getopt::Std;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::RSApp;
use Common::RSMath;

use lib '/app/tools/rps/lib';

use RPS::DB::Item::CALicenseReserve;
use RPS::DB::Item::CAMechanicalRun;
use RPS::DB::Item::CAMechanicalStatementItem;
use RPS::DB::Item::Product;
use RPS::DB::Item::Track;
use RPS::DB::Item::CATrackLicense;

# Allow tuning of the verbosity of our output.
#
my $gVerbosityLevel = 1;

# Keep track of reserves we've seen
#
my %gSeenReserve;

# Parse the command-line.
#
my %options;
parseCommandLine( \%options );

createMechanicalReservePipelineReport( $options{clientID}, $options{runID} );

#
# --- Subroutines
#

sub createMechanicalReservePipelineReport {
    my ( $clientID, $runID ) = @_;

    # Instantiate the application singleton object.
    #
    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    my %gPipeline;

    my $run       = RPS::DB::Item::CAMechanicalRun->Lookup( ca_mechanical_run_id => $runID );
    my $runStatus = $run->status();
    my $payorID   = $run->payor_id;

    _report( "---checking for reserves in run: id = " . $runID );

    _report( "---run status = " . $runStatus );

    _report( "---payor id = " . $payorID );

    # Let's grab all of the reserves from the current run first
    #
    my $currentReserves = RPS::DB::Item::CALicenseReserve->GetByMechanicalRunID($runID);
    my $currentTaken    = 1;

    while ( my $runReserveItem = $currentReserves->next() ) {
        addReservesToHash( $runReserveItem, $currentTaken, $runStatus, \%gPipeline );

    }

    if ( $runStatus == 2 ) {

        # Now let's grab all of the reserves liquidated in the current run.
        # This will only work if the run has been committed.
        my $currentLiquidatedReserves = RPS::DB::Item::CALicenseReserve->GetLiquidatedByMechanicalRunID($runID);
        $currentTaken = 0;

        while ( my $runReserveItem = $currentLiquidatedReserves->next() ) {
            addReservesToHash( $runReserveItem, $currentTaken, $runStatus, \%gPipeline );

        }
    }

    # Now we'll grab all of the reserves from the committed runs.
    # We're are also going to pass in the current run id, so that we can run this report
    # even if the current run has been committed.
    # However, if subsequent runs have been completed/committed, this report will not be accurate.
    #
    my $committedReserves = RPS::DB::Item::CALicenseReserve->GetAllCommittedByPayorID( $payorID, $runID );    # TODO
    $currentTaken = 0;

    while ( my $runReserveItem = $committedReserves->next() ) {
        addReservesToHash( $runReserveItem, $currentTaken, $runStatus, \%gPipeline );

    }

    # Write the data out to the table
    #

    foreach my $regionID ( keys %gPipeline ) {

        my $publisherIDHash = $gPipeline{$regionID};

        foreach my $publisherID ( keys %$publisherIDHash ) {

            my $albumIDHash = $publisherIDHash->{$publisherID};

            foreach my $albumID ( keys %$albumIDHash ) {

                my $trackIDHash = $albumIDHash->{$albumID};

                foreach my $trackID ( keys %$trackIDHash ) {

                    my $productUPCHash = $trackIDHash->{$trackID};

                    foreach my $upc ( keys %$productUPCHash ) {

                        my $data = $productUPCHash->{$upc};

                        _report("---processing albumID($albumID) trackID($trackID) upc($upc)");
                        _report( "-----total revenue = " . $data->{totalRevenue} );
                        _report( "-----total units = " . $data->{totalUnits} );

                        my $insertSQL =
                            "INSERT INTO ca_mechanical_reserve_pipeline SET "
                          . 'ca_mechanical_run_id=?,'
                          . 'ca_publisher_id=?,'
                          . 'album_id=?,'
                          . 'track_id=?,'
                          . 'upc=?,'
                          . 'region_id=?,'
                          . 'total_revenue=?,'
                          . 'total_units=?,'
                          . 'revenue_0=?,'
                          . 'units_0=?,'
                          . 'revenue_1=?,'
                          . 'units_1=?,'
                          . 'revenue_2=?,'
                          . 'units_2=?,'
                          . 'revenue_3=?,'
                          . 'units_3=?,'
                          . 'revenue_4=?,'
                          . 'units_4=?,'
                          . 'revenue_5=?,'
                          . 'units_5=?,'
                          . 'revenue_6=?,'
                          . 'units_6=?,'
                          . 'revenue_7=?,'
                          . 'units_7=?,'
                          . 'revenue_8=?,'
                          . 'units_8=?,'
                          . 'revenue_9=?,'
                          . 'units_9=?';

                        my $dbo = Common::RSApp::GetClientDB();
                        $dbo->DoCmdWithPlaceholders(
                            $insertSQL,
                            [
                                $runID,              $publisherID,      $albumID,              $trackID,
                                $upc,                $regionID,         $data->{totalRevenue}, $data->{totalUnits},
                                $data->{0}{revenue}, $data->{0}{units}, $data->{1}{revenue},   $data->{1}{units},
                                $data->{2}{revenue}, $data->{2}{units}, $data->{3}{revenue},   $data->{3}{units},
                                $data->{4}{revenue}, $data->{4}{units}, $data->{5}{revenue},   $data->{5}{units},
                                $data->{6}{revenue}, $data->{6}{units}, $data->{7}{revenue},   $data->{7}{units},
                                $data->{8}{revenue}, $data->{8}{units}, $data->{9}{revenue},   $data->{9}{units}
                            ]
                        );
                    }    # product
                }    # track
            }    # album
        }    # publisher
    }    # region
}

sub addReservesToHash {
    my ( $runReserveItem, $currentTaken, $runStatus, $pipeline ) = @_;

    my $reserveID = $runReserveItem->ca_license_reserve_id;

    return if ( $gSeenReserve{$reserveID} );
    $gSeenReserve{$reserveID} = 1;

    my $reserve = RPS::DB::Item::CALicenseReserve->Lookup( ca_license_reserve_id => $reserveID );

    # Get the license attached to the reserve
    #
    my $licenseID = $reserve->ca_track_license_id;
    my $license = RPS::DB::Item::CATrackLicense->Lookup( ca_track_license_id => $licenseID );

    my $regionID    = $license->region_id;
    my $trackID     = $license->track_id;
    my $publisherID = $license->ca_publisher_id;

    my $productID = $reserve->product_id;
    my $product = RPS::DB::Item::Product->Lookup( product_id => $productID );

    # Per Case FB15315, we should aggregate across all products.  The tricky part
    # is that we need to show the UPC on the report, and the UPC is not guaranteed
    # to be the same across all album products.  Hence, we'll aggregate based on
    # UPC...
    my $upc;

    # If this product has a parent_product_id, we should grab the upc from the parent.
    if ( $product->parent_product_id ) {
        my $parentProduct = RPS::DB::Item::Product->Lookup( product_id => $product->parent_product_id );
        $upc = $parentProduct->upc_ean;
    } else {
        $upc = $product->upc_ean;
    }

    # The product will most likely be an album-based product...
    my $albumID = _getAlbumIDFromProduct($product);

    my $reservePeriod = $reserve->periods_remaining;

    my $reserveUnits   = $reserve->units;
    my $effectiveRate  = $reserve->effective_rate;
    my $reserveRevenue = $reserveUnits * $reserve->effective_rate;

    my $liquidated = $reserve->liquidated_run_id;

    if ( $runStatus != 2 && $reservePeriod == 1 ) {
        $liquidated = 1;
    }

    if ( $runStatus == 2 ) {

        # I'm going to increment this by one to make room for the current
        # liquidations if we are dealing with a committed run.
        #
        $reservePeriod++;
    }

    my $incomeItemID = $reserve->original_statement_item_id;
    my $incomeItem = RPS::DB::Item::CAMechanicalStatementItem->Lookup( ca_mechanical_statement_item_id => $incomeItemID );
    my $units;
    my $grossSales;
    my $revenueReserved;
    my $reserveRate;
    my $totalUnitsReserved;
    my $liquidationPercentage;
    my $liquidationUnits;

    if ( !$liquidated ) {
        $pipeline->{$regionID}{$publisherID}{$albumID}{$trackID}{$upc}{totalUnits}   += $reserveUnits;
        $pipeline->{$regionID}{$publisherID}{$albumID}{$trackID}{$upc}{totalRevenue} += ( $reserveUnits * $effectiveRate );
    } else {

        #_report("-------found liquidations for this run: reserve id = $reserveID");
        # If these reserves are being liquidated, we'll set the reserve period to 1
        # so that they show up in the proper place in the hash.
        #
        $reservePeriod = 1;
    }

    $pipeline->{$regionID}{$publisherID}{$albumID}{$trackID}{$upc}{$reservePeriod}{units}   += $reserveUnits;
    $pipeline->{$regionID}{$publisherID}{$albumID}{$trackID}{$upc}{$reservePeriod}{revenue} += ( $reserveUnits * $effectiveRate );

    # We want to display a total for reserves taken in the current period.
    #
    if ( $currentTaken == 1 ) {
        $pipeline->{$regionID}{$publisherID}{$albumID}{$trackID}{$upc}{0}{units}   += $reserveUnits;
        $pipeline->{$regionID}{$publisherID}{$albumID}{$trackID}{$upc}{0}{revenue} += ( $reserveUnits * $effectiveRate );
    }

}    # addReservesToHash

sub _getAlbumIDFromProduct {
    my ($product) = @_;

    if ( RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id ) {
        my $trackID = $product->asset_id;
        my $track = RPS::DB::Item::Track->Lookup( track_id => $trackID );
        return $track->album_id;
    } else {
        return $product->asset_id;
    }
}

#
# Boring script stuff below...
#

sub parseCommandLine {
    my ($settings) = @_;

    my %opt;
    getopts( 'c:r:', \%opt );

    if ( !$opt{c} || !$opt{r} ) {
        usage();
        exit(1);
    }
    $settings->{clientID} = $opt{c};
    $settings->{runID}    = $opt{r};

}

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -r <run_id>\n";
    print STDERR "\n";
    print STDERR "Arguments:\n";
    print STDERR "\t-c <client_id>\t\t\tThe client_id of the client to process\n";
    print STDERR "\t-r <run_id>\tThe id of the CA mechanical run to process\n";
}

sub _report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}