#------------------------------------------------------------
# Copyright (C) 2007 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------

package RPS::DB::Item::ReportQueries::BaseLicenseOverage;

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::Product;
use RPS::DB::Item::ReportQueries;

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

sub _config {
    return {
        'label_name'      => {},
        'catalog_number'  => {},
        'album_title'     => {},
        'track_title'     => {},
        'artist_name'     => {},
        'track_order'     => {},
        'track_id'        => {},
        'album_id'        => {},
        'product_type_id' => {},
        'region_name'     => {},
        'overage'         => {},
    };
}

sub _getLicenseTableName {
    my ($self) = @_;
    assert( 0, 'override this' );
}

sub GetTracksWithLicenseOverage {
    my ($class) = @_;

    # This method uses database queries against the track license table
    # (the name of which must be defined in the derived class) to
    # determine if a track has a license overage.
    #
    my $licenseTable = $class->_getLicenseTableName();

    # Get a list of all the distinct product type ids from the client's
    # set of track licenses.
    #
    my @distinctProductTypeIDs = $class->_GetDistinctProductTypeIDs($licenseTable);

    my $dbo = Common::RSApp::GetClientDB();

    # A temporary table is used to keep track of tracks and their licenses.
    #
    # First, the table is pre-filled with the following types of licenses:
    #
    #   * "All Products" (product_type_id = NULL)
    #   * "All Physical Products" (product_type_id = 255)
    #   * "All Digital Products" (product_type_id = 254)
    #
    # Note: see RPS/DB/Item/Product.pm for product and license type definitions.
    #
    # Also, notice that we're forcing the data type on the overage column to
    # DECIMAL when creating the table (if we don't do this it defaults to DOUBLE).
    #
    # If left as floating point, this can cause unexpected behavior when we try
    # to query for records having an overage <= 100, due to the overage not
    # being stored as an exact value.  Changing the overage to DECIMAL results
    # in an exact value being stored, which allows us to successfully query for
    # records with an overage <= 100.
    #

    $dbo->DoCmd('DROP TABLE IF EXISTS zzoverage');
    $dbo->DoCmd( 'CREATE TEMPORARY TABLE zzoverage (overage DECIMAL(8,4) NOT NULL) AS'
          . ' SELECT'
          . '   b.track_id,'
          . '   b.artist_id,'
          . '   b.album_id,'
          . '   a.product_type_id,'
          . '   a.region_id,'
          . '   sum(a.share) as overage'
          . ' FROM '
          . $licenseTable
          . ' a, track b'
          . ' WHERE a.track_id = b.track_id'
          . '  AND ( a.product_type_id is NULL'
          . '        OR a.product_type_id IN (254, 255) ) '
          . '  AND a.inactive = 0'
          . '  AND ( a.term_start IS NULL '
          . '        OR ( a.term_start IS NOT NULL AND a.term_start <= now() ) ) '
          . '  AND ( a.term_end IS NULL '
          . '        OR ( a.term_end IS NOT NULL AND a.term_end >= now() ) ) '
          . ' GROUP BY a.track_id, a.region_id, a.product_type_id ' );

    # Next, we loop over all of the licensed product types and check for
    # any licensing overlap due to an ALL, ALL-D and/or ALL-P license.
    #
    my %shareMap;
    foreach my $productTypeID (@distinctProductTypeIDs) {
        my $sth1 =
          $dbo->DoCmd( ' SELECT'
              . '   b.track_id,'
              . '   b.artist_id,'
              . '   b.album_id,'
              . '   a.product_type_id,'
              . '   a.region_id,'
              . '   sum(a.share) as overage'
              . ' FROM '
              . $licenseTable
              . ' a, track b'
              . ' WHERE a.track_id = b.track_id'
              . "  AND a.product_type_id = " . $dbo->DBQuote($productTypeID)
              . '  AND a.inactive = 0'
              . '  AND ( a.term_start IS NULL '
              . '        OR ( a.term_start IS NOT NULL AND a.term_start <= now() ) ) '
              . '  AND ( a.term_end IS NULL '
              . '        OR ( a.term_end IS NOT NULL AND a.term_end >= now() ) ) '
              . ' GROUP BY a.track_id, a.region_id, a.product_type_id ' );
        while ( my $hr1 = $sth1->fetchrow_hashref() ) {
            my $artistID = $hr1->{'artist_id'};
            if ( !$artistID ) {

                # !!! Not all tracks _have_ an artist ID...
                #
                $artistID = 'NULL';
            }

            my $productID = $hr1->{'product_type_id'};

            # Store the share percentage for the license product type.  If we've already
            # seen a similar license then we'll add onto the share value.
            #
            $shareMap{ $hr1->{'track_id'} }{ $hr1->{'album_id'} }{$artistID}{$productTypeID}{ $hr1->{'region_id'} } +=
                $hr1->{'overage'};

            # Look for overages due to All Products (NULL), All Physical (255),
            # and/or All Digital (254) licenses.
            #
            # Note: for ease of maintenance, this section of code is shared by both the US
            # and CA overage reports, however the ringtone license exclusion only applies
            # to US Mechanicals since there are no RING licenses in CA Mechanicals.  E.g.,
            # for the CA report we're always going to be checking for overages with all licenses.
            #
            if ( $productID && RPS::DB::Item::Product::kProductTypeRingtone != $productID ) {

                # If this is a digital license, grab the ALL DIGITAL (254) total,
                # if any, and add to our current value.
                #
                if (   RPS::DB::Item::Product::kProductTypeDigital == $productID
                    || RPS::DB::Item::Product::kProductTypeDigitalTrack == $productID ) {
                    my $selectSql =
                        'SELECT overage from zzoverage where track_id='
                      . $hr1->{'track_id'}
                      . ' AND album_id='
                      . $hr1->{'album_id'}
                      . " AND artist_id=$artistID"
                      . ' AND region_id='
                      . $hr1->{'region_id'}
                      . ' AND product_type_id = '
                      . RPS::DB::Item::Product::kAllDigitalProducts;    # 254

                    my $sth2 = $dbo->DoCmd($selectSql);
                    my $hr2  = $sth2->fetchrow_hashref();
                    if ($hr2) {
                        $shareMap{ $hr1->{'track_id'} }{ $hr1->{'album_id'} }{$artistID}{$productTypeID}{ $hr1->{'region_id'} } +=
                          $hr2->{'overage'};
                    }
                }

                elsif (RPS::DB::Item::Product::kAllDigitalProducts != $productID
                    && RPS::DB::Item::Product::kAllPhysicalProducts != $productID ) {

                    # If the license isn't digital, and it's not ALL-D or ALL-P,
                    # then it must be physical.
                    # Grab the ALL PHYSICAL (255) total, if any, and add to our current value.
                    #
                    my $selectSql =
                        'SELECT overage from zzoverage where track_id='
                      . $hr1->{'track_id'}
                      . ' AND album_id='
                      . $hr1->{'album_id'}
                      . " AND artist_id=$artistID"
                      . ' AND region_id='
                      . $hr1->{'region_id'}
                      . ' AND product_type_id = '
                      . RPS::DB::Item::Product::kAllPhysicalProducts;    # 255

                    my $sth2 = $dbo->DoCmd($selectSql);
                    my $hr2  = $sth2->fetchrow_hashref();
                    if ($hr2) {
                        $shareMap{ $hr1->{'track_id'} }{ $hr1->{'album_id'} }{$artistID}{$productTypeID}{ $hr1->{'region_id'} } +=
                          $hr2->{'overage'};
                    }
                }

                # Grab the ALL PRODUCTS ('NULL') total, if any, and add to our current value.
                #
                my $selectSql =
                    'SELECT overage from zzoverage where track_id='
                  . $hr1->{'track_id'}
                  . ' AND album_id='
                  . $hr1->{'album_id'}
                  . " AND artist_id=$artistID"
                  . ' AND region_id='
                  . $hr1->{'region_id'}
                  . ' AND product_type_id IS NULL';

                my $sth2 = $dbo->DoCmd($selectSql);
                my $hr2  = $sth2->fetchrow_hashref();
                if ($hr2) {
                    $shareMap{ $hr1->{'track_id'} }{ $hr1->{'album_id'} }{$artistID}{$productTypeID}{ $hr1->{'region_id'} } +=
                      $hr2->{'overage'};
                }
            }
        }
    }
    foreach my $trackID ( keys %shareMap ) {
        foreach my $albumID ( keys %{ $shareMap{$trackID} } ) {
            foreach my $artistID ( keys %{ $shareMap{$trackID}{$albumID} } ) {
                foreach my $productTypeID ( keys %{ $shareMap{$trackID}{$albumID}{$artistID} } ) {
                    foreach my $regionID ( keys %{ $shareMap{$trackID}{$albumID}{$artistID}{$productTypeID} } ) {
                        if ( !$artistID ) {
                            $artistID = 'NULL';
                        }
                        my $overage = $shareMap{$trackID}{$albumID}{$artistID}{$productTypeID}{$regionID};
                        $dbo->DoCmd( 'INSERT into zzoverage SET'
                              . " track_id="        . $dbo->DBQuote($trackID)       . ","
                              . " album_id="        . $dbo->DBQuote($albumID)       . ","
                              . " artist_id="       . $dbo->DBQuote($artistID)      . ","
                              . " product_type_id=" . $dbo->DBQuote($productTypeID) . ","
                              . " overage="         . $dbo->DBQuote($overage)       . ","
                              . " region_id="       . $dbo->DBQuote($regionID)
                        );
                    }
                }
            }
        }
    }

    $dbo->DoCmd('DELETE FROM zzoverage where overage <= 100');

    # Whew!  Ok, got the silly report table.  Now we can render it.
    #
    $class->_ProduceReportCollection();
}

sub _GetDistinctProductTypeIDs {
    my ( $class, $tableName ) = @_;

    my @productIDs;
    my $sql = 'SELECT DISTINCT product_type_id from ' . $tableName;
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        if ( defined $hr->{product_type_id} ) {
            push @productIDs, $hr->{product_type_id};
        }
    }

    return @productIDs;
}

sub _ProduceReportCollection {
    my ($class) = @_;

    my @selectFields;
    push @selectFields, "label.label_name as label_name";
    push @selectFields, "album.catalog_number as catalog_number";
    push @selectFields, "album.title as album_title";
    push @selectFields, "track.title as track_title";
    push @selectFields, "artist.name as artist_name";
    push @selectFields, "track.track_order as track_order";
    push @selectFields, "zzoverage.track_id as track_id";
    push @selectFields, "track.album_id as album_id";
    push @selectFields, "region.name as region_name";
    push @selectFields, "zzoverage.overage as overage";
    push @selectFields, "zzoverage.product_type_id as product_type_id";

    #push @selectFields, "CASE WHEN zzoverage.product_type_id is not null THEN product_type.description ELSE 'All' END AS 'product_type'";

    my $selectClause = join( ',', @selectFields );
    my $fromClause = "zzoverage";

    # Note the big nasty implicit join.
    #
    my $joinClause = 'LEFT OUTER JOIN artist ON zzoverage.artist_id = artist.artist_id';
    $joinClause .= ' LEFT OUTER JOIN album ON zzoverage.album_id = album.album_id';
    $joinClause .= ' LEFT OUTER JOIN track ON zzoverage.track_id = track.track_id';
    $joinClause .= ' LEFT OUTER JOIN label ON label.label_id = album.label_id';
    $joinClause .= ' LEFT OUTER JOIN region ON zzoverage.region_id = region.region_id';

    #$joinClause .= ' LEFT OUTER JOIN product_type ON zzoverage.product_type_id = product_type.product_type_id';

    my $orderByClause = 'label_name, album_title, track_order, region_name';

    my $sql = "SELECT DISTINCT $selectClause FROM $fromClause $joinClause ORDER BY $orderByClause";

    return $class->SUPER::GetAll($sql);
}

1;