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

# Going to need every field in artist_contract_term_reserve.
# I will also want to join to new_artist_contract to get the payee_id.
# Probably join to product to make sure the product still exists.
# need the rate_type_id from the term, so join that too.
#
# This should just be a set of single records, so we can use a flat text file.

package RPS::ArtistRoyalty::Fast::Static::Reserves;

use strict;
use Data::Dumper;
use File::Path;

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

use Common::RSApp;
use Common::Log;
use DB_File;

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

use constant kArtistContractTermReserveID => 0;
use constant kArtistContractTermID        => 1;
use constant kIncomeSourceID              => 2;
use constant kRegionID                    => 3;
use constant kChannelID                   => 4;
use constant kPriceLevelID                => 5;
use constant kOriginalStatementItemID     => 6;
use constant kProductID                   => 7;
use constant kProductFormatID             => 8;
use constant kUnits                       => 9;
use constant kRevenue                     => 10;
use constant kPrice                       => 11;
use constant kRevenueBased                => 12;
use constant kEffectiveRate               => 13;
use constant kPeriodsRemaining            => 14;
use constant kAlbumID                     => 15;
use constant kTrackID                     => 16;
use constant kArtistContractID            => 17;
use constant kArtistPayeeID               => 18;
use constant kContractRateTypeID          => 19;
use constant kCrossed                     => 20;
use constant kProrateTrackCount           => 21;

sub FileName { 'reserves' }

sub CacheQueryTableList {
"'artist_contract_term_reserve','product','new_artist_contract_term','new_artist_contract','artist_royalty_income_item','artist_royalty_album','artist_royalty_statement','artist_royalty_run','album_contract','track_contract','artist_payee'";
}

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

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

    # We need to account for old reserves we ported over from the previous schema, that will have an 'original_statement_item_id' of 0,
    # as well as reserves from committed or closed runs.
    # Rather than do an 'OR', which is oddly slow, we'll union two queries.

    # !!! Challenge:   I need to get the cross_collateralized flag from track_contract or album_contract, as appropriate.
    # !!! I do have the track and album id... but I don't know explicitly whether this was a track or album contract.
    # !!! I might have to break each of these queries into 2 distinct queries in order to get what I'm looking for.

    # !!! I seem to be getting fooled here.  I'm bringing up reserves that are associated with non-existent track contracts, because
    # !!! there is an album contract someplace.
    # !!! Basically, if the reserve lists a track id, then this reserve is associated with a track contract.  At least that's how the old
    # !!! code figures it.

    my $baseSql = qq/
    SELECT
    artist_contract_term_reserve.artist_contract_term_reserve_id,
    artist_contract_term_reserve.artist_contract_term_id,
    artist_contract_term_reserve.income_source_id,
    artist_contract_term_reserve.region_id,
    artist_contract_term_reserve.channel_id,
    artist_contract_term_reserve.price_level_id,
    artist_contract_term_reserve.original_statement_item_id,
    artist_contract_term_reserve.product_id,
    artist_contract_term_reserve.product_format_id,
    artist_contract_term_reserve.units,
    artist_contract_term_reserve.revenue,
    artist_contract_term_reserve.price,
    artist_contract_term_reserve.revenue_based,
    artist_contract_term_reserve.effective_rate,
    artist_contract_term_reserve.periods_remaining,
    artist_contract_term_reserve.album_id,
    artist_contract_term_reserve.track_id,
    new_artist_contract.artist_contract_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract_term.contract_rate_type_id,
    album_contract.cross_collateralized,
    artist_contract_term_reserve.prorate_track_count
    FROM artist_contract_term_reserve
    JOIN product USING (product_id)
    JOIN new_artist_contract_term USING (artist_contract_term_id)
    JOIN new_artist_contract USING (artist_contract_id)
    JOIN artist_royalty_income_item ON (artist_royalty_income_item.artist_royalty_income_item_id = artist_contract_term_reserve.original_statement_item_id)
    JOIN artist_royalty_album USING (artist_royalty_album_id)
    JOIN artist_royalty_statement USING (artist_royalty_statement_id)
    JOIN artist_royalty_run USING (artist_royalty_run_id)
    JOIN album_contract ON (album_contract.album_id=artist_contract_term_reserve.album_id AND album_contract.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 new_artist_contract.payor_id=$payorID
    AND artist_contract_term_reserve.periods_remaining>0
    AND artist_royalty_run.status IN (2,5)
    AND album_contract.status=1
    AND artist_payee.status IN (1,2)
    AND artist_contract_term_reserve.track_id=0
    UNION
    SELECT
    artist_contract_term_reserve.artist_contract_term_reserve_id,
    artist_contract_term_reserve.artist_contract_term_id,
    artist_contract_term_reserve.income_source_id,
    artist_contract_term_reserve.region_id,
    artist_contract_term_reserve.channel_id,
    artist_contract_term_reserve.price_level_id,
    artist_contract_term_reserve.original_statement_item_id,
    artist_contract_term_reserve.product_id,
    artist_contract_term_reserve.product_format_id,
    artist_contract_term_reserve.units,
    artist_contract_term_reserve.revenue,
    artist_contract_term_reserve.price,
    artist_contract_term_reserve.revenue_based,
    artist_contract_term_reserve.effective_rate,
    artist_contract_term_reserve.periods_remaining,
    artist_contract_term_reserve.album_id,
    artist_contract_term_reserve.track_id,
    new_artist_contract.artist_contract_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract_term.contract_rate_type_id,
    track_contract.cross_collateralized,
    artist_contract_term_reserve.prorate_track_count
    FROM artist_contract_term_reserve
    JOIN product USING (product_id)
    JOIN new_artist_contract_term USING (artist_contract_term_id)
    JOIN new_artist_contract USING (artist_contract_id)
    JOIN artist_royalty_income_item ON (artist_royalty_income_item.artist_royalty_income_item_id = artist_contract_term_reserve.original_statement_item_id)
    JOIN artist_royalty_album USING (artist_royalty_album_id)
    JOIN artist_royalty_statement USING (artist_royalty_statement_id)
    JOIN artist_royalty_run USING (artist_royalty_run_id)
    JOIN track_contract ON (track_contract.track_id=artist_contract_term_reserve.track_id AND track_contract.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 new_artist_contract.payor_id=$payorID
    AND artist_contract_term_reserve.periods_remaining>0
    AND artist_royalty_run.status IN (2,5)
    AND track_contract.status=1
    AND artist_payee.status IN (1,2)
    AND artist_contract_term_reserve.track_id>0
    /;

    my $oldSql = qq/
    SELECT
    artist_contract_term_reserve.artist_contract_term_reserve_id,
    artist_contract_term_reserve.artist_contract_term_id,
    artist_contract_term_reserve.income_source_id,
    artist_contract_term_reserve.region_id,
    artist_contract_term_reserve.channel_id,
    artist_contract_term_reserve.price_level_id,
    artist_contract_term_reserve.original_statement_item_id,
    artist_contract_term_reserve.product_id,
    artist_contract_term_reserve.product_format_id,
    artist_contract_term_reserve.units,
    artist_contract_term_reserve.revenue,
    artist_contract_term_reserve.price,
    artist_contract_term_reserve.revenue_based,
    artist_contract_term_reserve.effective_rate,
    artist_contract_term_reserve.periods_remaining,
    artist_contract_term_reserve.album_id,
    artist_contract_term_reserve.track_id,
    new_artist_contract.artist_contract_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract_term.contract_rate_type_id,
    album_contract.cross_collateralized,
    artist_contract_term_reserve.prorate_track_count
    FROM artist_contract_term_reserve
    JOIN product USING (product_id)
    JOIN new_artist_contract_term USING (artist_contract_term_id)
    JOIN new_artist_contract USING (artist_contract_id)
    JOIN album_contract ON (album_contract.album_id=artist_contract_term_reserve.album_id AND album_contract.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 new_artist_contract.payor_id=$payorID
    AND artist_contract_term_reserve.periods_remaining>0
    AND artist_contract_term_reserve.original_statement_item_id=0
    AND album_contract.status=1
    AND artist_payee.status IN (1,2)
    AND artist_contract_term_reserve.track_id=0
    UNION
    SELECT
    artist_contract_term_reserve.artist_contract_term_reserve_id,
    artist_contract_term_reserve.artist_contract_term_id,
    artist_contract_term_reserve.income_source_id,
    artist_contract_term_reserve.region_id,
    artist_contract_term_reserve.channel_id,
    artist_contract_term_reserve.price_level_id,
    artist_contract_term_reserve.original_statement_item_id,
    artist_contract_term_reserve.product_id,
    artist_contract_term_reserve.product_format_id,
    artist_contract_term_reserve.units,
    artist_contract_term_reserve.revenue,
    artist_contract_term_reserve.price,
    artist_contract_term_reserve.revenue_based,
    artist_contract_term_reserve.effective_rate,
    artist_contract_term_reserve.periods_remaining,
    artist_contract_term_reserve.album_id,
    artist_contract_term_reserve.track_id,
    new_artist_contract.artist_contract_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract_term.contract_rate_type_id,
    track_contract.cross_collateralized,
    artist_contract_term_reserve.prorate_track_count
    FROM artist_contract_term_reserve
    JOIN product USING (product_id)
    JOIN new_artist_contract_term USING (artist_contract_term_id)
    JOIN new_artist_contract USING (artist_contract_id)
    JOIN track_contract ON (track_contract.track_id=artist_contract_term_reserve.track_id AND track_contract.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 new_artist_contract.payor_id=$payorID
    AND artist_contract_term_reserve.periods_remaining>0
    AND artist_contract_term_reserve.original_statement_item_id=0
    AND track_contract.status=1
    AND artist_payee.status IN (1,2)
    AND artist_contract_term_reserve.track_id>0
    /;

    # I don't know yet whether I'll want this data arranged in any particular way.  It seems to me that I've got
    # everything I need in this one query already, so I don't think I need to rely on efficiently accessing any other
    # data.
    #
    my $sql = "$baseSql UNION $oldSql";

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while ( my $ar = $sth->fetchrow_arrayref() ) {
        print RESERVEFILE join( "\t", @$ar ) . "\n";
    }

    close RESERVEFILE;
}

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

    # 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.
    #
    # !!! Or, we'll just return a file handle...   Don't really need fancy tied arrays for this, do we?
    #
    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;