#------------------------------------------------------------
# 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 $sql =
        "SELECT * FROM "
        . kTable
        . " WHERE artist_contract_id = "
        . $class->quote($artistContractID)
        . " ORDER BY priority";

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

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

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

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

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

    my $sql =
        "SELECT * FROM "
        . kTable
        . " WHERE artist_contract_id = "
        . $class->quote($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 = "
        . $class->quote($artistContractID)
        . " AND priority = 0";

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

    return $collection->next();
}

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

    my $dbo   = Common::RSApp::GetClientDB();
    my $sql   = "SELECT COUNT(*) FROM " . kTable . " WHERE region_id = ?";
    my $sth   = $dbo->DoCmdWithPlaceholders($sql, [$regionID]);
    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 1 = 1
            AND 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 = "
      . $class->quote($artistContractID)
      . " AND ("
      . _termTest( 'channel_id', $class->quote($channelID) ) . ")"
      . " AND ("
      . _termTest( 'price_level_id', $class->quote($priceLevelID) ) . ")"
      . " AND (contract_term_source_id IN (" . join( ',', map { $class->quote($_) } @$contractTermSourceIDs ) . "))"
      . " 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;