#!/usr/bin/perl -Ilibs

use strict;
use warnings;

use API::Controller ();

package API::ApplicatorAdmin;

use Moose;
use JSON;
use ECS::SQL;
use ECS::Site;
use ECS::Product;
use Try::Tiny;

extends 'API::Controller';

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

	my $savedApps = decode_json($self->input->{'saveData'});
	my $dbHandles = {
		'ECS' => ECS::SQL::connect(),
		'TMS' => ECS::SQL::connect_tms(),
	};

	$dbHandles->{'ECS'}->{RaiseError} = 1;
	$dbHandles->{'TMS'}->{RaiseError} = 1;
	
	my $new_configs = add_vehicle_configs({
        'dbh'       => $dbHandles->{'ECS'},
        'configs'   => $savedApps->{matchingApps},
    });
	
	try {		
	
		$dbHandles->{'ECS'}->begin_work;
		$dbHandles->{'TMS'}->begin_work;
		
		my $productID = $self->input->{productID};		
		deleteFilterData($dbHandles, $self->input->{productID});
		
		foreach my $site ( ECS::Site->getAll() ){
			set_applications({
				'dbh' => $dbHandles->{$site->name},
				'newApps' => [grep {$_->{'website'} eq $site->name} @$new_configs],
				'productID' => $productID,
			});
		}

		setWorldPacModified($productID, $dbHandles->{'ECS'});

		foreach my $group ( @{$savedApps->{'filters'}} ){
			# Skip group with no category and vehicle apps
			next if (!scalar @{$group->{'vehicleApps'}} && !scalar keys %{$group->{'categoryApps'}});

			my $groupID = insertGroup($dbHandles->{'ECS'}, $productID);
			insertVehicleApps($dbHandles->{'ECS'}, $groupID, $group->{'vehicleApps'});
			insertCategoryApps($dbHandles, $groupID, $group->{'categoryApps'});
			insertProductAttributes($dbHandles, $groupID, $group->{'productAttributes'});
		}		
		
	} catch {
		$dbHandles->{'ECS'}->rollback();
		$dbHandles->{'TMS'}->rollback();
		$self->error("failed to update data");	
	};
	
	unless ( $self->error ){ ##as we can't return from catch block
		$dbHandles->{'ECS'}->commit();
		$dbHandles->{'TMS'}->commit();
	}

	return $self->result;
}

sub insertGroup {

	my ($dbh, $productID) = @_;
	my $productApplicationGroup = $dbh->do("Insert into productApplicationGroup (productID) values(?)", {}, $productID);
	my $groupID = $dbh->{mysql_insertid};	
	return $groupID;
}

sub insertVehicleApps {

	my ($dbh, $groupID, $vehicleApps) = @_;
	my $productVehicleApplicationGroupInsert = $dbh->prepare("Insert into productVehicleApplicationGroup (productApplicationGroupID) values (?)");
	
	my $productVehicleApplicationInsert = $dbh->prepare("Insert into productVehicleApplication (productVehicleApplicationGroupID, vehicleConfigID) values (?,?)");
	
	my $vehicleMapping = {
		'make'     => 0,
		'year'     => 0,
		'bodyCode' => 0,
		'model'    => 0,
		'subModel' => 0,
		'bodyType' => 0,
		'engine'   => 0
	};

	foreach my $vehicleRow ( @$vehicleApps ) {
	
		$productVehicleApplicationGroupInsert->execute($groupID);
		my $vehicleRowID = $productVehicleApplicationGroupInsert->{mysql_insertid};
		my $vehicles = {};
		foreach my $row ( @$vehicleRow ){
			next if(defined $row->{'productVehicleApplicationGroupID'});
			
			my @keysToDelete = ();
			
			foreach my $val (@{$row->{'values'}}){
				if ( defined $row->{mapping}->{$val} ){
					push @keysToDelete, $row->{mapping}->{$val} if($val ne $row->{mapping}->{$val});
					if( ! defined $vehicles->{$row->{mapping}->{$val}}->{$row->{option}}) {
						$vehicles->{$row->{mapping}->{$val}}->{$row->{option}} = $val;
						$vehicles->{$val} = $vehicles->{$row->{mapping}->{$val}};
					} else {
						my %copy = %{$vehicles->{$row->{mapping}->{$val}}};
						$copy{$row->{option}} = $val;
						$vehicles->{$val} = \%copy;
					}					
				} else {
					$vehicles->{$val}->{$row->{option}} = $val;
				}				
			}
			foreach my $key(@keysToDelete){
				delete $vehicles->{$key};
			}
		}
		
		my @vehicleConfigIDs = ();
		foreach my $k ( keys %$vehicles ){
			my $vehicleConfigID = isVehicleExists( $dbh, $vehicles->{$k} );
			$vehicleConfigID = insertVehicle( $dbh, $vehicles->{$k} ) if ( !$vehicleConfigID );
			push @vehicleConfigIDs, $vehicleConfigID;
		}

		if (! scalar keys %$vehicles) {
			push @vehicleConfigIDs, 0;
		}
		
		foreach my $vehicleConfigID ( @vehicleConfigIDs ){
			$productVehicleApplicationInsert->execute($vehicleRowID, $vehicleConfigID );
		}
		$vehicleMapping = {map { $_ => 0} keys %$vehicleMapping};
	}

	if (! scalar @$vehicleApps) {
		$productVehicleApplicationGroupInsert->execute($groupID);
		my $vehicleRowID = $productVehicleApplicationGroupInsert->{mysql_insertid};
		$productVehicleApplicationInsert->execute($vehicleRowID, undef );
	}
}

sub insertVehicle {

	my ( $dbh, $vehicle ) = @_;
	my $query = "insert into
            vehicleConfig
        set
            detailVehicle = 0,
            yearID = ?,
            makeID = ?,
            modelID = ?,
            subModelID = ?,
            bodyTypeID = ?,
            bodyCodeID = ?,
            engineID = ?";
    my $add_config = $dbh->prepare($query);
	$add_config->execute(
		$vehicle->{'year'} || 0,
		$vehicle->{'make'} || 0,
		$vehicle->{'model'} || 0,
		$vehicle->{'subModel'} || 0,
		$vehicle->{'bodyType'} || 0,
		$vehicle->{'bodyCode'} || 0,
		$vehicle->{'engine'} || 0,
	);
	my $vehicleConfigID = $dbh->{'mysql_insertid'} || 0;
	return $vehicleConfigID;
}
sub isVehicleExists {

	my ( $dbh, $vehicle ) = @_;
	
	my $query = "select
            vehicleConfig.vehicleConfigID
        from
            vehicleConfig
        where
            vehicleConfig.yearID = ?
            and vehicleConfig.makeID = ?
            and vehicleConfig.modelID = ?
            and vehicleConfig.subModelID = ?
            and vehicleConfig.bodyTypeID = ?
            and vehicleConfig.bodyCodeID = ?
            and vehicleConfig.engineID = ?
        limit 1";
	my $check_config = $dbh->prepare($query);
	
	$check_config->execute(
		$vehicle->{'year'}||0,
		$vehicle->{'make'}||0,
		$vehicle->{'model'}||0,
		$vehicle->{'subModel'}||0,
		$vehicle->{'bodyType'}||0,
		$vehicle->{'bodyCode'}||0,
		$vehicle->{'engine'}||0,
	);
	my $vehicleConfigID = $check_config->fetchrow_array();
	return $vehicleConfigID;
}

sub insertCategoryApps {

	my ( $dbHandles, $groupID, $categoryApps ) = @_;
	
	foreach my $site ( ECS::Site->getAll() ){
		next if( exists $categoryApps->{$site->name} && scalar @{$categoryApps->{$site->name}} == 0 );
		
		my $productCategoryApplicationInsert = $dbHandles->{$site->name}->prepare("Insert into productCategoryApplication (productApplicationGroupID, productCategoryID) values ( ? , ? )");
		
		my $i = 0;
		foreach my $categoryRow ( @{$categoryApps->{$site->name}} ){
			foreach my $row ( @$categoryRow ){
				if ( $row->{'value'} ){ $i++; };
			}

			my $categoryID = $categoryRow->[$i-1]->{'value'};
			$productCategoryApplicationInsert->execute($groupID, $categoryID);
			$i = 0;
		}		
	}
}

sub insertProductAttributes {
	my ( $dbHandles, $groupID,  $productAttributes) = @_;

	foreach my $site ( ECS::Site->getAll() ) {
		next if( scalar @{$productAttributes->{$site->name}} == 0 );
		my @sqlClause;
		my @bindVars;
		foreach my $attribute (@{$productAttributes->{$site->name}}) {
			push @sqlClause, '(? , ?)';
			push @bindVars, ($groupID, $attribute);
		}

		my $sql = "INSERT INTO productAttribute (productApplicationGroupID, attributeValueID) VALUES " . join(', ', @sqlClause);
		$dbHandles->{$site->name}->do($sql, undef, @bindVars);
	}
}

sub add_vehicle_configs {
	my $p = shift;

	my $query = "insert into
            vehicleConfig
        set
            detailVehicle = 0,
            yearID = ?,
            makeID = ?,
            modelID = ?,
            subModelID = ?,
            bodyTypeID = ?,
            bodyCodeID = ?,
            engineID = ?";
    my $add_config = $p->{'dbh'}->prepare($query);

	$query = "select
            vehicleConfig.vehicleConfigID
        from
            vehicleConfig
        where
            vehicleConfig.yearID = ?
            and vehicleConfig.makeID = ?
            and vehicleConfig.modelID = ?
            and vehicleConfig.subModelID = ?
            and vehicleConfig.bodyTypeID = ?
            and vehicleConfig.bodyCodeID = ?
            and vehicleConfig.engineID = ?
        limit 1";
	my $check_config = $p->{'dbh'}->prepare($query);

	my @new_configs = ();
	for my $config_hash (@{$p->{'configs'}}) {
		if (scalar @{$config_hash->{vehicles}}) {
			foreach my $vehicle (@{$config_hash->{vehicles}}) {
				my $vehicleConfigID = $vehicle->{vehicleConfigID} || 0;
				if (! $vehicleConfigID && ! $vehicle->{fitsAllVehicle}) {
					$check_config->execute(
						$vehicle->{'year'}||0,
						$vehicle->{'make'}||0,
						$vehicle->{'model'}||0,
						$vehicle->{'subModel'}||0,
						$vehicle->{'bodyType'}||0,
						$vehicle->{'bodyCode'}||0,
						$vehicle->{'engine'}||0,
					);
					$vehicleConfigID = $check_config->fetchrow_array();
					if (! $vehicleConfigID) {
						$add_config->execute(
							$vehicle->{'year'} || 0,
							$vehicle->{'make'} || 0,
							$vehicle->{'model'} || 0,
							$vehicle->{'subModel'} || 0,
							$vehicle->{'bodyType'} || 0,
							$vehicle->{'bodyCode'} || 0,
							$vehicle->{'engine'} || 0,
						);
						$vehicleConfigID = $p->{'dbh'}->{'mysql_insertid'} || 0;
					}
				}
				if ($vehicle->{fitsAllVehicle}) {
					$vehicleConfigID = 0;
				}
				push(@new_configs, {
					'website' => $config_hash->{'website'},
					'vehicleConfigID' => $vehicleConfigID,
					'productCategoryID' => $config_hash->{'category'} || 0,
					'appDesc' => $vehicle->{'appDesc'},
				});
			}
		}
		elsif ($config_hash->{'category'}) {
			push(@new_configs, {
				'website' => $config_hash->{'website'},
				'vehicleConfigID' => undef,
				'productCategoryID' => $config_hash->{'category'},
				'appDesc' => '',
            });
		}
	}
	
    return \@new_configs;
}

sub set_applications {
	my $p = shift;

	my $existing_apps = get_apps({
		'dbh'   => $p->{'dbh'},
		'productID' => $p->{'productID'},
	});

	my $new_apps;
	if (ref $p->{'newApps'} eq 'HASH') {
		$new_apps = $p->{'newApps'};
	}
	else {
		$new_apps = {map({
			$_->{'productCategoryID'} . "_" . $_->{'vehicleConfigID'} => $_
		}
		@{$p->{'newApps'}})};
	}

	for my $app_key (keys %{$existing_apps}) {
		my $app = $existing_apps->{$app_key};
		if(not defined $new_apps->{$app_key}) {
			delete_app({
				'dbh' => $p->{dbh},
				'productApplicationID' => $app->{'productApplicationID'},
			});
		}
	}

	my $insertQuery = "INSERT INTO productApplication (productID, vehicleConfigID, productCategoryID, appDesc) VALUES (?, ?, ?, ?)";
	my $updateQuery = "UPDATE productApplication SET appDesc = ? WHERE productApplicationID = ?";
    my $updateProduct = 'UPDATE product SET ts = ts, date_modified = NOW() WHERE productID = ?';
    my $productSTH = ECS::Site::fromID(1)->estore_dbh->prepare( $updateProduct );
	my $insertSTH = $p->{'dbh'}->prepare($insertQuery);
	my $updateSTH = $p->{'dbh'}->prepare($updateQuery);

	for my $app_key (keys %{$new_apps}) {
		my $app = $new_apps->{$app_key};
		if(not defined $existing_apps->{$app_key}) {
			$insertSTH->execute($p->{'productID'}, $app->{vehicleConfigID}, $app->{productCategoryID}, $app->{appDesc});
            $productSTH->execute( $p->{productID} );
		}
		else {
			my $old_app = $existing_apps->{$app_key};
			if (
				(defined $old_app->{appDesc} and not defined $app->{appDesc})
				or (defined $app->{appDesc} and not defined $old_app->{appDesc})
				or (defined $app->{appDesc} and defined $old_app->{appDesc} and $old_app->{appDesc} ne $app->{appDesc})
			) {
				$updateSTH->execute($app->{appDesc}, $old_app->{productApplicationID});
                $productSTH->execute( $p->{productID} );
			}
		}
	}
}

sub get_apps {
	my $p = shift;

	my $query = "SELECT productApplicationID, productID, vehicleConfigID, productCategoryID, appDesc FROM productApplication WHERE productID = ?";
	my $get_apps = $p->{'dbh'}->prepare($query);

	my %apps = ();
	$get_apps->execute($p->{'productID'});
	while(my $app = $get_apps->fetchrow_hashref()) {
        $app->{'appDesc'} ||= "";

        my $key = $app->{'productCategoryID'} . "_" . $app->{'vehicleConfigID'};
        $apps{$key} = $app;
    }
    return \%apps;
}

sub delete_app {
	my $p = shift;

	my $query = "DELETE FROM productApplication WHERE productApplicationID = ?";
    my $updateProduct = 'UPDATE product SET ts = ts, date_modified = NOW() WHERE productID = ?';
    my $productSTH = ECS::Site::fromID(1)->estore_dbh->prepare( $updateProduct );
	my $delete_app = $p->{'dbh'}->prepare($query);
    $delete_app->execute($p->{'productApplicationID'});
    return 1 unless $p->{productID};
    $productSTH->execute( $p->{productID} );
    
}

sub setWorldPacModified {

	my ( $productID, $dbh ) = @_;
	my $query = "select
            product.partnumber
        from
            product
            JOIN productVendor
                USING (productID)
            JOIN vendor
                USING (vendorID)
        where
            vendor.mm_vendorID = 'WORLDPACD'
            and product.productID = ?
        limit 1";
    my $get_worldpac_partnumber = $dbh->prepare($query);
    $get_worldpac_partnumber->execute($productID);
    my $worldpac_partnumber = $get_worldpac_partnumber->fetchrow_array();

    if(defined $worldpac_partnumber) {
        $query = "insert ignore into
                wpProductModified
            set
                partnumber = ?,
                k = 'applications'";
        my $add_modified = $dbh->prepare($query);
        $add_modified->execute($worldpac_partnumber);
    }

}

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

	my $dbHandles = {
		'ECS' => ECS::SQL::connect(),
		'TMS' => ECS::SQL::connect_tms(),
	};

	$dbHandles->{'ECS'}->{RaiseError} = 1;
	$dbHandles->{'TMS'}->{RaiseError} = 1;

	try {
		$dbHandles->{'ECS'}->begin_work;
		$dbHandles->{'TMS'}->begin_work;

		deleteFilterData($dbHandles, $self->input->{destinationID});

		foreach my $site ( ECS::Site->getAll() ) {
			my $sourceApps = get_apps({
				'dbh' => $dbHandles->{$site->name},
				'productID' => $self->input->{sourceID},
			});
			set_applications({
				'dbh' => $dbHandles->{$site->name},
				'newApps' => $sourceApps,
				'productID' => $self->input->{destinationID},
			});
		}


		my $groups = $dbHandles->{'ECS'}->selectall_arrayref('SELECT productApplicationGroupID from productApplicationGroup where productID = ?', { Slice => {}}, $self->input->{sourceID});
		foreach my $group (@$groups) {
			my $groupID = insertGroup($dbHandles->{'ECS'}, $self->input->{destinationID});
			copyVehicleApps($dbHandles->{'ECS'}, $groupID, $group->{productApplicationGroupID});
			copyCategoryApps($dbHandles, $groupID, $group->{productApplicationGroupID});
			copyProductAttributes($dbHandles, $groupID, $group->{productApplicationGroupID});
		}
	} catch {
		$dbHandles->{'ECS'}->rollback();
		$dbHandles->{'TMS'}->rollback();
		$self->error("DB error. Failed to copy apps");
	};
	
	unless ( $self->error ){
		$dbHandles->{'ECS'}->commit();
		$dbHandles->{'TMS'}->commit();
	}

	return $self->result;
}

#################################################################################
sub deleteFilterData {
	my $dbHandles = shift;
	my $productID = shift;

	my @previousGroupIDs = map{ $_->{productApplicationGroupID} } @{$dbHandles->{'ECS'}->selectall_arrayref("select productApplicationGroupID from productApplicationGroup where productID = ?", { Slice => {}}, $productID)};

	##delete previous vehicleApps and groups
	if(  scalar @previousGroupIDs ){
		$dbHandles->{'ECS'}->do("Delete from productApplicationGroup where productID = ?", {}, $productID);
		
		my $deleteVehicleApps = "
			Delete
			productVehicleApplicationGroup,
			productVehicleApplication
		from 
			productVehicleApplicationGroup 
			join productVehicleApplication using (productVehicleApplicationGroupID)
		where
			productApplicationGroupID in (" . join( ',', map {'?'} @previousGroupIDs) . ')';
		
		$dbHandles->{'ECS'}->do($deleteVehicleApps, {}, @previousGroupIDs);
		
		my $deleteCategoryApps =  "
			delete 
				productCategoryApplication 
			from 
				productCategoryApplication 
			where 
			productApplicationGroupID in (" . join( ',', map {'?'} @previousGroupIDs) . ')';
		
		my $deleteProductAttributes = "DELETE productAttribute FROM productAttribute WHERE productApplicationGroupID IN (" . join( ',', map {'?'} @previousGroupIDs) . ')';
		
		##delete previous categoryApps
		foreach my $site ( ECS::Site->getAll() ){
			$dbHandles->{$site->name}->do($deleteCategoryApps, {}, @previousGroupIDs);
			$dbHandles->{$site->name}->do($deleteProductAttributes, {}, @previousGroupIDs);
		}
	}
}

#################################################################################
sub copyVehicleApps {
	my ($dbh, $newGroupID, $copyFromGroupID) = @_;

	my $vehicleGroups = $dbh->selectall_arrayref('SELECT productVehicleApplicationGroupID FROM productVehicleApplicationGroup WHERE productApplicationGroupID = ?', {Slice=>{}}, ($copyFromGroupID));

	foreach my $vehicleGroup (@$vehicleGroups) {
		$dbh->do("INSERT INTO productVehicleApplicationGroup (productApplicationGroupID) values (?)", undef, ($newGroupID));
		my $productVehicleApplicationGroupID = $dbh->{mysql_insertid};
		$dbh->do("INSERT INTO productVehicleApplication (productVehicleApplicationGroupID, vehicleConfigID) SELECT ?, vehicleConfigID FROM productVehicleApplication WHERE productVehicleApplicationGroupID = ?", undef, ($productVehicleApplicationGroupID, $vehicleGroup->{'productVehicleApplicationGroupID'}));
	}
}

#################################################################################
sub copyCategoryApps {
	my ($dbHandles, $newGroupID, $copyFromGroupID) = @_;

	foreach my $site ( ECS::Site->getAll() ){
		$dbHandles->{$site->name}->do("INSERT INTO productCategoryApplication (productApplicationGroupID, productCategoryID) SELECT ?, productCategoryID FROM productCategoryApplication WHERE productApplicationGroupID = ?", undef, ($newGroupID, $copyFromGroupID));
	}
}

#################################################################################
sub copyProductAttributes {
	my ($dbHandles, $newGroupID, $copyFromGroupID) = @_;

	foreach my $site ( ECS::Site->getAll() ){
		$dbHandles->{$site->name}->do("INSERT INTO productAttribute (productApplicationGroupID, attributeValueID) SELECT ?, attributeValueID FROM productAttribute WHERE productApplicationGroupID = ?", undef, ($newGroupID, $copyFromGroupID));
	}
}

1;	
