#!/usr/bin/perl
use strict;

package API::Order::Shipping::BoxEstimate;

our $Config;
BEGIN {
	use API::Config;
	$Config = $API::Config::Config;
}
use lib ($Config->{'Website_libs'});

use ECS::SQL;
use ECS::Shipping;
use ECS::Shipping::Box;

# Not an API method
# Gets all available shipping boxes for an order
# It will recalculate the shipping estimate for the order.  Part of this will delete and repopulate the
# shippingBoxRecalc and shippingBoxRecalcProduct tables for the order.  It will then search to see if 
# there was a previous order with the same products.  If it finds one, it will set suggested boxes.
# 
# Requires
# - $p->{'order'}
# - $p->{'courier'} (from API::Order::Shipping::getCourier)
# - $p->{'shipment'} (from API::Order::Shipping::getWarehouseShipment)
# - $p->{'packProducts'}
# - $p->{'boxes'} (from API::Order::Shipping::getAvailableBoxes -> ECS::Shipping::Box::getBoxes)
# Sets
# - $p->{'order'}->{'boxEstimates'}
# - $p->{'order'}->{'boxEstimatesVolume'}
sub getBoxes {
	my $p = shift;
	my $order = $p->{'order'};

	$order->{'boxEstimates'} = [];
	$order->{'boxEstimatesVolume'} = 0;
	
	return 0 if (!$p->{'shipment'});
	
	local $p->{'recalcBoxes'} = 0;
	
	if (API::Order::Shipping::BoxEstimate::recalcBoxes($p)) {
		$p->{'recalcBoxes'} = 1;
	}

	# If this order matches a previous shipment (shared or exact match), suggest box sizes to the packer
	if ($order->{'shipmentID'}) {
		
		$order->{'boxEstimates'} = API::Order::Shipping::BoxEstimate::fetchBoxes($p);
		
		my $total_box_volume = 0;
		foreach my $box (@{ $order->{'boxEstimates'} }) {
			$total_box_volume += ($box->{'length'} * $box->{'width'} * $box->{'height'});
		}
		
		$order->{'boxEstimatesVolume'} = $total_box_volume;
	}
	
	return 1;
}

sub fetchBoxes {

	my $p = shift;
	my $order = $p->{'order'};
	
	my $table = 'shippingBoxEstimate';
	
	if ($p->{'recalcBoxes'}) {
		$table = 'shippingBoxRecalc';
	}
	
	my @box_estimates;
	my $box_estimates_by_ID = {};
	
	my $sql = 'SELECT * FROM '.$table.' LEFT JOIN shippingBox USING(shippingBoxID) WHERE orderID = '.$order->{'orderID'};
	my $DBHr = $p->{DBHr}||$p->{DBH};
	my $boxes = ECS::SQL::query({ 'DBH' => $DBHr, 'sql' => $sql });

	if ($boxes) {
		
		$sql = 'SELECT * FROM '.$table.'Product WHERE';
		
		foreach my $box (@{ $boxes }) {
		
			#$total_volume += ($box->{'length'} * $box->{'width'} * $box->{'height'});
			
			if ($box->{'barcode'} eq 'CUSTOM' and $box->{'enabled'}) {
				$box->{'customBox'} = 1;
				
				local $p->{'shippingBoxID'} = $box->{'shippingBoxID'};
				$box->{'boxComponents'} = ECS::Shipping::Box::getBoxComponents($p);	
			}
			
			$box->{'barcode'} = '' if !$box->{'customBox'};
			
			$box->{'barcode'} = $p->{'boxes'}->{$box->{'shippingBoxID'}}->{'barcode'} if (
				$box->{'shippingBoxID'}
				and $p->{'boxes'}->{$box->{'shippingBoxID'}}
			);
			
			delete $box->{'shippingBoxID'} if (defined $box->{'shippingBoxID'});
			
			$box->{'weightEstimate'} = $box->{'weight'};
			delete $box->{'weight'};
			
			push(@box_estimates, $box);
			$box_estimates_by_ID->{$box->{$table.'ID'}} = $box;
			
			$sql .= ' '.$table.'ID = '.$box->{$table.'ID'}.' OR';
			
			delete $box->{'orderID'};
			delete $box->{$table.'ID'};
		}

		if (scalar(@box_estimates)) {
			
			$sql = substr($sql, 0, -3);

			my $box_products = ECS::SQL::query({ 'DBH' => $DBHr, 'sql' => $sql });
			
			if ($box_products) {
				foreach my $box_product (@{ $box_products }) {
				
					my $box = $box_estimates_by_ID->{$box_product->{$table.'ID'}};
					
					$box->{'products'}->{$box_product->{'productID'}} = {
						'qty' => $box_product->{'qty'},
						'qtyRemain' => $box_product->{'qty'}
					};
				}
			}
		}
	}
	
	return \@box_estimates;
}

# Not an API method
# Recalculates the Shipping Box Estimates for an order
# Requires
# - $p->{'order'} (including 'intl')
# - $p->{'shipment'} (from API::Order::Shipping::getWarehouseShipment)
sub recalcBoxes {
	my $p = shift;
	my $order = $p->{'order'};

	my @recalc_reasons;
	
	# If a product's dimensions were changed after the order was placed, recalc box sizes is needed
	my $order_time_epoch = ECS::Time::datetimeToEpoch($order->{'order_time'});
	my $last_modified_dims_time = 0;
	
	# will need to recalc the boxes if there is a mix of free shipping and non free shipping products
	my $hasShipFree; 
	my $productShipsFree; 

	foreach my $product (@{ $order->{'packProducts'} }) {
		$productShipsFree ||= !!($product->{shipsFree});
		
		if (!$hasShipFree) {
			$hasShipFree = $productShipsFree xor !!($product->{shipsFree});
		}
		$productShipsFree = !!($product->{shipsFree});
		
		my $time_cart_modified = ECS::Time::datetimeToEpoch($product->{'dtCartModified'});
		my $time_dims_modified = ECS::Time::datetimeToEpoch($product->{'dtDimsModified'});
		
		$last_modified_dims_time = $time_dims_modified if (
			(
				$time_dims_modified > $time_cart_modified # If the dims were modified after the cart record (box estimates are recalculated when changing the products on an order)
				|| !$order->{'estimateShipmentID'} # If the product was added to the order with missing dimensions, no estimateShipmentID could exist
			)
			&& $time_dims_modified > $last_modified_dims_time # Latest time
		);
	}
	
	# hasShipFree is set if there is a mix of free and non free shipping products
	if ($hasShipFree) {
		push (@recalc_reasons, 'dims');
	} elsif ($last_modified_dims_time > $order_time_epoch) {
		# Product dims were modified after this order was placed. recalculate the estimated box sizes for the updated product dimensions
		push(@recalc_reasons, 'dims');
	}
	
	my $box_estimates_products = {};
	
	foreach my $box (@{ API::Order::Shipping::BoxEstimate::fetchBoxes($p) }) {
		foreach my $productID (keys %{ $box->{'products'} }) {
			if (defined $box_estimates_products->{$productID}) {
				$box_estimates_products->{$productID}->{'qty'} += $box->{'products'}->{$productID}->{'qty'};
			} else {
				$box_estimates_products->{$productID} = {
					'qty' => $box->{'products'}->{$productID}->{'qty'},
					'found' => 0
				};
			}
		}
	}

	# Check to see if the estimates boxes contain the same products/quantities as the order currently contains
	# - Shipping Box Recalc Reason: Kit Contents have changed
	my $products_match = 1;

	# Check to see if the estimates boxes contain the same products/quantities as the order currently contains
	foreach my $product (@{ $order->{'packProducts'} }) {
		if (defined $box_estimates_products->{$product->{'productID'}}) {
			$box_estimates_products->{$product->{'productID'}}->{'found'} = 1;
		} else {
			# Product on order not found in any box estimates
			$products_match = 0;
			last;
		}

		if ($product->{'qty'} != $box_estimates_products->{$product->{'productID'}}->{'qty'}) {
			# Product quantity on order does not match box estimates quantity
			$products_match = 0;
			last;
		}
	}

	# Check to see if all estimate products were found
	if ($products_match) {
		foreach my $productID (keys %{ $box_estimates_products }) {
			if (!$box_estimates_products->{$productID}->{'found'}) {
				# Box estimate product exists, but product not on order
				$products_match = 0;
				last;
			}
		}
	}

	# If the estimates boxes do not contain the same products/quantities as the order, recalculate the estimate box sizes
	if (!$products_match) {
		push(@recalc_reasons, 'kit');
	}
	
	local $p->{'shipment_courier'} = {
		'intl' => $order->{'intl'},
		'shipmentID' => 0,
		'boxes' => [],
		'productWeight' => $p->{'shipment'}->{'productWeight'},
		'packProducts' => $p->{'shipment'}->{'packPriority'},
		'boxLimits' => $order->{'boxLimits'},
        'products' => $p->{'shipment'}->{'products'},
	};

	local $p->{ignoreFreeShipping} = 1;
	return 0 if (!ECS::Shipping::Shipment::getBoxes($p));
	
	if (
		($p->{'shipment_courier'}->{'shipmentID'} || 0) # now matches shipment
		and
		(
			!($order->{'estimateShipmentID'} || 0) # previously did not match shipment
			or 
			!ECS::Shipping::Shipment::isShipmentActive({'DBH'=> $p->{'DBH'}, 'shipmentID'=> $order->{'estimateShipmentID'} }) # shipment quote from checkout is not active any more
			or
			!ECS::Shipping::Shipment::isShipmentActive({'DBH'=> $p->{'DBH'}, 'shipmentID'=> $order->{'shipmentID'} }) # primary shipment quote is not active any more
		)	
	) {
		# Previous Shipment data is now available
		push(@recalc_reasons, 'shipment');
	}

	if (@recalc_reasons) {
		
		local $p->{DBH} = ECS::SQL::connect();
		local $p->{'recalcReasons'} = \@recalc_reasons;
		API::Order::Shipping::BoxEstimate::saveRecalcReasons($p);
	
		API::Order::Shipping::BoxEstimate::saveBoxes($p);

		return 1;
	}

	return 0;
}

# Saves the shippingBoxRecalcReason records when a recalc is required
# Requires
# - $p->{'order'}
# - $p->{'recalcReasons'} (array)
sub saveRecalcReasons {
	my $p = shift;
	my $orderID = $p->{'order'}->{'orderID'};
	
	my $sql = 'DELETE FROM shippingBoxRecalcReason WHERE orderID = '.$orderID;
	ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => $sql });
	
	my @insert_values;
	
	foreach my $reason (@{ $p->{'recalcReasons'} }) {
		push(@insert_values, '('.$orderID.", '".$reason."')");
	}
	
	$sql = 'INSERT INTO shippingBoxRecalcReason (orderID, reason) VALUES '.join(',', @insert_values);
	ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => $sql });
	
	return 1;
}

# Saves boxes to shippingBoxRecalc when recalculated
# It will delete the existing shippingBoxRecalc and shippingBoxRecalcProduct records for the order
# and then add new ones based on the information in the shipment_courier hash.  Any products in 
# the hash need to be consolidated by product within box or the insert will fail due to a duplicate
# key error.
#
# Requires
# - $p->{'shipment_courier'} (hash)
sub saveBoxes {
	my $p = shift;
	my $order = $p->{'order'};
	
	# If shipmentID has changed, save it
	if (
		($p->{'shipment_courier'}->{'shipmentID'} || 0)
		!=
		($order->{'shipmentID'} || 0)
	) {
	
		$order->{'shipmentID'} = $p->{'shipment_courier'}->{'shipmentID'};
		$order->{'shipmentID'} = undef if (!$order->{'shipmentID'}); # NULL instead of zero
		
		# Update the order with the new shipmentID
		ECS::SQL::update({
			'DBH' => $p->{'DBH'},
			'key' => 'orderID',
			'table' => 'orderShipping',
			'columns' => [ qw( orderID shipmentID ) ],
			'hash' => $order
		});
	}
	
	# remove any existing box sizes
	my $sql = '
		DELETE
			shippingBoxRecalc,
			shippingBoxRecalcProduct
		FROM shippingBoxRecalc
			LEFT JOIN shippingBoxRecalcProduct USING (shippingBoxRecalcID)
		WHERE
			orderID = '.$order->{'orderID'};
	
	ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => $sql });

	# Insert the new estimated box sizes.
	# First, consolidate the products within each box.  The shippingBoxRecalcProduct table has a key on
	# shippingBoxRecalcID and productID.  It is possible that there will be multiple entries for a productID
	# due to kits possibly having one or more of the same individual product.  The consolidated list will be
	# used to insert the new estimated box sizes and the original list will be left intact.
	my $combinedProducts = consolidateBoxProducts($p->{'shipment_courier'}->{'boxes'});

	foreach my $box (@{ $combinedProducts }) {

		$box->{'orderID'} = $order->{'orderID'};
		
		my $box_estimate_id = ECS::SQL::insert({
			'DBH' => $p->{'DBH'},
			'table' => 'shippingBoxRecalc',
			'columns' => [ qw( orderID shippingBoxID weight length width height ) ],
			'hash' => $box
		});

		if ($box_estimate_id) {
			foreach my $product (@{$box->{'products'}}) {
				my $result = ECS::SQL::insert({
                        'DBH' => $p->{'DBH'},
                        'table' => 'shippingBoxRecalcProduct',
                        'columns' => [ qw( shippingBoxRecalcID productID qty ) ],
                        'hash' => {
                            'shippingBoxRecalcID' => $box_estimate_id,
                            'productID' => $product->{'productID'},
                            'qty' => $product->{'qty'}
                            }
                    });
				# 4/22/2014 - rswiderski
				# If the insert fails, stop processing so that the page doesn't render.
				# The insert method returns undef if the SQL is not executed successfully.
				if (!defined $result) {
					die "Error inserting shippingBoxRecalcProduct record";
				}
				
			}
		}
	}
	
	return 1;
}

# Consolidate the quantity information found in the data.  This is done by looping through the products list and creating a
# hash with the productID as the key.  The hash is searched for each entry in the product list.  When it finds a productID in
# the list, it increments the quantity.  Otherwise it adds that product to the list.  It will return the consolidated data.
sub consolidateBoxProducts {
     my $p = shift;
     my @boxes = ();

     foreach my $box (@{$p}) {
          my %new_box = ();
          my %consolidatedProducts = ();
		  
          foreach my $product (@{$box->{'products'}}) {
               if (exists $consolidatedProducts{$product->{'productID'}}) {
                    $consolidatedProducts{$product->{'productID'}}->{'qty'} += $product->{'qty'};
               }
			   else {
					$consolidatedProducts{ $product->{'productID'} } = {
                    'qty' => $product->{'qty'},
                    'productID' => $product->{'productID'}};
               }
          }
		  my @prods = values %consolidatedProducts;
		  $new_box{'products'} = \@prods;
		  
		  $new_box{'shippingBoxID'} = $box->{'shippingBoxID'};
		  $new_box{'weight'} = $box->{'weight'};
		  $new_box{'length'} = $box->{'length'};
		  $new_box{'width'} = $box->{'width'};
		  $new_box{'height'} = $box->{'height'};
		  
          push( @boxes, \%new_box );
     }
     return \@boxes;
}

