#!/usr/bin/perl -Ilibs

use strict;
use warnings;

use API::Controller ();
use ECS::ProductCategory::Attribute;
use ECS::ProductCategory;
use ECS::Site;
use ECS::dbConfig;

package API::CategoryAttributeAdmin;

use Moose;
use Data::Dumper;
use JSON;
extends 'API::Controller';

has 'site_dbh' => (
    'is'    => "rw",
    'isa'   => "DBI::db",
    'lazy' => 1,
	'builder' => "_build_site_dbh",
);
has 'site_dbhr' => (
    'is'    => "rw",
    'isa'   => "DBI::db",
    'lazy' => 1,
	'builder' => "_build_site_dbhr",
);

__PACKAGE__->meta->make_immutable;

sub _build_site_dbh {
    my $self = shift;
    return ECS::Site->fromName( $self->input->{site})->site_dbh;
}
sub _build_site_dbhr {
    my $self = shift;
    return ECS::Site->fromName( $self->input->{site})->site_dbhr;
}

sub viewCategory {
	my $self = (shift)->new({'input' => shift || {}});

    $self->data->{'templates'} = [ "category.html" ];

    return $self->result;
}

sub saveAttribute {
	my $self = (shift)->new({'input' => shift || {}});
	my $attribute = ECS::ProductCategory::Attribute->new({
		'dbh' => $self->site_dbh,
		($self->input->{attributeID}
			? (attributeID => $self->input->{attributeID})
			: ()
		),
	});

	my $originalSortOrder = ($attribute->has_attributeID) ? $attribute->sortOrder : 0;
	my $newSortOrder = $self->input->{sortOrder};
	my $attributeLogData;
	foreach my $col (keys %{$self->input}) {
		next if (grep {$_ eq $col} ('action', 'site', 'values', 'employeeID'));
		
		$attributeLogData->{$col}->{'oldValue'} = $attribute->has_attributeID ? $attribute->$col : '';
		$attributeLogData->{$col}->{'newValue'} = $self->input->{$col};
		
		$attribute->$col($self->input->{$col}) if ($self->input->{$col});
	}
	$attribute->save;
	$attributeLogData->{'attributeID'}->{'newValue'} = $attribute->attributeID if(!$attributeLogData->{'attributeID'}->{'newValue'});
	# save attibute Values
	if ($self->input->{'values'}) {
		my $newValues = decode_json($self->input->{'values'});
		$attributeLogData->{'value'}->{'oldValue'} = '';
		$attributeLogData->{'value'}->{'newValue'} = join ',', map{values %$_} @{$newValues};

		my $currentValues = $attribute->values;
		foreach my $value (@$currentValues) {
			$attributeLogData->{'value'}->{'oldValue'} .= $value->name . ',';
			if (! grep $_->{$value->attributeValueID}, @$newValues) {
				$value->delete;
			}
			elsif (! grep {$_->{$value->attributeValueID} && $_->{$value->attributeValueID} eq $value->name} @$newValues )
			{
				$value->name($newValues->{$value->attributeValueID});
				$value->save;
			}
		}
		
		foreach my $newVal (map {values %$_ } grep { grep {/new_/ } keys %$_ } @$newValues) 
		{
			my $value = ECS::ProductCategory::Attribute::Value->new({
				'dbh' => $self->site_dbh,
			});

			$value->attributeID($attribute->attributeID);
			$value->name($newVal);
			$value->save;
		}
	}
	$attributeLogData->{'value'}->{'oldValue'} =~ s/,$//;
	$self->logAttributeChanges($attributeLogData);
	
	if ($originalSortOrder != $newSortOrder) {
		$self->input->{originalSortOrder} = $originalSortOrder;
		$self->input->{newSortOrder} = $newSortOrder;
		if (! $originalSortOrder || ($newSortOrder < $originalSortOrder)) {
			$self->input->{sortOrderAction} = 'ADD';
		}
		else {
			$self->input->{sortOrderAction} = 'SUBTRACT';
		}
		$self->updateSortOrder($attribute);
	}
	
	$self->data->{'redirect'} = "./?categoryID=".$self->input->{productCategoryID}."&site=".$self->input->{site};
	
	return $self->result;
}

sub updateCategory {

	my $self = (shift)->new({'input' => shift || {}});
	my $logData;
	my $category = ECS::ProductCategory->new({
		'dbh' => $self->site_dbh,
		($self->input->{'productCategoryID'} ? 
			('productCategoryID' => $self->input->{'productCategoryID'})
		    :()
		),
	});
	my $wasEnabled = $category->has_productCategoryID ? $category->enabled : 'NO';
	my $originalSortOrder = $category->has_productCategoryID ? $category->sortOrder : 0;
	my $newSortOrder = $self->input->{sortOrder};
	
	foreach my $col (keys %{$self->input}) {
		next if (grep {$_ eq $col} ('action', 'site', 'employeeID'));
		$logData->{$col}->{'oldValue'} = $category->has_productCategoryID ? $category->$col : '';
		$logData->{$col}->{'newValue'} = $self->input->{$col};
		
		$category->$col($self->input->{$col}) if ($self->input->{$col} ne '');
	}
	
	$category->save;
	$logData->{'productCategoryID'}->{'newValue'} = $category->productCategoryID if(!$logData->{'productCategoryID'}->{'newValue'});
	
	$self->logProductCategoryChanges($logData);

	if( $wasEnabled eq 'NO' && $category->enabled eq 'YES' ) {
		my $parents = $category->parents;
		foreach my $parent ( @$parents ){
			$parent->enabled('YES');
			$parent->save;
		}
	}
		
	$self->disableChilds($category) if( $wasEnabled eq 'YES' && $category->enabled eq 'NO' );
	
	if ($originalSortOrder != $newSortOrder) {
		$self->input->{originalSortOrder} = $originalSortOrder;
		$self->input->{newSortOrder} = $newSortOrder;
		
		if (! $originalSortOrder || ($newSortOrder < $originalSortOrder)) {
			$self->input->{sortOrderAction} = 'ADD';
		}
		else {
			$self->input->{sortOrderAction} = 'SUBTRACT';
		}
		$self->updateSortOrder($category);
	}
	
	$self->data->{'redirect'} = ($self->input->{parentID} != 0 ) ? "./?categoryID=".$self->input->{parentID}."&site=".$self->input->{site} : "./?site=".$self->input->{site};
								
	return $self->result;
}

# update sortOrder of other categories/attributes.
sub updateSortOrder {
	my $self = shift;
	my $obj = shift;

	my %partQuery = (	
		ref $obj eq 'ECS::ProductCategory' ? 		
		(
			'parentID'  => $obj->parentID,
			'productCategoryID' => {'!=', $obj->productCategoryID},
		):
		(
			'productCategoryID' => $self->input->{productCategoryID},
			'attributeID' => {'!=', $obj->attributeID},
		)
	);
	
	#get all the categories/attributes affected by sortOrder change
	my $search = [{
		%partQuery,
		($self->input->{sortOrderAction} eq 'ADD'
			? (
				'sortOrder' => {'>', $self->input->{newSortOrder} - 1},
				(
					$self->input->{originalSortOrder}
					? ('SortOrder' => {'<', $self->input->{originalSortOrder}})
					: ()
				),
			)
			: (
				'sortOrder' => {'<', $self->input->{newSortOrder} + 1},
				'SortOrder' => {'>', $self->input->{originalSortOrder}},
			)
		),
	}];

	my $attributes = (ref $obj)->search({
		'dbh' => $self->site_dbh,
		'search' => $search,
	});

	foreach my $attribute (@$attributes) {
		if ($self->input->{sortOrderAction} eq 'ADD') {
			$attribute->sortOrder($attribute->sortOrder + 1);
		}
		else {
			$attribute->sortOrder($attribute->sortOrder - 1);
		}
		$attribute->save;
	}
}


sub disableChilds {
	my $self = shift;
	my $category = shift;
	my $childs = $category->childs;
	
	return if (!scalar @$childs);
	
	foreach my $child (@$childs) {
		$child->enabled('NO');
		$child->save;
		$self->disableChilds( $child );
	}
}

sub removeCategory {
	my $self = (shift)->new({'input' => shift || {}});

	if (! $self->input->{'productCategoryID'}) {
		$self->error('productCategoryID is missing.');
		return $self->result;
	}

	my $estorePrefix = $ECS::dbConfig::db{mysql}->{db};
	my $tmsPrefix = $ECS::dbConfig::db{tms}->{db};
	my $otherWebsitePrefix = ($self->input->{site} eq 'ECS')
		? $tmsPrefix
		: $estorePrefix;

	my $category = ECS::ProductCategory->new({
		'dbh' => $self->site_dbh, 
		'productCategoryID' => $self->input->{'productCategoryID'},
	});
	
	my $maxSortOrder;
	my $parents = $category->parents;
	$maxSortOrder = scalar @{$parents->[-1]->childs} if( scalar @$parents );
	
	if( !scalar @$parents ){
		$parents = ECS::ProductCategory->search({
			'dbh' => $self->site_dbh,
			'search' => [{'parentID' => 0}],
		});
		$maxSortOrder = scalar @$parents if( scalar @$parents);
	}
	
	if (scalar @$parents) {
		if ($maxSortOrder != $category->sortOrder) {
			local $self->input->{sortOrderAction} = 'SUBTRACT';
			local $self->input->{newSortOrder} = $maxSortOrder;
			local $self->input->{originalSortOrder} = $category->sortOrder;
			$self->updateSortOrder($category);
		}
	}

	my $applicatedProducts = $category->products;
	if (keys %$applicatedProducts) {
		my $matchingApplicationGroupSQL = "SELECT 
				DISTINCT productApplicationGroupID 
			FROM 
				$estorePrefix.productApplicationGroup
				JOIN productCategoryApplication USING (productApplicationGroupID)
			WHERE 
				productCategoryApplication.productCategoryID = ?";
		my $matchingApplicationGroups = $self->site_dbh->selectall_arrayref($matchingApplicationGroupSQL, { Slice => {} }, ($self->input->{'productCategoryID'}));

		my @currentCategoryAttributeValues = map {$_->attributeValueID} (map {@{$_->values}} @{$category->attributes});

		# delete productAttribute linked to parent category attributes if there is no other application of it parent in that group.
		my @parentCategoryAttributeValues = map {$_->attributeValueID} (map {@{$_->values}} @{$category->parentCategoryAttributes});
		my $parentCategoryApplicationSQL = "SELECT 
				DISTINCT productApplicationGroupID 
			FROM
				$estorePrefix.productApplicationGroup
				JOIN productCategoryApplication USING (productApplicationGroupID)
			WHERE 
				productCategoryApplication.productCategoryID IN (" .
				join(',', map {'?'} @{$category->parents})
				. ") AND productApplicationGroupID IN (" .
				join(',', map {'?'} @{$matchingApplicationGroups})
				. ")";

		my $matchingParentApplicationGroups = $self->site_dbh->selectall_arrayref($parentCategoryApplicationSQL, { Slice => {} }, ((map {$_->productCategoryID} @{$category->parents}), (map {$_->{'productApplicationGroupID'}} @{$matchingApplicationGroups})));
		my @nonParentChildApplications = grep {
			my $applicationGroupID = $_->{'productApplicationGroupID'};
			grep { $_->{'productApplicationGroupID'} != $applicationGroupID } @$matchingParentApplicationGroups;
		} @$matchingApplicationGroups;
		if (@nonParentChildApplications) {
			$self->site_dbh->do("DELETE
				FROM productAttribute
				WHERE
					attributeValueID IN (" .
					join(',', map {'?'} @parentCategoryAttributeValues)
					. ")
					AND productApplicationGroupID IN (" .
					join(',', map {'?'} @nonParentChildApplications)
					. ")",
				undef, (@parentCategoryAttributeValues, map {$_->{'productApplicationGroupID'}} @nonParentChildApplications));
		}

		# delete application group 
		# if (doesn't exists any vehicle application)
		# && (doesn't exists any other category application)
		# && (doesn't exists any other site category application)
		my $deleteApplicationGroupSQL = "
			DELETE $estorePrefix.productApplicationGroup
			FROM $estorePrefix.productApplicationGroup
			LEFT JOIN $estorePrefix.productVehicleApplicationGroup AS vehicleApps USING (productApplicationGroupID)
			LEFT JOIN (
				SELECT 
					productApplicationGroupID
				FROM 
					productCategoryApplication 
				WHERE 
					productCategoryID != ?
					AND productApplicationGroupID IN (" . join(',', map {'?'} @$matchingApplicationGroups) . ")
			) AS otherCategoryApps USING (productApplicationGroupID)
			LEFT JOIN $otherWebsitePrefix.productCategoryApplication AS otherSiteApps USING (productApplicationGroupID)
			WHERE
				vehicleApps.productApplicationGroupID IS NULL
				AND otherCategoryApps.productApplicationGroupID IS NULL
				AND otherSiteApps.productApplicationGroupID IS NULL
				AND productApplicationGroupID IN (" . join(',', map {'?'} @$matchingApplicationGroups) . ")";
		
		$self->site_dbh->do($deleteApplicationGroupSQL, undef, (
			$self->input->{'productCategoryID'},
			(map { $_->{'productApplicationGroupID'} } @$matchingApplicationGroups),
			(map { $_->{'productApplicationGroupID'} } @$matchingApplicationGroups),
		));

		# delete productCategoryApplication rows
		$self->site_dbh->do("DELETE FROM productCategoryApplication WHERE productCategoryID = ?", undef, ($self->input->{'productCategoryID'}));
	}

	# delete category, attributes and values
	$category->delete;

	$self->data->{'redirect'} = ($self->input->{parentID} != 0 ) ? "./?categoryID=".$self->input->{parentID}."&site=".$self->input->{site} : "./?site=".$self->input->{site};

	return $self->result;
}

sub getChildCategories {
	my $self = (shift)->new({'input' => shift || {}});

	$self->data->{'dataType'} = "json";

	my $category = ECS::ProductCategory->new({
		'dbh' => $self->site_dbh, 
		'productCategoryID' => $self->input->{'productCategoryID'},
	});

	my @childs = grep {$_->productCategoryID != $self->input->{'migratingCategory'}} @{$category->childs};

	$self->data->{'data'} = JSON->new->allow_blessed->convert_blessed->encode(\@childs);
	return $self->result;
}

sub migrateCategory {
	my $self = (shift)->new({'input' => shift || {}});

	if (! $self->input->{'productCategoryID'}) {
		$self->error('productCategoryID is missing.');
		return $self->result;
	}

	my $category = ECS::ProductCategory->new({
		'dbh' => $self->site_dbh, 
		'productCategoryID' => $self->input->{'productCategoryID'},
	});

	my @newParentCategories = split(',', $self->input->{'moveTo'});
	my $newParentID = $newParentCategories[$#newParentCategories];
	my $newParentCategory = ECS::ProductCategory->new({
		'dbh' => $self->site_dbh, 
		'productCategoryID' => $newParentID,
	});
	my $newSortOrder = scalar @{$newParentCategory->childs} + 1;

	my @parentAttributes = (@{$newParentCategory->attributes}, @{$newParentCategory->parentCategoryAttributes});
	my (@attributeToMerge, @nonMergingAttribute);
	foreach my $attribute (@{$category->attributes}) {
		my @alreadyExistingAttribute = grep { uc($_->name) eq uc($attribute->name) } @parentAttributes;
		if (scalar @alreadyExistingAttribute) {
			push @attributeToMerge, {
				'mergeTo' => $alreadyExistingAttribute[0],
				'mergeFrom' => $attribute,
			};
		}
		else {
			push @nonMergingAttribute, $attribute;
		}
	}

	if ($self->input->{'subaction'} eq 'move') {
		my $parents = $category->parents;
		my $maxSortOrder;
		if(scalar @$parents)
		{
			$maxSortOrder = scalar @{$parents->[-1]->childs};
		}
		else
		{
			$maxSortOrder = scalar @{ECS::ProductCategory->search({
				'dbh' => $self->site_dbh,
				'search' => [{'parentID' => 0}],
				})};
		}

		if ($maxSortOrder != $category->sortOrder) {
			local $self->input->{sortOrderAction} = 'SUBTRACT';
			local $self->input->{newSortOrder} = $maxSortOrder;
			local $self->input->{originalSortOrder} = $category->sortOrder;
			$self->updateSortOrder($category);
		}
		
		$category->parentID($newParentID);
		$category->sortOrder($newSortOrder);
		$category->save;
	}
	else {
		my $newCategory = ECS::ProductCategory->new({
			'dbh' => $self->site_dbh,
		});
		$newCategory->parentID($newParentID);
		$newCategory->name($category->name);
		$newCategory->description($category->description);
		$newCategory->enabled($category->enabled);
		$newCategory->sortOrder($newSortOrder);
		$newCategory->save;

		foreach my $attribute (@nonMergingAttribute) {
			my $newAttribute = ECS::ProductCategory::Attribute->new({
				'dbh' => $self->site_dbh,
			});
			$newAttribute->productCategoryID($newCategory->productCategoryID);
			$newAttribute->name($attribute->name);
			$newAttribute->sortOrder($attribute->sortOrder);
			$newAttribute->enabled($attribute->enabled);
			$newAttribute->save;

			foreach my $value (@{$attribute->values}) {
				my $newAttributeValue = ECS::ProductCategory::Attribute::Value->new({
					'dbh' => $self->site_dbh,
				});
				$newAttributeValue->attributeID($newAttribute->attributeID);
				$newAttributeValue->name($value->name);
				$newAttributeValue->save;

				foreach my $productAttribute (@{$value->productAttributes}) {
					my $newProductAttribute = ECS::ProductCategory::Attribute::ProductAttribute->new({
						'dbh' => $self->site_dbh,
					});
					$newProductAttribute->productApplicationGroupID($productAttribute->productApplicationGroupID);
					$newProductAttribute->attributeValueID($newAttributeValue->attributeValueID);
					$newProductAttribute->save;
				}
			}
		}

		# Copy rows into productCategoryApplication table
		$self->site_dbh->do("INSERT INTO productCategoryApplication (productCategoryID, productApplicationGroupID) SELECT ? AS productCategoryID, productApplicationGroupID FROM productCategoryApplication WHERE productCategoryID = ?", undef, ($newCategory->productCategoryID, $category->productCategoryID));
	}

	if (@attributeToMerge) {
		$self->mergeAttributes(\@attributeToMerge, $self->input->{'subaction'});
	}

	$self->data->{'redirect'} = ($self->input->{parentID} != 0 ) ? "./?categoryID=".$self->input->{parentID}."&site=".$self->input->{site} : "./?site=".$self->input->{site};

	return $self->result;
}

sub mergeAttributes {
	my $self = shift;
	my $attributesToMerge = shift;
	my $action = shift;

	foreach my $mergeData (@$attributesToMerge) {
		my $mergeTo = $mergeData->{mergeTo};
		my $mergeFrom = $mergeData->{mergeFrom};
		foreach my $mergeFromValue (@{$mergeFrom->values}) {
			my ($matchingValue) = grep {uc($_->name) eq uc($mergeFromValue->name)} @{$mergeTo->values};
			if ($matchingValue) {
				if ($action eq 'move') {
					foreach my $productAttribute (@{$mergeFromValue->productAttributes}) {
						$productAttribute->attributeValueID($matchingValue->attributeValueID);
						$productAttribute->save;
					}
				}
			}
			else {
				if ($action eq 'move') {
					$mergeFromValue->attributeID($mergeTo->attributeID);
					$mergeFromValue->save;
				}
				else {
					my $newAttributeValue = ECS::ProductCategory::Attribute::Value->new({
						'dbh' => $self->site_dbh,
					});
					$newAttributeValue->attributeID($mergeTo->attributeID);
					$newAttributeValue->name($mergeFromValue->name);
					$newAttributeValue->save;

					foreach my $productAttribute (@{$mergeFromValue->productAttributes}) {
						my $newProductAttribute = ECS::ProductCategory::Attribute::ProductAttribute->new({
							'dbh' => $self->site_dbh,
						});
						$newProductAttribute->productApplicationGroupID($productAttribute->productApplicationGroupID);
						$newProductAttribute->attributeValueID($newAttributeValue->attributeValueID);
						$newProductAttribute->save;
					}					
				}
			}
		}
		$mergeFrom->delete if ($action eq 'move');
	}
}

sub removeAttribute
{
	my $self = (shift)->new({'input' => shift || {}});

	if (! $self->input->{'attributeID'}) {
		$self->error('attributeID is missing.');
		return $self->result;
	}
	
	my $attribute = ECS::ProductCategory::Attribute->new({
		'dbh' => $self->site_dbh,
		($self->input->{attributeID}
			? (attributeID => $self->input->{attributeID})
			: ()
		),
	});
	
	#get all the attributes under this category.
	my $allAttributes = ECS::ProductCategory->new({
		'dbh' => $self->site_dbh, 
		'productCategoryID' => $self->input->{'productCategoryID'},
	})->attributes;
	
	my $maxSortOrder;
	$maxSortOrder = @{$allAttributes} if(@$allAttributes);
	
	if ($maxSortOrder != $attribute->sortOrder) 
	{
			local $self->input->{sortOrderAction} = 'SUBTRACT';
			local $self->input->{newSortOrder} = $maxSortOrder;
			local $self->input->{originalSortOrder} = $attribute->sortOrder;
			$self->updateSortOrder($attribute);
	}

	$attribute->delete;
	
	$self->data->{'redirect'} = "./?categoryID=".$self->input->{'productCategoryID'}."&site=".$self->input->{site};
	
	return $self->result;
}

sub logProductCategoryChanges
{
	my $self = shift;
	my $data = shift;
	
	my $employeeID = $self->input->{employeeID} || '';
	my $sql = qq{insert into productCategoryLog (employeeID, productCategoryID, field, oldValue, newValue) values };
	my @insertRows;
	foreach my $col(keys %{$data})
	{
		next if($data->{$col}->{'oldValue'} eq $data->{$col}->{'newValue'});
		my $newVal = ECS::SQL::quote({DBH=>$self->site_dbh, value=>$data->{$col}->{'newValue'}});
		my $oldVal = ECS::SQL::quote({DBH=>$self->site_dbh, value=>$data->{$col}->{'oldValue'}});
		my $insertTemp = qq[($employeeID , $data->{'productCategoryID'}->{'newValue'}, '$col', $oldVal, $newVal)];
		push @insertRows, ($insertTemp);
	}
	
	$sql = $sql . join ',', @insertRows;
	$self->site_dbh->do($sql , undef, ()) if(scalar @insertRows);
}

sub logAttributeChanges
{
	my $self = shift;
	my $data = shift;
	
	my $employeeID = $self->input->{employeeID} || '';
	my $sql = qq{insert into attributeLog (employeeID, attributeID, field, oldValue, newValue) values };
	my @insertRows;
	foreach my $col(keys %{$data})
	{
		next if($data->{$col}->{'oldValue'} eq $data->{$col}->{'newValue'});
		my $newVal = ECS::SQL::quote({DBH=>$self->site_dbh, value=>$data->{$col}->{'newValue'}});
		my $oldVal = ECS::SQL::quote({DBH=>$self->site_dbh, value=>$data->{$col}->{'oldValue'}});
		my $insertTemp = qq[($employeeID , $data->{'attributeID'}->{'newValue'}, '$col', $oldVal, $newVal)];
		push @insertRows, ($insertTemp);
	}
	
	$sql = $sql . join ',', @insertRows;
	$self->site_dbh->do($sql , undef, ()) if(scalar @insertRows);
}

1;
