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

package RPS::ArtistRoyalty::Fast::Static::LicenseIncomeSales;

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

use Data::Dumper;
use DB_File;

use Common::Log;

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

use constant kSaleID              => 0;
use constant kArtistPayeeID       => 1;
use constant kArtistContractID    => 2;
use constant kLicenseIncomeID     => 3;
use constant kUnits               => 4;
use constant kRevenue             => 5;
use constant kMemo                => 6;
use constant kLicenseIncomeTypeID => 7;
use constant kAlbumID             => 8;
use constant kTrackID             => 9;
use constant kRate                => 10;
use constant kCrossed             => 11;

sub FileName { 'license_income' }

sub CacheQueryTableList {
"'sale_run_map','artist_royalty_license_income_item','artist_royalty_statement','artist_royalty_run','sale','license_income','file','new_artist_contract','artist_payee','album','artist_contract_license_income','album_contract','track','track_contract'";
}

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

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

    my $dbo             = Common::RSApp::GetClientDB();
    my $dbName          = Common::RSDB::ClientIDToDBName( Common::RSApp::GetClientID() );

    # RSD-3085: Enable License Income Enhancement (LIE) feature for all clients.  If for some reason you
    # need to turn this off for a particular client, set checkStatements to 0 for that client.
    my $checkStatements = 1;

    my %contractHash;
    my %paidHash;

    my $paidSql = "SELECT sale_run_map.sale_id";
    if ($checkStatements) {
        $paidSql .=
            ", artist_royalty_license_income_item.artist_contract_id"
          . ", artist_royalty_license_income_item.album_id"
          . ", artist_royalty_license_income_item.track_id";
    }
    $paidSql .= " FROM sale_run_map" . " JOIN artist_royalty_run ON (sale_run_map.run_id = artist_royalty_run.artist_royalty_run_id)";
    if ($checkStatements) {
        $paidSql .=
          " JOIN artist_royalty_license_income_item" . " ON (sale_run_map.statement_item_id = artist_royalty_license_income_item_id)";
    }
    $paidSql .= " WHERE sale_run_map.run_type='ARTR'" . " AND sale_run_map.status='paid'" . " AND artist_royalty_run.status IN (2,5)";

    my $paidSTH = $dbo->DoCmd($paidSql);
    while ( my $arrayRef = $paidSTH->fetchrow_arrayref() ) {
        my $saleID = $arrayRef->[0];

        if ($checkStatements) {
            my $contractID = $arrayRef->[1];
            my $albumID    = 0;
            my $trackID    = 0;
            if ( $arrayRef->[2] ) {
                $albumID = $arrayRef->[2];
            }
            if ( $arrayRef->[3] ) {
                $trackID = $arrayRef->[3];
            }

            $contractHash{$saleID}{$contractID}{$albumID}{$trackID} = 1;
        } else {
            $paidHash{$saleID} = 1;
        }
    }

    # JPK - We need to make sure the sale date falls within the contract term_start/end dates, if these are specified.
    # There may possibly be some mysql date functions we can use.

    # This query is intended to match situations where we have a contract_id in the license_incomme record and
    # a track_contract (not an album_contract) association.
    #
    my $sql0 = qq/
    SELECT 
    sale.sale_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract.artist_contract_id,
    license_income.license_income_id,
    license_income.units,
    license_income.revenue,
    license_income.memo,
    license_income.license_income_type_id,
    license_income.album_id,
    license_income.track_id,
    artist_contract_license_income.percent,
    track_contract.cross_collateralized
    FROM sale
    JOIN license_income USING (sale_id)
    JOIN file ON (license_income.file_id = file.file_id)
    JOIN new_artist_contract ON (new_artist_contract.artist_contract_id = license_income.contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    JOIN track ON (license_income.track_id = track.track_id)
    JOIN album ON (album.album_id = track.album_id)
    JOIN artist_contract_license_income ON (artist_contract_license_income.artist_contract_id = new_artist_contract.artist_contract_id AND artist_contract_license_income.license_income_type_id = license_income.license_income_type_id)
    JOIN track_contract ON (track.track_id = track_contract.track_id AND new_artist_contract.artist_contract_id = track_contract.artist_contract_id)
    WHERE
    file.period_id > 0
    AND sale.artist_royalty_status <> 2
    AND sale.free <> 1
    AND sale.product_type = 'L'
    AND album.inactive <> 1
    AND album.status <> 0
    AND new_artist_contract.payor_id=$payorID
    AND artist_contract_license_income.inactive=0
    AND track_contract.status=1
    AND artist_payee.status in (1,2)
    AND ((new_artist_contract.term_start IS NULL || new_artist_contract.term_start <= sale.date_end) && (new_artist_contract.term_end IS NULL || new_artist_contract.term_end >= sale.date_end))
    /;

    #    AND new_artist_contract.payor_id=$payorID

    # This one finds matching album contracts based on the contract id.
    # !!! Going to join album_contract, so I can get the cross collateralized flag.
    # !!! We'll see how that works out...
    #
    my $sql1 = qq/
    SELECT 
    sale.sale_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract.artist_contract_id,
    license_income.license_income_id,
    license_income.units,
    license_income.revenue,
    license_income.memo,
    license_income.license_income_type_id,
    license_income.album_id,
    license_income.track_id,
    artist_contract_license_income.percent,
    album_contract.cross_collateralized
    FROM sale
    JOIN license_income USING (sale_id)
    JOIN file ON (license_income.file_id = file.file_id)
    JOIN new_artist_contract ON (new_artist_contract.artist_contract_id = license_income.contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    JOIN album ON (license_income.album_id = album.album_id)
    JOIN artist_contract_license_income ON (artist_contract_license_income.artist_contract_id = new_artist_contract.artist_contract_id AND artist_contract_license_income.license_income_type_id = license_income.license_income_type_id)
    JOIN album_contract ON (album.album_id = album_contract.album_id AND new_artist_contract.artist_contract_id = album_contract.artist_contract_id)
    WHERE
    file.period_id > 0
    AND sale.artist_royalty_status <> 2
    AND sale.free <> 1
    AND sale.product_type = 'L'
    AND album.inactive <> 1
    AND album.status <> 0
    AND new_artist_contract.payor_id=$payorID
    AND artist_contract_license_income.inactive=0
    AND album_contract.status=1
    AND artist_payee.status in (1,2)
    AND ((new_artist_contract.term_start IS NULL || new_artist_contract.term_start <= sale.date_end) && (new_artist_contract.term_end IS NULL || new_artist_contract.term_end >= sale.date_end))
    /;

    #    AND new_artist_contract.payor_id=$payorID

    # This one finds matching album contracts based on the album id.
    # Note this should also match album contracts to track license income, assuming
    # the album_id is set. I think I can assume that...?  Test that!
    #
    my $sql2 = qq/
    SELECT 
    sale.sale_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract.artist_contract_id,
    license_income.license_income_id,
    license_income.units,
    license_income.revenue,
    license_income.memo,
    license_income.license_income_type_id,
    license_income.album_id,
    license_income.track_id,
    artist_contract_license_income.percent,
    album_contract.cross_collateralized
    FROM sale
    JOIN license_income USING (sale_id)
    JOIN file ON (license_income.file_id = file.file_id)
    JOIN album_contract ON (album_contract.album_id = license_income.album_id)
    JOIN new_artist_contract ON (new_artist_contract.artist_contract_id = album_contract.artist_contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    JOIN album ON (license_income.album_id = album.album_id)
    JOIN artist_contract_license_income ON (artist_contract_license_income.artist_contract_id = new_artist_contract.artist_contract_id AND artist_contract_license_income.license_income_type_id = license_income.license_income_type_id)
    WHERE
    file.period_id > 0
    AND (license_income.contract_id = 0 OR license_income.contract_id IS NULL)
    AND sale.artist_royalty_status <> 2
    AND sale.free <> 1
    AND sale.product_type = 'L'
    AND album.inactive <> 1
    AND album.status <> 0
    AND new_artist_contract.payor_id=$payorID
    AND artist_contract_license_income.inactive=0
    AND album_contract.status=1
    AND artist_payee.status in (1,2)
    AND ((new_artist_contract.term_start IS NULL || new_artist_contract.term_start <= sale.date_end) && (new_artist_contract.term_end IS NULL || new_artist_contract.term_end >= sale.date_end))
    /;

    # !!! This one found it, crossed

    # This one finds exact track contract to track license matches.
    #
    my $sql3 = qq/
    SELECT 
    sale.sale_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract.artist_contract_id,
    license_income.license_income_id,
    license_income.units,
    license_income.revenue,
    license_income.memo,
    license_income.license_income_type_id,
    license_income.album_id,
    license_income.track_id,
    artist_contract_license_income.percent,
    track_contract.cross_collateralized
    FROM sale
    JOIN license_income USING (sale_id)
    JOIN file ON (license_income.file_id = file.file_id)
    JOIN track_contract ON (track_contract.track_id = license_income.track_id)
    JOIN track ON (track.track_id = track_contract.track_id)
    JOIN album ON (track.album_id = album.album_id)
    JOIN new_artist_contract ON (new_artist_contract.artist_contract_id = track_contract.artist_contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    JOIN artist_contract_license_income ON (artist_contract_license_income.artist_contract_id = new_artist_contract.artist_contract_id AND artist_contract_license_income.license_income_type_id = license_income.license_income_type_id)
    WHERE
    file.period_id > 0
    AND (license_income.contract_id = 0 OR license_income.contract_id IS NULL)
    AND sale.artist_royalty_status <> 2
    AND sale.free <> 1
    AND sale.product_type = 'L'
    AND album.inactive <> 1
    AND album.status <> 0
    AND new_artist_contract.payor_id=$payorID
    AND artist_contract_license_income.inactive=0
    AND track_contract.status=1
    AND artist_payee.status in (1,2)
    AND ((new_artist_contract.term_start IS NULL || new_artist_contract.term_start <= sale.date_end) && (new_artist_contract.term_end IS NULL || new_artist_contract.term_end >= sale.date_end))
    /;

    # THis is the query that needs work.
    # We want to match any track contracts to an album-only license income.
    # So I think I need to make sure track_id is 0 here.
    my $sql4 = qq/
    SELECT 
    sale.sale_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract.artist_contract_id,
    license_income.license_income_id,
    license_income.units,
    license_income.revenue,
    license_income.memo,
    license_income.license_income_type_id,
    license_income.album_id,
    license_income.track_id,
    artist_contract_license_income.percent,
    track_contract.cross_collateralized
    FROM sale
    JOIN license_income USING (sale_id)
    JOIN file ON (license_income.file_id = file.file_id)
    JOIN track ON (track.album_id = license_income.album_id)
    JOIN album ON (track.album_id = album.album_id)
    JOIN track_contract ON (track_contract.track_id = track.track_id)
    JOIN new_artist_contract ON (new_artist_contract.artist_contract_id = track_contract.artist_contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    JOIN artist_contract_license_income ON (artist_contract_license_income.artist_contract_id = new_artist_contract.artist_contract_id AND artist_contract_license_income.license_income_type_id = license_income.license_income_type_id)
    WHERE
    file.period_id > 0
    AND (license_income.contract_id = 0 OR license_income.contract_id IS NULL)
    AND (license_income.track_id = 0 OR license_income.track_id IS NULL)
    AND sale.artist_royalty_status <> 2
    AND sale.free <> 1
    AND sale.product_type = 'L'
    AND album.inactive <> 1
    AND album.status <> 0
    AND new_artist_contract.payor_id=$payorID
    AND artist_contract_license_income.inactive=0
    AND track_contract.status=1
    AND artist_payee.status in (1,2)
    AND ((new_artist_contract.term_start IS NULL || new_artist_contract.term_start <= sale.date_end) && (new_artist_contract.term_end IS NULL || new_artist_contract.term_end >= sale.date_end))
    /;

    #    AND sale.sale_id=4809944
    # !!! This one also found it, crossed = 0;
    # So I guess the issue here is that this contract is attached both at the track and the album level.  It pops up twice because
    # the album contract is crossed, while the track contract is not.

# !!! Looks like I will need one more query, to grab license income sales that have neither a track nor an album id, but just a contract_id.
# !!! I don't see how we can determine the cross_collateralized flag, so I will force that to '0'.
#
    my $sql5 = qq/
    SELECT 
    sale.sale_id,
    new_artist_contract.artist_payee_id,
    new_artist_contract.artist_contract_id,
    license_income.license_income_id,
    license_income.units,
    license_income.revenue,
    license_income.memo,
    license_income.license_income_type_id,
    0,
    0,
    artist_contract_license_income.percent,
    0
    FROM sale
    JOIN license_income USING (sale_id)
    JOIN file ON (license_income.file_id = file.file_id)
    JOIN new_artist_contract ON (new_artist_contract.artist_contract_id = license_income.contract_id)
    JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)
    JOIN artist_contract_license_income ON (artist_contract_license_income.artist_contract_id = new_artist_contract.artist_contract_id AND artist_contract_license_income.license_income_type_id = license_income.license_income_type_id)
    WHERE
    file.period_id > 0
    AND (license_income.track_id = 0 OR license_income.track_id IS NULL)
    AND sale.artist_royalty_status <> 2
    AND sale.free <> 1
    AND sale.product_type = 'L'
    AND new_artist_contract.payor_id=$payorID
    AND artist_contract_license_income.inactive=0
    AND artist_payee.status in (1,2)
    AND (license_income.album_id = 0 OR license_income.album_id IS NULL)
    AND (license_income.track_id = 0 OR license_income.track_id IS NULL)
    AND ((new_artist_contract.term_start IS NULL || new_artist_contract.term_start <= sale.date_end) && (new_artist_contract.term_end IS NULL || new_artist_contract.term_end >= sale.date_end))
    /;

    # Need to take end date into account, if provided.
    #
    if ($endDate) {
        $sql0 .= " AND date_end <= " . $dbo->DBQuote($endDate);
        $sql1 .= " AND date_end <= " . $dbo->DBQuote($endDate);
        $sql2 .= " AND date_end <= " . $dbo->DBQuote($endDate);
        $sql3 .= " AND date_end <= " . $dbo->DBQuote($endDate);
        $sql4 .= " AND date_end <= " . $dbo->DBQuote($endDate);
        $sql5 .= " AND date_end <= " . $dbo->DBQuote($endDate);
    }

    my $sql = "SELECT * FROM ($sql0 UNION $sql1 UNION $sql2 UNION $sql3 UNION $sql4 UNION $sql5) AS subq ORDER BY 1,2,3";

    my $productSTH = $dbo->DoCmd($sql);
    while ( my @data = $productSTH->fetchrow_array() ) {
        my $contractID = $data[kArtistContractID] || 0;
        my $albumID    = $data[kAlbumID]          || 0;
        my $trackID    = $data[kTrackID]          || 0;
        next if ( $paidHash{ $data[kSaleID] } || $contractHash{ $data[kSaleID] }{$contractID}{$albumID}{$trackID} );
        my $escapedArray = $class->EscapeArray( \@data );
        print OUTFILE join( "\t", @$escapedArray ) . "\n";
    }

    close OUTFILE;
}

sub GetLicenseIncomeSales {
    my ( $class, $dataPath ) = @_;
    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;