#------------------------------------------------------------
# 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 = " . $class->quote($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 = " . $class->quote($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;