#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::NewArtistContractTerm;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::ItemCollection;
use Common::DB::Item;
use base 'Common::DB::Item';

use lib '/app/tools/rps/lib';
use RPS::RoyaltyRun::Status;

use constant kTable => 'new_artist_contract_term';
use constant kDB => Common::DB::Item::kClientDB();




sub GetAll
{
    my ($class) = @_;
	my $sql = "SELECT * FROM ".kTable." ORDER by artist_contract_id, priority";
    return $class->SUPER::GetAll($sql);
}

sub LookupActive
{
	my $class = shift;
	my %args = @_;
	my $termID = $args{artist_contract_term_id} || return;

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

	my $sql = "SELECT * FROM ".kTable." WHERE artist_contract_term_id = " . $dbo->DBQuote($termID) . " AND ( inactive IS NULL OR inactive <> 1 )";
    return $class->SUPER::GetAll($sql)->next;
}

sub GetByArtistContractID
{
    my ($class, $artistContractID) = @_;
	assert($artistContractID);

	my $order = "ORDER BY priority";
	my $sql = "SELECT * FROM ".kTable." WHERE artist_contract_id=$artistContractID $order";

    return $class->SUPER::GetAll($sql);
}

sub GetActiveByArtistContractID
{
    my ($class, $artistContractID) = @_;
	assert($artistContractID);

	my $order = "ORDER BY priority";
	my $sql = "SELECT * FROM ".kTable." WHERE artist_contract_id=$artistContractID AND ( inactive IS NULL OR inactive <> 1 ) $order";

	return $class->SUPER::GetAll($sql);
}

sub GetActiveDefault
{
    my ($class, $artistContractID) = @_;
	assert($artistContractID);

	my $sql = "SELECT * FROM ".kTable." WHERE artist_contract_id=$artistContractID AND priority=0 and ( inactive IS NULL OR inactive <> 1 )";


	my $collection = $class->SUPER::GetAll($sql);

	return $collection->next();;
}

sub GetDefault
{
    my ($class, $artistContractID) = @_;
	assert($artistContractID);

	my $sql = "SELECT * FROM ".kTable." WHERE artist_contract_id=$artistContractID AND priority=0";


	my $collection = $class->SUPER::GetAll($sql);

	return $collection->next();;
}


sub IsRegionUsed
{
    my ($class, $regionID) = @_;
    assert($regionID);

    my $sql = "SELECT COUNT(*) FROM ".kTable." WHERE region_id=$regionID";
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref;
    my $count = $hr->{'COUNT(*)'};
    return ($count > 0);
}


sub GetTermDependencyByArtistContractID
{
    my ($class, $artistContractID) = @_;
	$artistContractID = 0 unless($artistContractID);

    my %dependency = ();

    # check reserve dependencies
    # Note that we only care about reserves that have not liquidated
    # AND we ignore reserves associated with Invalidated, Aborted, and Errored runs.
	my $sql = "
        SELECT new_artist_contract_term.artist_contract_term_id, count(*) AS dependencies
        FROM new_artist_contract_term JOIN artist_contract_term_reserve USING(artist_contract_term_id)
        WHERE artist_contract_id=? AND artist_contract_term_reserve.periods_remaining > 0 
        AND artist_contract_term_reserve.original_statement_item_id IN 
        (SELECT artist_royalty_income_item_id FROM artist_royalty_income_item WHERE artist_royalty_album_id IN 
        (SELECT artist_royalty_album_id FROM artist_royalty_album WHERE artist_royalty_statement_id IN 
        (SELECT artist_royalty_statement_id FROM artist_royalty_statement WHERE artist_royalty_run_id IN 
        (SELECT artist_royalty_run_id FROM artist_royalty_run WHERE status NOT IN 
        (".RPS::RoyaltyRun::Status::kAborted.",".RPS::RoyaltyRun::Status::kError.",".RPS::RoyaltyRun::Status::kInvalidated.")))))         
        GROUP BY 1";

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$artistContractID]);
    while (my $hr = $sth->fetchrow_hashref())
    {
        $dependency{$hr->{artist_contract_term_id}} = 1;
    }

    return \%dependency;
}

# JPK - Wrote this little helper function to simplify writing GetMatchingTerms.
#
sub _termTest
{
    my ($fieldName, $value) = @_;
    my @bits;
    push @bits, "$fieldName = 0";
    push @bits, "$fieldName IS NULL";
    push @bits, "$fieldName = $value" if $value;
    return join(' OR ', @bits);
}

sub GetMatchingTerms
{
    my ($class, %args) = @_;
    my $artistContractID = $args{artist_contract_id};
    my $countryCode = $args{country_code};
    my $channelID = $args{channel_id};
    my $contractTermSourceIDs = $args{contract_term_source_id};
    my $priceLevelID = $args{price_level_id};

    # I expect a list of possible source ids, so if we just get a single scalar make it an array.
    #
    $contractTermSourceIDs = [ $contractTermSourceIDs ] unless 'ARRAY' eq ref($contractTermSourceIDs);

    # !!! We have to test for BOTH NULL and 0 for some of these fields.
    # !!! Pretty much because we have some dumb code.
    #


    my $sql = "SELECT * FROM new_artist_contract_term"
     . " LEFT JOIN region_country_map ON (new_artist_contract_term.region_id = region_country_map.region_id)"
     . " WHERE artist_contract_id=$artistContractID"
     . " AND (" . _termTest('channel_id', $channelID) . ")"
     . " AND (" . _termTest('price_level_id', $priceLevelID) . ")"
#     . " AND (" . _termTest('income_source_id', $incomeSourceID) . ")"   # THIS IS WRONG
     . " AND (contract_term_source_id IN (".join(',', @$contractTermSourceIDs)."))"
#     . " AND (channel_id=0 OR channel_id=$channelID)"
#     . " AND (income_source_id=0 OR income_source_id=$incomeSourceID)"
#     . " AND (price_level_id=0 OR price_level_id=$priceLevelID)"
     . " AND (region_country_map.country_code = " . $class->quote($countryCode) . " OR (new_artist_contract_term.region_id = 0 OR new_artist_contract_term.region_id IS NULL))"
	 . " AND ( inactive IS NULL OR inactive <> 1 )"
     . " AND priority > 0"
     . " ORDER BY priority";

    return $class->SUPER::GetAll($sql);
}


1;