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

package RPS::ArtistRoyalty::Fast::Static::ProductTerms;

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 Common::RSApp;

use Data::Dumper;
use DB_File;

use Common::Log;

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

use constant kProductID            => 0;
use constant kArtistContractTermID => 1;
use constant kArtistContractID     => 2;
use constant kPriority             => 3;
use constant kTrackID              => 4;
use constant kAlbumID              => 5;
use constant kDefaultPriceLevelID  => 6;
use constant kProductTypeID        => 7;
use constant kProrateTrackCount    => 8;
use constant kIsDigital            => 9;
use constant kCrossed              => 10;
use constant kTermStart            => 11;
use constant kTermEnd              => 12;
use constant kRateTypeID           => 13;

sub FileName { 'product_terms' }

sub CacheQueryTableList {
    "'product','track','album','track_contract','album_contract','artist_payee','new_artist_contract','new_artist_contract_term'";
}

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

    #
    # This data is a bit too big to use a serialized hash.
    # But it is critical that we maintain the terms' sort order.
    # So we'll use a Berkeley DB_Hash to store a serialized array of arrays
    # for each product_id.
    #

    my %hash;
    $DB_BTREE->{'flags'} = R_DUP;
    tie %hash, "DB_File", $filename, O_RDWR | O_CREAT, 0666, $DB_HASH or die "Error opening $filename: $!\n";

    # This configures Dumper to use a format that can be eval'd directly into a variable.
    # Otherwise it lards it up with additional notation.
    #
    $Data::Dumper::Terse = 1;

    my $dbo = Common::RSApp::GetClientDB();

    # A UNION would serve us in good stead here.
    # I want to get:
    #  all the track_contract terms for each track product
    #  all the track_contract terms for each track on each album product
    #  all the album_contract terms for each album product
    #

    # Let's also add a flag that tells us whether the product is digital, which we will
    # use to decide whether it holds reserves or not.
    #

    # What about cross-collateralized?  We'll need to know that.
    # I think we can attach that state here, and pass it along.
    #
    my @unionQueries;

    my $trackProductTrackContractSQL = qq/
    SELECT
    product.product_id,
    new_artist_contract_term.artist_contract_term_id,
    new_artist_contract_term.artist_contract_id,
    new_artist_contract_term.priority,
    track.track_id,
    track.album_id,
    product.default_price_level_id,
    product.product_type_id,
    0,
    CASE product.product_type_id
        WHEN 3 THEN 1
        WHEN 4 THEN 1
        WHEN 15 THEN 1
        ELSE 0
        END,
    track_contract.cross_collateralized,
    new_artist_contract.term_start,
    new_artist_contract.term_end,
    new_artist_contract_term.contract_rate_type_id
    FROM product
    join track on (product.asset_id = track.track_id)
    join album on (album.album_id = track.album_id)
    join track_contract on (track_contract.track_id = track.track_id)
    join new_artist_contract_term on (track_contract.artist_contract_id = new_artist_contract_term.artist_contract_id)
    join new_artist_contract on (new_artist_contract_term.artist_contract_id = new_artist_contract.artist_contract_id)
    join artist_payee using (artist_payee_id)
    WHERE product_type_id=4 AND (new_artist_contract_term.inactive = 0 OR new_artist_contract_term.inactive IS NULL) AND payor_id=$payorID
    AND artist_payee.status in (1,2)
    AND track_contract.status=1
    /;
    push @unionQueries, $trackProductTrackContractSQL;

    my $trackProductAlbumContractSQL = qq/
    SELECT
    product.product_id,
    new_artist_contract_term.artist_contract_term_id,
    new_artist_contract_term.artist_contract_id,
    new_artist_contract_term.priority,
    track.track_id,
    track.album_id,
    product.default_price_level_id,
    product.product_type_id,
    0,
    CASE product.product_type_id
        WHEN 3 THEN 1
        WHEN 4 THEN 1
        WHEN 15 THEN 1
        ELSE 0
        END,
    album_contract.cross_collateralized,
    new_artist_contract.term_start,
    new_artist_contract.term_end,
    new_artist_contract_term.contract_rate_type_id
    FROM product
    join track on (product.asset_id = track.track_id)
    join album on (album.album_id = track.album_id)
    join album_contract on (album_contract.album_id = album.album_id)
    join new_artist_contract_term on (album_contract.artist_contract_id = new_artist_contract_term.artist_contract_id)
    join new_artist_contract on (new_artist_contract_term.artist_contract_id = new_artist_contract.artist_contract_id)
    join artist_payee using (artist_payee_id)
    WHERE product_type_id=4 AND (new_artist_contract_term.inactive = 0 OR new_artist_contract_term.inactive IS NULL) and payor_id=$payorID
    AND artist_payee.status in (1,2)
    AND album_contract.status=1
    /;
    push @unionQueries, $trackProductAlbumContractSQL;

    # !!! So, because these queries are product_type_id specific, I'm not going to get the terms
    # !!! for a product/contract more than once for any product.
    # !!! The assumption of one contract association per product is built in.
    # !!! This seems to be at odds with how the old code works.  But, which is actually _correct_ ?
    # !!! Well, we easily allow this situation.  So, that means we'll need to be able to pay it correctly.
    #
    # I'll have to make changes.   The mapping loop doesn't seem to work right.   I'm still a little shaky on
    # the exact logical sequence here - Need to re-examine that statement.
    # What the old code is 'duplicating' is when we have a track sale.  If we have an album-level contract, and
    # a track-level contract, it looks like we essentially pay the track twice.
    # -> Ultimately it's the same term that gets hit - It just needs to be counted _twice_.   My queries here
    #    do not allow that to happen, which is an issue.
    # !!! So, if I can somehow determine not just the possible terms, but the 'multiplier', that would be good.
    # !!! How many can there be?  Can it ever be more than 2?
    # --> what about an album sale?   Those don't appear to be getting multiplied in the old code.
    #     if we have an album contract and several track contracts, shouldn't we see 'more'?  Maybe we are?
    #     Run some runs with single sales, see how it comes through.
    #
    # Ok, it looks like this code is doing it CORRECTLY, and the old code is buggy.  So, I'm going to leave this as-is.

    my $albumProductAlbumContractSQL = qq/
    SELECT
    product.product_id,
    new_artist_contract_term.artist_contract_term_id,
    new_artist_contract_term.artist_contract_id,
    new_artist_contract_term.priority,
    0,
    album.album_id,
    product.default_price_level_id,
    product.product_type_id,
    0,
    CASE product.product_type_id
        WHEN 3 THEN 1
        WHEN 4 THEN 1
        WHEN 15 THEN 1
        ELSE 0
        END,
    album_contract.cross_collateralized,
    new_artist_contract.term_start,
    new_artist_contract.term_end,
    new_artist_contract_term.contract_rate_type_id
    FROM product
    join album on (album.album_id = product.asset_id)
    join album_contract on (album_contract.album_id = album.album_id)
    join new_artist_contract_term on (album_contract.artist_contract_id = new_artist_contract_term.artist_contract_id)
    join new_artist_contract on (new_artist_contract_term.artist_contract_id = new_artist_contract.artist_contract_id)
    join artist_payee using (artist_payee_id)
    WHERE product_type_id<>4 AND (new_artist_contract_term.inactive = 0 OR new_artist_contract_term.inactive IS NULL) and payor_id=$payorID
    AND artist_payee.status in (1,2)
    AND album_contract.status=1
    /;
    push @unionQueries, $albumProductAlbumContractSQL;

    # Note how we fetch the prorate only for album products attached via track contracts.
    #
    my $albumProductTrackContractSQL = qq/
    SELECT
    product.product_id,
    new_artist_contract_term.artist_contract_term_id,
    new_artist_contract_term.artist_contract_id,
    new_artist_contract_term.priority,
    track_contract.track_id,
    album.album_id,
    product.default_price_level_id,
    product.product_type_id,
    track_contract.prorate_track_count,
    CASE product.product_type_id
        WHEN 3 THEN 1
        WHEN 4 THEN 1
        WHEN 15 THEN 1
        ELSE 0
        END,
    track_contract.cross_collateralized,
    new_artist_contract.term_start,
    new_artist_contract.term_end,
    new_artist_contract_term.contract_rate_type_id
    FROM product
    join album on (album.album_id = product.asset_id)
    join track on (track.album_id = album.album_id)
    join track_contract on (track_contract.track_id = track.track_id)
    join new_artist_contract_term on (track_contract.artist_contract_id = new_artist_contract_term.artist_contract_id)
    join new_artist_contract on (new_artist_contract_term.artist_contract_id = new_artist_contract.artist_contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    WHERE product_type_id<>4 AND (new_artist_contract_term.inactive = 0 OR new_artist_contract_term.inactive IS NULL) and payor_id=$payorID
    AND artist_payee.status in (1,2)
    AND track_contract.status=1
    /;
    push @unionQueries, $albumProductTrackContractSQL;

    # !!! Changing the ordering.   Order by track before priority.
    #
    my $bigSql = "SELECT * FROM ( " . join( "\nUNION\n", @unionQueries ) . " ) AS subq ORDER BY 1,3,5,4";

    #    my $bigSql = "SELECT * FROM ( " . join ("\nUNION\n", @unionQueries) . " ) AS subq ORDER BY 1,3,4,5";
    my $sth = $dbo->DoCmd($bigSql);

    # Note that fetchrow_array ref returns a reference
    # to an array that the DBI layer _reuses_.  So you cannot
    # simply stash that away for later reference.
    # That's why we call fetchrow_array, which gives us our
    # own array.
    #
    # The data is sorted by product_id, contract_id, priority and track_id.
    # So we'll accumulate all the data rows for every product_id in an array, then
    # serialize that array into the database.
    #
    my $lastProductID = undef;
    my $arrayRef      = undef;
    while ( my @array = $sth->fetchrow_array() ) {
        if ( $lastProductID != $array[kProductID] ) {
            if ($lastProductID) {
                $hash{$lastProductID} = Dumper($arrayRef);
            }
            $arrayRef      = [];
            $lastProductID = $array[kProductID];
        }

        push @$arrayRef, \@array;
    }
    if ($lastProductID) {
        $hash{$lastProductID} = Dumper($arrayRef);
    }

    untie %hash;
}

sub GetProductTermData {
    my ( $class, $dataPath ) = @_;

    my $filename = "$dataPath/" . $class->FileName();

    my %hash;
    my $dbObj = tie %hash, "DB_File", $filename, O_RDONLY, 0666, $DB_HASH or die "Error opening $filename: $!\n";

    return \%hash;
}

1;