#------------------------------------------------------------
# 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 = " . $class->quote( $args{label_id} );
    }

    my $whereClause = "WHERE 1";
    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 = " . $class->quote($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( ",", map { $class->quote($_) } @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} ) ) {
            my @albumIDs = map { $dbo->DBQuote($_) } @{ $args{album_id} };
            $sql .= " AND album_id in (" . join( ",", @albumIDs ) . ")";
        } 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( ',', map { $class->quote($_) } @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;
    $id = $class->quote($id);
    my $sql = qq/
      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 ) = @_;

    $trackID = $class->quote($trackID);
    my $sql = qq/
        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 ) = @_;
    assert($contractID);

    my $sql = "SELECT * FROM album WHERE artist_contract_id = " . $class->quote($contractID);

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

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

    my $sql = "SELECT * FROM album WHERE artist_id = " . $class->quote($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 = " . $dbo->DBQuote($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 HasUSLicenses {
    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_license 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 HasCALicenses {
    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 ca_track_license 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 HasUKLicenses {
    my $class   = shift;
    my $albumID = shift;
    my $dbo     = Common::RSApp::GetClientDB();

    assert($albumID);

    my $sql =
        "SELECT * FROM album "
      . "INNER JOIN product ON (album_id = asset_id) "
      . "INNER JOIN mcps_license ON (product.product_id = mcps_license.product_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 = " . $dbo->DBQuote( $track->{master_id} ) . " AND track_id <> " . $dbo->DBQuote( $track->{track_id} ) );

        # If the master isn't referrenced by other tracks then delete it.
        unless ( $trackSth && $trackSth->fetchrow_hashref() ) {

            # If the song isn't referenced by other masters then delete it.
            my $sth2      = $dbo->DoCmd( "SELECT * FROM master WHERE master_id = " . $dbo->DBQuote( $track->{master_id} ) );
            my $master    = $sth2->fetchrow_hashref();

            my $masterSth = $dbo->DoCmd( "SELECT * FROM master WHERE song_id = " . $dbo->DBQuote( $master->{song_id} ) . " AND master_id <> " . $dbo->DBQuote( $track->{master_id} ) );

            unless ( $masterSth && $masterSth->fetchrow_hashref() ) {
                $dbo->DoCmd( "DELETE FROM song WHERE song_id = " . $dbo->DBQuote( $master->{song_id} ) );
            }

            # Delete the master
            $dbo->DoCmd( "DELETE FROM master WHERE master_id = " . $dbo->DBQuote( $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;