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

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

use lib '/app/tools/bookpub/lib';
use BookPub::DB::Item::File;
use BookPub::DB::Item::ContributorRole;

use base 'BookPub::DB::Item::SalePriceException';

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

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

    # Going to extend the base 'sale_price_exception' schema and add in the many columns we want to join.
    #
    my $config = BookPub::DB::Item::SalePriceException->_GenerateClassConfig();

    $config->{file_name}               = { _readOnly => 1 };
    $config->{file_id}                 = { _readOnly => 1 };
    $config->{service_name}            = { _readOnly => 1 };
    $config->{format}                  = { _readOnly => 1 };
    $config->{date_begin}              = { _readOnly => 1 };
    $config->{date_end}                = { _readOnly => 1 };
    $config->{line_num}                = { _readOnly => 1 };
    $config->{isbn13}                  = { _readOnly => 1 };
    $config->{isbn10}                  = { _readOnly => 1 };
    $config->{service_product_id}      = { _readOnly => 1 };
    $config->{work_id}                 = { _readOnly => 1 };
    $config->{book_title}              = { _readOnly => 1 };
    $config->{book_subtitle}           = { _readOnly => 1 };
    $config->{book_id}                 = { _readOnly => 1 };
    $config->{is_agency}               = { _readOnly => 1 };
    $config->{units}                   = { _readOnly => 1 };
    $config->{revenue}                 = { _readOnly => 1 };
    $config->{sale_currency_code}      = { _readOnly => 1 };
    $config->{list_price}              = { _readOnly => 1 };
    $config->{has_price}               = { _readOnly => 1 };
    $config->{country_code}            = { _readOnly => 1 };
    $config->{price_type_qualifier_id} = { _readOnly => 1 };
    $config->{publishing_status}       = { _readOnly => 1 };

    return $config;
}

sub SearchCount {
    my $class  = shift;
    my $search = $class->_searchComponents(@_);
    my @select;
    my @from;

    push( @select, 'SUM(price_exceptions) AS total' );
    push( @select, 'SUM(price_exceptions_unapproved) AS unapproved' );

    push( @from, "file" );

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

    $sql .= " WHERE (" . join( " AND ", @{ $search->{criteria} } ) . ")" if ( @{ $search->{criteria} } );

    #$sql .= " OR (" . join( " AND ", @{$search->{alternateCriteria}} ) . ")"
    #    if( @{$search->{criteria}} && @{$search->{alternateCriteria}} );

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

sub SearchGroupCount {
    my $class   = shift;
    my %args    = @_;
    my $orderBy = $args{order_by};
    my $search  = $class->_searchComponents(@_);

    # We have no grouping criteria so no counts required
    return unless ($orderBy);

    $orderBy = $orderBy->[0] if ( ref($orderBy) eq 'ARRAY' );

    my @select;
    push( @select, " $orderBy as group_field " );

    my $sql = "SELECT  " . join( ',', @select ) . " FROM  " . join( ' ', @{ $search->{from} } );

    $sql .= " WHERE (" . join( " AND ", @{ $search->{criteria} } ) . ")" if ( @{ $search->{criteria} } );
    $sql .= " OR (" . join( " AND ", @{ $search->{alternateCriteria} } ) . ")"
      if ( @{ $search->{criteria} } && @{ $search->{alternateCriteria} } );

    $sql .= " GROUP BY " . join( ", ", @{ $search->{groupby} } ) if ( @{ $search->{groupby} } );

    my $finalSQL .= "SELECT group_field, count(*) as count FROM ($sql) as subq GROUP BY group_field";

    Common::Log::Debug("QUERY: $finalSQL");

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

sub Search {
    my $class  = shift;
    my $search = $class->_searchComponents(@_);

    my $sql = "SELECT  " . join( ',', @{ $search->{select} } ) . " FROM  " . join( ' ', @{ $search->{from} } );

    $sql .= " WHERE (" . join( " AND ", @{ $search->{criteria} } ) . ")" if ( @{ $search->{criteria} } );
    $sql .= " OR (" . join( " AND ", @{ $search->{alternateCriteria} } ) . ")"
      if ( @{ $search->{criteria} } && @{ $search->{alternateCriteria} } );

    $sql .= " GROUP BY " . join( ", ", @{ $search->{groupby} } ) if ( @{ $search->{groupby} } );
    $sql .= " ORDER BY " . join( ", ", @{ $search->{orderby} } ) if ( @{ $search->{orderby} } );

    Common::Log::Debug("QUERY: $sql");

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

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

    my $modifiedSaleIDs = $args{modified_sales};
    my $orderBy         = $args{order_by};
    my $order           = $args{order};
    my $fileID          = $args{file_id};
    my $serviceID       = $args{service_id};
    my $hideApproved    = $args{hide_approved};

    my @groupBy;
    my @orderBy;
    my @criteria;
    my @alternateCriteria;
    my @select;
    my @from;

    assert( !$order || lc($order) eq 'asc' || lc($order) eq 'desc', "Invalid order by '$order'" );

    # Coerce into array refs
    $modifiedSaleIDs = [$modifiedSaleIDs] if ( $modifiedSaleIDs && ref($modifiedSaleIDs) ne 'ARRAY' );
    $orderBy         = [$orderBy]         if ( $orderBy         && ref($orderBy) ne 'ARRAY' );

    # First we set up our search criteria
    push( @criteria, "file_status = " . BookPub::DB::Item::File::STATUS_OPEN );
    push( @criteria, "file_id = " . $class->quote($fileID) ) if ( defined($fileID) );
    push( @criteria, "file.service_id = " . $class->quote($serviceID) ) if ( defined($serviceID) );
    push( @criteria, "NOT (approved)" ) if ($hideApproved);

    push( @select, kTable . ".*" );
    push( @select,
        "IF(r_native_list_price.r_native_list_price IS NOT NULL, r_native_list_price.r_native_list_price, sale.list_price) as list_price" );
    push( @select, "SUM(sale.units) as units" );
    push( @select, "SUM(sale.revenue) as revenue" );
    push( @select, "sale.is_agency as is_agency" );
    push( @select,
"IF(r_native_list_price.r_native_list_price_currency IS NOT NULL, r_native_list_price.r_native_list_price_currency, IF(sale.r_list_price_currency IS NOT NULL AND sale.r_list_price_currency != '', sale.r_list_price_currency, sale.currency_code) ) as sale_currency_code"
    );
    push( @select, "sale.date_begin as date_begin" );
    push( @select, "sale.date_end as date_end" );
    push( @select, "sale.country_code as country_code" );
    push( @select, "sale.price_type_qualifier_id as price_type_qualifier_id" );
    push( @select, "sale.line_num as line_num" );
    push( @select, "file.orig_file_name as file_name" );
    push( @select, "file.file_id as file_id" );
    push( @select, "book.title as book_title" );
    push( @select, "book.subtitle as book_subtitle" );
    push( @select, "book.book_id as book_id" );
    push( @select, "book_product.isbn10 as isbn10" );
    push( @select, "book_product.isbn13 as isbn13" );
    push( @select, "sale.service_product_id as service_product_id" );
    push( @select, "book_product.work_id as work_id" );
    push( @select, "book_product.onix_code_publishing_status as publishing_status" );
    push( @select, "service.service_name as service_name" );
    push( @select, "last_name as author_last_name" );
    push( @select, "book_product.book_format_id" );
    push( @select, "IF( format_subtype.onix_code_id, format_subtype.description, format_type.description ) as format" );
    push( @select, "IF( sale_price_exception.product_market_price_id, 1, 0 ) as has_price" );
    push( @select, "IF( sale_price_exception.sale_id in (" . join( ', ', @$modifiedSaleIDs ) . "), 1, NULL ) as modified " )
      if ($modifiedSaleIDs);

    push( @from, kTable );
    push( @from, " INNER JOIN sale USING (sale_id)" );
    push( @from, " LEFT JOIN r_native_list_price USING (sale_id)" );
    push( @from, " INNER JOIN file USING (file_id)" );
    push( @from, " LEFT JOIN service ON (file.service_id = service.service_id)" );
    push( @from, " LEFT JOIN book_product USING (product_id)" );
    push( @from, " LEFT JOIN book_format USING (book_format_id)" );
    push( @from, " LEFT JOIN book USING (book_id)" );
    push( @from, " LEFT JOIN book_contributor ON (book.book_id = book_contributor.book_id AND sequence = 1)" );
    push( @from, " LEFT JOIN contributor USING (contributor_id)" );
    push( @from, " LEFT JOIN book_format_type USING (book_format_type_id)" );
    push( @from, " LEFT JOIN onix_code as format_type ON ( book_format_type.onix_code_id = format_type.onix_code_id )" );
    push( @from, " LEFT JOIN book_format_subtype USING (book_format_subtype_id)" );
    push( @from, " LEFT JOIN onix_code as format_subtype ON ( book_format_subtype.onix_code_id = format_subtype.onix_code_id )" );

    push( @alternateCriteria, "sale_price_exception.sale_id in (" . join( ', ', @$modifiedSaleIDs ) . ") " )
      if ( $modifiedSaleIDs && @criteria );

    if ($orderBy) {
        for ( my $i = 0 ; $i < @$orderBy ; $i++ ) {
            my $o = $order && $i == 0 ? " $order " : "";
            push( @orderBy, "$orderBy->[$i]$o" );
        }
    }

    # the following group by is _very_ tightly coupled with the group by in
    # BookPub::DB::Item::File::updatePriceValidationCounts
    #
    # i'm sure i should marry the two (i.e. pull it centrally), but i also just want
    # to get what it mostly a nice enhancement rolled out
    #
    # if you muck with this, you probably should muck with the other

    push( @groupBy,
        'date_begin',         'date_end',          'product_id', 'list_price', 'sale.currency_code',
        'sale.price_type_id', 'sale.country_code', 'price',      'variance',   'approved' );

    return {
        select            => \@select,
        from              => \@from,
        criteria          => \@criteria,
        alternateCriteria => \@alternateCriteria,
        orderby           => \@orderBy,
        groupby           => \@groupBy
    };
}

1;