#------------------------------------------------------------
# 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;