#!/usr/bin/perl -Ilibs
use strict;

package API::Order;

use API::Order::Cart;
use API::Order::Payment;
use API::Utils::Scrub;

our $Config;
BEGIN {
	use API::Config;
	$Config = $API::Config::Config;
}
use lib ($Config->{'Website_libs'});

use ECS::SQL;
use ECS::Error;
use ECS::Time;

use ECS::Order ();
use ECS::Product::Inventory::Committed;
use ECS::Product::Inventory::WIP;
use ECS::Product::LogChange ();

use List::Util; 

# input requirements:
# - orders (array of order hashes) (required)
#    - status (required)
#    - any other orders field (optional)
# - columns (array of orders columns to update)
# - user (required)
# - ipaddress (required)
sub updateOrders {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	foreach my $k ( qw(orders user ipaddress) ) {
		if (!defined $input->{$k} || !$input->{$k}) {
			$result->{'success'} = 0;
			$result->{'error'} = ucfirst($k).' not specified';
			return $result;
		}
	}
	
	my $num_orders = scalar(@{ $input->{'orders'} });
	
	if (!$num_orders) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No Orders specified to update';
		return $result;
	}
	
	if (!scalar(@{ $input->{'columns'} })) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No columns specified to update';
		return $result;
	}
	
	my $DBH = $input->{'DBH'} || ECS::SQL::connect();
	
	my $log_status_change = 0;
	my $sql = 'INSERT INTO orders (orderID,';
	# prevent SQL errors when updating order status, status is sometimes passed
	# in twice in input->columns
	foreach my $k (List::Util::uniq @{ $input->{'columns'} }) {
		$log_status_change = 1 if ($k eq 'status');
		
		$sql .= ' '.$k.',';
	};
	
	chop($sql);
	$sql .= ') VALUES';
	
	foreach my $order (@{ $input->{'orders'} }) {
		
		$sql .= ' ('.$order->{'orderID'}.',';
		
		foreach my $k (List::Util::uniq @{ $input->{'columns'} }) {
			
			my $v;
			
			if (defined $order->{$k}) {
				if ($order->{$k} eq 'NOW()' || $order->{$k} eq 'CURDATE()') { # no quotes/escape required
					$v = $order->{$k};
				} else {
					$v = ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $order->{$k} });
				}
			} else {
				$v = 'NULL';
			}
				
			$sql .= ' '.$v.',';
		}
		
		chop($sql);
		$sql .= '),';
	}
	
	chop($sql);
	$sql .= ' ON DUPLICATE KEY UPDATE';
	
	my $q_req_status;
	
	if (defined $input->{'reqStatus'}) {
		$q_req_status = ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $input->{'reqStatus'} });
	}
	foreach my $k (List::Util::uniq @{ $input->{'columns'} }) {
		$sql .= ' '.$k.' = ';
		
		if (defined $input->{'reqStatus'}) {
			$sql .= 'IF((status = '.$q_req_status.'), VALUES('.$k.'), status),';
		} else {
			$sql .= 'VALUES('.$k.'),';
		}
	}
	
	chop($sql);
	
	my $update_success = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql });
	
	if (!$update_success) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Failed to update orders';
		return $result;
	}
	
	my $all_updated = 0;
	$all_updated = 1 if ($update_success == (3 * $num_orders)); # on duplicate key update = 3 per record
	
	if ($log_status_change) {
	
		my $updated_orders = [];
		
		if ($all_updated) {
			$updated_orders = $input->{'orders'};
		} else {
			# calculate which orders were updated
			
			my $orders_by_orderID = {};
			
			$sql = 'SELECT orderID, status FROM orders WHERE';
			
			foreach my $order (@{ $input->{'orders'} }) {
				$orders_by_orderID->{$order->{'orderID'}} = $order;
				
				$sql .= ' orderID = '.$order->{'orderID'}.' OR';
			}
			
			$sql = substr($sql, 0, -3);
			
			my $orders_status = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql });
			
			if ($orders_status) {
				foreach my $order_status (@{ $orders_status }) {
					my $order = $orders_by_orderID->{$order_status->{'orderID'}};
					
					push(@{ $updated_orders }, $order) if ($order->{'status'} eq $order_status->{'status'}); # status matches: it was updated
				}
			}
		}
		
		if (scalar(@{ $updated_orders })) {
			
			$sql = 'INSERT INTO orderstatus (orderID, user, ipaddress, timestamp, status, notes) VALUES';
			
			foreach my $order (@{ $updated_orders }) {
				$sql .= ' ('.$order->{'orderID'}
					. ', '.ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $input->{'user'} })
					. ', '.ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $input->{'ipaddress'} })
					. ', NOW()'
					. ', '.ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $order->{'status'} });
					
				if (defined $order->{'notes'} && $order->{'notes'}) {
					$sql .= ', '.ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $order->{'notes'} });
				} else {
					$sql .= ', NULL';
				}
				
				$sql .= '),';
				
				# return the updated orderIDs and their status
				push @{$result->{'updatedOrderIDs'}}, {
					'orderID'	=>	$order->{'orderID'},
					'status'	=>	$order->{'status'}
				};
			}
			
			chop($sql);
			
			my $log_success = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql });
			
			if (
				!$log_success
				|| $log_success != scalar(@{ $updated_orders })
			) {
				$result->{'success'} = 0;
				$result->{'error'} = 'Failed to log status updates';
				return $result;
			}
		}
	}
	
	if (!$all_updated) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Not all orders were updated';
		return $result;
	}
	
	return $result;
}


# input requirements:
# - orders (array of order hashes) (required)
#    - orderID (required)
#    - status (required)
# - user (required)
# - ipaddress (required)
sub markPickedOrders {
	my $self = shift;
	my $input = shift;

	my $result = {
		'success' => 1,
		'error' => undef,
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	foreach my $k ( qw(orders user ipaddress) ) {
		if (!defined $input->{$k} || !$input->{$k}) {
			$result->{'success'} = 0;
			$result->{'error'} = ucfirst($k).' not specified';
			return $result;
		}
	}
	
	my $num_orders = scalar(@{ $input->{'orders'} });
	
	if (!$num_orders) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No Orders specified to update';
		return $result;
	}
	
	$input->{'DBH'} ||= ECS::SQL::connect();
	$input->{'columns'} = [ qw(status) ];
	
	my $p = {
		'DBH' => $input->{'DBH'} ,
		'input' => $input,
	};
	
	my $response = API::Order::updateOrders(undef, $input);
	
	
	# check if some of the updated orders are OSOOB because the WIP needs updated accordingly
	if ( scalar(@{$response->{'updatedOrderIDs'}}) ) {
		
		my $pickedOsoobOrderIDs;
		
		# get all the Picked OSOOB orders that just got updated
		foreach my $order ( @{$response->{'updatedOrderIDs'}} ) {
			push @{$pickedOsoobOrderIDs}, $order->{'orderID'}  if($order->{'status'} eq 'Picked OSOOB');
		}
		
		if ( defined $pickedOsoobOrderIDs and scalar(@{$pickedOsoobOrderIDs}) ) {
			
			local $p->{'input'}->{'orderIDs'} = $pickedOsoobOrderIDs;
			local $p->{picking} = 1;
			my $individualOrderProducts = API::Order::Cart::getIndividualProducts($p);

			foreach my $orderID (keys %{$individualOrderProducts}) {
				# move 1 qty from WIP_OSOO to WIP_OSOOB
				# move the first product only since the Picked OSOOB should only have one item
				# if the OSOOB order has multiple items, 
				# it will be caught at the end of the day by the staff that checks the Picked OSOOB queue
				my $qtyMoved = ECS::Product::Inventory::WIP::adjustWipQty({
				'dbh'   =>	$p->{'DBH'},
				'info'  => {
					'productID'	=>	$individualOrderProducts->{$orderID}->[0]->{'productID'},
					'fromLoc'	=>	'osoo',
					'toLoc'		=>	'osoob',
					'qtyToMove'	=>	1
					}
				});	
			}		
		}		
	}
	
	if (!$response->{'success'}) {
		$result->{'success'} = 0;
		$result->{'error'} = $response->{'error'};
		return $result;
	}
	
	return $result;
}

# Not an API method
# Requires
# - $p->{'order'}
# - $p->{'notes'}
sub addNotes {
	my $p = shift;
	
	return 0 unless (
		$p->{'order'}
		and $p->{'notes'}
	);
	
	return ECS::SQL::insert({
		'DBH' => $p->{'DBH'},
		'table' => 'orderstatus',
		'columns' => [qw(orderID user ipaddress timestamp status notes)],
		'hash' => {
			'orderID' => $p->{'order'}->{'orderID'},
			'user' => $p->{'input'}->{'user'},
			'ipaddress' => $p->{'input'}->{'ipaddress'},
			'timestamp' => 'NOW()',
			'status' => 'Notes',
			'notes' => $p->{'notes'}
		}
	});
}

# Get counts of order statuses
# requires $input->{'statuses'} (array)
sub getStatusCounts {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	if (!defined $input->{'statuses'} || !$input->{'statuses'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No order statuses specified to count';
		return $result;
	}
	
	my $status_counts = {};
	$result->{'data'}->{'statusCounts'} = $status_counts;
	
	my $DBH = ECS::SQL::connect('SLAVE');
	
	my $sql = 'SELECT status, COUNT(*) AS count FROM orders WHERE';
	
	foreach my $status (@{ $input->{'statuses'} }) {
		$sql .= ' status = '.ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $status }).' OR';
		
		$status_counts->{$status} = 0;
	}
		
	$sql = substr($sql, 0, -3);
	$sql .= ' GROUP BY status';
	
	my $rd = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql, useSlave => 1 });
	
	return $result if (!$rd);
	
	foreach my $count (@{ $rd }) {
		$status_counts->{$count->{'status'}} = $count->{'count'};
	}
	
	return $result;
}

# Get counts of order statuses for today only
# requires $input->{'statuses'} (array)
sub getTodayStatusCounts {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'data' => {},
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	if (!defined $input->{'statuses'} || !$input->{'statuses'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'No order statuses specified to count';
		return $result;
	}
	
	my $status_counts = {};
	$result->{'data'}->{'statusCounts'} = $status_counts;
	
	my $DBH = ECS::SQL::connect('SLAVE');
	
	my $datetime = ECS::Time::epochToDatetime(time());
	my ($date, $time) = split(' ', $datetime);
	
	my $sql = "SELECT status, COUNT(distinct orderID) AS count FROM orderstatus WHERE timestamp > '".$date." 00:00:00' AND (";
	
	foreach my $status (@{ $input->{'statuses'} }) {
		$sql .= ' status = '.ECS::SQL::quote({ 'DBH' => $DBH, 'value' => $status }).' OR';
		
		$status_counts->{$status} = 0;
	}
		
	$sql = substr($sql, 0, -3);
	$sql .= ') GROUP BY status';
	
	my $rd = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql, useSlave => 1 });
	
	return $result if (!$rd);
	
	foreach my $count (@{ $rd }) {
		$status_counts->{$count->{'status'}} = $count->{'count'};
	}
	
	return $result;
}

# Not an API method
# Requires
# - order
# - reqStatus (lock the order only if it has this status)
sub lock {
	my $p = shift;
	
	my $sql = "UPDATE
            orders
        SET
            status = 'LOCKED'
        WHERE
            orderID = ".$p->{'order'}->{'orderID'};

    if(not ref $p->{'reqStatus'}) {
        # looking for a specific order status
        my $qReqStatus = ECS::SQL::quote({'DBH' => $p->{'DBH'}, 'value' => $p->{'reqStatus'}});
        $sql .= " AND status = $qReqStatus";
    }
    else {
        # reqStatus is a list -- check for orders in those statuses
        my @qReqStatus = ();
        for my $status (@{ $p->{'reqStatus'} }) {
            local $p->{'value'} = $status;
            push @qReqStatus, ECS::SQL::quote($p);
        }
        $sql .= " AND status IN (" . join(", ", @qReqStatus) . ")";
    }
	
	return ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => $sql });
}

# Not an API method
# Requires
# - order
sub mmLock {
	my $p = shift;
	
	my $sql = "UPDATE orders SET mmlock = 'YES' WHERE orderID = ".$p->{'order'}->{'orderID'}." AND mmlock != 'YES'";
	
	return ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => $sql });
}

# Marks all products on an order as re-weigh (productDimensions.dimSet = 0)
sub reweighProducts {
	my $self = shift;
	my $input = shift;
	
	my $result = {
		'success' => 1,
		'error' => undef,
		'update_DB'	=> $Config->{'update_DB'}
	};
	
	$input->{'orderID'} = (int($input->{'orderID'}) || 0);
	
	if (!$input->{'orderID'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order not specified';
		return $result;
	}
	
	my $p = {
		'DBH' => ECS::SQL::connect(),
		'input' => {
			'orderIDs' => [$input->{'orderID'}]
		}
	};
	
	my $individual_products = API::Order::Cart::getIndividualProducts($p);
	my $products = $individual_products->{$input->{'orderID'}};
	
	my @values = ();
	my $logValues;
	foreach my $product (@{ $products }) {
		push(@values, '('.$product->{'productID'}.',0)');
		$logValues->{$product->{'productID'}} = {
			'dimSet' => {'old'=>$product->{'dimSet'},'new'=>0 },
		};
	}
	
	my $query = 'INSERT INTO productDimension (productID, dimSet) VALUES '.join(',', @values).' ON DUPLICATE KEY UPDATE dimSet=VALUES(dimSet)';
	ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => $query });
	local $p->{'changes'} = $logValues;
	local $p->{'user'} = $p->{'info'}->{'employeeID'};
	ECS::Product::LogChange::logMultipleProductsChanges($p);
	
	ECS::SQL::query({ 'DBH' => $p->{'DBH'}, 'sql' => 'UPDATE orderShipping SET reweighProducts = 1 WHERE orderID = '.$input->{'orderID'} });
	
	return $result;
}

# Get the order status for the orderID
sub getOrderStatus {
	my $self = shift;
	my $input = shift;

	my $status = undef;
	my $result = {
		'success' => 1,
		'update_DB' => $Config->{'update_DB'},
		'error' => undef,
		'data' => {}
	};

	if(!$input->{'orderID'}) {
		$result->{'success'} = 0;
		$result->{'error'} = 'OrderID is missing from input';
		return $result;
	}

	my $DBH = ECS::SQL::connect('SLAVE');
	my $sql = 'SELECT status FROM orders WHERE orderID = '.$input->{'orderID'};
	my $queryResult = ECS::SQL::query({ 'DBH' => $DBH, 'sql' => $sql, useSlave => 1 });

	if($queryResult){
		$status = @{$queryResult}[0]->{'status'};
	}
	else {
		$result->{'success'} = 0;
		$result->{'error'} = 'Order not found.';
	}

	$result->{'data'}->{'orderStatus'} = $status;

	return $result;
}
