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

use strict;
use warnings;
use Data::Dumper;

use lib '/app/tools/common/lib';
use lib '/app/tools/bookpub/lib';
use Common::Assert;
use BookPub::RDAS::Service;
use BookPub::DB::Item::ProductMarket;
use BookPub::DB::Item::ProductMonitorItemRank;
use BookPub::DB::Item::Region;
use BookPub::DB::Item::Archive::BookProduct;

use base 'BookPub::DB::MetadataItem';

sub _NewArchiveItem {
    my ( $class, %args ) = @_;
    return BookPub::DB::Item::Archive::BookProduct->Create(%args);
}

sub _ArchiveTableName {
    return 'archive_book_product';
}

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

# These two status codes are important for determining which products should be considered 'active'.
#
use constant kStatusUnspecified => '00';
use constant kStatusForthcoming => '02';
use constant kStatusActive      => '04';
use constant kStatusOutOfPrint  => '07';

sub GetBookCountByImprintID {
    my ( $class, $imprintID ) = @_;
    assert($imprintID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd( "SELECT COUNT(distinct book_id) as book_count FROM " . kTable . " WHERE imprint_id=$imprintID" );
    my $hr  = $sth->fetchrow_hashref();

    return $hr->{book_count};
}

sub GetAll {
    my ( $class, $sql ) = @_;

    $sql = "SELECT * FROM " . kTable unless ($sql);

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

sub GetAllSortedForReport {
    my ($class) = @_;

    my $sql = "SELECT * FROM book_product ";
    $sql .= "LEFT JOIN book ON book_product.book_id = book.book_id ";
    $sql .= "LEFT JOIN book_format ON book_format.book_format_id = book_product.book_format_id ";
    $sql .= "LEFT JOIN book_format_type ON book_format_type.book_format_type_id = book_format.book_format_type_id ";
    $sql .= "LEFT JOIN onix_code ON onix_code.onix_code_id = book_format_type.onix_code_id ";
    $sql .= "ORDER BY book.title, onix_code.description, book_product.isbn13 ";
    return $class->SUPER::GetAll($sql);
}

sub GetAllHardbacks {
    my ($class) = @_;

    my $sql = "SELECT * FROM book_product ";
    $sql .= "LEFT JOIN book ON book_product.book_id = book.book_id ";
    $sql .= "WHERE book_format_id = 2 ";
    $sql .= "ORDER BY book.title";
    return $class->SUPER::GetAll($sql);
}

sub GetAllWithLimit {
    my ( $class, %args ) = @_;
    my $limit        = $args{limit};
    my $serviceID    = $args{serviceID};
    my $bookID       = $args{bookID};
    my $ebookOnly    = $args{ebookOnly};
    my $physicalOnly = $args{physicalOnly};
    my $groupEBook   = $args{groupEBook};
    my $order        = $args{order} || "";
    my $current      = $args{current};
    my $table        = kTable;

    assert($serviceID);

    my $select = $groupEBook ? "$table.*" : "$table.*";

    my $sql =
        "SELECT $select, MAX(last_verified) as last_verified FROM $table "
      . "LEFT JOIN product_monitor_item ON ( book_product.product_id = product_monitor_item.product_id AND service_id = "
      . $class->quote($serviceID) . " ) ";

    if ( $ebookOnly || $groupEBook || $physicalOnly ) {
        $sql .=
            " 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) ";
    }

    $sql .= " WHERE " . " ( service_id = " . $class->quote($serviceID) . " OR " . "   service_id IS NULL ) ";

    $sql .= " AND book_id = " . $class->quote($bookID) if ($bookID);

    if ( $ebookOnly || $groupEBook ) {
        $sql .= " AND code_value = 'DG'";
    } elsif ($physicalOnly) {
        $sql .= " AND code_value <> 'DG'";
    }

    if ($current) {
        $sql .= " AND product_monitor_item.status = 'current' ";
    }

    $sql .= " GROUP BY ";

    $sql .= $groupEBook ? "$table.book_id" : "$table.product_id";

    $sql = "SELECT * FROM ($sql) as subq ORDER BY last_verified $order ";
    $sql .= " LIMIT $limit" if ($limit);

    Log->debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql);

}

sub IsEBook {
    my ( $class, %args ) = @_;
    my $productID = $args{productID};

    assert($productID);

    my $sql =
        "SELECT "
      . kTable . ".* " . "FROM "
      . kTable . " "
      . " 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 code_value = 'DG' AND book_product.product_id = "
      . $class->quote($productID);

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

sub IsHardback {
    my ( $class, %args ) = @_;
    my $productID = $args{productID};

    assert($productID);

    my $sql =
        "SELECT "
      . kTable . ".* " . "FROM "
      . kTable . " "
      . " 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 code_value = 'BB' AND book_product.product_id = "
      . $class->quote($productID);

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

sub IsPaperback {
    my ( $class, %args ) = @_;
    my $productID = $args{productID};

    assert($productID);

    my $sql =
        "SELECT "
      . kTable . ".* " . "FROM "
      . kTable . " "
      . " 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 code_value = 'BC' AND book_product.product_id = "
      . $class->quote($productID);

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

sub IsSubFormat {
    my ( $class, %args ) = @_;
    my $productID = $args{productID};
    my $subformat = $args{subformat};

    assert($productID);

    my $sql =
        "SELECT "
      . kTable . ".* " . "FROM "
      . kTable . " "
      . " 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 code_value = 'DG' AND book_product.product_id = "
      . $class->quote($productID);

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

# By 'similar', we mean that these products have the same book_id, and the same book_format_type_id.
# The returned array will contain the original product_id as well (so you should _always_ get at least 1 product_id).
#
# We also now want to take _price_ into account.   So only return products that have the same price point with the same price type.
# This will help further distinguish all the random eBook types.
#
# It needs to be :  the current 'USD' price... in whatever region is 'US'.
#
sub GetProductIDsOfSimilarProducts {
    my ( $class, $productID, %additionalArgs ) = @_;

    assert($productID);

    my $marketID     = $additionalArgs{marketID};
    my $currencyCode = $additionalArgs{currencyCode};
    my $price        = $additionalArgs{price};

    my $bookProduct = $class->Lookup( product_id => $productID );
    die "ERROR - invalid product_id $productID" unless $bookProduct;

    my @joins;
    my @where;
    push @joins, " INNER JOIN book_format USING (book_format_id)";
    push @where, "book_id=" . $bookProduct->book_id;
    push @where, "book_format.book_format_type_id = (select book_format_type_id from book_format where book_format_id="
      . $bookProduct->book_format_id . ") ";

    if ( $marketID && $currencyCode && $price ) {
        push @joins, " INNER JOIN product_market USING (product_id)";
        push @joins, " INNER JOIN product_market_price ON (product_market.product_market_id = product_market_price.product_market_id)";
        push @where, "product_market.market_id=" . $class->quote($marketID);
        push @where, "product_market_price.currency_code=" . $class->quote($currencyCode);
        push @where, "product_market_price.price=" . $class->quote($price);
    }

    my $sql = "SELECT product_id FROM " . kTable . ' ' . join( ' ', @joins ) . " WHERE " . join( ' AND ', @where );

    # Just want to returns ids.  Don't want to bless everything into a collection of DB::Items.
    #
    my @returnedIDs;
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @returnedIDs, $hr->{product_id};
    }

    return @returnedIDs;
}

sub GetAllByBookID {
    my ( $class, $bookID ) = @_;
    assert($bookID);

    my $sql = "SELECT * FROM " . kTable . " WHERE book_id=" . $class->quote($bookID);
    return $class->GetAll($sql);
}

sub GetRowsForShowBookByBookID {
    my ( $class, $bookID ) = @_;
    assert($bookID);

    my $sql = qq/
        SELECT
            bp.product_id,
            bp.onix_code_publishing_status,
            bp.isbn10,
            bp.isbn13,
            bp.edition,
            bp.imprint_id,
            p.release_date,
            type_onix.code_value AS format_code,
            type_onix.description AS format_description,
            subtype_onix.code_value AS subtype_code,
            subtype_onix.description AS subtype_description
        FROM book_product bp
        INNER JOIN product p ON p.product_id = bp.product_id
        INNER JOIN book_format bf ON bf.book_format_id = bp.book_format_id
        INNER JOIN book_format_type bft ON bft.book_format_type_id = bf.book_format_type_id
        INNER JOIN onix_code AS type_onix ON type_onix.onix_code_id = bft.onix_code_id
        LEFT JOIN book_format_subtype bfs ON bfs.book_format_subtype_id = bf.book_format_subtype_id
        LEFT JOIN onix_code AS subtype_onix ON subtype_onix.onix_code_id = bfs.onix_code_id
        WHERE bp.book_id = ?
        ORDER BY bp.product_id
    /;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, [$bookID] );

    my @rows;
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @rows, $hr;
    }

    return \@rows;
}

sub GetAllByBookIDBookFormatID {
    my ( $class, $bookID, $bookFormatID ) = @_;
    assert($bookID);
    assert($bookFormatID);

    $bookID = $class->quote($bookID);
    $bookFormatID = $class->quote($bookFormatID);
    my $sql = "SELECT * FROM " . kTable . " WHERE book_id=$bookID AND book_format_id=$bookFormatID";
    return $class->GetAll($sql);
}

sub GetProductString {
    my ( $class, $onixProdCode ) = @_;

    return
        $onixProdCode eq 'DG'        ? 'E-book'
      : $onixProdCode =~ m/^A(C|J)$/ ? 'Audiobook'
      : $onixProdCode eq 'BB'        ? 'Hardback'
      : $onixProdCode eq 'BC'        ? 'Paperback'
      :                                '';
}

sub GetAllWithEitherISBN {
    my ( $class, $isbn10, $isbn13 ) = @_;
    assert( $isbn10 || $isbn13 );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
      "SELECT * FROM " . kTable . " WHERE (" . "isbn10=" . $dbo->DBQuote($isbn10) . " OR " . "isbn13=" . $dbo->DBQuote($isbn13) . ")";

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

sub GetAllByISBN13 {
    my ( $class, $isbn13 ) = @_;
    assert($isbn13);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE isbn13=" . $dbo->DBQuote($isbn13);

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

sub GetCountByISBN13 {
    my ( $class, $isbn13 ) = @_;
    assert($isbn13);

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd( "SELECT COUNT(*) as product_count FROM " . kTable . " WHERE isbn13=" . $dbo->DBQuote($isbn13) );
    my $hr  = $sth->fetchrow_hashref();

    return $hr->{product_count};
}

sub GetISBN {
    my $self = shift;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd( "SELECT isbn10, isbn13 FROM " . kTable );
    my %isbn;
    while (my $isbn = $sth->fetchrow_hashref()){
        $isbn{isbn10}{$isbn->{isbn10}}++ if ($isbn->{isbn10});
        $isbn{isbn13}{$isbn->{isbn13}}++ if ($isbn->{isbn13});
    }
    return \%isbn;
}

sub GetAllByISBN10 {
    my ( $class, $isbn10 ) = @_;
    assert($isbn10);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE isbn10=" . $dbo->DBQuote($isbn10);

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

sub GetCountByISBN10 {
    my ( $class, $isbn10 ) = @_;
    assert($isbn10);

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd( "SELECT COUNT(*) as product_count FROM " . kTable . " WHERE isbn10=" . $dbo->DBQuote($isbn10) );
    my $hr  = $sth->fetchrow_hashref();

    return $hr->{product_count};
}

sub GetAllByImprintID {
    my ( $class, $imprintID ) = @_;
    assert($imprintID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE imprint_id=" . $dbo->DBQuote($imprintID) . " ORDER BY isbn13";

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

sub GetAllWithMissingURL {
    my $class     = shift;
    my %args      = @_;
    my $serviceID = $args{serviceID};
    my $productID = $args{productID};
    my $limit     = $args{limit};

    assert($serviceID);

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " LEFT JOIN product_service_url ON ( book_product.product_id = product_service_url.product_id "
      . " AND service_id = " . $class->quote($serviceID) . " ) "
      . "WHERE product_service_url.product_id IS NULL  ";
    $sql .= " AND book_product.product_id = " . $class->quote($productID) if ($productID);
    $sql .= " LIMIT $limit" if ($limit && $limit =~ /^\d+$/);

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

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

    assert( $args{product_id},    "Product ID Required" );
    assert( $args{currency_code}, "Currency Code Required" );
    assert( $args{country_code} || $args{market_id}, "Country Code or Market ID Required" );
    assert( $args{price}, "Price Required" );

    my $productID   = $args{product_id};
    my $marketID    = $args{market_id};
    my $countryCode = $args{country_code};

    my @productIDs = $productID;
    if ( BookPub::DB::Item::BookProduct->IsEBook( productID => $productID ) ) {
        if ( !$marketID ) {
            my $usRegions = BookPub::DB::Item::Region->GetAllRegions( country_code => $countryCode );
            die "ERROR - unable to find a region for " . $countryCode unless $usRegions->size() > 0;

            my @regionIDs;
            while ( my $region = $usRegions->next() ) {
                push @regionIDs, $region->region_id;
            }

            my $productMarkets = BookPub::DB::Item::ProductMarket->GetAllByRegion( region_id => \@regionIDs, product_id => $productID );

            # !!! I am going to assume just 1.   This might be false...
            #
            die "ERROR - More than one product_market associated with product: $productID, country " . $countryCode
              if ( $productMarkets->size() > 1 );

            die "ERROR - No product_market found for product: $productID, country " . $countryCode
              if ( $productMarkets->size() == 0 );

            my $productMarket = $productMarkets->next();
            $marketID = $productMarket->market_id();
        }

        my %additionalArgs;
        $additionalArgs{marketID}     = $marketID;
        $additionalArgs{currencyCode} = $args{currency_code};
        $additionalArgs{price}        = $args{price};

        @productIDs = BookPub::DB::Item::BookProduct->GetProductIDsOfSimilarProducts( $productID, %additionalArgs );
    }

    my @serviceIDs = BookPub::RDAS::Service->GetAllServiceIDs();

    my $aggRank  = 0;
    my $numRanks = 0;
    foreach (@serviceIDs) {

        my $rank = BookPub::DB::Item::ProductMonitorItemRank->GetCurrent( service_id => $_, product_id => \@productIDs );

        if ($rank) {
            if ( $rank->rank ) {
                $aggRank += $rank->rank;
                $numRanks++;
            }
        }
    }

    if ( $numRanks > 0 ) {
        my $averageRank = $aggRank / $numRanks;

        # Round it.
        $averageRank = int( $averageRank + 0.5 );

        my @quotedProductIDs;
        foreach my $id (@productIDs) {
            push @quotedProductIDs, $class->quote($id);
        }

        my $productIDString = join( ',', @quotedProductIDs );

        my $sql = "UPDATE " . kTable . " SET average_rank = $averageRank WHERE " . "product_id IN ( $productIDString )";

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


1;