#!/usr/bin/perl

use strict;
use warnings;

$| = 1;

package API::Product::Pricing;

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


use ECS::Product;
use ECS::Product::Pricing;
use ECS::Cache::ProductCache;
use POSIX;
use ECS::SQL;
use List::MoreUtils 'all';
use List::Util 'max';

my $c = \%ECS::Config::c;
my $markupMethods = { map {$_->{'name'} => $_->{'label'}} @ECS::Product::MARKUP_METHODS };

my ($p,$parts,$kits,$reports,@updatedProductIDs);
my ($countCost,$countRetailCut,$countRetail,$countBreakCostBlank,$countChangedKit) = (0,0,0,0,0);

my $lowCostProfitSplit = 35.5;

return 1;

###############################################################################
# **OLD NOTE** calc_price_retail should be integrated with calculate_retail in ECS::Product::Pricing . It is currently not finished and not being used, The ticket reference http://trac.ecstuning.local/env/programming/ticket/615
# 
# Calculates the retail values
# 
# REQUIRED
# -- $productHash (hash ref)
# 
# Hash keys used to calculate the retail price
# markup_method (productVendor.markup_method)
# cost (IFNULL(productVendor.price_costoverride, productStock.cost)) 
# target_markup (productVendor.target_markup)
# price_vendor_retail (productVendor.price_vendor_retail)
# price_retail (productVendor.price_retail)
#
#
sub calc_price_retail {
	my $p = shift;
	$p->{'cost'} = max($p->{'cost'}, $p->{'break_cost'}) if all {defined} ($p->{'cost'}, $p->{'break_cost'});
	
	if($p->{'markup_method'} eq "markup_percent") {
		for my $key ( qw(cost target_markup) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				return undef;
			}
		}
		return $p->{'cost'} * (1 + $p->{'target_markup'} / 100);
	
	}
	elsif($p->{'markup_method'} eq "markup_dollar") {
		for my $key ( qw(cost target_markup) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				return undef;
			}
		}
		return $p->{'cost'} + $p->{'target_markup'};
	
	}
	elsif($p->{'markup_method'} eq "markup_profitsplit") {
		for my $key ( qw(cost price_vendor_retail target_markup) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				return undef;
			}
		}
		return $p->{'cost'} + ( ($p->{'price_vendor_retail'} - $p->{'cost'}) * ($p->{'target_markup'} / 100) );
	
	}
	elsif($p->{'markup_method'} eq "markup_fixed") {
		my $return = 1;
		for my $key ( qw(price_retail) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				$return = 0;
			}
		}
		return $p->{'price_retail'} if $return;
		
		$return = 1;
		for my $key ( qw(cost target_markup) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				$return = 0;
			}
		}
		return $p->{'cost'} + $p->{'target_markup'} if $return;
		
		$return = 1;
		for my $key ( qw(price_vendor_retail) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				$return = 0;
			}
		}
		return $p->{'price_vendor_retail'} if $return;
		
		return undef;
		
	}
	elsif($p->{'markup_method'} eq "markdown_dollar") {
		for my $key ( qw(price_vendor_retail target_markup) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				return undef;
			}
		}
		return $p->{'price_vendor_retail'} - $p->{'target_markup'};
		
	}
	elsif($p->{'markup_method'} eq "markdown_percent") {
		for my $key ( qw(price_vendor_retail target_markup) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				return undef;
			}
		}
		return $p->{'price_vendor_retail'} * ( 1 - ( $p->{'target_markup'} / 100 ) );
		
	}
	elsif ($p->{'markup_method'} eq "markup_matchretail") {
		for my $key ( qw(price_retail) ) {
			if(not defined $p->{$key}
				or $p->{$key} == 0
				) {
				return undef;
			}
		}
		return $p->{'price_retail'};
		
	}
	
	return $p->{'price_retail'};
}

###############################################################################

sub get_cost(%) {

	my $p = shift;
	
	my $product = ECS::Product->new({
		'dbh'	   => $p->{'DBH'},
		'productID' => $p->{'hash'}->{'productID'},
	});
	
	return $product->productCost;
	
}

# cheap way of passing variables
sub getVar{
	my $var = shift;
	if($var eq 'countCost') { return $countCost; }
	if($var eq 'countRetailCut') { return $countRetailCut; }
	if($var eq 'countRetail') { return $countRetail; }
	if($var eq 'countBreakCostBlank') { return $countBreakCostBlank; }
	if($var eq 'countChangedKit') { return $countChangedKit; }
	return;
}

###############################################################################
# 
# Standardizes the format and changes string to decimals
# 
# REQUIRED
# -- $setPrice (bool)
# -- $setBreakCost (bool)
# -- $productHash (hash ref)
# 
# Hash keys used to update cost/price info
# markup_method (productVendor.markup_method)
# cost (IFNULL(productVendor.price_costoverride, productStock.cost)) 
# target_markup (productVendor.target_markup)
# price_vendor_retail (productVendor.price_vendor_retail)
# price_retail (productVendor.price_retail)
# break_cost (productVendor.break_cost) {optional}
# qtyRequired (productComponent.qtyRequired) {optional}
#
sub standardProductInfo {
	my $setPrice = shift;
	my $setBreakCost = shift;
	my $productHash = shift;
	
	# convert strings to numbers
	foreach my $key ( qw(price_vendor_retail price_retail target_markup break_cost cost qtyRequired price_sale target_sale) ) {
		$productHash->{$key} = 0 + $productHash->{$key} if $productHash->{$key};
	}
	
	#calculate the retail price
	if($setPrice) {
		$productHash->{'price_retail'} = calc_price_retail($productHash) || $productHash->{'price_retail'};
		$productHash->{'price_sale'} = $productHash->{'price_retail'} * (1 - ($productHash->{'target_sale'}/100)) if $productHash->{'target_sale'};
	}
	
	#if no break cost or not force to set break
	if($setBreakCost) {
		#if no break, try to set one
		#$productHash->{'break_cost'} ||= $productHash->{'cost'}; # break_cost needs better processing
		$productHash->{'break_cost_change'} = 100 * ( $productHash->{'cost'} - $productHash->{'break_cost'} ) / $productHash->{'break_cost'} if $productHash->{'break_cost'};
	}
	
	return $productHash;
}

###############################################################################
# 
# This will update the retail prices in the DB and send reports on the changes
# 
sub UpdatePrices {

	# initialize
	$p->{'DBH'} = ECS::SQL::connect();
    $p->{'DBHr'} = ECS::SQL::connect('SLAVE');
	$p->{'nolimit'} = 1;
	
	# step 1 - update all parts
	#partCostUpdate();
	
	# step 2 - update all kits
	# Skip step 2, this rule is no longer required
	# and changes too much
	# kitCostUpdate();
		
	# step 3 - send reports
	#costChangeReport();
	#retailChangeReport();
	#kitsReport();
	
	#sendReports();
	
	#ClearCache();
	$p->{'productIDs'} = \@updatedProductIDs;
	ECS::Cache::ProductCache::deleteProductInfo($p);
    ECS::Product::productModified({ 
        DBH      => $p->{DBH},
        products => \@updatedProductIDs,
    });
	
	return 1;
}


###############################################################################
# This placeholder is for refactoring the code out of the two price_update.cgi files
sub partCostUpdate {
	return 1;
}

sub getParts {
	return 1;
}

###############################################################################
# Updates the kits, currently only update the low cost components kits
sub kitCostUpdate {
		
	#For limiting to low cost components only
	my $limitToComponents = getLowCostComponents();
	
	#populate original values of kits
	$kits = getCurrentKits($limitToComponents);

	#get kits with no kit-components OR with kit-components that have already been updated
	getKitComponents();
	
	while (my ($productID, $kit) = each %$kits) {
		my $goNext = 0;

		while (my ($key, $component) = each %{ $kit->{'components'} } ) {
			#if limited to specific set of components and this component is not in that set
			if( $limitToComponents && !$limitToComponents->{$key}) {
				$goNext = 1;
				last;
			}
		}

		if($goNext) {
			$kits->{$productID} = undef;
			next;
		}

		# set new markup method
		$kit->{'_newValues'}->{'markup_method'} = "markup_matchretail";
		$kit->{'_newValues'}->{'groupPricing'} = "NO";
		# Kits must be updated before recursion, so apply any changes
		foreach my $key (keys %{ $kit->{'_newValues'} }) {
			if(defined($kit->{'_origValues'}->{$key}) && ($kit->{'_origValues'}->{$key} || '') ne ($kit->{'_newValues'}->{$key} || '')) {
				push @{$kit->{'_changes'}}, $key;
			}
		}
		
		# Update and log kit changes
		if(defined $kit->{'_changes'} && @{$kit->{'_changes'}}) {
			saveChanges($kit, 'mmsync - kits');
		} else {
			$kits->{$productID} = undef;
		}
		
	}
	
	foreach my $productID (keys %$kits) {
		delete $kits->{$productID} unless $kits->{$productID}; 
	}
	
	$countChangedKit = keys %$kits;

	return $kits;
}

# returns a hash of components that are a profit split of 25.5 using componentProductID as key
sub getLowCostComponents {
	$p->{'sql'} = "
SELECT DISTINCT 
	productComponent.componentProductID
FROM
	productComponent
INNER JOIN product ON product.productID = productComponent.componentProductID
INNER JOIN productVendor ON productVendor.productID = productComponent.componentProductID
WHERE
	(
	productVendor.markup_method = 'markup_profitsplit'
	AND productVendor.target_markup = 25.5
	AND product.nla != 'YES'
	)
";
	local $p->{useSlave} = 1;
	local $p->{DBHr} = ECS::Site::fromName(ECS)->estore_dbhr;

	my %results = map { $_->{'componentProductID'} => 1; } @{ECS::SQL::query($p) || []};
	return \%results;
}

# returns a hash of the current kits/assemblies in the DB
sub getCurrentKits {
	my $limitComponentIDs = shift;
	
	#get original values
	$p->{'sql'} = "
SELECT DISTINCT 
	productVendor.productID, 
	UPPER(product.partnumber) as partnumber, 
	product.description, 
	productVendor.markup_method, 
	product.groupPricing, 
	productVendor.vendorID
FROM productVendor 
	JOIN productStock USING (productID) 
	JOIN productComponent USING (productID) 
	JOIN product USING (productID) 
	LEFT JOIN vendor USING (vendorID) 
WHERE product.nla != 'YES' 
	AND product.type = 'Kit'
";
if(keys %$limitComponentIDs) {
	$p->{'sql'} .= '
	AND productComponent.componentProductID IN ('.join(',',keys %$limitComponentIDs).')
'
}

	my $originalValues = ECS::SQL::query($p) || [];
	my $currentKits;
	foreach my $kit (@$originalValues) {
		$currentKits->{$kit->{'productID'}}->{'_origValues'} = $kit;
	}
	
	return $currentKits;
}

# builds out the kits hash
sub getKitComponents{
	my $return;

    local $p->{useSlave} = $p->{useSlave};
    $p->{useSlave} = 1;
	# get all components that are not kits OR are in the limiter (to allow kits to be added)
	$p->{'sql'} = "
SELECT 
	productComponent.productID AS kitID, 
	productComponent.componentProductID AS componentID, 
	IFNULL(product.nla, 'NO') AS nla,
	IFNULL(productVendor.currentVendor, 'NO') AS currentVendor
FROM productComponent
	JOIN productVendor ON productVendor.productID = productComponent.componentProductID
	JOIN product ON product.productID = productComponent.componentProductID
	LEFT JOIN productStock ON productStock.productID = productComponent.componentProductID
	LEFT JOIN vendor USING (vendorID)
WHERE 
	productComponent.productID IN (".join(',', keys %$kits).")
";
	my $components = ECS::SQL::query($p) || [];
	
	for my $component (@$components) {
		
		# if a component is no longer available, then don't update the kit and remove it from the possible updates
		if($component->{'nla'} eq 'YES') {
			delete $kits->{$component->{'kitID'}};
			next;
		}
		
		# if a component has multiple vendors, use the first OR overwrite with the current one
		if($component->{'currentVendor'} ne 'YES' && ($kits->{$component->{'kitID'}}->{'components'}->{$component->{'componentID'}}||0)) {
			next;
		}
		
		$component = standardProductInfo(0,1,$component);
		
		$kits->{$component->{'kitID'}}->{'components'}->{$component->{'componentID'}} = $component;
		
		# keep track of built but unupdated kits
		$return->{$component->{'kitID'}} = 1;
		
	}
	
	return $return;
}

###############################################################################
# 
# saves & logs changes
# 
# REQUIRED
# -- $productHash (hash) the product info, must have:
#		$productHash->{'_origValues'}
#		$productHash->{'_newValues'}
#		$productHash->{'_changes'}
# -- $changeAgent (string) default is 'mmsync'
# 
sub saveChanges {
	my $productHash = shift;
	my $changeAgent = shift;
	$changeAgent ||= "mmsync";
	
	# fields used for updating data
	# key = alias used
	# value = field name
	my %updatableFields = (
		'productVendor' => {
			'where' => [qw(productID vendorID)],
			'update' => {
				'markup_method' => 'markup_method',
				'target_markup' => 'target_markup',
				'price_retail' => 'price_retail',
				'price_vendor_retail' => 'price_vendor_retail',
				'price_sale' => 'price_sale',
				'break_cost' => 'break_cost',
				'cost' => 'price_costoverride',
			},
		},
		'product' => {
			'where' => [qw(productID)],
			'update' => {
				'groupPricing' => 'groupPricing'
			}
		},
	);
	
	my $query;
	
	foreach my $table (keys %updatableFields) {
	
		my @fields = grep {defined $_} map { $_ if $updatableFields{$table}->{'update'}->{$_} } @{ $productHash->{'_changes'} };
		
		my @values = map { $productHash->{'_newValues'}->{$_} } @fields;
		
		if(!@fields) {
			next;
		}
		$query = 
			'UPDATE '.$table.' SET ' . join(', ', map { $updatableFields{$table}->{'update'}->{$_}.'=?' } @fields )
			.' WHERE '.join(' AND ', map {
				if ($productHash->{'_origValues'}->{$_}) {
					$_." = ".$productHash->{'_origValues'}->{$_}
				} else {
					'('.$_.' IS NULL OR '.$_.' = 0)'
				}  
			} @{$updatableFields{$table}->{'where'}});
		
		my $updateQuery = $p->{'DBH'}->prepare($query);
		$updateQuery->execute(@values);
		
		my @logParameters;
		my @logValues;
	
		if($p->{'DBH'}->errstr) {
			#some sort of error handling
		} else {
			foreach my $field (@{ $productHash->{'_changes'} }) {
				if(!$updatableFields{$table}->{'update'}->{$field}) {
					next;
				}
				
				if ($table eq 'productVendor') {
					push(@logParameters, "\n(?, ?, NOW(), ?, ?, ?, ?)");
					push(@logValues, (
						$productHash->{'_origValues'}->{'productID'},
						($productHash->{'_origValues'}->{'vendorID'} || 0),
						$changeAgent,
						$updatableFields{$table}->{'update'}->{$field},
						$productHash->{'_origValues'}->{$field},
						$productHash->{'_newValues'}->{$field}
					));
				} else {
					push(@logParameters, "\n(?, NOW(), ?, ?, ?, ?)");
					push(
						@logValues,
						$productHash->{'_origValues'}->{'productID'},
						$changeAgent,
						$updatableFields{$table}->{'update'}->{$field},
						$productHash->{'_origValues'}->{$field},
						$productHash->{'_newValues'}->{$field}
					);
				}
			}
		}
		
		# If there are no changes to log, skip changelog inserts
		next unless @logValues;
		if ($table eq 'productVendor') {
			$query =
				'INSERT INTO productVendorLog'
				.' (productID, vendorID, modifyDate, ipAddress, field, valueOld, valueNew)'
				.' VALUES '
				.join(',', @logParameters);
		} else {
			$query =
				'INSERT INTO changelog'
				.' (productID, modifydate, ipaddr, field, value_old, value_new)'
				.' VALUES '
				.join(',', @logParameters);
		}
        
		
		my $logQuery = $p->{'DBH'}->prepare($query);
		
		$logQuery->execute(@logValues);
		
	}
	
	return 1;
}


###############################################################################
# REPORTS

sub kitsReport {
	
	my @updateableFields = qw(markup_method groupPricing);
	my $fields = {
		'markup_method' => 'Retail Calc Type',
		'groupPricing' => 'Dealer Pricing',
	};
	my @kitColumns = ('ES #', 'Part #', 'Description');

	my $kitHeader = '<tr><th colspan="'.(2 + @kitColumns).'">Kit Info</th>';
	map { 
		$kitHeader .= '<th colspan="2">'.$fields->{$_}.'</th>' if $fields->{$_}
	} @updateableFields;
	$kitHeader .= "</tr>\n<tr>";
	map { 
		$kitHeader .= '<th'.($_ eq 'Description' ? ' colspan="3"' : '').'>'.$_.'</th>'
	} @kitColumns;
	map{
		$kitHeader .= '<th>OLD</th><th>NEW</th>'
	} @updateableFields;
	$kitHeader .= "</tr>\n";
	
	my ($report,$significantRetailReport);
	# kit header is not repeated because components are not listed on the first run
	$report = $kitHeader;
	
	my $reportCounter = 0;
	$kits ||= {};
	while (my ($productID, $kit) = each %$kits ) {
		
		if(!$kit->{'_changes'}) {
			next;
		}
		
	 	$report .= '<tr>';
		$report .= '<td><a href="'.$c->{'product_admin'}.'/?action=editProduct&productID='.$productID.'">ES'.$productID."</a></td>";
		$report .= '<td><a href="http://'.$c->{'homeURL'}."/ES".$productID.'">'.$kit->{'_origValues'}->{'partnumber'}."</a>\n";
		$report .= '<td colspan = "3">'.($kit->{'_origValues'}->{'description'}||'')."</td>";
		foreach my $field (@updateableFields) {
			my $isChanged = ($kit->{'_origValues'}->{$field}||'') ne ($kit->{'_newValues'}->{$field}||'');
			$report .= '<td align="center" nowrap>'.(($markupMethods->{($kit->{'_origValues'}->{$field}||'')}||$kit->{'_origValues'}->{$field})||'')."</td>";
			$report .= '<td align="center" nowrap bgcolor="'.($isChanged?"ccccff":"").'">'.(($markupMethods->{($kit->{'_newValues'}->{$field}||'')}||$kit->{'_newValues'}->{$field})||'')."</td>";
		}
		$report .= $kitHeader if !ceil(++$reportCounter % 20);
	}
	
	my $return = '';
	$return .= "<b>Updated Kits</b><br>\n";
	$return .= '<table border="1">'."\n".$report."</table>\n<br><br>\n";
	
	#print $return;
	return $return;
}

# to be updated later as a way to build and send all the reports at once. Ticket 1366
sub sendReports {
	my $reports = shift;
	

	my $msg = $reports->{'costChange'} . $reports->{'retailChange'} . $reports->{'kitChange'};
	$msg .= $reports->{'total'};
	
	my $obj = new SendMail('127.0.0.1') || &html_error("Could not create email object.");
	$obj->setDebug($obj->OFF);
	$obj->From("cron11profitprotect\@ecstuning.com");
	$obj->setMailHeader("Content-type","text/html");
	$obj->Subject("Automated Pricing Report (".time2str('%m/%d/%Y %r', time).")");
	
	if ($c->{'sandbox'}) {
		$obj->To($c->{'sandbox_email'});
	} else {
		$obj->To("cron11profitprotect\@ecstuning.com");
		#$obj->To("lnicolanti\@ecstuning.com");
		#$obj->To("aharper\@ecstuning.com");
		#$obj->To("mfoote\@ecstuning.com");
	}
	
	$obj->ErrorsTo("aharper\@ecstuning.com");
	$obj->setMailBody($msg);
	#$obj->sendMail();
	
	print $msg;
	
}



