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

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';
use Common::DB::Item;
use Common::Assert;
use Common::Log;
use Common::DB::ItemCollection;
use RPS::DB::Item::Product;
use RPS::DB::Item::UKMechanicalRun;

use base 'Common::DB::Item';


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

use constant kRetentionCategoryStandard => 'S';
use constant kRetentionCategoryTVAdvertised => 'T';

use constant kDVDCategoryA => 'A';
use constant kDVDCategoryB => 'B';
use constant kDVDCategoryC => 'C';
use constant kDVDCategoryAVP => 'AVP';

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

	my $sql = "SELECT * FROM " . kTable . " " .
              "INNER JOIN product USING (product_id) " .
              "WHERE product.asset_id = " . $dbo->DBQuote($albumID);

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


sub GetMcpsLicenseByProductID
{
	my ($class, $productID) = @_;
	my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT * FROM " . kTable . " " .
              "WHERE product_id = " . $productID;

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


sub GetByPayorID
{
	my ($class, $payorID, $statementID) = @_;
	my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT * FROM " . kTable . " " .
		      "LEFT JOIN product ON (" . kTable . ".product_id = product.product_id) " .
	          "LEFT JOIN album ON (album.album_id = product.asset_id) " .
              "WHERE payor_id = " . $payorID . " " .
              "ORDER BY album.catalog_number";
    return $class->GetAll($sql);
}


sub GetAllAP1Licenses
{
    my ($class, %args) = @_;

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

	my $sql = "SELECT * FROM " . kTable . " " .
              "INNER JOIN product USING (product_id) " .
              "WHERE product.product_type_id NOT IN ( " .
              RPS::DB::Item::Product::kProductTypeDigital . ',' .
              RPS::DB::Item::Product::kProductTypeDigitalTrack . ',' . 
              RPS::DB::Item::Product::kProductTypeDVD. ")";

    if ($args{payorID})
    {
        $sql .= " AND payor_id=".$args{payorID};
    }

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


sub GetAllDVD1Licenses
{
    my ($class, %args) = @_;

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

	my $sql = "SELECT * FROM " . kTable . " " .
              "INNER JOIN product USING (product_id) " .
              "WHERE product.product_type_id = " . RPS::DB::Item::Product::kProductTypeDVD .
              " AND dvd_category <> '".kDVDCategoryAVP."'";

    if ($args{payorID})
    {
        $sql .= " AND payor_id=".$args{payorID};
    }

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

sub GetAllAVPLicenses
{
    my ($class, %args) = @_;

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

	my $sql = "SELECT * FROM " . kTable . " " .
              "INNER JOIN product USING (product_id) " .
              "WHERE product.product_type_id = " . RPS::DB::Item::Product::kProductTypeDVD .
              " AND dvd_category = '".kDVDCategoryAVP."'";

    if ($args{payorID})
    {
        $sql .= " AND payor_id=".$args{payorID};
    }

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


sub GetAllWithActiveRetentions
{
	my ($class, $maxPeriods, $payorID) = @_;
	my $dbo = Common::RSApp::GetClientDB();


    # Perhaps this could all be done in a single query, but I am too dumb to 
    # figure out how to do that.
    #
    # First, what are the committed or closed run ids?
    #
    my @committedRuns;
    my $allRuns = RPS::DB::Item::UKMechanicalRun->GetAll();
    while (my $run = $allRuns->next())
    {
        push @committedRuns, $run->uk_mechanical_run_id 
         if ($run->status == RPS::RoyaltyRun::Status::kCommitted || $run->status == RPS::RoyaltyRun::Status::kClosed);
    }

    my @activeRetentionLicenses;
    my $allLicenses = $class->GetAll();
    while (my $license = $allLicenses->next())
    {
        my $count = $license->initial_periods_completed();
        if (scalar @committedRuns)
        {
            my $inSql = 'SELECT COUNT(*) FROM mcps_license_run WHERE mcps_license_id = ' . $license->mcps_license_id;
            $inSql .= " AND uk_mechanical_run_id IN (" . join(',', @committedRuns) . ")";
            my $sth = $dbo->DoCmd($inSql);
            my $hr = $sth->fetchrow_hashref();
            $count += $hr->{'COUNT(*)'};
        }        
        if ($count <= $maxPeriods)
        {
            push @activeRetentionLicenses, $license->mcps_license_id ;
        }
    }

    return undef unless scalar @activeRetentionLicenses;
    
    my $goodLicenseString = '(' . join(',', @activeRetentionLicenses) . ')';

	my $sql = "SELECT * FROM " . kTable . " WHERE mcps_license_id IN $goodLicenseString";
              
    
    if ($payorID)
    {          
        $sql .= " AND payor_id = " . $payorID;
    }

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


1;