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