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

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

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

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

use BookPub::DB::Item::Archive::Book;
use base 'BookPub::DB::MetadataItem';

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

sub _ArchiveTableName {
    return 'archive_book';
}

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

    my $sql = 'SELECT * FROM ' . kTable . ' WHERE title=' . $class->quote($title);

    if ( defined $subtitle ) {
        $sql .= ' AND subtitle=' . $class->quote($subtitle);
    } else {
        $sql .= ' AND (subtitle IS NULL OR subtitle = \'\')';
    }

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

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

    my $sql = 'SELECT * FROM ' . kTable . ' WHERE title_clean=' . $class->quote($title);

    if ( defined $subtitle ) {
        $sql .= ' AND subtitle_clean=' . $class->quote($subtitle);
    }

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

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

    my $sql = 'SELECT * FROM ' . kTable;
    $sql .= ' ORDER BY title';
    return $class->GetAll($sql);
}

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

    # imprint_id now belongs to book_product.
    # So we'll need to join that.

    $imprintID = $class->quote($imprintID);
    my $sql = "SELECT * FROM " . kTable . " WHERE book_id IN (select book_id from book_product where imprint_id = $imprintID) ORDER BY title";
    return $class->GetAll($sql);
}

sub GetAllForContributorSortedByTitle {
    my ( $class, $contributorID ) = @_;
    assert($contributorID);

    $contributorID = $class->quote($contributorID);
    my $sql =
        "SELECT book.* FROM "
      . kTable
      . " LEFT JOIN (contributor, book_contributor) ON (contributor.contributor_id=book_contributor.contributor_id AND book_contributor.book_id=book.book_id)"
      . " WHERE contributor.contributor_id = $contributorID"
      . " ORDER BY title";

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

sub Search {
    my ( $class, $searchTerm ) = @_;
    assert($searchTerm);

    my $term = $searchTerm;
    $term =~ s/^\s+|\s+$//g;

    # ISBN search branch - if the search term looks like an ISBN, search for it as such.
    my $isbnField;
    if ( $term =~ /^\d{13}$/ ) {
        $isbnField = 'isbn13';
    } elsif ( $term =~ /^\d{9}[\dX]$/i ) {
        $isbnField = 'isbn10';
    }

    if ($isbnField) {
        my $sql =
            "SELECT DISTINCT book.* FROM book"
          . " INNER JOIN book_product ON book_product.book_id=book.book_id"
          . " WHERE book_product.$isbnField=" . $class->quote($term)
          . " ORDER BY title";

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

    # main search branch - search title, subtitle, and author name
    my $dbo = Common::RSApp::GetClientDB();
    my $likePattern = $dbo->DBQuote( '%' . $term . '%' );

    my $sql =
        "SELECT DISTINCT book.* FROM book"
        . " LEFT JOIN book_contributor ON book_contributor.book_id=book.book_id"
        . " LEFT JOIN contributor ON contributor.contributor_id=book_contributor.contributor_id"
        . " WHERE title LIKE $likePattern"
        . " OR subtitle LIKE $likePattern"
        . " OR contributor.name_inverted LIKE $likePattern";

    my ( $first, $last ) = split( / /, $term );
    if ( $first && $last ) {
        my $reorderedPattern = $dbo->DBQuote( '%' . "$last, $first" . '%' );
        $sql .= " OR contributor.name_inverted LIKE $reorderedPattern";
    }

    $sql .= " ORDER BY title";

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

sub GetCountByContributorID {
    my ( $class, $contributorID ) = @_;
    assert($contributorID);

    my $dbo = Common::RSApp::GetClientDB();
    $contributorID = $dbo->DBQuote($contributorID);

    my $sql =
        "SELECT COUNT(*) as book_count FROM "
      . kTable
      . " LEFT JOIN (contributor, book_contributor) ON (contributor.contributor_id=book_contributor.contributor_id AND book_contributor.book_id=book.book_id)"
      . " WHERE contributor.contributor_id = $contributorID";
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();

    return $hr->{book_count};
}

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

    $imprintID = $class->quote($imprintID);
    my $sql =
        'SELECT letter, min(page) as pagenum FROM'
      . q[ (SELECT lower(substring(title, 1, 1)) as 'letter', FLOOR(((((@rownum := @rownum + 1) ) / ]
      . $pageSize
      . q[ ) + 1)) as 'page' ]
      . q[ FROM book INNER JOIN book_product ON (book.book_id = book_product.book_id) ]
      . q[ WHERE imprint_id = ]
      . $imprintID
      . q[ ORDER BY LOWER(book.title) ]
      . q[ ) as subq GROUP BY letter ];

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

sub GetTitleAlphaIndexByContributorID {
    my ( $class, $pageSize, $contributorID ) = @_;
    assert($pageSize);
    assert($contributorID);

    $contributorID = $class->quote($contributorID);
    my $sql =
        'SELECT letter, min(page) as pagenum FROM'
      . q[ (SELECT lower(substring(title, 1, 1)) as 'letter', FLOOR(((((@rownum := @rownum + 1) ) / ]
      . $pageSize
      . q[ ) + 1)) as 'page' ]
      . q[ FROM book INNER JOIN book_contributor ON (book.book_id = book_contributor.book_id) ]
      . q[ WHERE contributor_role_id=1 AND contributor_id = ]
      . $contributorID
      . q[ ORDER BY LOWER(book.title) ]
      . q[ ) as subq GROUP BY letter ];

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

sub GetTitleAlphaIndex {
    my ( $class, $pageSize ) = @_;
    assert($pageSize);

    my $sql =
        'SELECT letter, min(page) as pagenum FROM'
      . q[ (SELECT lower(substring(title, 1, 1)) as 'letter', @rownum := @rownum + 1, FLOOR((((@rownum - 1) /]
      . $pageSize
      . q[) + 1)) as 'page' ]
      . q[ FROM book JOIN ( SELECT @rownum := 0 ) r ]
      . q[ ORDER BY LOWER(title) ]
      . q[ ) as subq GROUP BY letter ];

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

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

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

    # We need to 'seed' @rownum in a seperate query.
    #
    my $preset = $dbo->DoCmd('set @rownum := 0');

    my $sth = $dbo->DoCmd($sql);

    my %resultHash;
    while ( my $row = $sth->fetchrow_hashref() ) {
        my $letter = $row->{letter};
        my $page   = $row->{pagenum};
        if ( !defined $page ) {

            # !!! This ordinarily should NOT HAPPEN.
            # !!! However, there seems to be a DBI-related bug
            # !!! that can cause some of our more complex queries to
            # !!! not work (queries that WORK FINE via the mysql client).
            # !!! So in that case, there is no point in returning a broken
            # !!! alpha index.
            #
            return undef;
        }
        if ( $letter =~ /[a-z]/ ) {
            $resultHash{ uc($letter) } = $page;
        }
    }

    return \%resultHash;
}

1;