#------------------------------------------------------------
# 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;