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

use strict;
#use warnings;

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

use BookPub::DB::Item::Region;
use BookPub::DB::Item::Archive::ProductMarketPrice;

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

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

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

sub _ArchiveTableName {
    return 'archive_product_market_price';
}

sub DeleteFuturePrices {
    my ( $class, %args ) = @_;
    my $productMarketID = $args{productMarketID};
    my $date            = $args{date};
    assert($productMarketID);
    assert($date);

    # ... Should be a straightforward query.
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "DELETE FROM " . kTable . " WHERE product_market_id = ? AND start_date > ?";
    my @sqlParams = ($productMarketID, $date);
    $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
}

# This method differs from 'GetCurrentPrice', in that we want to find the last price that isn't a 'future' price.
# This should return the price that we want to replace with a new price.
#
# !!! This needs to also take price_type_id into account.  Seems odd, but some customers will have both types of
# !!! prices in the same market for the same product.
#
sub GetLastPrice {
    my ( $class, %args ) = @_;
    my $productMarketID      = $args{product_market_id};
    my $regionID             = $args{region_id};
    my $currencyCode         = $args{currency_code};
    my $date                 = $args{date};
    my $priceTypeID          = $args{price_type_id};
    my $priceTypeQualifierID = $args{price_type_qualifier_id};
    my $discountID           = $args{discount_id};

    assert($date);
    assert($productMarketID);
    assert($currencyCode);

    my @where;
    push @where, "product_market_id = " . $class->quote($productMarketID);
    push @where, "currency_code = ". $class->quote($currencyCode);
    push @where, "region_id = " . $class->quote($regionID) if ($regionID);
    push @where, "price_type_id = " . $class->quote($priceTypeID) if ($priceTypeID);

    if ($discountID) {
        push @where, "discount_id = " . $class->quote($discountID);
    } else {
        push @where, "discount_id IS NULL";
    }

    # !!! We are going to treat price qualifier values of NULL, 0 and 5 as if they were the same.
    if ( $priceTypeQualifierID && $priceTypeQualifierID != 0 && $priceTypeQualifierID != 5 ) {
        push @where, "price_type_qualifier_id = " . $class->quote($priceTypeQualifierID);
    } else {
        push @where, "(price_type_qualifier_id IN (0,5) OR price_type_qualifier_id IS NULL)";
    }

    push @where, "(start_date IS NULL OR start_date <= " . $class->quote($date) . ")";

    my $sql = "SELECT * FROM " . kTable . " WHERE " . join( ' AND ', @where ) . " ORDER BY start_date desc";

    my $collection = $class->GetAll($sql);
    return undef unless ( $collection && $collection->size() > 0 );
    my $currentItem = $collection->next();
    return $currentItem;
}

sub GetCurrentPrice {
    my ( $class, %args ) = @_;
    my $productMarketID = $args{product_market_id};
    my $date            = $args{date};
    my $regionID        = $args{region_id};
    my $currencyCode    = $args{currency_code};

    assert($date);
    assert($productMarketID);
    assert($currencyCode);

    # There should ONLY be 1 price that encompasses the passed-in date.
    # It's critical that we maintain the price table to reflect this...
    #
    my @where;
    push @where, "product_market_id = " . $class->quote($productMarketID);
    push @where, "currency_code = " . $class->quote($currencyCode);
    push @where, "region_id = " . $class->quote($regionID) if ($regionID);

    push @where, "((start_date IS NULL OR start_date <= "
    . $class->quote($date)
    . " ) AND (end_date IS NULL OR end_date > "
    . $class->quote($date)
    . " ))";

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();

    return undef unless $hr;
    return $class->Lookup( hash => $hr );
}

# This method is similar to GetCurrentPrice, except we can take a sale date range
# which could result in multiple price matches.
sub GetAllPrices {
    my ( $class, %args ) = @_;

    my $productMarketID      = $args{product_market_id};
    my $productID            = $args{product_id};
    my $marketID             = $args{market_id};
    my $priceTypeID          = $args{price_type_id};
    my $priceTypeQualifierID = $args{price_type_qualifier_id};
    my $startDate            = $args{start_date};
    my $endDate              = $args{end_date};
    my $regionID             = $args{region_id};
    my $currencyCode         = $args{currency_code};
    my $oldestFirst          = $args{oldest_first};
    my $discountID           = $args{discount_id};

    my $startGracePeriod = $args{start_date_padding} || 0;
    my $endGracePeriod   = $args{end_date_padding}   || 0;

    my $join = "";

    assert($startDate) if ($endDate);
    assert($endDate)   if ($startDate);

    # We need to be able to limit price to a market.
    assert( $productID || $productMarketID );

    my @where;
    push @where, "product_market_id = " . $class->quote($productMarketID) if ($productMarketID);
    push @where, "currency_code = " . $class->quote($currencyCode)        if ($currencyCode);
    push @where, "price_type_id = " . $class->quote($priceTypeID)         if ($priceTypeID);
    push @where, "market_id = " . $class->quote($marketID)                if ($marketID);
    push @where, "discount_id = " . $class->quote($discountID)            if ($discountID);

    # !!! We are going to treat price qualifier values of NULL, 0 and 5 as if they were the same.
    if ( defined $priceTypeQualifierID ) {
        if ( $priceTypeQualifierID != 0 && $priceTypeQualifierID != 5 ) {
            push @where, "price_type_qualifier_id = " . $class->quote($priceTypeQualifierID);
        } else {
            push @where, "(price_type_qualifier_id IN (0,5) OR price_type_qualifier_id IS NULL)";
        }
    }

    # Make sure we find prices that match _either_ sale date, so we can match prices that change between these dates...
    #
    if ( $startDate && $endDate ) {
        push @where,
            "("
          . "(start_date IS NULL OR start_date = 0 OR (DATE(DATE_SUB(start_date, interval "
          . $class->quote($startGracePeriod)
          . " day)) <= DATE("
          . $class->quote($endDate)
          ." )))"
          . " AND "
          . "(end_date IS NULL OR end_date = 0 OR (DATE(DATE_ADD(end_date, interval "
          . $class->quote($endGracePeriod)
          . " day)) >= DATE("
          . $class->quote($startDate)
          . " )))"
          . ")";
    }

    if ($regionID) {
        if ( ref($regionID) ) {
            my @regionID = map { $class->quote($_) } grep{$_} @$regionID;
            push @where, "region_id IN (" . join( ',', @regionID ) . ")";
        } else {
            push @where, "region_id =" . $class->quote($regionID);
        }
    }

    if ($productID) {
        push @where, "product_id = " . $class->quote($productID);
        $join .= " INNER JOIN product_market USING (product_market_id) ";
    }

    my $sql = "SELECT * FROM " . kTable . " $join WHERE " . join( ' AND ', @where ) . " ORDER BY start_date";
    if ( !$oldestFirst ) {
        $sql .= " DESC";
    }

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

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

# This method is similar to GetCurrentPrice, except we can take a sale date range
# which could result in multiple price matches.
sub GetAllCurrentPrice {
    my ( $class, %args ) = @_;

    assert( $args{price_type_id} );
    assert( $args{currency_code} );
    assert( $args{start_date} );
    assert( $args{end_date} );
    assert( $args{region_id} );

    return $class->GetAllPrices(%args);
}

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

    assert( $args{marketID} );
    assert( $args{productID} );

    my $sql =
        "SELECT DISTINCT(currency_code) as currency_code "
      . "FROM "
      . kTable . " "
      . "INNER JOIN product_market USING (product_market_id) "
      . "WHERE product_id = "
      . $class->quote( $args{productID} ) . " AND "
      . "market_id  = "
      . $class->quote( $args{marketID} );

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

sub GetRowsForShowBookByProductIDs {
    my ( $class, $aProductIDs ) = @_;

    return [] unless $aProductIDs && ref($aProductIDs) eq 'ARRAY' && @$aProductIDs;

    my $placeholders = join( ',', ('?') x scalar(@$aProductIDs) );

    my $sql = qq/
        SELECT
            pm.product_id,
            pm.product_market_id,
            pm.market_id,
            m.region_id AS market_region_id,
            pmp.product_market_price_id,
            pmp.region_id AS price_region_id,
            pmp.currency_code,
            pmp.price_type_id,
            pmp.price_type_qualifier_id,
            pmp.price,
            pmp.start_date,
            pmp.end_date
        FROM product_market pm
        INNER JOIN market m ON m.market_id = pm.market_id
        LEFT JOIN product_market_price pmp ON pmp.product_market_id = pm.product_market_id
        WHERE pm.product_id IN ($placeholders)
        ORDER BY pm.product_id, pm.product_market_id, pmp.currency_code, pmp.start_date DESC
    /;

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

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

    return \@rows;
}

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

    my $productMarketIDs = $args{productMarketID};
    my $regionIDs        = $args{regionID};
    my $currencyCode     = $args{currencyCode};
    assert($productMarketIDs);
    assert($regionIDs);

    $productMarketIDs = [$productMarketIDs] unless 'ARRAY' eq ref($productMarketIDs);
    $regionIDs = [$regionIDs] unless 'ARRAY' eq ref($regionIDs);

    my $productMarketIDString = join( ',', map {'?'} @$productMarketIDs );
    my $regionIDString        = join( ',', map {'?'} @$regionIDs );

    my @sqlParams = (@$productMarketIDs, @$regionIDs);
    my $sql =
        "SELECT DISTINCT product_market_id FROM product_market_price"
      . " WHERE product_market_id IN ( $productMarketIDString )"
      . " AND region_id IN ( $regionIDString )";

    if ($currencyCode) {
        $sql .= ' AND currency_code = ?';
        push @sqlParams, $currencyCode;
    }

    my @results;
    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    while ( my $id = $sth->fetchrow_array() ) {
        push @results, $id;
    }

    return @results;
}


1;