#!/usr/bin/perl
use strict;
use warnings;

package API::Customer;

our $Config;
BEGIN{
	use API::Config;
	$Config = $API::Config::Config;
}
use lib ($Config->{Website_libs});

use ECS::SQL;

my $dbh;
my $dbh_orders;
my $dbhr;
my $dbhr_orders;

sub set_dbh_for_site {
	my $input = shift;

    my %dispatch = (
        'ECS' => \&ECS::SQL::connect,
        'TMS' => \&ECS::SQL::connect_tms,
     );
    my %ro_dispatch = (
        'ECS' => sub { ECS::SQL::connect('SLAVE') },
        'TMS' => sub { ECS::SQL::connect_tms('SLAVE') },
     );
    
    $dbh = $dispatch{$input->{sites}{$input->{siteID}}->{name}}->();
    $dbh_orders = $dispatch{'ECS'}->();
    $dbhr = $ro_dispatch{$input->{sites}{$input->{siteID}}->{name}}->();
    $dbhr_orders = $ro_dispatch{'ECS'}->();
}
sub update{
	
	my $self = shift;
	my $input = shift;
    set_dbh_for_site($input);
	my $result = {
		'success'	=> 1,
		'error'		=> undef,
		'update_DB'	=> $Config->{update_DB},
		'reply'	=> {
				'customerID'	=> $input->{customerID}
		}
	};


	# IF we want to write to DB (Determined by API::Config)
	# --------------------------------------------------
	if ($Config->{update_DB}){	
		$result = &update_individual($input);

		if($result->{success}){
			if(defined($input->{cid_delete})){
                _getCustomerInfoForDelete( $input );
				$result = &delete($input->{cid_delete});
				if(!$result->{success}){
					$result->{error} .= "Unable to call delete() in Customer.pm. $!\n";
				}
			}
			$result = &writeLog($input);
			if(!$result->{success}){
				$result->{error} .= "Unable to call writeLog() in Customer.pm. $!\n";
			}
		}
		if ($result->{error}) {
			$result->{success} = 0;
		}
	}
    return $result;
}

# -----------------------------------------------------------
sub get_customer_columns{
	my $input = shift;

	my $sql = "SELECT email 
				FROM customer 
				WHERE customerID = ? LIMIT 1";
	my $sth = $dbhr->prepare($sql);

	$sth->execute($input->{customerID});
	my $previous_info = $sth->fetchrow_hashref;
	my @columns;
	if(defined($input->{email}) and ($previous_info->{email} ne $input->{email})){
		push @columns, 'email';
		$input->{previousEmail} = $previous_info->{email};
	}

	return @columns;

}

# -----------------------------------------------------------
sub get_billinfo_columns{
	# Which columns are changing??
	# ----------------------------
	my $input = shift;
	
	my $sql = "SELECT company,
					name_first,
					name_last,
					address,
					address2,
					city,
					state,
					zip,
					county,
					country,
					phone,
					alt_phone,
					phone_fax
				FROM billinfo
				LEFT JOIN customer on (customer.billID=billinfo.billID)
				WHERE customer.customerID = ?
				LIMIT 1";
		
	my $sth = $dbhr->prepare($sql);

	$sth->execute($input->{customerID});
	my $previous_info = $sth->fetchrow_hashref;

	my @columns;
	{
		no warnings 'uninitialized';
		if($previous_info->{company} ne $input->{company}){
			push @columns, 'company';
		}
		if($previous_info->{name_first} ne $input->{name_first}){
			push @columns, 'name_first';
		}
		if($previous_info->{name_last} ne $input->{name_last}){
			push @columns, 'name_last';
		}
		if($previous_info->{address} ne $input->{address}){
			push @columns, 'address';
		}
		if($previous_info->{address2} ne $input->{address2}){
			push @columns, 'address2';
		}
		if($previous_info->{city} ne $input->{city}){
			push @columns, 'city';
		}
		if($previous_info->{state} ne $input->{state}){
			push @columns, 'state';
		}
		if($previous_info->{zip} ne $input->{zip}){
			push @columns, 'zip';
		}
		if($previous_info->{county} ne $input->{county}){
			push @columns, 'county';
		}
		if($previous_info->{country} ne $input->{country}){
			push @columns, 'country';
		}
		if($previous_info->{phone} ne $input->{phone}){
			push @columns, 'phone';
		}
		if($previous_info->{alt_phone} ne $input->{alt_phone}){
			push @columns, 'alt_phone';
		}
		if($previous_info->{phone_fax} ne $input->{phone_fax}){
			push @columns, 'phone_fax';
		}
		return @columns;
	}
	
}

# -----------------------------------------------------------
sub get_shipinfo_columns{
	# Which columns are changing??
	# ----------------------------
	my $input = shift;
	
	my $sql = "SELECT ship_company,
					ship_name_first,
					ship_name_last,
					ship_address,
					ship_address2,
					ship_city,
					ship_state,
					ship_zip,
					ship_county,
					ship_country
				FROM shipinfo
				LEFT JOIN customer on (customer.shipID=shipinfo.shipID)
				WHERE customer.customerID = ?
				LIMIT 1";
		
	my $sth = $dbhr->prepare($sql);

	$sth->execute($input->{customerID});
	my $previous_info = $sth->fetchrow_hashref;

	my @columns;
	{
		no warnings 'uninitialized';
		if($previous_info->{ship_company} ne $input->{ship_company}){
			push @columns, 'ship_company';
		}
		if($previous_info->{ship_name_first} ne $input->{ship_name_first}){
			push @columns, 'ship_name_first';
		}
		if($previous_info->{ship_name_last} ne $input->{ship_name_last}){
			push @columns, 'ship_name_last';
		}
		if($previous_info->{ship_address} ne $input->{ship_address}){
			push @columns, 'ship_address';
		}
		if($previous_info->{ship_address2} ne $input->{ship_address2}){
			push @columns, 'ship_address2';
		}
		if($previous_info->{ship_city} ne $input->{ship_city}){
			push @columns, 'ship_city';
		}
		if($previous_info->{ship_state} ne $input->{ship_state}){
			push @columns, 'ship_state';
		}
		if($previous_info->{ship_zip} ne $input->{ship_zip}){
			push @columns, 'ship_zip';
		}
		if($previous_info->{ship_county} ne $input->{ship_county}){
			push @columns, 'ship_county';
		}
		if($previous_info->{ship_country} ne $input->{ship_country}){
			push @columns, 'ship_country';
		}
		return @columns;
	}
	
}

# -----------------------------------------------------------
sub update_individual{

	my $input = shift;
	my $result = {
		'success'	=> 1,
		'error'		=> '',
		'update_DB'	=> $Config->{update_DB},
		'reply'	=> {
				'customerID'	=> $input->{customerID}
		}
	};
	my $sql;
	my $sth;
    my $sthOrders;
    
	my @customer_columns = &get_customer_columns($input);
	my @billinfo_columns = &get_billinfo_columns($input);
	my @shipinfo_columns = &get_shipinfo_columns($input);

	if(@customer_columns){
		if(defined($input->{email}) and defined($input->{previousEmail}) and ($input->{previousEmail} ne $input->{email})){
			# change UPDATE to UPDATE IGNORE 
			# to prevent failures once emailAccount.email is unique
			$sql = "UPDATE IGNORE emailAccount SET";
			$input->{new}{ email } = $input->{email}; 
			foreach my $column (@customer_columns){
				$sql .= " " . $column . " = ";
				if($input->{$column}){
					$sql .= $dbh->quote($input->{$column});
				}else{
					$sql .= "NULL";
				}
				$sql .= ",";
			}
			$sql =~ s/,$//;
			$sql .= " WHERE email = ?";
			my $sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{previousEmail})){
				$result->{success} = 0;
				$result->{error} = "Unable to update emailAccount table. $!\n";
				return $result;
			}
		}
		if ( 	$input->{cid_delete} )  {
			# if this is a merge, make sure updating
			# the customer email will not violate the email
			# unique key
			$sql = "SELECT customerID FROM customer WHERE
								customerID <> ? AND email = ?";
			$sth = $dbhr->prepare( $sql );
			$sth->execute( @{ $input }{ customerID => 'email' } );
			my $ret = $sth->fetchall_arrayref( {} );
			# if there is a potential duplicate, modify it to something 
			# hopefully unique
			if ($ret and ref($ret) and @$ret) {
				$sql = 'UPDATE customer SET email = CONCAT( ?, email ) WHERE customerID = ?';
				$sth = $dbh->prepare( $sql );
				$sth->execute( "dupe.$$.${\ time }", $input->{cid_delete} ); 				
			}
		}
		
		$sql = "UPDATE customer SET";
		foreach my $column (@customer_columns){
			$sql .= " " . $column . " = ";
			if($input->{$column}){
				$sql .= $dbh->quote($input->{$column});
				$input->{new}{ $column } = $input->{$column} if exists $input->{changed}{ $column };
			}else{
				$sql .= "NULL";
				$input->{new}{ $column } = undef if exists $input->{changed}{ $column };
			}
			$sql .= ",";
		}
		$sql =~ s/,$//;
		$sql .= " WHERE customerID = ?";
		$sth = $dbh->prepare($sql);	
		if(!$sth->execute($input->{customerID})){
			$result->{success} = 0;
			$result->{error} = "Unable to update customer table. $!\n";
			return $result;
		}
	}
	
	if(@billinfo_columns){
		$sql = "UPDATE billinfo SET";
		foreach my $column (@billinfo_columns){
			$sql .= " " . $column . " = ";
			if($input->{$column}){
				$sql .= $dbh->quote($input->{$column});
				$input->{new}{ $column } = $input->{$column} if exists $input->{changed}{ $column };
			}else{
				$sql .= "NULL";
				$input->{new}{ $column } = undef if exists $input->{changed}{ $column };
			}
			$sql .= ",";
		}
		$sql =~ s/,$//;
		$sql .= " WHERE customerID = ?";

		my $sth = $dbh->prepare($sql);
		if(!$sth->execute($input->{customerID})){
			$result->{success} = 0;
			$result->{error} = "Unable to update billinfo table. $!\n";
			return $result;
		}
	}
	
	if(@shipinfo_columns){
		$sql = "UPDATE shipinfo SET";
		
		foreach my $column (@shipinfo_columns){
			$sql .= " " . $column . " = ";
			if($input->{$column}){
				$sql .= $dbh->quote($input->{$column});
				$input->{new}{ $column } = $input->{$column} if exists $input->{changed}{ $column };
			}else{
				$sql .= "NULL";
				$input->{new}{ $column } = undef if exists $input->{changed}{ $column };
			}
			$sql .= ",";
		}

		$sql =~ s/,$//;
		$sql .= " WHERE customerID = ?";

		my $sth = $dbh->prepare($sql);

		if(!$sth->execute($input->{customerID})){
			$result->{success} = 0;
			$result->{error} = "Unable to update shipinfo table. $!\n";
			return $result;
		}
	}
	
	if(defined($input->{cid_delete})){
		$sql = "UPDATE orders
			SET customerID=?
			WHERE customerID=?";
			$sthOrders = $dbh_orders->prepare($sql);
			if(!$sthOrders->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update orders. $!\n";
			}
			
		$sql = "UPDATE emailAccount
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to move old email accounts to new customer. $!\n";
			}
			
		# --- Find any duplicate email addresses and handle them.

		$sql = 'SELECT 
					eaID,
					? AS email,
					catSale,
					catBackInStock,
					catPriceDrop,
					catProductRelease,
					catGenMarketing,
					catNowAvailable,
					catAlzor,
					catSchwaben,
					catZiza
				FROM emailAccount 
				WHERE customerID = ?
				AND email IN ( ? , ? )';
		$sth = $dbhr->prepare($sql);
		my @bind_values = ($input->{email}, $input->{customerID}, $input->{email});
		push @bind_values, 
			$input->{previousEmail} 
				? $input->{previousEmail} 
				: $input->{email};
		if(!$sth->execute( @bind_values )) {
			$result->{error} .= "Unable to get duplicate customer emails. $!\n";
		}
		my $emails = $sth->fetchall_arrayref({});
		my %consolidatedEmails;
		my $eaIDs = '';
		if (scalar (@{$emails}) > 1) {
			my $catSale = 1;
			my $catBackInStock = 1;
			my $catPriceDrop = 1;
			my $catProductRelease = 1;
			my $catGenMarketing = 1;
			my $catNowAvailable = 1;
			my $catAlzor = 1;
			my $catSchwaben = 1;
			my $catZiza = 1;
			foreach my $email (@{ $emails }) {
				if ($eaIDs eq '') {
					$eaIDs .= $email->{'eaID'};
				} else {
					$eaIDs .= ", " . $email->{'eaID'};
				}
				if ($email->{'catSale'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catSale'} == 0)) {
					$catSale = 0;
				}
				if ($email->{'catBackInStock'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catBackInStock'} == 0)) {
					$catBackInStock = 0;
				}
				if ($email->{'catPriceDrop'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catPriceDrop'} == 0)) {
					$catPriceDrop = 0;
				}
				if ($email->{'catProductRelease'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catProductRelease'} == 0)) {
					$catProductRelease = 0;
				}
				if ($email->{'catGenMarketing'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catGenMarketing'} == 0)) {
					$catGenMarketing = 0;
				}
				if ($email->{'catNowAvailable'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catNowAvailable'} == 0)) {
					$catNowAvailable = 0;
				}
				if ($email->{'catAlzor'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catAlzor'} == 0)) {
					$catAlzor = 0;
				}
				if ($email->{'catSchwaben'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catSchwaben'} == 0)) {
					$catSchwaben = 0;
				}
				if ($email->{'catZiza'} == 0 
					or ($consolidatedEmails{$email->{'email'}} and $consolidatedEmails{$email->{'email'}}->{'catZiza'} == 0)) {
					$catZiza = 0;
				}
				
				$consolidatedEmails{$email->{'email'}} = {
					'email' => $email->{'email'},
					'catSale' => $catSale,
					'catBackInStock' => $catBackInStock,
					'catPriceDrop' => $catPriceDrop,
					'catProductRelease' => $catProductRelease,
					'catGenMarketing' => $catGenMarketing,
					'catNowAvailable' => $catNowAvailable,
					'catAlzor' => $catAlzor,
					'catSchwaben' => $catSchwaben,
					'catZiza' => $catZiza,
				};
			}
			
			if ($eaIDs ne '') {
				$sql = "DELETE FROM emailAccount
					WHERE eaID IN (" . $eaIDs . ")";
				$sth = $dbh->prepare($sql);
				if(!$sth->execute()){
					$result->{error} .= "Unable to delete old email accounts. $!\n";
				}
			}
			
			if (%consolidatedEmails) {
				my $emailString;
				foreach my $email (values %consolidatedEmails) {
					$emailString ||= '';
					if ($emailString  eq '') {
						$emailString .= "('" . 
							$email->{'email'} . "', " . 
							$input->{customerID} . ", " . 
							$email->{'catSale'} . ", " . 
							$email->{'catBackInStock'} . ", " . 
							$email->{'catPriceDrop'} . ", " . 
							$email->{'catProductRelease'} . ", " . 
							$email->{'catGenMarketing'} . ", " . 
							$email->{'catNowAvailable'} . ", " . 
							$email->{'catAlzor'} . ", " . 
							$email->{'catSchwaben'} . ", " . 
							$email->{'catZiza'} . ")";
					} else {
						$emailString .= ", ('" . 
							$email->{'email'} . "', " . 
							$input->{customerID} . ", " . 
							$email->{'catSale'} . ", " . 
							$email->{'catBackInStock'} . ", " . 
							$email->{'catPriceDrop'} . ", " . 
							$email->{'catProductRelease'} . ", " . 
							$email->{'catGenMarketing'} . ", " . 
							$email->{'catNowAvailable'} . ", " . 
							$email->{'catAlzor'} . ", " . 
							$email->{'catSchwaben'} . ", " . 
							$email->{'catZiza'} . ") ";
					}
				}
				$sql = "INSERT INTO emailAccount (email, customerID, catSale, catBackInStock, catPriceDrop, catProductRelease, catGenMarketing, catNowAvailable, catAlzor, catSchwaben, catZiza ) VALUES $emailString";
				$sth = $dbh->prepare($sql);
				if(!$sth->execute()){
					$result->{error} .= "Unable to create new email accounts. $!\n";
				}
			}
		}

		$sql = "UPDATE reward
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update reward. $!\n";
			}
			
		$sql = "UPDATE rewardTrans
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update rewardTrans. $!\n";
			}
		
		#  - Customer Vehicles -
		# Merged customer vehicles are first inserted into the new customer's 
		# vehicles while existing vehicles are ignored
			$sql = "INSERT IGNORE INTO customerVehicle
                        (customerID, vehicleConfigID, defaultVehicle, active, dateAdded)
					SELECT
						?,
						vehicleConfigID,
						'NO',
						active,
						NOW()
					FROM customerVehicle
					WHERE customerID = ?;";
					$sth = $dbh->prepare($sql);
					if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
						$result->{error} .= "unable to update customerVehicle";
					}
			
			# Then they're deleted from the to-be-deleted customer.
			$sql = "DELETE FROM customerVehicle
					WHERE customerID = ?";
					$sth = $dbh->prepare($sql);
					if(!$sth->execute($input->{cid_delete})){
						$result->{error} .= "Unable to remove merged customer from customerVehicle table. $!\n";
					}
			
		$sql = "UPDATE customerGroupMatch
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update customerGroupMatch. $!\n";
			}
		
		$sql = "UPDATE customerFixedPricing
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update customerFixedPricing. $!\n";
			}
		
		$sql = "UPDATE emailAccountChanged
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update emailAccountChanged. $!\n";
			}
		
		$sql = "UPDATE vehicleinfo
			SET customerID=?
			WHERE customerID=?";
			$sth = $dbh->prepare($sql);
			if(!$sth->execute($input->{customerID}, $input->{cid_delete})){
				$result->{error} .= "Unable to update vehicleinfo. $!\n";
			}
	}
	
	return $result;
}


# Delete the customer whose customerID was not selected
# -----------------------------------------------------------
sub delete{

	my $deleteID = shift;
    
	my $result = {
		'success'	=> 1,
		'error'		=> undef,
		'update_DB'	=> $Config->{update_DB},
	};    

	if($Config->{update_DB}){
		my $sql = "DELETE customer, 
						billinfo, 
						shipinfo
					FROM customer
						LEFT JOIN billinfo ON (customer.billID = billinfo.billID)
						LEFT JOIN shipinfo ON (customer.shipID = shipinfo.shipID)
					WHERE customer.customerID=?";
		my $sth;

		$sth = $dbh->prepare($sql);
		if(!$sth->execute($deleteID)){
			$result->{success} = 0;
			$result->{error} = "Unable to delete from DB. $!\n";
		};
	}

	return $result;
}

sub _getCustomerInfoForDelete {
    my ($input) = @_;

    my $sql = q{
        SELECT
            email,
            name_first,
            name_last,
            address,
            address2,
            city,
            state,
            zip,
            county,
            country,
            phone,
            alt_phone,
            phone_fax,
            ship_name_first,
            ship_name_last,
            ship_address,
            ship_address2,
            ship_city,
            ship_state,
            ship_zip,
            ship_county,
            ship_country
        FROM customer
        LEFT JOIN billinfo
            USING (customerID)
        LEFT JOIN shipinfo
            USING (customerID)
        WHERE
            customerID = ?
    };
    my $sth = $dbhr->prepare( $sql );
    $sth->execute( $input->{'cid_delete'} );
    $input->{'changed'} = $sth->fetchrow_hashref;

    return;
}

# -----------------------------------------------------------
sub writeLog{ 
	# This function is only called if update() succeeded
	my $input = shift;
	
    my $log_dbh  = ECS::SQL::connect;
    my $log_dbhr = ECS::SQL::connect('SLAVE');

	my $result = {
		'success'	=> 1,
		'error'		=> undef,
		'update_DB'	=> $Config->{update_DB},
	};
	
	if($input->{cid_delete}){
		my $sql = "INSERT INTO customerAdminLog ( siteID, employeeID, oldAcctNo, newAcctNo";
			if($input->{changed}){
				$sql .= ", fieldsModified";
				$sql .= ", fieldsFinal";
			}
			$sql .= ") VALUES (" . 
				($input->{siteID} || 1).','.
				$log_dbh->quote($input->{employeeID}) . "," .
				$log_dbh->quote($input->{cid_delete}) . "," .
				$log_dbh->quote($input->{customerID});
				if(defined($input->{changed}) and $input->{changed} ne ''){
					my $fields_modified = '';
					my $fields_final = '';
					my %fields_changed = %{$input->{changed}};
					foreach my $field_name ( keys  %fields_changed ){
						$fields_modified .= $field_name . "->" . $fields_changed{$field_name} . ",";
						$fields_final .= $field_name . "->" . $input->{new}{$field_name} . ",";
					}
					$fields_modified =~ s/,$//;
					$fields_final =~ s/,$//;
					$sql .= join ',' => ('', map($log_dbh->quote( $_ ), $fields_modified, $fields_final ));
					#$sql .=  "," . $log_dbh->quote($fields_modified);
				}
			$sql .= ")";
					
		my $sth = $log_dbh->prepare($sql);
			$sth->execute();
	}else{
		my $sql = "INSERT INTO customerAdminLog ( siteID, employeeID, newAcctNo";
			if($input->{changed}){
				$sql .= ", fieldsModified";
				$sql .= ", fieldsFinal";
			}
			$sql .= ") VALUES (" . 
				($input->{siteID} || 1).','.
				$log_dbh->quote($input->{employeeID}) . "," .
				$log_dbh->quote($input->{customerID});
				if(defined($input->{changed}) and $input->{changed} ne ''){
					my $fields_modified = '';
					my $fields_final = '';
					my %fields_changed = %{$input->{changed}};
					foreach my $field_name ( keys  %fields_changed ){
						$fields_modified .= $field_name . "->" . $fields_changed{$field_name} . ",";
						$fields_final .= $field_name . "->" . $input->{new}{$field_name} . ",";
					}
					$fields_modified =~ s/,$//;
					$fields_final =~ s/,$//;
					$sql .= join ',' => ('', map($log_dbh->quote( $_ ), $fields_modified, $fields_final ));
					# $sql .=  "," . $log_dbh->quote($fields_modified);
				}
			$sql .= ")";
					
		my $sth = $log_dbh->prepare($sql);
			$sth->execute();
	}
	return $result;
}
1;
