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

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::Assert;

use lib '/app/tools/rps/lib';

use lib '/app/tools/metadata/lib';


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

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


sub Delete {
    my $class = shift;
	my %args = @_;
	my $albumID = $args{album_id};
    my $dbo = Common::RSApp::GetClientDB();

	assert($albumID);

	my $sql = "DELETE FROM album_distribution_ready where album_id = " . $dbo->DBQuote($albumID);
	$dbo->DoCmd( $sql );
}

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

	my $sql = "DELETE FROM album_distribution_ready";
	$dbo->DoCmd( $sql );
}

sub ClearCache {
    my $class = shift;
	my %args = @_;

    assert($class);

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

    if( defined($args{"artist.artist_id"}) ) {
        $args{"track_artist.artist_id"}  = $args{"artist.artist_id"};
        $args{"master_artist.artist_id"} = $args{"artist.artist_id"};
        $args{"song_artist.artist_id"}   = $args{"artist.artist_id"};

		delete( $args{"artist.artist_id"} );
	}

	my $where;
	my $sql = "SELECT DISTINCT(album.album_id)
FROM
   album
   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 ( master.master_id = track.master_id )
   LEFT JOIN artist AS master_artist ON ( master.artist_id = master_artist.artist_id )
   LEFT JOIN song ON ( song.song_id = master.song_id )
   LEFT JOIN artist AS song_artist ON ( track.artist_id = song_artist.artist_id )
   LEFT JOIN composer ON ( song.composer_id = composer.composer_id )
   LEFT JOIN product ON ( album.album_id = product.asset_id )
   LEFT JOIN product_track ON ( product_track.track_id = track.track_id )
   LEFT JOIN album_file ON ( album.album_id = album_file.album_id )
";

	foreach ( keys(%args) ) {
		$where .= " OR " if( $where );
		$where .= " $_ = " . $dbo->DBQuote( $args{$_} );
	}

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

	my $delete_sql = "DELETE FROM " . kTable;

	$delete_sql .= " WHERE album_id IN ( $sql ) " if( $where );

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

	$dbo->DoCmd( $delete_sql );
}

sub CacheRecordDirty {
    my $class = shift;
	my %args = @_;
    my $album_id = $args{album_id};

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

	my $sql = "SELECT COUNT(DISTINCT(album.album_id)) " . _metadataTablesQueryBase();

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

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

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

    my ($modified) = $sth->fetchrow_array();
    return ($modified);
}

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

    my $sql = "SELECT DISTINCT(album.album_id) " . _metadataTablesQueryBase();
    Common::Log::Debug( "$sql" );

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

sub ClearInvalidCache {
    my $class = shift;
    my %args = @_;
    my $album_id;
    my @ids;

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

    my $sql = "SELECT DISTINCT(album_distribution_ready.album_id) " . _metadataTablesQueryBase();
    Common::Log::Debug( "$sql" );

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

    while( my ($album_id) = $sth->fetchrow_array() ) {
        push( @ids, $album_id ) if( $album_id );
    }

    if( @ids && scalar @ids > 0 ) {
        my $ids = join( ",", @ids );
        return unless($ids);

        $sql = "DELETE FROM album_distribution_ready WHERE album_id IN ( " .
                 $ids . " )";

        Common::Log::Debug( "$sql" );
        $dbo->DoCmd( $sql );
    }
}

sub _metadataTablesQueryBase {
<<EOF;
FROM
    album
    LEFT JOIN album_distribution_ready
	  ON ( album.album_id = album_distribution_ready.album_id )
    LEFT JOIN track FORCE INDEX ( album_id )
	  ON ( album.album_id = track.album_id )
    LEFT JOIN artist AS album_artist
	  ON ( album.artist_id = album_artist.artist_id )
    LEFT  JOIN album_additional_artist FORCE INDEX (album_id)
	  ON ( album.album_id = album_additional_artist.album_id )
    LEFT JOIN artist AS aa_album_artist
	  ON ( album.artist_id = aa_album_artist.artist_id )
    LEFT  JOIN track_additional_artist FORCE INDEX (track_id)
	  ON ( track.track_id = track_additional_artist.track_id )
    LEFT JOIN artist AS aa_track_artist
	  ON ( album.artist_id = aa_track_artist.artist_id )
    LEFT JOIN label
	  ON ( album.label_id = label.label_id )
    LEFT JOIN artist AS track_artist
	  ON ( track.artist_id = track_artist.artist_id )
    LEFT JOIN master
	  ON ( master.master_id = track.master_id )
    LEFT JOIN artist AS master_artist
	  ON ( master.artist_id = master_artist.artist_id )
    LEFT JOIN song
	  ON ( song.song_id = master.song_id )
    LEFT JOIN artist AS song_artist
	  ON ( track.artist_id = song_artist.artist_id )
    LEFT  JOIN composer
	  ON ( song.composer_id = composer.composer_id )
    LEFT JOIN product
	  ON ( album.album_id = product.asset_id AND product.product_type_id = 3 )
    LEFT JOIN album_file
	  ON ( album.album_id  = album_file.album_id )
    LEFT JOIN media_file as album_media_file
	  ON ( album_file.media_file_id = album_media_file.media_file_id )
    LEFT JOIN product_track
	  ON ( track.track_id = product_track.track_id )
    LEFT JOIN product_track_file USING ( product_track_id )
    LEFT JOIN media_file AS pt_media_file
	  ON ( product_track_file.media_file_id = pt_media_file.media_file_id )

WHERE (
    album_distribution_ready.date_modified IS NULL OR
	album.date_modified                      > album_distribution_ready.date_modified OR
    track.date_modified                      > album_distribution_ready.date_modified OR
    album_artist.date_modified               > album_distribution_ready.date_modified OR
    album_additional_artist.date_modified    > album_distribution_ready.date_modified OR
    aa_album_artist.date_modified            > album_distribution_ready.date_modified OR
    track_additional_artist.date_modified    > album_distribution_ready.date_modified OR
    aa_track_artist.date_modified            > album_distribution_ready.date_modified OR
    label.date_modified                      > album_distribution_ready.date_modified OR
    track_artist.date_modified               > album_distribution_ready.date_modified OR
    master.date_modified                     > album_distribution_ready.date_modified OR
    master_artist.date_modified              > album_distribution_ready.date_modified OR
    song.date_modified                       > album_distribution_ready.date_modified OR
    song_artist.date_modified                > album_distribution_ready.date_modified OR
    composer.date_modified                   > album_distribution_ready.date_modified OR
    product.date_modified                    > album_distribution_ready.date_modified OR
    album_file.date_modified                 > album_distribution_ready.date_modified OR
    album_media_file.date_modified           > album_distribution_ready.date_modified OR
    product_track.date_modified              > album_distribution_ready.date_modified OR
    product_track_file.date_modified         > album_distribution_ready.date_modified OR
    pt_media_file.date_modified              > album_distribution_ready.date_modified )
EOF
}

sub ExpireCache {
    my $sql = "DELETE FROM album_distribution_ready " .
              "WHERE ( ! metadata_valid or ! audio_valid OR ! image_valid ) AND " .
                     "date_created > DATE_ADD( NOW(), interval 7 day ) ";

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

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

	# Disable expiration for a little while.
	#$dbo->DoCmd( $sql );
}

sub GetCacheProgress {
    my $class = shift;
    assert($class);

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

	my $sql = "SELECT count(album_distribution_ready.album_id) missing_albums, count(*) as total_albums, " .
                 " unix_timestamp(now()) - unix_timestamp(max(album_distribution_ready.date_modified)) as idle " .
	       "FROM album LEFT JOIN album_distribution_ready USING( album_id )";
	my $sth = $dbo->DoCmd($sql);
	my ($cached, $count, $idle) = $sth->fetchrow_array();
	my $average = $cached / $count if( $count );

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

	return 0 unless( $average );

    return ( sprintf( "%.4f", $average ), $cached, $count, $idle );
}

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

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

sub hasUpdate {
	my $class = shift;
	my $interval = shift || '30 second';
    my $dbo = Common::RSApp::GetClientDB();

	my $sql = "SELECT * FROM " . kTable .
	          " WHERE date_modified > DATE_SUB( NOW(), interval $interval )";

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

	my $sth = $dbo->DoCmd($sql);
	my $row = $sth->fetchrow_hashref();
	return $row ? 1 : undef;
}

sub Finalize {
    my $class = shift;
    assert($class);

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

	my $sql = " UPDATE " . kTable . " SET date_created = NOW() ";
	my $sth = $dbo->DoCmd($sql);
}

1;