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

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::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 base 'RPS::DB::Item::ReportQueries';

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 now (br product).
#
sub GetRetentionSummary {
    my ( $class, $runID ) = @_;

    # First, create a temporary table for this report.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('DROP TABLE IF EXISTS zzuk_mech_retention_summary');
    $dbo->DoCmd( 'CREATE TEMPORARY TABLE zzuk_mech_retention_summary ('
          . '`catalog_number` varchar(255),'
          . '`album_title` varchar(255),'
          . '`album_id` int(10) unsigned,'
          . '`artist_name` varchar(255),'
          . '`product_type` varchar(25),'
          . '`release_date` date,'
          . '`tv_advertised` tinyint(1),'
          . '`current_period` tinyint(1),'
          . '`p1_held` int(10),'
          . '`p2_held` int(10),'
          . '`p3_held` int(10),'
          . '`p4_held` int(10),'
          . '`total_held` int(10) )' );

    my $maxPeriods = 5;

    my $licenses = RPS::DB::Item::McpsLicense->GetAllWithActiveRetentions($maxPeriods);

    # Okay, now we've got all the mcps licenses that are still in the first 5 periods.

    while ( defined $licenses && $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 $currentPeriod = $initialPeriods + $committedPeriods;

        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 $albumID       = $album->album_id();
        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 > 3 ) {
            $p4Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod( $licenseID, 4 );
        }

        if ( $currentPeriod > 2 ) {
            $p3Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod( $licenseID, 3 );
        }

        if ( $currentPeriod > 1 ) {
            $p2Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod( $licenseID, 2 );
        }

        if ( $currentPeriod > 0 ) {
            $p1Held = RPS::DB::Item::McpsLicenseRetention->GetUnitsByLicenseAndPeriod( $licenseID, 1 );
        }

        my $totalHeld = $p1Held + $p2Held + $p3Held + $p4Held;

        $dbo->DoCmdWithPlaceholders(
            'INSERT INTO zzuk_mech_retention_summary SET'
              . " 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= ?",
            [
                $catalogNumber, $albumTitle, $albumID, $artistName, $productType, $releaseDate, $tvAdvertised,
                $currentPeriod, $p1Held,     $p2Held,  $p3Held,     $p4Held,      $totalHeld,
            ]
        );

    }

    my $sql = "SELECT * FROM zzuk_mech_retention_summary 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();
}

1;