#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::TrackLicense;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;
use Common::Log;
use base 'Common::DB::Item';

use RPS::DB::Item::ReserveLiquidation;

use constant kRateBasisSale		=> 1;
use constant kRateBasisLock     => 2;

use constant kRateTypeFull		=> 1;
use constant kRateTypeMinimum	=> 2;
use constant kRateTypePenny		=> 3;

use constant kPublishingLicense => 1;
use constant kControlledComposition => 2;
use constant kUnknownPublisher => 3;
use constant kPublicDomain => 4;
use constant kRingtone => 5;

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

sub _forPublisher
{
    my ($publisherID) = @_;

    my $sql = "(publisher_id NOT IN (select publisher_id from publisher where status = 0) AND ((publisher_id=$publisherID AND publisher_direct=1) OR (publisher_direct = 0 AND (publisher_id in (select publisher_id from publisher where agent_id=$publisherID OR (agent_id IS NULL or agent_id=0) AND admin_id=$publisherID )) OR (publisher_id in (select publisher_id from publisher where ((agent_id IS NULL or agent_id=0) AND (admin_id IS NULL or admin_id=0) AND publisher_id=$publisherID))))))";
    return $sql;
}


sub GetByParentID
{
	my ($class, $parentID) = @_;

	my $sql = "SELECT * FROM track_license WHERE track_license_id=$parentID OR parent_track_license_id=$parentID";
    return $class->GetAll($sql);
}


sub GetLicensesByTrackID
{
	my ($class, $trackID, $type) = @_;
    assert($trackID);

	my $sql = "SELECT * FROM track_license WHERE track_id=$trackID";

    if (defined $type)
    {
        $sql .= " AND type=$type";
    }

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




sub GetLicensesByAlbumID
{
	my ($class, $albumID, $type) = @_;
    assert($albumID);
	my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT * FROM track_license " .
              "INNER JOIN track USING (track_id) " .
              "WHERE mechanical_exempt <> 1 AND album_id = " . $dbo->DBQuote($albumID);

    if (defined $type)
    {
        $sql .= " AND type=$type";
    }

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



sub GetIssuedLicensesByDate
{
    my ($class, $beginDate, $endDate) = @_;
    assert($beginDate);
    assert($endDate);

    # Quote the dates properly.
    #
	my $dbo = Common::RSApp::GetClientDB();
    $beginDate = $dbo->DBQuote($beginDate);
    $endDate = $dbo->DBQuote($endDate);

    my $sql = "SELECT * from track_license WHERE "
     . " ("
     . "   ( "
     . "     term_start IS NOT NULL AND term_start <= $endDate"
     . "   ) "
     . "   OR "
     . "   ( "
     . "     term_start IS NULL"
     . "   ) "
     . " )"
     . " AND "
     . " ("
     . "   ( "
     . "     term_end IS NOT NULL AND term_end >= $endDate"
     . "   ) "
     . "   OR "
     . "   ( "
     . "     term_end IS NULL"
     . "   ) "
     . " ) AND inactive=0";
#     . " ) AND date_issued IS NOT NULL";


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


sub GetLicensesByTrackIDAndDate
{
    my ($class, $trackID, $beginDate, $endDate, $skipUnissuedFlag) = @_;
    assert($trackID);
    assert($beginDate);
    assert($endDate);

    # Quote the dates properly.
    #
	my $dbo = Common::RSApp::GetClientDB();
    $beginDate = $dbo->DBQuote($beginDate);
    $endDate = $dbo->DBQuote($endDate);

    my $sql = "SELECT * from track_license WHERE track_id=$trackID"
     . " AND "
     . " ("
     . "   ( "
     . "     term_start IS NOT NULL AND term_start <= $endDate"
     . "   ) "
     . "   OR "
     . "   ( "
     . "     term_start IS NULL"
     . "   ) "
     . " )"
     . " AND "
     . " ("
     . "   ( "
     . "     term_end IS NOT NULL AND term_end >= $endDate"
     . "   ) "
     . "   OR "
     . "   ( "
     . "     term_end IS NULL"
     . "   ) "
     . " ) AND inactive=0";

    if ($skipUnissuedFlag)
    {
        $sql .= " AND date_issued IS NOT NULL";
    }

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


sub GetLicensesForPublisherStatement
{
    my ($class, $publisherID, $payorID, $skipUnissuedFlag) = @_;
    assert($publisherID);

    # Quote the dates properly.
    #
	my $dbo = Common::RSApp::GetClientDB();

    # We want all _active_ licenses that are either:
    # - direct licenses with this publisher or
    # - indirect licenses, where this publisher is the agent or admin
    #   (and agents _trump_ admins)
    #
    my $sql = "SELECT * from track_license"
     . " WHERE " . _forPublisher($publisherID)
     . " AND payor_id=$payorID"
     . " AND inactive=0";

    if ($skipUnissuedFlag)
    {
        $sql .= " AND date_issued IS NOT NULL";
    }

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

sub GetLicensesForPublisherStatementWithTrackAndRegion
{
    my ($class, $trackID, $regionID, $beginDate, $endDate, $publisherID, $payorID, $skipUnissuedFlag) = @_;
    assert($trackID);
    assert($regionID);
    assert($beginDate);
    assert($endDate);
    assert($publisherID);
    assert($payorID);

    # Quote the dates properly.
    #
	my $dbo = Common::RSApp::GetClientDB();
    $beginDate = $dbo->DBQuote($beginDate);
    $endDate = $dbo->DBQuote($endDate);

    # We want all _active_ licenses that are either:
    # - direct licenses with this publisher or
    # - indirect licenses, where this publisher is the agent or admin
    #   (and agents _trump_ admins)
    #
    # alas, the 'publisher_direct' flag is not always accurate, in that
    # if a publisher HAS NO agent or admin, then the license is always
    # treated as direct.... that makes the query uglier.
    #
    my $sql = "SELECT * from track_license WHERE track_id=$trackID"
     . " AND region_id=$regionID"
     . " AND " . _forPublisher($publisherID)
     . " AND payor_id=$payorID"
     . " AND "
     . " ("
     . "   ( "
     . "     term_start IS NOT NULL AND term_start <= $endDate"
     . "   ) "
     . "   OR "
     . "   ( "
     . "     term_start IS NULL"
     . "   ) "
     . " )"
     . " AND "
     . " ("
     . "   ( "
     . "     term_end IS NOT NULL AND term_end >= $endDate"
     . "   ) "
     . "   OR "
     . "   ( "
     . "     term_end IS NULL"
     . "   ) "
     . " ) AND inactive=0";

    if ($skipUnissuedFlag)
    {
        $sql .= " AND date_issued IS NOT NULL";
    }

    return $class->GetAll($sql);

}



sub GetLicensesWithPendingTransactionsForMechanicalStatement
{
    my ($class, $publisherID, $payorID) = @_;

    my $sql = "SELECT * FROM track_license WHERE finance_account_id IN (select finance_account_id from pending_transaction)"
     . " AND " . _forPublisher($publisherID)
     . " AND payor_id=$payorID"
     . " AND inactive=0";

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

sub GetLicensesWithPendingTransactions
{
    my ($class) = @_;

    my $sql = "SELECT * from track_license where finance_account_id in (select finance_account_id from pending_transaction)";
    return $class->GetAll($sql);
}




# !!! Just used in the mechanicals script.
# !!! THis does nothing - that flag is never set.
sub GetNewTrackLicenses
{
    my ($class) = @_;

    # Return all track licenses that have yet to experience the joy of a royalty run
    #
    my $sql = "SELECT * FROM track_license WHERE last_mechanical_run_id = 0";

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


sub GetCCompTrackLicensesForAlbum
{
    my ($class, $albumID) = @_;

    my $sql = "SELECT * FROM track_license WHERE type=".kControlledComposition
     . " AND track_id IN "
     . " ("
     . "   SELECT track_id FROM track WHERE album_id=$albumID"
     . " )";

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




sub GetByType
{
	my ($class, $type) = @_;
    assert($type);

	my $sql = "SELECT * FROM track_license WHERE type=$type";
    return $class->GetAll($sql);
}






sub GetByTrackPublisherProductType
{
	my ($class, $trackID, $publisherID, $productTypeID) = @_;

    my $productTypeClause;
    if (! $productTypeID)
    {
        $productTypeClause = "(product_type_id IS NULL or product_type_id=0)";
    }
    else
    {
        $productTypeClause = "product_type_id=$productTypeID";
    }

	my $sql = "SELECT * FROM track_license"
		. " WHERE track_id=$trackID AND publisher_id=$publisherID AND $productTypeClause";

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

sub GetByTrackPublisherProductTypeAndDate
{
	my ($class, $trackID, $publisherID, $productTypeID, $termStart, $termEnd) = @_;
    my $dateSelect = "";
    my $dbo = Common::RSApp::GetClientDB();
	my $productTypeGroupSQL;
	my @ids = ref($productTypeID) ? @$productTypeID : ($productTypeID);
	my $hasDigital;
	my $hasPhysical;

    foreach my $id ( @ids ) {
		if( $id == 3 || $id == 4 || $id == 15 ) {
			$hasDigital = 1;
		} else {
			$hasPhysical = 1;
		}
	}

	$productTypeGroupSQL = " OR product_type_id = 254 " if( $hasDigital );
	$productTypeGroupSQL = " OR product_type_id = 255 " if( $hasPhysical );
	$productTypeGroupSQL = "" unless( $productTypeGroupSQL );

    my $typeIDs = '(' . join( ',', @ids ) . ')';

	if( $termStart && $termEnd ) {
		$dateSelect .= ", if( term_start >= " . $dbo->DBQuote($termStart) .
		                    " AND term_end <= " . $dbo->DBQuote($termStart) . ", 1, \\N ) as start_term_overlap ";
		$dateSelect .= ", if( term_start >= " . $dbo->DBQuote($termEnd) .
		                    " AND term_end <= " . $dbo->DBQuote($termEnd) . ", 1, \\N ) as end_term_overlap ";
	}

	elsif( $termStart ) {
		$dateSelect .= ", if( term_end > " . $dbo->DBQuote($termStart) . ", 1, \\N ) as start_term_overlap ";
		$dateSelect .= ", \\N as end_term_overlap";
	}

	elsif( $termEnd ) {
		$dateSelect .= ", \\N as start_term_overlap";
		$dateSelect .= ", if( term_start < " . $dbo->DBQuote($termEnd) . ", 1, \\N ) as end_term_overlap ";
	}

	else {
		$dateSelect .= ", 1 as start_term_overlap, 1 as end_term_overlap ";
	}

    my $productTypeClause = "";
    if ($productTypeID)
    {
        $productTypeClause = " AND ( product_type_id IN $typeIDs OR product_type_id = 0 OR product_type_id IS NULL $productTypeGroupSQL ) ";
    }

	my $sql = "SELECT * $dateSelect FROM track_license"
		. " WHERE track_id=$trackID AND publisher_id=$publisherID $productTypeClause";


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

sub GetByPublisherIDPayorID
{
	my ($class, $publisherID, $payorID) = @_;

    assert($publisherID);
    assert($payorID);

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

	my $sql = "SELECT * FROM ".kTable." WHERE publisher_id=$publisherID AND payor_id=$payorID";
    return $class->GetAll($sql);
}

sub GetDirectByPublisherIDPayorID
{
	my ($class, $publisherID, $payorID) = @_;

    assert($publisherID);
    assert($payorID);

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

	my $sql = "SELECT * FROM ".kTable." WHERE publisher_id=$publisherID AND payor_id=$payorID AND publisher_direct=1";
    return $class->GetAll($sql);
}

sub GetIndirectByPublisherIDPayorID
{
	my ($class, $publisherID, $payorID) = @_;

    assert($publisherID);
    assert($payorID);

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

	my $sql = "SELECT * FROM ".kTable." WHERE publisher_id=$publisherID AND payor_id=$payorID AND publisher_direct=0";
    return $class->GetAll($sql);
}

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

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

	my $sql = "SELECT * FROM ".kTable." WHERE payor_id=$payorID";
    return $class->GetAll($sql);
}

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

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

	my $sql = "SELECT COUNT(*) as count FROM ".kTable." WHERE payor_id=$payorID";
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    return $hr->{count};
}


sub GetAllWithoutReserveLiquidation {
    my $class = shift;

	my $types = join( ',', ( RPS::DB::Item::ReserveLiquidation::kTypePublishingLicense,
							 RPS::DB::Item::ReserveLiquidation::kTypeControlledComposition,
							 RPS::DB::Item::ReserveLiquidation::kTypeRingtoneLicense ) );
	my $table = kTable;

	my $sql = "SELECT *
               FROM $table
                  LEFT JOIN reserve_liquidation ON ( $table.track_license_id = reserve_liquidation.id AND reserve_liquidation.type IN ($types) )
               WHERE reserve_percentage > 0 AND reserve_liquidation.id IS NULL and inactive = 0";

    Common::Log::Debug( "QUERY: $sql" );
	return $class->SUPER::GetAll($sql);
}

sub GetAllWithInvalidPennyRate {
    my $class = shift;

	my $table = kTable;

	my $sql = "SELECT *
               FROM $table
               WHERE rate_type = " . kRateTypePenny . " AND penny_rate <= 0";

    Common::Log::Debug( "QUERY: $sql" );
	return $class->SUPER::GetAll($sql);
}

sub GetAllWithZeroShare {
    my $class = shift;

	my $table = kTable;

	my $sql = "SELECT *
               FROM $table
               WHERE share = 0 AND inactive = 0";

    Common::Log::Debug( "QUERY: $sql" );
	return $class->SUPER::GetAll($sql);
}

# This will return an array reference of hash references that look like this:
# [ { publisher_id => 123, count => 5 }, ... ]
#
sub GetCountsForAllPublishers
{
    my ($class) = @_;

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

    my @returnArray;
    my $sql = "SELECT publisher_id, COUNT(*) AS count FROM track_license GROUP BY publisher_id";
    my $sth = $dbo->DoCmd($sql);
    while (my $hr = $sth->fetchrow_hashref())
    {
        push @returnArray, $hr;
    }

    return \@returnArray;
}





1;