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

package RPS::DB::Item::ReportQueries::CATrackWithoutLicenses;

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 GetTracksWithoutLicenses
{
    my ($class) = @_;

    # Can't think of a good way to do this all in a single query
    #
    # Can I think of a good way to use multiple queries, then create
    # a big ole' nasty explicit sql statement to pass to Item::Collection?
    #
    my %productTypeIDMap;

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

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

    # We'll create a temporary table.
    #  !!! To avoid weird race conditions, it seems wise to create a truly unique table name...
    #  !!! but then I'll have to get rid of it.  That will be harder...
    #
    $dbo->DoCmd('DROP TABLE IF EXISTS zzunderage');
    $dbo->DoCmd('CREATE TEMPORARY TABLE zzunderage AS'
     . ' SELECT'
     . '   b.track_id,'
     . '   b.artist_id,'
     . '   b.album_id,'
     . '   a.product_type_id,'
     . '   a.region_id,'
     . '   sum(a.share) as underage'
     . ' FROM ca_track_license a, track b'
     . ' WHERE a.track_id = b.track_id'
     . '  AND a.product_type_id is NULL'
     . '  AND b.ca_mechanical_exempt=0'
     . '  AND a.inactive=0'
     . ' GROUP BY a.track_id, a.region_id');

    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 underage'
        . ' FROM ca_track_license a, track b'
        . ' WHERE a.track_id = b.track_id'
        . "  AND a.product_type_id=$productTypeID"
        . '  AND b.ca_mechanical_exempt=0'
        . '  AND a.inactive=0'
        . ' GROUP BY a.track_id, a.region_id'
        );
        while (my $hr1 = $sth1->fetchrow_hashref())
        {
            my $artistID = $hr1->{'artist_id'};
            if (! defined $artistID)
            {
                $artistID = 'NULL';
            }
            my $share = $hr1->{'underage'};

            # Grab the 'NULL' total, if any, and add to our current value.
            #
            my $sth2 = $dbo->DoCmd('SELECT underage from zzunderage where track_id='.$hr1->{'track_id'}
             .' AND album_id='.$hr1->{'album_id'}
             .' AND region_id='.$hr1->{'region_id'}
             .' AND product_type_id IS NULL');
            my $hr2 = $sth2->fetchrow_hashref();
            if( $hr2 )
            {
                $share += $hr2->{underage};
                $dbo->DoCmd('INSERT into zzunderage SET'
                 . ' track_id='.$hr1->{'track_id'}.','
                 . ' album_id='.$hr1->{'album_id'}.','
                 . " artist_id=$artistID,"
                 . ' region_id='.$hr1->{'region_id'}.','
                 . ' product_type_id='.$hr1->{'product_type_id'}.','
                 . ' underage='.$share
                 );
            }
        }
    }

    # We also want to see tracks that have _no_ licenses in them.
    my $noneH = $dbo->DoCmd('select * from track where ca_mechanical_exempt=0 and track_id not in (select track_id from ca_track_license where inactive=0)');
    while (my $noneHR = $noneH->fetchrow_hashref())
    {
        my $artistID = $noneHR->{'artist_id'};
        if (! defined $artistID)
        {
            $artistID = 'NULL';
        }

        $dbo->DoCmd('INSERT INTO zzunderage SET'
         . ' track_id='.$noneHR->{'track_id'}.','
         . ' album_id='.$noneHR->{'album_id'}.','
         . " artist_id=$artistID,"
         . ' underage=0');
    }

    $dbo->DoCmd('DELETE FROM zzunderage where underage >= 99');


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


sub _GetDistinctProductTypeIDs
{
    my ($class) = @_;
    
    my @productIDs;
    my $sql = 'SELECT DISTINCT product_type_id from ca_track_license';
    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.underage 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;