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

package RPS::ArtistRoyalty::Fast::Static::ArtistPayeeAccounts;

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 kMinPayment                   => 0;
use constant kBalance                      => 1;
use constant kContractLicenseIncomeBalance => 2;
use constant kOnHold                       => 3;
use constant kPreviousCrossedBalance       => 4;
use constant kPreviousUncrossedBalance     => 5;

# Tie in -
# contract_level_license_income_balance_account
#
sub FileName            { 'artist_payee_accounts' }
sub CacheQueryTableList { "'finance_transaction','artist_payee_account','artist_payee', 'contract_level_license_income_balance_account'" }

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

    # !!! Ok, so I need to fetch the last transaction

    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();

    my %tempHash;
    my $sth;

    my $artistAccountSQL = qq/
    SELECT 
    artist_payee_account.artist_payee_id,
    artist_payee_account.min_payment,
    artist_payee_account.finance_account_id,
    artist_payee.status
    FROM artist_payee_account
    JOIN artist_payee ON (artist_payee_account.artist_payee_id = artist_payee.artist_payee_id)
    WHERE artist_payee_account.payor_id=$payorID
    AND artist_payee.status in (1,2)
    /;
    $sth = $dbo->DoCmd($artistAccountSQL);

    while ( my @array = $sth->fetchrow_array() ) {
        my $accountID = $array[2];

        my $balance                              = _accountBalance( $accountID, $dbo );
        my ($crossedBalance, $uncrossedBalance)  = _previousBalance( $array[0], $payorID, $dbo );

        $tempHash{ $array[0] }{min_payment}     = $array[1];
        $tempHash{ $array[0] }{balance}         = $balance;
        $tempHash{ $array[0] }{on_hold}         = 2 == $array[3] ? 1 : 0;

        $tempHash{ $array[0] }{crossed_balance}   = $crossedBalance;
        $tempHash{ $array[0] }{uncrossed_balance} = $uncrossedBalance;
    }

    my $licenseIncomeAccountSQL = qq/
    SELECT 
    contract_level_license_income_balance_account.artist_payee_id,
    contract_level_license_income_balance_account.finance_account_id,
    artist_payee.status
    FROM contract_level_license_income_balance_account
    JOIN artist_payee USING (artist_payee_id)
    WHERE contract_level_license_income_balance_account.payor_id=$payorID
    AND artist_payee.status in (1,2)
    /;
    $sth = $dbo->DoCmd($licenseIncomeAccountSQL);

    while ( my @array = $sth->fetchrow_array() ) {
        my $accountID = $array[1];
        my $balance = _accountBalance( $accountID, $dbo );

        $tempHash{ $array[0] }{li_balance} = $balance;
        $tempHash{ $array[0] }{on_hold} = 2 == $array[2] ? 1 : 0;
    }

    my $artistContractSQL = qq/
    SELECT 
    distinct(new_artist_contract.artist_payee_id),
    artist_payee.status
    FROM new_artist_contract 
    JOIN artist_payee USING (artist_payee_id)
    WHERE new_artist_contract.payor_id=$payorID
    AND new_artist_contract.deleted = 0
    AND artist_payee.status in (1,2)
    /;
    $sth = $dbo->DoCmd($artistContractSQL);

    while ( my @array = $sth->fetchrow_array() ) {

        # This essentially just puts a blank record in the hash.
        #
        $tempHash{ $array[0] }{on_hold} = 2 == $array[1] ? 1 : 0;
    }

    # Now output the data
    #
    while ( my ( $key, $value ) = each %tempHash ) {
        my $balance    = $value->{balance}     ? $value->{balance}     : 0;
        my $liBalance  = $value->{li_balance}  ? $value->{li_balance}  : 0;
        my $minPayment = $value->{min_payment} ? $value->{min_payment} : 0;
        my $onHold     = $value->{on_hold};

        my $crossedBal   = $value->{crossed_balance};
        my $uncrossedBal = $value->{uncrossed_balance};

        $hash{$key} = "$minPayment\t$balance\t$liBalance\t$onHold\t$crossedBal\t$uncrossedBal";
    }

    untie %hash;
}

sub _accountBalance {
    my ( $accountID, $dbo ) = @_;
    my $balanceSql =
"select balance from finance_transaction where finance_transaction_id = (select MAX(finance_transaction_id) from finance_transaction where finance_account_id=$accountID)";

    my $innerSTH = $dbo->DoCmd($balanceSql);
    my $bArray   = $innerSTH->fetchrow_arrayref();
    my $balance  = $bArray->[0];
    Log->info("balance for account $accountID: $balance");

    return $balance;
}

sub _previousBalance {
    my ( $payeeID, $payorID, $dbo ) = @_;
    # grab previous crossed balance and uncrossed subtotal from most recent closed/committed run
    my $previousBalanceSQL = qq/
    SELECT
    rs.cross_collateralized_subtotal, rs.uncrossed_subtotal
    FROM artist_royalty_statement rs
    JOIN artist_royalty_run r USING (artist_royalty_run_id)
    WHERE rs.payee_id=$payeeID
    AND r.payor_id=$payorID
    AND r.status IN (2,5)
    ORDER BY rs.artist_royalty_run_id DESC LIMIT 1
    /;
    my $sth = $dbo->DoCmd($previousBalanceSQL);
    my $bArray   = $sth->fetchrow_arrayref();
    my $crossedBalance    = $bArray->[0];
    my $uncrossedBalance  = $bArray->[1];
    Log->info("Previous balances for payee $payeeID payor $payorID: crossed($crossedBalance) uncrossed($uncrossedBalance)");
    return ($crossedBalance, $uncrossedBalance);
}

sub GetArtistPayeeAccounts {
    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;
}

# !!!  Issues with statements not quite adding up.
# !!!  We seem to have some rounding errors in the net revenue terms on occasion.
#

1;