#------------------------------------------------------------
# Copyright (C) 2007 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::ReportQueries::UKMechRetentionRunSummary;

use strict;
use warnings;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::UKMechanicalRun;
use RPS::DB::Item::McpsStatementItem;
use RPS::DB::Item::McpsLicenseRun;
use RPS::DB::Item::McpsLicense;
use RPS::DB::Item::McpsLicenseRetention;
use RPS::DB::Item::ReportQueries;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::Artist;

use RPS::RoyaltyRun::Status;

use base 'RPS::DB::Item::ReportQueries';

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


sub _config
{
    return 
    {
        'catalog_number'    => {},
        'album_title'       => {},
        'album_id'          => {},
        'artist_name'       => {},
        'product_type'      => {},
        'release_date'      => {},
        'tv_advertised'     => {},        
        'current_period'    => {},
        'p1_held'           => {},
        'p2_held'           => {},
        'p3_held'           => {},
        'p4_held'           => {},        
        'total_held'        => {},

    };
}


# This will return a list of retentions held and released as of the current period (by product).
#
sub GetRetentionSummary
{
    my ($class, $runID) = @_;
    
    my $sql = "SELECT * FROM " . kTable . " WHERE uk_mechanical_run_id = $runID ORDER BY catalog_number, product_type";
    
    return $class->SUPER::GetAll($sql);
    
}

sub Delete {
    my ($class, $runID) = @_;
    
    # Let's make sure we have a run id before we get crazy with the deleting.
    #
    assert($runID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "DELETE FROM " . kTable . " WHERE " .
              "uk_mechanical_run_id = " . $runID;

    $dbo->DoCmd($sql);
}

sub CreateRetentionSummary
{
    my ($class, $runID) = @_;
       
    my $runData = RPS::DB::Item::UKMechanicalRun->Lookup(uk_mechanical_run_id => $runID);
    my $payorID = $runData->payor_id();
    my $runStatus = $runData->status();
    my $maxPeriods = 5;
    
    # Depending on whether a run is committed or not, we want to grab mcps licenses
    # with a periods_completed value less than 5 or 4, respectively.
    if ($runStatus == RPS::RoyaltyRun::Status::kComplete)
    {
        $maxPeriods--;
    }    
    
    my $licenses = RPS::DB::Item::McpsLicense->GetAllWithActiveRetentions($maxPeriods, $payorID);

    if ($licenses)
    {

        # Okay, now we've got all the mcps licenses for this payor that are still in the first 5 periods.
        # The trick is that we need to check the mcps_license_retention table (for past retentions) 
        # and the mcps_statement_item table (for current retentions) to get the full picture.
    
    
        my $dbo = Common::RSApp::GetClientDB();
     
    	while ($licenses->hasNext())
    	{
    
    		my $license = $licenses->next();    
    		my $licenseID = $license->mcps_license_id;
    		
    		my $initialPeriods = $license->initial_periods_completed;
    		my $committedPeriods = RPS::DB::Item::McpsLicenseRun->NumCommittedRuns($licenseID);
    		
    		my $completedPeriods = $initialPeriods + $committedPeriods;
    		
    		my $currentPeriod = _getPeriodsCompleted($completedPeriods, $runStatus);
    	
    		my $productID = $license->product_id;
    		my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);		
            my $productType = _idToProductType($product->product_type_id);
            my $releaseDate = $product->release_date();
            
            my $albumID = $product->asset_id();
            my $album = RPS::DB::Item::Album->Lookup(album_id => $albumID);
            my $catalogNumber = $album->catalog_number();
            my $albumTitle = $album->title();
            my $artistID = $album->artist_id();
            my $artist = RPS::DB::Item::Artist->Lookup(artist_id => $artistID);
            my $artistName = $artist->name();      
                
            my $tvAdvertised = 0;
            my $retentionCategory = $license->retention_category();
            if ($retentionCategory eq 'T')
            {
                $tvAdvertised = 1;   
            }         
            
            my $p1Held = 0;
            my $p2Held = 0;
            my $p3Held = 0;
            my $p4Held = 0;
    
            # So here's the fun part.  The units could be stored in the mcps_license_retention table 
            # or the mcps_statement_item table. It depends on the current period AND the run status.
        
            if (($currentPeriod == 4 && $runStatus != RPS::RoyaltyRun::Status::kComplete) || $currentPeriod > 4)
            {
                $p4Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 4);
            }
            elsif ($currentPeriod == 4)
            {
                $p4Held = RPS::DB::Item::McpsStatementItem->GetRetentionsByLicenseAndRun($licenseID, $runID, $tvAdvertised);
            }    
     
            if (($currentPeriod == 3 && $runStatus != RPS::RoyaltyRun::Status::kComplete) || $currentPeriod > 3)
            {
                $p3Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 3);
            }
            elsif ($currentPeriod == 3)
            {
                $p3Held = RPS::DB::Item::McpsStatementItem->GetRetentionsByLicenseAndRun($licenseID, $runID, $tvAdvertised);
            }              
                    
            if (($currentPeriod == 2 && $runStatus != RPS::RoyaltyRun::Status::kComplete) || $currentPeriod > 2)
            {
                $p2Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 2);
            }
            elsif ($currentPeriod == 2)
            {
                $p2Held = RPS::DB::Item::McpsStatementItem->GetRetentionsByLicenseAndRun($licenseID, $runID, $tvAdvertised);
            }        
    
            if (($currentPeriod == 1 && $runStatus != RPS::RoyaltyRun::Status::kComplete) || $currentPeriod > 1)
            {
                $p1Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod($licenseID, 1);
            }
            elsif ($currentPeriod == 1)
            {
                $p1Held = RPS::DB::Item::McpsStatementItem->GetRetentionsByLicenseAndRun($licenseID, $runID, $tvAdvertised);
            }   
            
    
            my $totalHeld = $p1Held + $p2Held + $p3Held + $p4Held;
            
    
            $dbo->DoCmdWithPlaceholders('INSERT INTO uk_mech_retention_run_summary SET'
            . " uk_mechanical_run_id= ?,"
            . " catalog_number= ?,"
            . " album_title= ?,"
            . " album_id= ?,"        
            . " artist_name= ?,"
            . " product_type= ?,"
            . " release_date= ?,"
            . " tv_advertised= ?,"             
            . " current_period= ?,"
            . " p1_held= ?,"
            . " p2_held= ?,"     
            . " p3_held= ?,"
            . " p4_held= ?,"          
            . " total_held= ?",
            [
                $runID,
                $catalogNumber,
                $albumTitle,
                $albumID,
                $artistName,
                $productType,
                $releaseDate,
                $tvAdvertised,
                $currentPeriod,
                $p1Held,
                $p2Held,
                $p3Held,
                $p4Held,            
                $totalHeld,
            ]
            ); 
                             
        }    
    
    }    
}


sub _idToProductType
{
    my ($id) = @_;

    # We have a table for this, might as well use it.
    #
    my $productTypeEntry = RPS::DB::Item::ProductType->Lookup(product_type_id => $id);
    if (! $productTypeEntry)
    {
        return '???';
    }
    return $productTypeEntry->description();
}


sub _getPeriodsCompleted
{
    my ($periodsCompleted, $runStatus) = @_;
    
    # Periods completed is not updated until a run is committed,
    # so let's see if we need to increment it.
    
    if ($runStatus == RPS::RoyaltyRun::Status::kComplete)
    {
        $periodsCompleted++;
    }
    
    return $periodsCompleted;
}


1;