#------------------------------------------------------------
# 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( ",", map { $dbo->DBQuote($_) } @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;