#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2010 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------
package BookPub::DB::Item::ProductServiceUrl;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/bookpub/lib';
use Common::Assert;
use BookPub::DB::Item::File;

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

use constant kTable => 'product_service_url';
use constant kDB    => Common::DB::Item::kClientDB;

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

    my $productID = $args{product_id};
    my $serviceID = $args{service_id};
    my $generated = $args{generated};

    # product_id can be either a single id, or a reference to an array of ids.
    # We'll convert the single ID to an array of one
    #
    my @quotedProductIDs;
    if ( 'ARRAY' ne ref( $args{product_id} ) ) {
        push @quotedProductIDs, $class->quote( $args{product_id} );
    } else {
        foreach my $id ( @{ $args{product_id} } ) {
            push @quotedProductIDs, $class->quote($id);
        }
    }
    my $productIDString = join( ',', @quotedProductIDs );

    my $sql = "SELECT * FROM product_service_url WHERE " . "service_id = " . $class->quote($serviceID);

    $sql .= " AND product_id IN ( $productIDString )"      if ($productID);
    $sql .= " AND generated IS NOT NULL AND generated > 0" if ($generated);

    Log->debug("QUERY: $sql");
    my $collection = $class->SUPER::GetAll($sql);

    return $collection->next();
}

# Find all valid URLs based on Product or Book ID
sub FindURL {
    my $class = shift;
    my %args  = @_;

    my $bookID    = $args{bookID};
    my $productID = $args{productID};
    my $serviceID = $args{serviceID};

    assert( $bookID || $productID );

    my $sql = "SELECT " . kTable . ".* FROM " . kTable . " ";

    if ($bookID) {
        $sql .=
            "INNER JOIN book_product USING (product_id) "
          . "INNER JOIN book_format USING (book_format_id) "
          . "INNER JOIN book_format_type ON ( book_format.book_format_type_id = book_format_type.book_format_type_id ) "
          . "INNER JOIN onix_code USING (onix_code_id) "
          . "WHERE book_id = "
          . $class->quote($bookID)
          . " AND code_value = 'DG'";
    } else {
        $sql .= "WHERE product_id = " . $class->quote($productID);
    }

    $sql .= " AND service_id = " . $class->quote($serviceID);
    $sql .= " AND ( invalid IS NULL OR invalid <> 1 )";

    Log->debug("Query: $sql");

    my $collection = $class->SUPER::GetAll($sql);
    my $dbItem     = $collection->next();

    if ( $collection->hasNext() ) {
        $class->InvalidateURL(@_);
        return;
    }

    return $dbItem;
}

# Find all valid URLs based on Product or Book ID
sub InvalidateURL {
    my $class = shift;
    my %args  = @_;

    my $bookID    = $args{bookID};
    my $productID = $args{productID};
    my $serviceID = $args{serviceID};

    assert( $bookID || $productID );

    my $sql = "UPDATE " . kTable . " ";

    if ($bookID) {
        $sql .=
            "INNER JOIN book_product USING (product_id) "
          . "INNER JOIN book_format USING (book_format_id) "
          . "INNER JOIN book_format_type ON ( book_format.book_format_type_id = book_format_type.book_format_type_id ) "
          . "INNER JOIN onix_code USING (onix_code_id) "
          . "SET product_service_url.invalid = 1 "
          . "WHERE book_id = "
          . $class->quote($bookID)
          . " AND code_value = 'DG'";
    } else {
        $sql .= "SET product_service_url.invalid = 1 " . "WHERE product_id = " . $class->quote($productID);
    }

    $sql .= " AND service_id = " . $class->quote($serviceID);
    $sql .= " AND ( invalid IS NULL OR invalid <> 1 )";

    Log->debug("Query: $sql");

    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);
}

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

    my $productID = $args{productID};
    my $serviceID = $args{serviceID};
    my $url       = $args{url};
    my $invalid   = $args{invalid};

    assert($productID);
    assert($serviceID);

    my $where = '';

    $where .= " AND url = " . $class->quote($url) if ($url);

    if ( defined($invalid) ) {
        $where .= $invalid ? " AND (invalid IS NOT NULL OR invalid <> 0) " : " AND (invalid IS NULL OR invalid = 0)";
    }

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " WHERE product_id = "
      . $class->quote($productID)
      . " AND service_id = "
      . $class->quote($serviceID)
      . $where
      . " ORDER BY invalid, date_modified DESC";

    Log->debug("Query: $sql");
    my $collection = $class->SUPER::GetAll($sql);
    return $collection->next();
}

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

    my $bookID    = $args{bookID};
    my $productID = $args{productID};
    my $serviceID = $args{serviceID};

    assert( $bookID || $productID );
    assert($serviceID);

    my $sql =
        "UPDATE "
      . kTable
      . " INNER JOIN book_product USING (product_id) "
      . "SET invalid = 1 "
      . "WHERE generated = 1 AND service_id = "
      . $class->quote($serviceID) . " AND ";

    $sql .= $bookID ? " book_id = " . $class->quote($bookID) : " product_id = " . $class->quote($productID);

    Log->debug("QUERY: $sql");

    my $dbo = Common::RSApp::GetClientDB;
    $dbo->DoCmd($sql);
}

1;