#!/usr/bin/perl -Ilibs

package API::ProductCategoryAdmin; 

use strict;
use warnings;
use namespace::autoclean;

use API::Controller;

# website libs
use ECS::Product;
use ECS::SQL;

use Carp;
use Moose;
use Scalar::Util;
use Try::Tiny;
use JSON::XS;

extends 'API::Controller';

__PACKAGE__->meta->make_immutable;

return 1;

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

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

    $self->data->{'templates'} = [ "index.html" ];
		
		my $DBH = ECS::SQL::connect('SLAVE');
		my $categorySQL = q'SELECT categoryInfo.categoryInfoID AS id, categoryInfo.categoryName as name from categoryInfo ORDER BY categoryName ASC';
		my $categories = ECS::SQL::query({ 
			DBH	=> $DBH,
			sql	=> $categorySQL
		});
		$categories ||= [];
		@{ $_ }{qw/selectedFrom selectedTo/} = ( (JSON::XS::false)x2 );
		$self->data->{categories} = $categories;
		$self->data->{'data'}->{'success'} = 1;

	
    return $self->result;
}

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

		my $categoryName = $self->input->{newCategory};
		unless ($categoryName) {
			$self->error("Couldn't add category $categoryName");
		} else {
			my $DBH = ECS::SQL::connect();
			my $input = $self->input;
			my $categoryInfoID = ECS::SQL::insert({
				'DBH' => $DBH,
				'table' => 'categoryInfo',
				'columns' => [qw(categoryName)],
				'hash' => {
					categoryName => $categoryName,
				}
			});
			unless ($categoryInfoID) {
				$self->error("Couldn't add category $categoryName");
			} else {
				my $categories = $input->{categories};
				$_->{selectedTo} = 0 for @$categories;
				push @$categories, { id => $categoryInfoID, name => $categoryName, selectedFrom => 0, selectedTo => 1 };
				$self->data->{data}{categories} = $categories;
				$self->data->{categoryName} = $categoryName;
				$self->data->{products} = $self->input->{products};
				$self->data->{disabled} = $self->input->{disabled};
				$self->data->{'dataType'} = "json";
				$self->data->{'data'}{'success'} = 1;
			}
		}
		
    return $self->result;
		
}
###############################################################################
sub delCategory {
    my $self = (shift)->new({'input' => shift || {}});
		my $input = $self->input;

		unless ($input->{categories} && ref $input->{categories} && @{ $input->{categories} }) {
			$self->error("Couldn't delete categories");
		} else {
			my $dbh = ECS::SQL::connect();
			my $categories = $input->{categories};
			my @delCategoryIDs = map {
				$_->{id} 
			} grep {
				$_->{selectedFrom}
			} @$categories;
			unless (@delCategoryIDs) {
				$self->error("Couldn't delete categories");
			} else {	
				my $sqlFormat = 'DELETE FROM categoryInfo WHERE categoryInfoID IN (%s)';
				my @binds = @delCategoryIDs;
				my $sql = sprintf $sqlFormat => join ',' => ('?')x@binds;
				my $rs = $dbh->prepare( $sql );
				my $result = eval {
					$rs->execute( @binds );
					1;
				};
				unless ($result) {
					$self->error("Couldn't delete categories");
				} else {
					my $categories = [ grep { ! $_->{selectedFrom} } @$categories ];
					$self->data->{data}{categories} = $categories;
					$self->data->{products} = [];
					$self->data->{disabled} = $self->input->{disabled};
					$self->data->{'dataType'} = "json";
					$self->data->{'data'}{'success'} = 1;
				}
			}
		}
		
    return $self->result;
		
}
###############################################################################
sub loadCategoryProducts {
    my $self = (shift)->new({'input' => shift || {}});

		my $DBH = ECS::SQL::connect('SLAVE');

		my $input = $self->input;
		my $sqlFormat = q'SELECT productID, partnumber, description, categoryInfo.categoryName as category FROM
												product JOIN productCategoryInfo USING (productID) JOIN categoryInfo USING (categoryInfoID)
												WHERE categoryInfoID IN (%s) ORDER BY categoryInfo.categoryName, productID';
		my @ids = map {
				0 + ($_->{ id }||0) 
		}  grep {
			$_->{selectedFrom} 
		} @{ $input->{categories} || [] };
		my $oldProducts = $input->{products}||[];
		my %oldSelected = map { ($_->{productID} => $_->{selected}) } @$oldProducts;
		my $products = [];
		if (@ids) {
			my $productSQL = sprintf $sqlFormat => (join ',' => @ids);
			$products = ECS::SQL::query({ 
				DBH	=> $DBH,
				sql	=> $productSQL,
			});
		}
		for my $product (@$products) {
			if (defined $oldSelected{ $product->{productID } }) {
				$product->{selected} = $oldSelected{ $product->{productID} };
			} else {
				$product->{selected} = 1;
			}
		}
		$self->data->{data}{categories} = $input->{categories};
		$self->data->{data}{products} = $products;
		$self->data->{data}{disabled} = 0;
		$self->data->{'dataType'} = "json";
		$self->data->{'data'}{'success'} = 1;
		
    return $self->result;
}

# not an API method
sub _moveProducts {
	my $self = shift;
	
	my $dbh = ECS::SQL::connect();
	my ($fromCategoryIDs, $toCategoryID, @productIDs) = @_;
	my $sqlFormat;
	my @binds;
	if ($toCategoryID) {
		$sqlFormat = 'INSERT INTO productCategoryInfo ( productID, categoryInfoID ) 
						VALUES (%s) ON DUPLICATE KEY UPDATE categoryInfoID  = VALUES( categoryInfoID )';
		@binds = map { ( $_, $toCategoryID ) } @productIDs;
	} else {
		$sqlFormat = 'DELETE FROM productCategoryInfo WHERE productID IN (%s)';
		@binds = @productIDs;
	}
	my $sql = sprintf $sqlFormat => join ',' => ('?')x@binds;
	my $rs = $dbh->prepare( $sql );
	my $selectFormat = 'SELECT productID, categoryInfoID FROM productCategoryInfo WHERE productID IN (%s)';
	my $selectSQL = sprintf $selectFormat => join ',' => ('?')x@productIDs;
	my $selectRS = $dbh->prepare( $selectSQL );
	$selectRS->execute( @productIDs );
	
	for my $product (@{ $selectRS->fetchall_arrayref( )}) {
		my ($productID, $old) = @{ $product };
		my $new = $toCategoryID;
		ECS::Product::LogChange::logMiscChange({
			dbh					=> $dbh,
			productID		=> $productID,
			employeeID	=> $self->input->{employeeID},
			field				=> 'categoryInfoID',
			old					=> $old,
			new					=> $new,
		});
	}
	$rs->execute( @binds );
	return 1;
	
}

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

		my $input = $self->input;
		my $products = $input->{products};
		my $categories = $input->{categories};
		
		my $selectedProducts = [ grep { $_->{selected} } @$products ];
		my $selectedCategories = [ grep { $_->{selectedFrom} } @$categories ];

		if (scalar @$selectedCategories && scalar @$selectedProducts) {
			my @productIDs = 	map {
				$_->{productID}
			} @$selectedProducts;
			my @fromCategoryIDs = map { $_->{id} } @$selectedCategories;
			if ($self->_moveProducts( \@fromCategoryIDs, undef, @productIDs )) {
				$products = [ grep { !$_->{selected} } @$products ];
			}
		}
		$self->data->{data}{categories} = $categories;
		$self->data->{data}{products} = $products;
		$self->data->{data}{disabled} = 0;
		$self->data->{'dataType'} = "json";
		$self->data->{'data'}{'success'} = 1;
		
    return $self->result;
}
		
sub changeCategories {
    my $self = (shift)->new({'input' => shift || {}});

		my $input = $self->input;
		my $products = $input->{products};
		my $categories = $input->{categories};
		
		my $selectedProducts = [ grep { $_->{selected} } @$products ];
		my $selectedCategories = [ grep { $_->{selectedFrom} } @$categories ];
		my ($toCategory) =  grep { $_->{selectedTo} } @$categories;

		if (scalar @$selectedCategories && scalar @$selectedProducts && $toCategory) {
			my @productIDs = 	map {
				$_->{productID}
			} @$selectedProducts;
			my @fromCategoryIDs = map { $_->{id} } @$selectedCategories;
			my $toCategoryID = $toCategory->{id};
			if ($self->_moveProducts( \@fromCategoryIDs, $toCategoryID, @productIDs )) {
				$products = [ grep { !$_->{selected} } @$products ];
			}
		}
		$self->data->{data}{categories} = $categories;
		$self->data->{data}{products} = $products;
		$self->data->{data}{disabled} = 0;
		$self->data->{'dataType'} = "json";
		$self->data->{'data'}{'success'} = 1;
		
    return $self->result;
}
		
