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

package RPS::DB::Item::ReportQueries::TrackWithoutLicenses;

use strict;
use warnings;
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::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'       => {},
        'share'             => {},

    };
}

sub _licenseTable { die "Must be overloaded" }

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

    # There are some big assumptions in these sets of queries:
    #
    #   1. All tracks have digital track products IF there is a digital album product.
    #   2. If a product is licensed in 1 or more regions, its corresponding unknown
    #      region record is removed.
    #   3. Licenses on tracks that don't have a corresponding product type are ignored.
    #      i.e. Album #1 has products DA, CD and implicitly DT for each track.
    #           Track #1 on this album has a license for RING.  That license will be
    #           ignored in this report.

    my %productTypeIDMap;
    my $licenseTable = $class->_licenseTable();


    # Get a list of all the distinct product type ids
    #
    my @distinctProductTypeIDs = $class->_GetDistinctProductTypeIDs();

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

    # A temporary table is required here because multiple passes are needed to
    # calculate missing coverage.
    $dbo->DoCmd('DROP TABLE IF EXISTS zzunderage');
    $dbo->DoCmd('CREATE TABLE `zzunderage` (' .
                ' track_id int(10) unsigned NOT NULL, ' .
                ' artist_id int(10) unsigned NOT NULL, ' .
                ' album_id int(10) unsigned NOT NULL, ' .
                ' product_type_id int(10) unsigned DEFAULT NULL, ' .
                ' region_id int(10) unsigned NOT NULL, ' .
                ' share decimal(16,4) default 0, ' .
                ' UNIQUE KEY `zzunderage_index1` (`track_id`, `region_id`, `product_type_id`) ' .
                ' )'
    );

    # First we need to add all tracks with album level products (i.e. CD, DA, etc)
    # to the table with their license information.
    #
    # Note:  the product_type_id > 0 clause is added because it looks like if there is an
    # error on the product entry page a product can be created with type 0
    #
    # So, there seems to be a bug or something in MySQL that can cause DISTINCTROW to hang
    # when combined with GROUP BY. https://bugs.mysql.com/bug.php?id=26943
    #
    # Instead we'll run with the ON DUPLICATE trick with a net effect of leaving share untouched.
    # Duplicates can occur when we have the same product_type_id for a few products on the
    # one ablum, for example.
    $dbo->DoCmd( "
        INSERT INTO zzunderage
            SELECT track.track_id, track.artist_id, track.album_id, product.product_type_id,
                   $licenseTable.region_id, sum( IF($licenseTable.share IS NULL, 0, $licenseTable.share) )
            FROM track
                 INNER JOIN product ON ( album_id = product.asset_id and product.product_type_id <> 4 )
                 LEFT JOIN $licenseTable ON ( track.track_id = $licenseTable.track_id AND
                                              product.product_type_id = $licenseTable.product_type_id )
            WHERE product.product_type_id > 0
            GROUP BY track.track_id, product_id, $licenseTable.region_id
        ON DUPLICATE KEY UPDATE zzunderage.share = zzunderage.share;
    ");

    # Now we need to add all digital track products.  The assumption here is that all
    # tracks implicitly have a digital track product IF there is a digital album product.
    #
    $dbo->DoCmd( "
        INSERT INTO zzunderage
            SELECT track.track_id, track.artist_id, track.album_id, 4 as product_type_id,
                   $licenseTable.region_id, sum( IF($licenseTable.share IS NULL, 0, $licenseTable.share) )
            FROM track
                 INNER JOIN product ON ( album_id = product.asset_id and product.product_type_id = 3 )
                 LEFT JOIN $licenseTable ON ( track.track_id = $licenseTable.track_id AND
                                              $licenseTable.product_type_id = 4 )
            GROUP BY track.track_id, $licenseTable.region_id;
    ");

    # Now we need to go through and apply all the licenses with aggregate types.
    #
    # This is using a little MySQL trickery.  We join all of the track licenses
    # for the aggregate type to the relevant rows in the underage table.  Then we
    # attempt to insert a new row for the region.  By using a unique constraint
    # on the table we are able to increment duplicate license share.

    # All Products
    $dbo->DoCmd("
        INSERT INTO zzunderage
            SELECT z.track_id, z.artist_id, z.album_id, z.product_type_id,
                   $licenseTable.region_id, $licenseTable.share
            FROM zzunderage as z
                 LEFT JOIN $licenseTable ON ( $licenseTable.track_id = z.track_id AND
                                               $licenseTable.product_type_id IS NULL )
        ON DUPLICATE KEY UPDATE zzunderage.share = zzunderage.share + $licenseTable.share;
    ");

    # All Digital
    my $digitalIDs = join( ",", $class->_getDigitalTypes );
    $dbo->DoCmd("
        INSERT INTO zzunderage
            SELECT z.track_id, z.artist_id, z.album_id, z.product_type_id,
                   $licenseTable.region_id, $licenseTable.share
            FROM zzunderage as z
                 INNER JOIN $licenseTable ON ( $licenseTable.track_id = z.track_id AND
                                               $licenseTable.product_type_id = 254 )
            WHERE z.product_type_id IN ( $digitalIDs )
        ON DUPLICATE KEY UPDATE zzunderage.share = zzunderage.share + $licenseTable.share;
    ");

    # All Physical
    my $physicalIDs = join( ",", $class->_getPhysicalTypes );
    $dbo->DoCmd("
        INSERT INTO zzunderage
            SELECT z.track_id, z.artist_id, z.album_id, z.product_type_id,
                   $licenseTable.region_id, $licenseTable.share
            FROM zzunderage as z
                 INNER JOIN $licenseTable ON ( $licenseTable.track_id = z.track_id AND
                                               $licenseTable.product_type_id = 255 )
            WHERE z.product_type_id IN ( $physicalIDs )
        ON DUPLICATE KEY UPDATE zzunderage.share = zzunderage.share + $licenseTable.share;
    ");

    # Now we need to go through and find all ringtone licenses because they do not
    # have products associated to albums
    $dbo->DoCmd("
        INSERT INTO zzunderage
            SELECT $licenseTable.track_id, artist_id, album_id, product_type_id,
                   region_id, sum(share) as share
            FROM $licenseTable INNER JOIN track using (track_id)
            WHERE product_type_id = 15
            GROUP BY $licenseTable.track_id, $licenseTable.region_id;
    ");

    # Clean up entries with 0 region_id that have matching entries with a region.
    $dbo->DoCmd('
        DELETE zzunderage.*
        FROM zzunderage
             INNER JOIN zzunderage as z2 ON ( zzunderage.track_id = z2.track_id AND
                                              zzunderage.product_type_id = z2.product_type_id AND
                                              zzunderage.region_id <> z2.region_id )
        WHERE zzunderage.region_id = 0
    ');

    # The next two queries aggregate product records that have no licenses associated to
    # them and creates a single record for 'all' products.  This cleans up the report a little
    #
    # The first query adds the 'ALL' reacord and the second query deletes all the
    # product specific records.
    $dbo->DoCmd("
        INSERT INTO zzunderage (track_id, artist_id, album_id)
            SELECT track_id, artist_id, album_id
            FROM
                (SELECT track.track_id, artist_id, album_id, count( $licenseTable.track_id ) as licensed, count(*) as total
                 FROM track
                      LEFT JOIN $licenseTable using (track_id)
                 GROUP BY track.track_id ) as subq
            WHERE licensed = 0;
    ");
    $dbo->DoCmd("
        DELETE zzunderage.*
        FROM zzunderage
             INNER JOIN
                (SELECT track.track_id, artist_id, album_id, count( $licenseTable.track_id ) as licensed, count(*) as total
                 FROM track
                      LEFT JOIN $licenseTable using (track_id)
                 GROUP BY track.track_id ) as subq USING (track_id)
        WHERE licensed = 0 AND product_type_id > 0;
    ");

    $dbo->DoCmd('DELETE FROM zzunderage where share >= 100');

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

sub _getDigitalTypes {
    my $class = shift;
    return $class->_getTypes( RPS::DB::Item::ProductType->GetAllDigital() );
}

sub _getPhysicalTypes {
    my $class = shift;
    return $class->_getTypes( RPS::DB::Item::ProductType->GetAllPhysical() );
}

sub _getTypes {
    my $class = shift;
    my $collection = shift || return;

    my @productTypes;
    while( my $type = $collection->next() ) {
        push( @productTypes, $type->product_type_id );
    }

    return @productTypes;
}


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

    my $licenseTable = $class->_licenseTable();
    my @productIDs;
    my $sql = "SELECT DISTINCT product_type_id from $licenseTable";
    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, "zzunderage.track_id as track_id";
    push @selectFields, "track.album_id as album_id";
    push @selectFields, "zzunderage.share as share";
    push @selectFields, "zzunderage.product_type_id as product_type_id";
    push @selectFields, "CASE WHEN zzunderage.region_id > 0 THEN region.name ELSE 'N/A' END AS 'region_name'";
    #push @selectFields, "CASE WHEN zzunderage.product_type_id is not null THEN product_type.description ELSE 'All' END AS 'product_type'";

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

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

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

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

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


1;