#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::LicenseReserve;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';

use RPS::RoyaltyRun::Status;
use Common::DB::Item;
use base 'Common::DB::Item';

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

# !!! Is this a deprecated class?

sub GetLicenseReservesByTrackLicenseIDProductID {
    my ( $class, $licenseID, $productID ) = @_;

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

    my $sql = "SELECT * FROM license_reserve WHERE track_license_id = " . $class->quote($licenseID) . " AND product_id = " . $class->quote($productID) . " ORDER BY date_created";

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

sub GetByProductID {
    my ( $class, $id ) = @_;

    my $sql = "SELECT * FROM license_reserve WHERE product_id = " . $class->quote($id);
    return $class->GetAll($sql);
}

sub CountRemainingPeriodsByProductID {
    my ( $class, @productID ) = @_;

    my $productID = join ',', map { $class->quote($_) } grep {defined $_} @productID;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE product_id IN($productID) and periods_remaining > 0";
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{'COUNT(*)'};
}

sub GetByStatementItemID {
    my ( $class, $id ) = @_;

    my $sql = "SELECT * FROM license_reserve WHERE original_statement_item_id = " . $class->quote($id);
    return $class->GetAll($sql);
}

sub CountByTrackLicenseID {
    my ( $class, $id ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) FROM " . kTable . " WHERE track_license_id = ?";
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, [$id] );
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{'COUNT(*)'};
}

sub DeleteByStatementID {
    my ( $class, $id ) = @_;

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

# !!! This old query looks inoccuous, but it's actually quite slow.
# !!! So instead, we'll do this in a loop.
#my $sql = "DELETE from license_reserve where original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id = $id)";
#$dbo->DoCmd($sql);

    my $allItems = RPS::DB::Item::MechanicalStatementItem->GetByMechanicalStatementID($id);
    while ( my $item = $allItems->next() ) {
        my $itemID = $item->mechanical_statement_item_id;
        my $sql    = "DELETE FROM license_reserve WHERE original_statement_item_id = ?";
        $dbo->DoCmdWithPlaceholders($sql, [$itemID]);
    }

}

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

    my $sql =
"SELECT * FROM license_reserve WHERE (original_statement_item_id = 0 OR original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id in (select mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from mechanical_run where status in (2,5))))) AND periods_remaining > 0";
    return $class->SUPER::GetAll($sql);
}

sub GetAllCommittedByTrackLicenseID {
    my ( $class, $trackLicenseID ) = @_;

    my $sql =
"SELECT * FROM license_reserve WHERE track_license_id = " . $class->quote($trackLicenseID) . " AND (original_statement_item_id = 0 OR original_statement_item_id in (select mechanical_statement_item_id from mechanical_statement_item where mechanical_statement_id in (select mechanical_statement_id from mechanical_statement where mechanical_run_id in (select mechanical_run_id from mechanical_run where status in (2,5))))) AND periods_remaining > 0";
    return $class->SUPER::GetAll($sql);
}

# NEW METHODS BELOW PER FB15315.
#
sub GetByMechanicalRunID {
    my ( $class, $runID ) = @_;

    my $run     = RPS::DB::Item::MechanicalRun->Lookup( mechanical_run_id => $runID );
    my $payorID = $run->payor_id;

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

    # Historical reserves for the same payor
    $sql .= "(original_statement_item_id = 0 AND track_license_id IN (SELECT track_license_id FROM track_license WHERE payor_id = " . $class->quote($payorID) . "))";

    $sql .= " OR ";

    # Non-historical reserves for the run
    $sql .=
        "(original_statement_item_id IN (
          SELECT mechanical_statement_item_id FROM mechanical_statement_item WHERE mechanical_statement_id IN (
          SELECT mechanical_statement_id FROM mechanical_statement WHERE mechanical_run_id = " . $class->quote($runID)
          .")))) AND periods_remaining > 0";

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

sub GetLiquidatedByMechanicalRunID {
    my ( $class, $runID ) = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE liquidated_run_id = " . $class->quote($runID);
    return $class->SUPER::GetAll($sql);
}

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

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

    # Historical reserves
    $sql .= "(original_statement_item_id = 0 AND track_license_id IN (SELECT track_license_id FROM track_license WHERE payor_id = " . $class->quote($payorID) . "))";

    $sql .= " OR ";

    # Non-historical reserves
    $sql .=
        "(original_statement_item_id IN (
          SELECT mechanical_statement_item_id FROM mechanical_statement_item WHERE mechanical_statement_id IN (
          SELECT mechanical_statement_id FROM mechanical_statement WHERE mechanical_run_id IN (
          SELECT mechanical_run_id FROM mechanical_run WHERE payor_id = " . $class->quote($payorID) . " AND status != " . RPS::RoyaltyRun::Status::kInvalidated;

    # If a run id is passed in, we want to exclude that run from this list.
    # We need do that so that we can regenerate the mechanical reserve pipeline
    # report for a committed run.
    if ($runID) {
        $sql .= " AND mechanical_run_id != " . $class->quote($runID);
    }

    $sql .= "))))) AND periods_remaining > 0";
    return $class->SUPER::GetAll($sql);
}

###
1;    #
###