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

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

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

use BookPub::Tracker::Service;

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

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

    my $config = BookPub::DB::Item::WatchListItem->_GenerateClassConfig();

    $config->{average_rank} = { _readOnly => 1 };
    $config->{service_rank} = { _readOnly => 1 };

    # !!! EXPERIMENTAL
    $config->{alert_count} = { _readOnly => 1 };

    return $config;
}

sub GetByWatchList {
    my ( $class, $watchListID ) = @_;

    my $sql =
        "SELECT w.*,b.average_rank, IF(b.average_rank IS NULL or b.average_rank='', 1, 0) AS isnull " . "FROM "
      . kTable . " w "
      . "LEFT JOIN book_product b ON (w.product_id = b.product_id) "
      . "INNER JOIN book USING (book_id) "
      . "WHERE w.watch_list_id = "
      . $watchListID
      . " AND w.deleted = 0 "
      . "ORDER BY isnull ASC, b.average_rank ASC, book.title";

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

sub GetDynamic {
    my $class = shift;
    my %args  = @_;
    my @where;
    my @join;
    my @orderBy;
    my @select;
    my $limit;

    my $watchList = $args{watchList} || die;

    @select = ("book_product.*");

    @join = (
        "book_product",
        "INNER JOIN book USING (book_id)",
        "INNER JOIN book_format USING (book_format_id)",
        "INNER JOIN book_format_type USING (book_format_type_id)",
        "INNER JOIN onix_code USING (onix_code_id)"
    );

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaPublicationDate( $watchList->PublicationDate );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaFormat( $watchList->Format );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaRank( $watchList->Rank, $watchList->Market );
    push( @join,    $j ) if ($j);
    push( @where,   $w ) if ($w);
    push( @orderBy, $o ) if ($o);
    push( @select,  $s ) if ($s);
    $limit = $l;

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaAuthor( $watchList->Author );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaImprint( $watchList->ImprintID );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaPublisher( $watchList->PublisherID );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaPrice( $watchList->Price, $watchList->getMarketIDs );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my ( $j, $w, $o, $l, $s ) = _dynamicCriteriaAlert( $watchList->Alert );
    push( @join,  $j ) if ($j);
    push( @where, $w ) if ($w);

    my $sql = "SELECT " . join( ', ', @select );

    if (@join) {
        $sql .= " FROM " . join( ' ', @join );
    }

    if (@where) {
        $sql .= " WHERE " . join( ' AND ', @where );
    }

    $sql .= " GROUP BY if( onix_code.code_value = 'DG', book_product.book_id, book_product.product_id ) ";

    if (@orderBy) {
        $sql .= " ORDER BY " . join( ', ', @orderBy );
    } else {
        $sql .= " ORDER BY IF( average_rank IS NULL, 0, 1 ) DESC, CAST( average_rank AS UNSIGNED ), book.title ";
    }

    if ($limit) {

        # Must wrap limit in sub query so the pagination tools can limit record set too.
        $sql .= " LIMIT $limit ";
        $sql = "SELECT * FROM ( $sql ) as q ";
    }

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

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

sub _dynamicCriteriaPublicationDate {
    my $publicationDate = shift;
    my $where;
    my $join;

    if ($publicationDate) {
        $join  = "INNER JOIN product USING (product_id)";
        $where = "(release_date >= DATE(DATE_SUB( NOW(), interval " . $publicationDate . " day )) AND release_date <= DATE(NOW()) )";
    }

    return ( $join, $where );
}

sub _dynamicCriteriaAlert {
    my $alert = shift;
    my $where;
    my $join;

    return unless ($alert);

    $join = "INNER JOIN product_monitor_item as pmi USING (product_id) "
      . "INNER JOIN product_monitor_alert as pma ON (pmi.product_monitor_item_id = pma.product_monitor_item_id)";

    # Had to add something to exclude Borders alerts
    $where = "pma.date_resolved IS NULL AND pmi.service_id != 49";

    return ( $join, $where );
}

sub _dynamicCriteriaFormat {
    my $format = shift;
    my $where;
    my $join;

    return unless ($format);

    if ( $format == 1 ) {
        $where = "code_value = 'DG'";
    }

    elsif ( $format == 2 ) {
        $where = "code_value NOT IN ('DG')";
    }

    else {
        die "Unknown format type: $format";
    }

    return ( $join, $where );
}

sub _dynamicCriteriaRank {
    my $rank      = shift;
    my $serviceID = shift;
    my $where;
    my $join;
    my $orderBy;
    my $limit;
    my $select;

    return unless ($rank);

    # Use rank from the best sellers lists
    if ( $serviceID && $serviceID eq BookPub::Tracker::Service::BESTSELLERLIST ) {
        $select  = "rank as service_rank";
        $join    = "INNER JOIN best_seller_book_product USING (product_id) ";
        $orderBy = "IF( rank IS NULL, 0, 1 ) DESC, CAST( rank AS UNSIGNED )";
        $limit   = $rank;
    }

    # Use rank from service
    elsif ($serviceID) {
        $select = "product_monitor_item_rank.rank as service_rank";
        $join =
          "INNER JOIN product_monitor_item USING (product_id) " . "INNER JOIN product_monitor_item_rank USING (product_monitor_item_id) ";

        $where =
          "product_monitor_item.service_id = " . Common::DB::Item->quote($serviceID) . " AND product_monitor_item.status = 'current' ";

        $orderBy = "IF( rank IS NULL, 0, 1 ) DESC, CAST( rank AS UNSIGNED )";
        $limit   = $rank;
    }

    # Use average rank from the product (across all services)
    else {
        $orderBy = "IF( average_rank IS NULL, 0, 1 ) DESC, CAST( average_rank AS UNSIGNED )";

        $limit = $rank;
    }

    $orderBy .= ", book.title";

    return ( $join, $where, $orderBy, $limit, $select );
}

sub _dynamicCriteriaAuthor {
    my $author = shift;
    my $where;
    my $join;

    return unless ($author);

    $join =
        "INNER JOIN book_contributor USING (book_id) "
      . "INNER JOIN contributor USING (contributor_id) "
      . "INNER JOIN contributor_role USING (contributor_role_id) ";

    $where = "last_name LIKE( " . Common::DB::Item->quote( $author . '%' ) . " ) ";
    " AND role = 'author' ";

    return ( $join, $where );
}

sub _dynamicCriteriaImprint {
    my $imprintID = shift;
    my $where;
    my $join;

    return unless ($imprintID);

    $where = "imprint_id = " . Common::DB::Item->quote($imprintID);

    return ( $join, $where );
}

sub _dynamicCriteriaPublisher {
    my $publisherID = shift;
    my $where;
    my $join;

    return unless ($publisherID);

    $where = "publisher_id = " . Common::DB::Item->quote($publisherID);

    return ( $join, $where );
}

sub _dynamicCriteriaPrice {
    my $price     = shift;
    my $marketIDs = shift;
    my $where;
    my $join;

    return unless ( defined($price) );

    assert($marketIDs);

    $join =
        "INNER JOIN product_monitor_item USING (product_id) "
      . "INNER JOIN product_monitor_item_price USING (product_monitor_item_id) "
      . "INNER JOIN product_market USING (product_id) "
      . "INNER JOIN product_market_price USING (product_market_id) ";

    $where = "product_market_price.price <= " . Common::DB::Item->quote($price) . " AND market_id IN (" . join( ', ', @$marketIDs ) . ")";

    return ( $join, $where );
}

1;