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

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::RoyaltyRun::Status;

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


sub _config
{
    return 
    {
        'catalog_number'    => {},
        'album_title'       => {},
        'album_id'          => {},
        'artist_name'       => {},
        'payee_name'        => {},
        'product_type'      => {},
        'release_date'      => {},
        'current_period'    => {},
        'retentions'        => {},
        'tv_retentions'     => {},
        'tv_advertised'     => {},
        'released'          => {},

    };
}


# This will return a list of retentions held and released in the current period (by product).
#
sub GetCurrentRetentions
{
    my ($class, $runID) = @_;
    
    # First, create a temporary table for this report.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('DROP TABLE IF EXISTS zzuk_mech_current_retentions');
    $dbo->DoCmd('CREATE TEMPORARY TABLE zzuk_mech_current_retentions ('
    . '`catalog_number` varchar(255),'
    . '`album_title` varchar(255),'
    . '`album_id` int(10) unsigned,' 
    . '`artist_name` varchar(255),'    
    . '`product_type` varchar(25),'
    . '`release_date` date,'
    . '`current_period` tinyint(1),'
    . '`retentions` int(10),'   
    . '`tv_retentions` int(10),' 
    . '`tv_advertised` tinyint(1),'
    . '`released` int(10) )');     
    
    my $statementItems = RPS::DB::Item::McpsStatementItem->GetByRunID($runID);
    
    # We'll need to know the run status to figure out current period, so let's grab that now.
    #
    my $runData = RPS::DB::Item::UKMechanicalRun->Lookup(uk_mechanical_run_id => $runID);
    my $runStatus = $runData->status();
 
	while ($statementItems->hasNext())
	{
		my $item = $statementItems->next();    
		
		my $retentions = $item->retentions;
		my $tvRetentions = $item->tv_retentions;
		
		my $mcpsLicenseID = $item->mcps_license_id;
        my $mcpsLicense = RPS::DB::Item::McpsLicense->Lookup(mcps_license_id => $mcpsLicenseID);
        next unless $mcpsLicense;

		my $currentPeriod = _getPeriodsCompleted($mcpsLicense, $runStatus);
		
		if ($retentions || $tvRetentions || $currentPeriod == 5)
		{

            my $productType = _idToProductType($item->product_type_id);
            
            my $tvAdvertised = 0;

            my $released = 0;
            # If this is period 5 for this license,
            # all retentions are liquidated.
            if ($currentPeriod == 5)
            {
                $released = RPS::DB::Item::McpsLicenseRetention->GetUnitSumByLicense($mcpsLicenseID);
                
                my $retentionCategory = $mcpsLicense->retention_category();
                if ($retentionCategory eq 'T')
                {
                    $tvAdvertised = 1;   
                } 
            }
            elsif ($tvRetentions > 0)
            {
                # We could figure this out in the template based on retentions and tv retentions values.
                # But since that won't work for lines with only releases, we may as well fill it in here too. 
               $tvAdvertised = 1;            
            }

            $dbo->DoCmdWithPlaceholders('INSERT INTO zzuk_mech_current_retentions SET'
            . " catalog_number= ?,"
            . " album_title= ?,"
            . " album_id= ?,"        
            . " artist_name= ?,"
            . " product_type= ?,"
            . " release_date= ?,"
            . " current_period= ?,"
            . " retentions= ?,"
            . " tv_retentions= ?,"     
            . " tv_advertised= ?,"            
            . " released= ?",
            [
                $item->catalog_number,
                $item->album_title,
                $item->album_id,
                $item->artist_name,
                $productType,
                $item->release_date,
                $currentPeriod,
                $retentions,
                $tvRetentions,
                $tvAdvertised,
                $released,
            ]
            ); 
        }  
                         
    }    
    
    my $sql = "SELECT catalog_number, album_title, album_id, artist_name, product_type, current_period, release_date, SUM(retentions) as retentions, SUM(tv_retentions) as tv_retentions, tv_advertised, released FROM zzuk_mech_current_retentions GROUP BY album_id, product_type ORDER BY catalog_number, product_type";
    
    return $class->SUPER::GetAll($sql);
    
}


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 ($mcpsLicense, $runStatus) = @_;

	my $initialPeriods = $mcpsLicense->initial_periods_completed;
	my $committedPeriods = RPS::DB::Item::McpsLicenseRun->NumCommittedRuns($mcpsLicense->mcps_license_id);
	
	my $periodsCompleted = $initialPeriods + $committedPeriods;    
    
    
    # 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;