#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::Album;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Util qw(escape_mysql_regexp escape_mysql_like);
use Common::DB::ItemCollection;
use Common::DB::Item;
use Common::Assert;
use Common::RSApp;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Product;

use lib '/app/tools/metadata/lib';
use Metadata::DB::Item::ContentImport;

use lib '/app/tools/distribution/lib';
use Distribution::DB::Item::DistributionService;


use base 'Common::DB::Item';

use constant kTable => 'album';
use constant kDB => Common::DB::Item::kClientDB();


sub Get
{
    my ($class, %args) = @_;

	my $order;
	if($args{order})
	{
		$order = $args{order};
	}
	else
	{
		$order = "album.title, artist.name";
	}

    my @where;

    if (defined $args{alpha})
    {
        my $alpha = $args{alpha};
        if ('0' eq $alpha)
        {
            push @where, "album.title < 'A'";
        }
        elsif ('Z' eq $alpha)
        {
            push @where, "album.title >= 'Z'";
        }
        else
        {
            push @where, "album.title >= '".uc($alpha)."'";
            push @where, "album.title < '".chr( ord(uc($alpha)) + 1)."'";
        }
    }

    if ($args{label_id})
    {
        push @where, "album.label_id = " . $args{label_id};
    }


    my $whereClause;
    if (scalar @where)
    {
        $whereClause = "WHERE " . join(' AND ', @where);
    }


	my $sql = "SELECT album.* FROM album LEFT JOIN artist ON artist.artist_id=album.artist_id $whereClause ORDER BY $order";

    return $class->GetAll($sql);

}

sub GetTitleAlphaIndexByLabelID
{
    my ($class, $pageSize, $labelID) = @_;


    my $sql = 'SELECT letter, min(page) as pagenum FROM'
     . q[ (SELECT lower(substring(title, 1, 1)) as 'letter', FLOOR(((((@rownum := @rownum + 1) ) / ] . $pageSize . q[ ) + 1)) as 'page' ]
     . q[ FROM album INNER JOIN artist ON (artist.artist_id = album.artist_id) ];

    if ($labelID)
    {
        $sql .= "WHERE label_id = " . $class->quote($labelID);
    }

    $sql .= ' ORDER BY lower(album.title) ) AS subq GROUP BY letter';

    return $class->_BuildAlphaIndex($sql);
}


sub _BuildAlphaIndex
{
    my ($class, $sql) = @_;

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

    # The @rownum variable will need to be 'seeded'.
    #
    my $preset = $dbo->DoCmd('set @rownum := 0');

    my $sth = $dbo->DoCmd($sql);

    my %resultHash;
    while (my $row = $sth->fetchrow_hashref())
    {
        my $letter = $row->{letter};
        my $page = $row->{pagenum};
        if (! defined $page)
        {
            return undef;
        }
        if ($letter =~ /[a-z]/)
        {
            $resultHash{ uc($letter) } = $page;
        }
    }

    return \%resultHash;
}


sub Search
{
	my ($class, $searchTerm) = @_;

	my $where;

	if($searchTerm)
	{
		$where = "WHERE ". $class->SearchSyntax(field => "album.title", value => $searchTerm)
			. " OR ". $class->SearchSyntax(field => "album.catalog_number", value => $searchTerm)
			. " OR ". $class->SearchSyntax(field => "upc_ean", value => $searchTerm)
			. " OR ". $class->SearchSyntax(field => "artist.name", value => $searchTerm);
	}
	else
	{
		die "no valid param passed to album search";
	}

	my $sql = "SELECT album.* FROM album LEFT JOIN artist ON artist.artist_id=album.artist_id LEFT JOIN product ON ( album_id = asset_id and product_type_id <> " . RPS::DB::Item::Product::kProductTypeDigitalTrack . " ) $where GROUP BY album_id ORDER BY title";

    Common::Log::Debug( "QUERY: $sql" );
    return $class->GetAll($sql);
}

sub SearchTitleAndCatalogNumber
{
    my ($class, %args) = @_;
   	my $dbo = Common::RSApp::GetClientDB();

    my $searchTerm = $args{searchTerm};
    my $limit = $args{limit};
    my $contractID = $args{contractID};

    my $where;

    if ($searchTerm)
    {
        $where = "(".$class->SearchSyntax(field => 'title', value => $searchTerm)
        . " OR ". $class->SearchSyntax(field => "catalog_number", value => $searchTerm).")";

		    if ($contractID)
		    {
		        $where .= " AND album_id IN (SELECT album_id FROM album_contract WHERE artist_contract_id = " . $contractID . ")";
		    }
    }

    my $sql = 'SELECT * FROM ' . kTable;
    if ($where)
    {
        $sql .= " WHERE $where";
    }

    $sql .= ' ORDER BY title';

    if ($limit)
    {
        $sql .= " LIMIT $limit";
    }

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

sub SearchByAlbumID
{
	my ($class, @ids) = @_;

    @ids = (0) unless( @ids );

	my $sql = "SELECT album.* " .
              "FROM album " .
              "WHERE album_id IN ( " . join( ",", @ids ) . " )";

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

sub HasQueuedJobs {
	my ($class, %args) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    assert( $args{album_id} || $args{upc_ean} );

    my $sql = "SELECT count(*) " .
              "FROM album " .
              "INNER JOIN product ON ( product.asset_id = album.album_id AND " .
                    "product_type_id = " . RPS::DB::Item::Product::kProductTypeDigital . " ) " .
              "INNER JOIN product_distribution USING (product_id) " .
              "WHERE date_queued IS NOT NULL AND delivery_date IS NULL AND denied IS NULL AND invalid <> 1";

    if( $args{album_id} ) {
        if( ref( $args{album_id} ) ) {
            $sql .= " AND album_id in (" . join( ",", @{$args{album_id}} ) . ")";
        } else {
            $sql .= " AND album.album_id = " . $dbo->DBQuote($args{album_id});
        }
    }

    if( $args{upc_ean} ) {
        $sql .= " AND album.album_id = " . $dbo->DBQuote($args{album_id});
    }

    Common::Log::Debug( "QUERY: $sql" );

    my $sth = $dbo->DoCmd( $sql );
    my ($count) = $sth->fetchrow_array();

    return $count ? 1 : undef;
}

sub GetAllNoDistributionCache {
    my $class = shift;
    my $sql = "SELECT album.* FROM album LEFT JOIN album_distribution_ready USING ( album_id ) " .
              "WHERE album_distribution_ready.album_id IS NULL ";
    Common::Log::Debug( "QUERY: $sql" );
    return $class->GetAll($sql);
}


sub GetAllDistributed {
	my ($class, %args) = @_;

    my $sql = "SELECT album.*, upc_ean, MAX(product_distribution.delivery_date) as date_delivered  " .
              "FROM album " .
              "INNER JOIN product ON ( product.asset_id = album.album_id AND " .
                    "product_type_id = " . RPS::DB::Item::Product::kProductTypeDigital . " ) " .
              "INNER JOIN product_distribution USING (product_id) " .
              "WHERE delivery_date IS NOT NULL " .
              "GROUP BY album.album_id " .
              "ORDER BY delivery_date DESC ";

    Common::Log::Debug( "QUERY: $sql" );
    return $class->GetAll($sql);
}

sub GetAllDenied {
	my $class = shift;
	my %args  = @_;
	my $query = $args{query};
	my $serviceID = $args{service_id};
	my $dbo = Common::RSApp::GetClientDB();

	my $denied;

	if( ! defined($query) ) {
		$denied = "denied IS NOT NULL";
	} elsif( $query eq 'delivery_fail' ) {
		$denied = "denied LIKE 'Delivery%'";
	} elsif( $query eq 'packager_fail' ) {
		$denied = "denied LIKE 'Packaging%'";
	} elsif( $query eq 'genre_restricted' ) {
		$denied = "denied LIKE 'Album genres%'";
	} elsif( $query eq 'territory_restricted' ) {
		$denied = "denied LIKE 'Album cannot%'";
	} elsif( $query eq 'utf8_restricted' ) {
		$denied = "denied LIKE 'Language%'";
	} else {
		assert( 0, "Unknown query type" );
	}

	my $serviceSQL = $serviceID ? " AND service_id = " . $dbo->DBQuote($serviceID) . " " : "";

    my $sql = "SELECT album.*, upc_ean, MAX(product_distribution.delivery_date) as date_delivered,  " .
	                " GROUP_CONCAT( DISTINCT(denied) ) as denied " .
              "FROM album " .
              "INNER JOIN product ON ( product.asset_id = album.album_id AND " .
                    "product_type_id = " . RPS::DB::Item::Product::kProductTypeDigital . " ) " .
              "INNER JOIN product_distribution USING (product_id) " .
              "WHERE not invalid AND ". $denied . " " .
			  $serviceSQL .
              "GROUP BY album.album_id " .
              "ORDER BY delivery_date DESC ";

    Common::Log::Debug( "QUERY: $sql" );
    return $class->GetAll($sql);
}

sub GetAllQueuedForDistribution {
	my ($class, %args) = @_;
	my @serviceIDs;
    my $serviceSQL = "";

	my $activeServices = Distribution::DB::Item::DistributionService->GetAllClientServices( Common::RSApp::GetClientID() );

	while( my $service = $activeServices->next() ) {
		push( @serviceIDs, $service->service_id ) if( $service->{configured} && $service->status eq 'active' );
	}

	$serviceSQL = " AND service_id in ( " . join( ',', @serviceIDs ) . " ) "
	    if( @serviceIDs );

    my $sql = "SELECT album.*, upc_ean, MAX(product_distribution.date_queued) as date_queued " .
              "FROM album " .
              "INNER JOIN product ON ( product.asset_id = album.album_id AND " .
                    "product_type_id = " . RPS::DB::Item::Product::kProductTypeDigital . " ) " .
              "INNER JOIN product_distribution USING (product_id) " .
              "WHERE invalid <> 1 AND date_queued IS NOT NULL AND delivery_date IS NULL AND denied IS NULL " .
			         $serviceSQL .
              "GROUP BY album.album_id " .
              "ORDER BY delivery_date DESC, date_queued DESC, date_created DESC ";

    Common::Log::Debug( "QUERY: $sql" );

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

sub GetAllWithInvalidAssets {
    my $class = shift;
    my $sql = "SELECT album.* FROM " . kTable . " INNER JOIN album_distribution_ready using (album_id) " .
             " WHERE metadata_valid = 1 AND (audio_valid = 0 OR image_valid = 0)";

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


# Criteria is passed in, generated from a table_change_log record.  Then we will
# return all albums that are related to that change that have been destributed
# before the date passed in.
sub GetAllWithChangedMetadata {
    my $class    = shift;
    my $column   = shift;
    my $criteria = shift;
    my $date     = shift;

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

    my $digital_track_type = RPS::DB::Item::Product::kProductTypeDigitalTrack;
    my $digital_album_type = RPS::DB::Item::Product::kProductTypeDigital;

    my $sql = "SELECT DISTINCT(album.album_id) as album_id " .
              "FROM album " .
                   "LEFT JOIN artist as album_artist ON ( album.artist_id = album_artist.artist_id ) " .
                   "LEFT JOIN label ON ( album.label_id = label.label_id ) " .

                   "LEFT JOIN track ON ( album.album_id = track.album_id ) " .
                   "LEFT JOIN artist as track_artist ON ( track.artist_id = track_artist.artist_id ) " .
                   "LEFT JOIN master ON ( track.master_id = master.master_id ) " .
                   "LEFT JOIN artist as master_artist ON ( master.artist_id = master_artist.artist_id ) " .
                   "LEFT JOIN song ON ( master.song_id = song.song_id ) " .
                   "LEFT JOIN composer ON ( song.composer_id = composer.composer_id ) " .

                   # Digital Track Products
                   "LEFT JOIN product as t_product ON ( track.track_id = t_product.asset_id AND " .
                                                       "t_product.product_type_id = $digital_track_type ) " .
                   "LEFT JOIN product_track as t_product_track ON ( t_product.product_id = t_product_track.product_id ) " .

                   # Digital Album Products
                   "LEFT JOIN product as a_product ON ( album.album_id = a_product.asset_id AND " .
                                                       "a_product.product_type_id = $digital_album_type ) " .
                   "LEFT JOIN product_track as a_product_track ON ( a_product.product_id = a_product_track.product_id ) " .

                   # Finally join the distribution table on the digital product
                   "LEFT JOIN product_distribution ON ( product_distribution.product_id = a_product.product_id ) " .
              "WHERE product_distribution_id IS NOT NULL AND ";

    # Now lets build the where clause

    my $where;
    # Which table are we looking at?
    if( $column eq 'album_id' ) {
        $where .= "album.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'artist_id' ) {
        $where .= "album_artist.artist_id = " . $dbo->DBQuote($criteria) . " OR ";
        $where .= "track_artist.artist_id = " . $dbo->DBQuote($criteria) . " OR ";
        $where .= "master_artist.artist_id = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'label_id' ) {
        $where .= "label.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'track_id' ) {
        $where .= "track.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'label_id' ) {
        $where .= "label.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'master_id' ) {
        $where .= "master.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'song_id' ) {
        $where .= "song.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'composer_id' ) {
        $where .= "composer.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'product_id' ) {
        $where .= "t_product.$column = " . $dbo->DBQuote($criteria) . " OR ";
        $where .= "a_product.$column = " . $dbo->DBQuote($criteria);

    } elsif( $column eq 'product_track_id' ) {
        $where .= "t_product_track.$column = " . $dbo->DBQuote($criteria) . " OR " .
                "t_product_track.$column = " . $dbo->DBQuote($criteria);

    } else {
        $where .= " 0 = 1 ";
    }
    $sql .= " ( $where ) " if( $where );

    # Set the date criteria
    $sql .= " AND ( ( delivery_date IS NOT NULL AND delivery_date < " . $dbo->DBQuote($date)  . " ) " .
               " OR ( date_queued IS NOT NULL AND date_queued < " . $dbo->DBQuote($date) . " ) ) ";

    # Now get the entire album record
    $sql = "SELECT * FROM album INNER JOIN ( $sql ) as subq ON ( subq.album_id = album.album_id )";
    Common::Log::Debug( "QUERY: $sql" );

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


sub SearchWithValidation
{
	my ($class, %args) = @_;

	my $sql = "SELECT album.*, metadata_valid, audio_valid, image_valid  " .
              "FROM album INNER JOIN album_distribution_ready USING (album_id)  ";

    my $where;

    # Check the valid flags in the cache table
    foreach ( grep( /_valid/, keys(%args) ) ) {
        if( $where ) {
            $where .= $args{$_} ? " AND " : " OR ";
        }

        $where .= sprintf( " %s %s 1 ", $_, $args{$_} ? "=" : "<>" );
    }

    # Check the product_distribution table if we are looking for distribution status
    if( defined($args{in_queue}) || defined($args{distributed})) {
        $sql .= "LEFT JOIN product ON ( product.asset_id = album.album_id AND " .
                    "product_type_id = " . RPS::DB::Item::Product::kProductTypeDigital . " ) " .
                "LEFT JOIN product_distribution USING (product_id) ";

        # Search for anything that has been queued for delivery, but not delivered
        if( $args{in_queue} ) {
            $where .= " AND " if( $where );
            $where .= " delivery_date IS NULL";

        # Search for anything that has been delivered
        } elsif( $args{distributed} ) {
            $where .= " AND " if( $where );
            $where .= " delivery_date IS NOT NULL";

        # Search for anything that is not in the delivery queue
        } else {
            $where .= " AND " if( $where );
            $where .= " product.product_id IS NOT NULL AND product_distribution.product_id IS NULL ";
        }
    }

    $sql .= " WHERE $where " if( $where );

    Common::Log::Debug( "SEARCH WITH VALIDATION QUERY:               $sql" );

    return $class->GetAll( "$sql" );
}


# Search for all albums that do not have images associated to it.
sub SearchNoImage
{
	my ($class, %args) = @_;
    my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT album.* " .
              "FROM album LEFT JOIN album_file USING (album_id) " .
              "WHERE album_file_id IS NULL ";

    $sql .= " AND album.album_id = " . $dbo->DBQuote( $args{album_id} ) if( $args{album_id} );

    Common::Log::Debug( "QUERY: $sql" );

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

# Return all albums that are missing a digital product or is missing audio files
# for the digital product or their status is pending.
sub SearchNoAudio
{
	my ($class, %args) = @_;
    my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT album.* " .
              "FROM album " .
                  "LEFT JOIN product ON ( album.album_id = product.asset_id AND " .
                                        " product_type_id = " . RPS::DB::Item::Product::kProductTypeDigital  . ") " .
                  "LEFT JOIN product_track USING ( product_id ) " .
                  "LEFT JOIN product_track_file USING ( product_track_id ) " .
                  "LEFT JOIN media_file USING ( media_file_id ) " .
              "WHERE ( media_file.media_file_id IS NULL OR  media_file.status = 'pending' ) ";

    $sql .= " AND album.album_id = " . $dbo->DBQuote( $args{album_id} ) if( $args{album_id} );

    $sql .= " GROUP BY album.album_id ";

    Common::Log::Debug( "QUERY: $sql" );
    return $class->GetAll($sql);
}


# Search for all albums that were successfully imported in the last import.
sub SearchImport
{
	my ($class) = @_;
    my $lastUpdate  = Metadata::DB::Item::ContentImport->getLastImport();

    my $id = $lastUpdate ? $lastUpdate->content_import_id : 0;

	my $sql = "SELECT album.* " .
              "FROM album LEFT JOIN content_import_stored_album " .
                  "USING (album_id) " .
              "WHERE content_import_id = " . $id;

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

sub SearchDuplicates
{
    my ($class, %args) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $where;

    $where .= " a.title = " . $dbo->DBQuote($args{album_title})
        if( $args{album_title} );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "catalog_number", $dbo->DBQuote($args{catalog_no}) )
        if( $args{catalog_no} );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "client_album_id", $dbo->DBQuote($args{client_album_id}) )
        if( $args{client_album_id} );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "isrc", $dbo->DBQuote($args{isrc}) )
        if( $args{isrc} );

# TODO Add client track ID support
#    $where .= sprintf( " %s %s = $s", $where ? "OR" : "",  $dbo->DBQuote($args{client_track_id}, $dbo->DBQuote($args{client_track_id} )
#        if( $args{client_track_id} );

    if( $args{digital_upc} ) {
        $where .= sprintf( " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote($args{digital_upc}),
            RPS::DB::Item::Product::kProductTypeDigital );

    }

    if( $args{cd_upc} ) {
        $where .= sprintf( " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote($args{cd_upc}),
            RPS::DB::Item::Product::kProductTypeDigital );

    }

    if( $args{vinyl_upc} ) {
        $where .= sprintf( " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote($args{vinyl_upc}),
            RPS::DB::Item::Product::kProductTypeDigital );

    }

    if( $args{dvd_upc} ) {
        $where .= sprintf( " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote($args{dvd_upc}),
            RPS::DB::Item::Product::kProductTypeDigital );

    }

    unless($where) {
#        die "no valid param passed to album search";
        $where = "1=1";
    }

    my $productIDs = { CD           => RPS::DB::Item::Product::kProductTypeCD,
                       Digital      => RPS::DB::Item::Product::kProductTypeDigital,
                       DigitalTrack => RPS::DB::Item::Product::kProductTypeDigitalTrack,
                       DVD          => RPS::DB::Item::Product::kProductTypeDVD,
                       LP           => RPS::DB::Item::Product::kProductTypeLP };

    my $sql = <<SQL;
SELECT a.*
FROM album as a
     LEFT JOIN artist  as ar ON ( a.artist_id = ar.artist_id )
     LEFT JOIN label   as l  ON ( a.label_id  = l.label_id )
     LEFT JOIN product as p  ON ( a.album_id  = p.asset_id )
     LEFT JOIN track   as t  ON ( a.album_id  = t.album_id )
     LEFT JOIN master  as m  ON ( t.master_id = m.master_id )
WHERE ( $where )
GROUP BY a.album_id
SQL

    Common::Log::Debug( "Running Query: $sql" );

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


sub GetByTrackID
{
	my ($class, $trackID) = @_;


	my $sql = "SELECT album.* FROM album"
		. " LEFT JOIN track ON track.album_id=album.album_id"
		. " WHERE track.track_id=$trackID";

	my $collection = Common::DB::ItemCollection->new(
	 class => "RPS::DB::Item::Album",
	 query => $sql,
	 dbID => kDB,
	);

	# return one or nothing
	#
	return ($collection->hasNext()) ? $collection->next() : undef;
}


sub GetByContractID
{
	my ($class, $contractID) = @_;


	my $sql = "SELECT * FROM album WHERE artist_contract_id=$contractID";

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


sub GetByArtistID
{
	my ($class, $artistID) = @_;

	my $sql = "SELECT * FROM album WHERE artist_id=$artistID";

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

sub GetByCatalogNumber
{
	my ($class, $catalogNumber, $wildCard) = @_;
	return undef if(!$catalogNumber || $catalogNumber eq '');

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

	my $sql = "SELECT * FROM album WHERE ";
    $sql .= $wildCard ?
            $class->SearchSyntax(field => "catalog_number", value => $catalogNumber) :
            "catalog_number=".$dbo->DBQuote($catalogNumber);

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

sub RenumberTracks
{
	my ($class, $albumID) = @_;
	assert($albumID);

	# get all tracks for this album
	#
	my $coll = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
	my @trackList = ();
	while($coll->hasNext())
	{
		push @trackList, $coll->next();
	}

	my $nextTrackNumber = 1;
	foreach my $track (sort {$a->track_order() <=> $b->track_order()} @trackList)
	{
		$track->track_order($nextTrackNumber);
		$track->save();
		$nextTrackNumber++;
	}
}

sub IsDistributionReady
{
    my ($class, $albumID) = @_;
    return 0 unless ($albumID);

    ## ensure has valid upc/ean, isrcs, track-numbering, release-date, genre, cline, pline
    return 1;
}

sub GetAlbumParentalAdvisory
{
    my ($class, $albumID) = @_;
    return 'N' unless ($albumID);

    my $advisory = 'N';

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

    my $sql = "SELECT parental_advisory FROM track WHERE album_id=$albumID";

    my $sth = $dbo->DoCmd($sql);
    while (my ($pa) = $sth->fetchrow_array())
    {
        if ($pa eq 'E') {
            $advisory = 'E';
        } elsif ($pa eq 'C' && $advisory ne 'E') {
            $advisory = 'C';
        }
    }

    return $advisory;
}


sub GetProductTypesAsString
{
    my ($class, $album_id) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT GROUP_CONCAT( product_type.description )  " .
              "FROM album " .
                  "INNER JOIN product ON ( album.album_id = product.asset_id ) " .
                  "INNER JOIN product_type USING ( product_type_id ) " .
              "WHERE product.product_type_id != 4 AND album_id = " . $dbo->DBQuote($album_id);

    Common::Log::Debug( "QUERY: $sql" );
    my $sth = $dbo->DoCmd( $sql );

    my ($result) = $sth->fetchrow_array() if( $sth );

    return $result;


}


sub GetTableStatus
{
	my ($class) = @_;
  my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SHOW TABLE STATUS LIKE '" . kTable . "'";

  my $sth = $dbo->DoCmd( $sql );

  my $result;
  my @results;
  while ($result = $sth->fetchrow_arrayref) {
  	push @results, @{$result};
  }
  return @results;
}


sub GetAlbumCount
{
	my $class = shift;
	my %args = @_;
    my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT count(DISTINCT(album.album_id)) as album_count, count(*) as track_count, " .
	          "       FORMAT(IF( count(*), count(*) / count(DISTINCT(album.album_id)), 0 ),2) as average " .
	          "FROM album INNER JOIN track USING (album_id) ";

    if( defined($args{status}) ) {
		$sql .= " WHERE status = " . $dbo->DBQuote($args{status});
	}

    Common::Log::Debug( "QUERY: $sql" );
    return $class->SUPER::GetAll($sql);
}

sub LookupByProductTrackID
{
	my $class = shift;
	my %args = @_;
	my $product_track_id = $args{product_track_id};

	assert($product_track_id);

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

	my $sql = "SELECT album.* FROM " . kTable .
	          " INNER JOIN product ON ( album_id = asset_id ) " .
			  " INNER JOIN product_track USING ( product_id ) " .
			  "WHERE product_track_id = " . $dbo->DBQuote($product_track_id);

	my $collection = $class->SUPER::GetAll($sql);
	return $collection->next();
}

sub HasAlbumSales {
	my $class = shift;
	my $albumID = shift;
	my $dbo = Common::RSApp::GetClientDB();

	assert( $albumID );

    my $sql = "SELECT * FROM album " .
				"INNER JOIN product as product ON (album.album_id = product.asset_id and product.product_type_id <> 4) " .
				"INNER JOIN sale USING (product_id) " .
			  "WHERE album_id = " . $dbo->DBQuote($albumID);


	Common::Log::Debug( $sql );

	my $collection = $class->SUPER::GetAll($sql);
	return $collection->next() ? 1 : 0;
}

sub HasTrackSales {
	my $class = shift;
	my $albumID = shift;
	my $dbo = Common::RSApp::GetClientDB();

	assert( $albumID );

    my $sql = "SELECT * FROM album " .
				"INNER JOIN track USING (album_id) " .
				"INNER JOIN product as product ON (track.track_id = product.asset_id and product.product_type_id = 4) " .
				"INNER JOIN sale USING (product_id) " .
			  "WHERE album_id = " . $dbo->DBQuote($albumID);

	Common::Log::Debug( $sql );

	my $collection = $class->SUPER::GetAll($sql);
	return $collection->next() ? 1 : 0;
}

sub HasAlbumContracts {
	my $class = shift;
	my $albumID = shift;
	my $dbo = Common::RSApp::GetClientDB();

	assert( $albumID );

    my $sql = "SELECT * FROM album " .
				"INNER JOIN album_contract USING (album_id) " .
			  "WHERE album_id = " . $dbo->DBQuote($albumID);


	Common::Log::Debug( $sql );

	my $collection = $class->SUPER::GetAll($sql);
	return $collection->next() ? 1 : 0;
}

sub HasTrackContracts {
	my $class = shift;
	my $albumID = shift;
	my $dbo = Common::RSApp::GetClientDB();

	assert( $albumID );

    my $sql = "SELECT * FROM album " .
				"INNER JOIN track USING (album_id) " .
				"INNER JOIN track_contract USING (track_id) " .
			  "WHERE album_id = " . $dbo->DBQuote($albumID);

	Common::Log::Debug( $sql );

	my $collection = $class->SUPER::GetAll($sql);
	return $collection->next() ? 1 : 0;
}

sub DeleteAlbum {
	my $class = shift;
	my $albumID = shift;

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

	# Delete product input map for the album and track
	$dbo->DoCmd( "DELETE product_input_map.* FROM product_input_map INNER JOIN product USING (product_id) INNER JOIN album ON (album_id = asset_id) WHERE album_id = " . $dbo->DBQuote($albumID) );
	$dbo->DoCmd( "DELETE product_input_map.* FROM product_input_map INNER JOIN product USING (product_id) INNER JOIN track ON (track_id = asset_id) WHERE album_id = " . $dbo->DBQuote($albumID) );

	# Delete album product
	$dbo->DoCmd( "DELETE product_digital.* FROM product_digital INNER JOIN product USING (product_id) WHERE product_type_id <> 4 AND asset_id = " . $dbo->DBQuote($albumID) );
	$dbo->DoCmd( "DELETE product.* FROM product WHERE product_type_id <> 4 AND asset_id = " . $dbo->DBQuote($albumID) );

	# Delete track products
	$dbo->DoCmd( "DELETE product_digital.* FROM track INNER JOIN product ON (track_id = asset_id AND product_type_id = 4) INNER JOIN product_digital USING (product_id) WHERE album_id = " . $dbo->DBQuote($albumID) );
	$dbo->DoCmd( "DELETE product.* FROM track INNER JOIN product ON (track_id = asset_id AND product_type_id = 4) WHERE album_id =" . $dbo->DBQuote($albumID) );

    # Delete the master if there are no other references
	my $sth = $dbo->DoCmd( "SELECT * FROM track WHERE album_id = " . $dbo->DBQuote($albumID) );
	while( my $track = $sth->fetchrow_hashref() ) {
        my $trackSth = $dbo->DoCmd( "SELECT * FROM track WHERE master_id = " . $track->{master_id} . " AND track_id <> " . $track->{track_id} );

		# If the master isn't referrenced by other tracks then delete it.
		unless( $trackSth && $trackSth->fetchrow_hashref() ) {
	        $dbo->DoCmd( "DELETE song.* FROM master INNER JOIN song USING (song_id) WHERE master_id = " . $track->{master_id} );
	        $dbo->DoCmd( "DELETE FROM master WHERE master_id = " . $track->{master_id} );
		}
	}

	# Delete tracks
	$dbo->DoCmd( "DELETE FROM track WHERE album_id = " . $dbo->DBQuote($albumID) );

	# Delete album
	$dbo->DoCmd( "DELETE FROM album WHERE album_id = " . $dbo->DBQuote($albumID) );
}

1;