#------------------------------------------------------------
# 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 = " . $class->quote($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 = "
      . $dbo->DBQuote($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 = " .$dbo->DBQuote( $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=" . $dbo->DBQuote( $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=" . $dbo->DBQuote( $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 = ' . $dbo->DBQuote( $license->mcps_license_id );
            $inSql .= " AND uk_mechanical_run_id IN (" . join( ',', map { $dbo->DBQuote($_) } @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( ',', map { $dbo->DBQuote($_) } @activeRetentionLicenses ) . ')';

    my $sql = "SELECT * FROM " . kTable . " WHERE mcps_license_id IN $goodLicenseString";

    if ($payorID) {
        $sql .= " AND payor_id = " . $dbo->DBQuote( $payorID );
    }

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

1;