#------------------------------------------------------------
# Copyright (C) 2009 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::BookMatch;
use strict;
use warnings;

#
# This is a somewhat unusual DB::Item class - There isn't a 'match' table.
# I just wanted a package in the DB::Item namespace to put the match-related
# queries.

use Data::Dumper;

use lib '/app/tools/data_classes/lib';
use File::Sale;

use lib '/app/tools/bookpub/lib';
use BookPub::DB::Item::Book;
use BookPub::DB::Item::BookProduct;
use BookPub::DB::Item::CatalogImportItem::Book;
use BookPub::DB::Item::CatalogImportItem::BookProduct;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::Log;
use Common::DB::Item;
use Common::Util;
use base 'Common::DB::Item';

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

# We don't have a 'match' table.  But I want to be able to return a DB::ItemCollection via this interface.
# So, we override this static _class_ method, to allow the creation of virtual DB::Item objects (of this class).
# Since we don't _ever_ want to WRITE into one of these things, all fields will be flagged _readOnly
#
sub _GenerateClassConfig {
    my ($class) = @_;

    return {
        product_id     => { _readOnly => 1 },
        author_name    => { _readOnly => 1 },
        release_date   => { _readOnly => 1 },
        isbn13         => { _readOnly => 1 },
        isbn10         => { _readOnly => 1 },
        title          => { _readOnly => 1 },
        title_clean    => { _readOnly => 1 },
        subtitle       => { _readOnly => 1 },
        subtitle_clean => { _readOnly => 1 },
        book_score     => { _readOnly => 1 },
    };
}

sub GetMatches {
    my ( $class, %args ) = @_;

    my @params;
    my $sql = $class->_BuildMatchQuery( '', \@params, %args );

    return undef unless $sql;

    return Common::DB::ItemCollection->new(
        class        => $class,
        query        => $sql,
        dbID         => $class->kDB,
        placeHolders => \@params,
    );
}

sub GetStagedMatches {
    my ( $class, %args ) = @_;

    my @params;
    my $sql = $class->_BuildMatchQuery( 'catalog_import_', \@params, %args );
    return undef unless $sql;

    return Common::DB::ItemCollection->new(
        class        => $class,
        query        => $sql,
        dbID         => $class->kDB,
        placeHolders => \@params,
    );
}

sub _BuildMatchQuery {
    my ( $class, $prefix, $params, %args ) = @_;
    #
    # '$prefix' allows us to use the same code for both the normal tables and the 'catalog_import_' tables.
    assert( $prefix eq '' || $prefix eq 'catalog_import_' );

    my @where;

    my $onixProductCodes;
    if ( $args{productTypes} ) {
        $onixProductCodes = join( '","', @{ $args{productTypes} } );
    }
    my $isbn13        = $args{isbn13};
    my $isbn10        = $args{isbn10};
    my $titleClean    = ( defined $args{title} && length $args{title} ) ? Common::Util::clean_book_title( $args{title} ) : undef;
    my $subtitleClean = ( defined $args{subtitle} && length $args{subtitle} ) ? Common::Util::clean_book_title( $args{subtitle} ) : undef;

    return undef unless ( $titleClean || $isbn13 || $isbn10 );

    #    Common::Log::Print("titleClean='$titleClean', subtitleClean='$subtitleClean', isbn13='$isbn13', isbn10='$isbn10'");

    # Let's find the specific product we're interested in first and then we can create a query
    # that includes all the joined information we're interested in

    my @productIDs;

    my $classbase = 'BookPub::DB::Item::';
    if ($prefix) {
        $classbase .= 'CatalogImportItem::';
    }

    if ( $isbn13 || $isbn10 ) {
        my $products = ( $classbase . 'BookProduct' )->GetAllWithEitherISBN( $isbn10, $isbn13 );
        while ( my $product = $products->next() ) {
            push( @productIDs, $product->product_id() );
        }
    }

    if ($titleClean) {
        my $books = ( $classbase . 'Book' )->GetAllByCleanTitleAndSubtitle( $titleClean, $subtitleClean );
        while ( my $book = $books->next() ) {
            my $products = ( $classbase . 'BookProduct' )->GetAllByBookID( $book->book_id() );
            while ( my $product = $products->next() ) {
                push( @productIDs, $product->product_id() );
            }
        }
    }

    if (@productIDs) {
        # we may have duplicate product IDs by now, but MySQL will happily deal with that for us
        push( @where, $prefix . 'product.' . $prefix . 'product_id IN (' . join( ',', @productIDs ) . ')' );
    } else {
        return undef;
    }

    # !!! This needs to be re-writted to join with 'contributor'.
    # !!! So, let's do it the 'naive' way first, and see what it does...
    #
    my $sql = "SELECT " . $prefix . 'product.' . $prefix . 'product_id as product_id,'
      . $prefix
      . 'product.release_date,'
      . $prefix
      . 'book_product.isbn13 as isbn13,'
      . $prefix
      . 'book_product.isbn10 as isbn10,'
      . $prefix
      . 'book.book_id as book_id,'
      . $prefix
      . 'book.title as title,'
      . $prefix
      . 'book.title_clean as title_clean,'
      . $prefix
      . 'book.subtitle as subtitle,'
      . $prefix
      . 'book.subtitle_clean as subtitle_clean,'
      . $prefix
      . 'onix_code.code_value as onix_code_value'
      . ' FROM '
      . $prefix . 'book,'
      . $prefix
      . 'product,'
      . $prefix
      . 'book_product,'
      . $prefix
      . 'book_format,'
      . $prefix
      . 'book_format_type,'
      . $prefix
      . 'onix_code';

    push @where, $prefix . 'product.' . $prefix . 'product_id=' . $prefix . 'book_product.' . $prefix . 'product_id';
    push @where, $prefix . 'book_product.' . $prefix . 'book_id=' . $prefix . 'book.' . $prefix . 'book_id';
    push @where, 'book_product.book_format_id=book_format.book_format_id';
    push @where, 'book_format.book_format_type_id=book_format_type.book_format_type_id';
    push @where, 'book_format_type.onix_code_id=onix_code.onix_code_id';

    if ( $args{catalog_import_id} ) {
        push @where, $prefix . 'book.catalog_import_id=' . $class->quote( $args{catalog_import_id} );
        push @where, $prefix . 'product.catalog_import_id=' . $class->quote( $args{catalog_import_id} );
        push @where, $prefix . 'book_product.catalog_import_id=' . $class->quote( $args{catalog_import_id} );
    }

    # Don't bother with any onix codes unless onixProductCodes is passed in.
    # (the rest are derivative)
    #
    if ($onixProductCodes) {
        push @where, $prefix . qq|onix_code.code_value IN("$onixProductCodes")|;
    }

    $sql .= ' WHERE ' . join( ' AND ', @where );
    return $sql;
}

sub GetPartialMatchesByTitle {
    my ( $class, $title ) = @_;
    assert($title);

    my @params;
    my $sql =
        "SELECT product_id, MATCH(book.title) AGAINST(?) as book_score"
      . " FROM book, book_product"
      . " WHERE book_product.book_id =book.book_id"
      . " AND MATCH(book.title) AGAINST(?)"
      . " ORDER BY book_score DESC"
      . " LIMIT 30";

    push( @params, $title, $title );
    return Common::DB::ItemCollection->new(
        class        => $class,
        query        => $sql,
        dbID         => $class->kDB,
        placeHolders => \@params,
    );
}

sub GetPartialMatchesByAuthor {
    my ( $class, $author ) = @_;
    assert($author);

    my @params;
    my $sql =
        "SELECT product_id, MATCH(contributor.name_inverted) AGAINST(?) as author_score"
      . " FROM book, book_product, book_contributor, contributor"
      . " WHERE book_product.book_id=book.book_id"
      . " AND book_contributor.book_id=book.book_id"
      . " AND contributor.contributor_id=book_contributor.contributor_id"
      . " AND book_contributor.contributor_role_id=1"
      . " AND MATCH(contributor.name_inverted) AGAINST(?)"
      . " ORDER BY author_score DESC"
      . " LIMIT 30";

    push( @params, $author, $author );
    return Common::DB::ItemCollection->new(
        class        => $class,
        query        => $sql,
        dbID         => $class->kDB,
        placeHolders => \@params,
    );
}

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

    my @params;
    my $sql =
        "SELECT product_id"
      . " FROM book, book_product"
      . " WHERE book_product.book_id=book.book_id"
      . " AND isbn10 like '%"
      . $isbn10 . "%'"
      . " LIMIT 30";

    return Common::DB::ItemCollection->new(
        class => $class,
        query => $sql,
        dbID  => $class->kDB,

        #        placeHolders => \@params,
    );
}

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

    my @params;
    my $sql =
        "SELECT product_id"
      . " FROM book, book_product"
      . " WHERE book_product.book_id=book.book_id"
      . " AND isbn13 like '%"
      . $isbn13 . "%'"
      . " LIMIT 30";

    return Common::DB::ItemCollection->new(
        class => $class,
        query => $sql,
        dbID  => $class->kDB,
    );
}


1;