#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package MediaServe::DB::Item::AlbumFile;
use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::Timer;
use Common::Assert;

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

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

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

use constant kFileTypeOriginal     => 'original';
use constant kFileTypeThumbnail    => 'thumbnail';
use constant kFileTypeLarge        => 'large';
use constant kFileTypeDistribution => 'distribution';

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

    assert( $args{album_id} );

    my $sql =
        "SELECT album_file.* "
      . "FROM album_file INNER JOIN album USING (album_id) "
      . "WHERE album.album_id = "
      . $dbo->DBQuote( $args{album_id} );

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

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

    my $upc = $args{upc};
    my $dbo = Common::RSApp::GetClientDB();

    assert($upc);

    my $sql =
        " SELECT "
      . kTable . ".* "
      . " FROM "
      . kTable
      . " INNER JOIN product ON ( album_id = asset_id AND product_type_id = 3 ) "
      . " WHERE upc_ean = "
      . $dbo->DBQuote($upc) . " OR "
      . " CONCAT( 'album_', album_id ) = "
      . $dbo->DBQuote($upc);

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

    my $result = $class->SUPER::GetAll($sql);

    return $result;
}

sub LookupByFilename {
    my $self = shift;
    my %args = @_;

    my $filename = $args{filename};
    my $dbo      = Common::RSApp::GetClientDB();

    assert($filename);

    my $sql = " SELECT " . kTable . ".* " . " FROM " . kTable . "     LEFT JOIN media_file USING (media_file_id) " . " WHERE ";

    $sql .= " filename = " . $dbo->DBQuote($filename) if ($filename);

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

    my $result = $self->SUPER::GetAll($sql);

    return $result->next;
}

sub LookupByType {
    my $self = shift;
    my %args = @_;

    my $album_id = $args{album_id};
    my $filename = $args{filename};
    my $type     = _getFileTypeID( $args{type} );

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

    assert($type);

    return unless ( $album_id || $filename );

    my $sql =
        " SELECT "
      . kTable . ".* "
      . " FROM "
      . kTable
      . "     LEFT JOIN media_file USING (media_file_id) "
      . " WHERE "
      . kTable
      . ".file_type = "
      . $dbo->DBQuote($type) . " AND ";

    $sql .= " album_id = " . $dbo->DBQuote($album_id) if ($album_id);
    $sql .= " filename = " . $dbo->DBQuote($filename) if ($filename);

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

    my $result = $self->SUPER::GetAll($sql);

    return $result->next;
}

sub SearchByFilename {
    my $self = shift;
    my %args = @_;

    my $filename = $args{filename};
    my $dbo      = Common::RSApp::GetClientDB();

    assert($filename);

    my $sql = " SELECT " . kTable . ".* " . " FROM " . kTable . "     LEFT JOIN media_file USING (media_file_id) " . " WHERE ";

    $sql .= " filename like " . $dbo->DBQuote("\%$filename\%") if ($filename);

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

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

sub _getFileTypeID {
    my $type = shift;

    assert( $type, "Type Required" );

    if ( $type eq 'thumbnail' ) {
        return kFileTypeThumbnail;
    } elsif ( $type eq 'large' ) {
        return kFileTypeLarge;
    } elsif ( $type eq 'original' ) {
        return kFileTypeOriginal;
    } elsif ( $type eq 'distribution' ) {
        return kFileTypeDistribution;
    } else {
        assert( 0, "Invalid type: $type" );
    }
}

sub GetUnmatchedFiles {
    my $self = shift;
    my %args = @_;

    assert($self);

    my $sql = "SELECT * FROM " . kTable . " INNER JOIN media_file USING (media_file_id) " . " WHERE album_id IS NULL";

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

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

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

    my $sql =
      "SELECT DISTINCT(album_id) FROM album  " . " LEFT JOIN product ON ( album_id = asset_id AND product_type_id <> 4 ) " . " WHERE ";

    if ( $args{album_id} ) {
        assert( !$args{upc_ean} );
        $sql .= 'album_id = ' . $dbo->DBQuote( $args{album_id} );
    } else {
        assert( !$args{album_id} );
        $sql .= 'upc_ean = ' . $dbo->DBQuote( $args{upc_ean} );
    }

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

    my @res;

    while ( my $id = $sth->fetchrow_array() ) {
        push( @res, $id );
    }

    return @res;
}

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

    my $dbo    = Common::RSApp::GetClientDB();
    my $quoted = $dbo->DBQuote($query);
    $quoted =~ s/'/'%/;
    $quoted =~ s/'$/%'/;

    my $where = defined($query) ? "WHERE filename like $quoted" : "";

    my $sql = "SELECT * FROM
	            ( SELECT DISTINCT(SUBSTRING_INDEX(SUBSTRING_INDEX(filename, '.', 1), '-', 1)) as upc
				  FROM album_file INNER join media_file USING (media_file_id)
                  $where ) as subq ORDER BY upc";

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

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

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

    return $class->searchUPCs();
}

sub GetDuplicateAssets {
    my $class    = shift;
    my $filename = shift;
    my $album_id = shift;

    assert($class);
    assert($filename);
    assert($album_id);

    my $sql =
        "SELECT * " . "FROM "
      . kTable
      . " INNER JOIN media_file USING (media_file_id) "
      . "WHERE "
      . "filename NOT LIKE '$filename%' "
      . "AND album_id = $album_id";

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

sub GetThumbnails {
    my $class = shift;
    my $sql   = "SELECT * FROM album_file WHERE file_type = 'thumbnail'";

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

1;