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

use lib '/app/tools/common/lib';

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

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

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

use constant kFileTypeOriginal => 'original';

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

    assert( $args{album_id} );

    my $sql =
        "SELECT product_track_file.* "
      . "FROM product_track_file "
      . "INNER JOIN product_track USING (product_track_id) "
      . "INNER JOIN product ON ( product_track.product_id = product.product_id AND "
      . "product_type_id = "
      . RPS::DB::Item::Product::kProductTypeDigital . " ) "
      . "WHERE asset_id = "
      . $dbo->DBQuote( $args{album_id} );

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

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

    assert( $args{filename} );
    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        " SELECT "
      . kTable . ".*"
      . " FROM "
      . kTable
      . " LEFT JOIN media_file USING (media_file_id) "
      . " WHERE filename="
      . $dbo->DBQuote( $args{filename} );

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

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

    return $result->next;
}

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

    return undef unless ( $args{track_id} );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        " SELECT "
      . kTable . ".*"
      . " FROM product_track "
      . "INNER JOIN "
      . kTable
      . " USING (product_track_id) "
      . "INNER JOIN media_file USING (media_file_id) "
      . " WHERE track_id="
      . $dbo->DBQuote( $args{track_id} );

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

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

    return $result->next;
}

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

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

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

    my $where;

    if ($upc) {
        $where = $class->SearchSyntax( field => "filename", value => $upc );
    } else {
        $where = "filename IS NULL";
    }

    $sql = "$sql $where ORDER BY filename";

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

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

    return $result;
}

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

    return $class->searchUPCs();
}

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 product_track_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 GetUnmatchedFiles {
    my $self = shift;
    my %args = @_;

    assert($self);

    my $sql =
        "SELECT *, product_track_file_id as id FROM "
      . kTable
      . " INNER JOIN media_file USING (media_file_id) "
      . " WHERE product_track_id IS NULL";

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

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

    assert( $args{album_id} );

    my $sql =
        "SELECT count(*) "
      . "FROM album "
      . "INNER JOIN product ON ( album_id = asset_id AND "
      . " product_type_id = "
      . RPS::DB::Item::Product::kProductTypeDigital . " ) "
      . "INNER JOIN media_file ON ( filename like CONCAT( upc_ean, '%' ) ) "
      . "INNER JOIN product_track_file USING (media_file_id) "
      . "WHERE album_id = "
      . $dbo->DBQuote( $args{album_id} ) . " AND "
      . "product_track_id IS NULL";

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

    return $count;
}

1;

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

    # Return an empty set if we don't have all of our search criteria
    return () unless ( defined( $args{upc_ean} )
        && defined( $args{disc_number} )
        && defined( $args{disc_track} ) );

    my $sql =
        "SELECT DISTINCT(product_track_id) FROM product_track "
      . "LEFT JOIN product USING ( product_id ) "
      . "WHERE upc_ean = "
      . $dbo->DBQuote( $args{upc_ean} )
      . " AND disc_track = "
      . $dbo->DBQuote( $args{disc_track} )
      . " AND product_type_id = "
      . RPS::DB::Item::Product::kProductTypeDigital;

    $sql .= " AND ( disc_number = " . $dbo->DBQuote( $args{disc_number} );
    if ( $args{disc_number} + 0 == 1 ) {
        $sql .= " OR disc_number IS NULL ";
    }
    $sql .= " )";

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

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

    return @res;
}

1;