<?php
class sqlhandler{
    private static $db ;
	private static $scrubHelpertb = "paycount" ;
	private static $declineHelper2tb = "dailydeclines" ;
	private static $hybridOrdersHelper = "hybrid_orders" ;
	
	//Helper functions
    public static function getconnection(){
		Global $configs;
        try{
			if(!isset(self::$db)){
				self::$db = new PDO("mysql:host={$configs->sql->host};dbname={$configs->sql->database};charset=utf8", "{$configs->sql->username}", "{$configs->sql->password}",array(
					PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
				));				
            }   
        }catch(PDOException $e){
            self::$db = null;
			throw $e;
        }    
    }

	public static function getCustomerCount($brand){
	    try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }

	        $query = self::$db->prepare("SELECT COUNT(*) AS PAYC FROM ".self::$scrubHelpertb." WHERE brand = '".$brand."'");
	        $query->execute();
	        $result = $query->fetch(PDO::FETCH_ASSOC);
	        
	        if(!empty($result))
	        {
	            return $result['PAYC'];
	        }
	        else{
	            throw new Exception("Could not get count");
	        }
	    }catch(Exception $e){
	        return false;
	    }
	}

	public static function addCustomerCount($fields=array()){
	    try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }
	        
	        $body=[
	            ':brand'=>empty($fields['brand'])?NULL:$fields['brand'],
	            ':orderId'=>empty($fields['orderId'])?NULL:$fields['orderId']
	        ];
	        
	        
	        self::$db->beginTransaction();
	        $query = self::$db->prepare("
            INSERT INTO ".self::$scrubHelpertb."
                 (brand,orderId) VALUES
                    (:brand,:orderId) ON DUPLICATE KEY UPDATE 
                    orderId=VALUES(orderId)
            ");
	        $result = $query->execute($body);
	        if(!$result)
	        {
	            throw new Exception("insert failed");
	        }
	        else{
	            self::$db->commit();
	            self::$db = null;
	            return $result;
	        }
	    }catch(Exception $e){
	        self::$db->rollBack();
	        self::$db = null;
	        throw $e;
	    }
	}

	public static function clearCustomerCount($brand){
	    try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }

	        $query = self::$db->prepare("DELETE FROM ".self::$scrubHelpertb." WHERE brand = '".$brand."'");
	        $result = $query->execute();
	        
	        if(!empty($result))
	        {
	            return $result;
	        }
	        else{
	            throw new Exception("Could not clear entries");
	        }
	    }catch(Exception $e){
	        return false;
	    }
	}

	public static function updateDecineOrderAttempts($fields){
	    try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }
	        
	        $body=[
	            ':declineOrder'=>empty($fields['orderId'])?$fields['prospectId']:$fields['orderId'],
				':email'=>empty($fields['email'])?NULL:$fields['email'],
				':isUpsale'=>empty($fields['orderId'])?0:1,
				':successPostback'=>(!empty($fields['orderId'])) || (empty($fields['s1_rerun_fire']))?NULL:$fields['s1_rerun_fire'],
				':brand'=> empty($fields['brand'])?NULL:$fields['brand'],
				':retryMessage' => empty($fields['retryMessage'])?NULL:$fields['retryMessage'],
				':initialMerchantId'=>(empty($fields['initialMerchantId'])?NULL:$fields['initialMerchantId'])
	        ];
	        
	        self::$db->beginTransaction();
	        $query = self::$db->prepare("
            INSERT INTO ".self::$declineHelper2tb."
                 (declineOrder,isUpsale,email,lastRetryOrder,retryMessage,successPostback,brand,declinedate,initialMerchantId) VALUES
                    (:declineOrder,:isUpsale,:email,:declineOrder,:retryMessage,:successPostback,:brand,NOW(),:initialMerchantId) ON DUPLICATE KEY UPDATE 
                    declineOrder=VALUES(declineOrder),attempts=0,lastRetryOrder=VALUES(declineOrder),retryMessage=VALUES(retryMessage),successPostback=VALUES(successPostback),brand=VALUES(brand),declinedate=NOW(),initialMerchantId=VALUES(initialMerchantId)
            ");
	        $result = $query->execute($body);
	        if(!$result)
	        {
	            throw new Exception("insert failed");
	        }
	        else{
	            self::$db->commit();
	            self::$db = null;
	            return $result;
	        }
	    }catch(Exception $e){
	        self::$db->rollBack();
	        self::$db = null;
	        throw $e;
	    }
	}

	public static function updateDecineOrderAttemptsCvv($fields){
	    try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }
	        
	        $body=[
	            ':declineOrder'=>empty($fields['orderId'])?$fields['prospectId']:$fields['orderId'],
				':email'=>empty($fields['email'])?NULL:$fields['email'],
				':isUpsale'=>empty($fields['orderId'])?0:1,
				':successPostback'=>(!empty($fields['orderId'])) || (empty($fields['s1_rerun_fire']))?NULL:$fields['s1_rerun_fire'],
				':brand'=> empty($fields['brand'])?NULL:$fields['brand'],
				':retryMessage' => empty($fields['retryMessage'])?NULL:$fields['retryMessage'],
				":paySource"				=> "CREDITCARD",
				":ipAddress" 			=> $fields['ipAddress'],
				":cardNumber"			=> trim(str_replace('-', '', $fields['creditCardNumber'])),
				":cardMonth"				=> trim($fields["expmonth"]),
				":cardYear"				=> trim($fields["expyear"]),
				":cardSecurityCode"=> trim($fields["CVV"]),
				":campaignId"			=> $fields['campaignId'],
				":phoneNumber" 		=> $fields['phone'],
				":firstName"				=> !empty($_POST['billingFirstName'])?$_POST['billingFirstName']:$_POST['firstName'],
				":lastName"				=> !empty($_POST['billingLastName'])?$_POST['billingLastName']:$_POST['lastName'],
				":address1"				=> !empty($_POST['billingAddress1'])?$_POST['billingAddress1']:$_POST['shippingAddress1'],
				":city"						=> !empty($_POST['billingCity'])?$_POST['billingCity']:$_POST['shippingCity'],
				":state"						=> !empty($_POST['billingState'])?$_POST['billingState']:$_POST['shippingState'],
				":postalCode"			=> !empty($_POST['billingZip'])?$_POST['billingZip']:$_POST['shippingZip'],
				":country"					=> "US",
				":shipAddress1"		=> $_POST['shippingAddress1'],
				":shipCity"				=> $_POST['shippingCity'],
				":shipState"				=> $_POST['shippingState'],
				":shipCountry"			=> "US",
				":shipPostalCode"	=> $_POST['shippingZip'],
				':affId'=>(empty($fields['AFFID'])?NULL:$fields['AFFID']),
				':sourceValue1'=>(empty($fields['C1'])?NULL:$fields['C1']),
				':sourceValue2'=>(empty($fields['C2'])?NULL:$fields['C2']),
				':sourceValue3'=>(empty($fields['C3'])?NULL:$fields['C3']),
				':initialMerchantId'=>(empty($fields['initialMerchantId'])?NULL:$fields['initialMerchantId']),
				':forceMerchantId'=>(empty($fields['forceMerchantId'])?(empty($fields['dboverwritegw'])?NULL:$fields['dboverwritegw']):$fields['forceMerchantId']),
				':zero_dollar_product_id'=>(empty($fields['zero_dollar_product_id'])?NULL:$fields['zero_dollar_product_id']),
				':is_hybrid'=>(empty($fields['is_hybrid'])?0:$fields['is_hybrid'])
	        ];

			if (isset($fields['offers'])) {
				$product_arr=array_column($fields['offers'],'product_id');
				$body[':products']=implode(",",$product_arr);
			}
	        
	        self::$db->beginTransaction();
	        $query = self::$db->prepare("
            INSERT INTO ".self::$declineHelper2tb."
                 (
					declineOrder,
					isUpsale,
					email,
					lastRetryOrder,
					retryMessage,
					successPostback,
					brand,
					declinedate,
					paySource,
					ipAddress,
					cardNumber,
					cardMonth,
					cardYear,
					cardSecurityCode,
					campaignId,
					phoneNumber,
					firstName,
					lastName,
					address1,
					city,
					state,
					postalCode,
					country,
					shipAddress1,
					shipCity,
					shipState,
					shipCountry,
					shipPostalCode,
					affId,
					sourceValue1,
					sourceValue2,
					sourceValue3,
					initialMerchantId,
					forceMerchantId,
					products,
					zero_dollar_product_id,
					is_hybrid
					) VALUES(
						:declineOrder,
						:isUpsale,
						:email,
						:declineOrder,
						:retryMessage,
						:successPostback,
						:brand,
						NOW(),
						:paySource,
						:ipAddress,
						:cardNumber,
						:cardMonth,
						:cardYear,
						:cardSecurityCode,
						:campaignId,
						:phoneNumber,
						:firstName,
						:lastName,
						:address1,
						:city,
						:state,
						:postalCode,
						:country,
						:shipAddress1,
						:shipCity,
						:shipState,
						:shipCountry,
						:shipPostalCode,
						:affId,
						:sourceValue1,
						:sourceValue2,
						:sourceValue3,
						:initialMerchantId,
						:forceMerchantId,
						:products,
						:zero_dollar_product_id,
						:is_hybrid
						) ON DUPLICATE KEY UPDATE 
                    declineOrder=VALUES(declineOrder),
					attempts=0,
					lastRetryOrder=VALUES(declineOrder),
					retryMessage=VALUES(retryMessage),
					successPostback=VALUES(successPostback),
					brand=VALUES(brand),
					declinedate=NOW(),
					paySource=VALUES(paySource),
					ipAddress=VALUES(ipAddress),
					cardNumber=VALUES(cardNumber),
					cardMonth=VALUES(cardMonth),
					cardYear=VALUES(cardYear),
					cardSecurityCode=VALUES(cardSecurityCode),
					campaignId=VALUES(campaignId),
					phoneNumber=VALUES(phoneNumber),
					firstName=VALUES(firstName),
					lastName=VALUES(lastName),
					address1=VALUES(address1),
					city=VALUES(city),
					state=VALUES(state),
					postalCode=VALUES(postalCode),
					country=VALUES(country),
					shipAddress1=VALUES(shipAddress1),
					shipCity=VALUES(shipCity),
					shipState=VALUES(shipState),
					shipCountry=VALUES(shipCountry),
					shipPostalCode=VALUES(shipPostalCode),
					affId=VALUES(affId),
					sourceValue1=VALUES(sourceValue1),
					sourceValue2=VALUES(sourceValue2),
					sourceValue3=VALUES(sourceValue3),
					initialMerchantId=VALUES(initialMerchantId),
					forceMerchantId=VALUES(forceMerchantId),
					products=VALUES(products),
					zero_dollar_product_id=VALUES(zero_dollar_product_id),
					is_hybrid=VALUES(is_hybrid)
            ");
	        $result = $query->execute($body);
	        if(!$result)
	        {
	            throw new Exception("insert failed");
	        }
	        else{
	            self::$db->commit();
	            self::$db = null;
	            return $result;
	        }
	    }catch(Exception $e){
	        self::$db->rollBack();
	        self::$db = null;
	        throw $e;
	    }
	}

	public static function saveZeroDollarVxCustomerDetails($fields){
	    try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }
	        
	        $body=[
				':email'=>empty($fields['email'])?NULL:$fields['email'],
				':brand'=> empty($fields['brand'])?NULL:$fields['brand'],
				":paySource"				=> "CREDITCARD",
				":ipAddress" 			=> $fields['ipAddress'],
				":cardNumber"			=> trim(str_replace('-', '', $fields['creditCardNumber'])),
				":cardMonth"				=> trim($fields["expmonth"]),
				":cardYear"				=> trim($fields["expyear"]),
				":cardSecurityCode"=> trim($fields["CVV"]),
				":campaignId"			=> $fields['campaignId'],
				":phoneNumber" 		=> $fields['phone'],
				":firstName"				=> !empty($_POST['billingFirstName'])?$_POST['billingFirstName']:$_POST['firstName'],
				":lastName"				=> !empty($_POST['billingLastName'])?$_POST['billingLastName']:$_POST['lastName'],
				":address1"				=> !empty($_POST['billingAddress1'])?$_POST['billingAddress1']:$_POST['shippingAddress1'],
				":city"						=> !empty($_POST['billingCity'])?$_POST['billingCity']:$_POST['shippingCity'],
				":state"						=> !empty($_POST['billingState'])?$_POST['billingState']:$_POST['shippingState'],
				":postalCode"			=> !empty($_POST['billingZip'])?$_POST['billingZip']:$_POST['shippingZip'],
				":country"					=> "US",
				":shipAddress1"		=> $_POST['shippingAddress1'],
				":shipCity"				=> $_POST['shippingCity'],
				":shipState"				=> $_POST['shippingState'],
				":shipCountry"			=> "US",
				":shipPostalCode"	=> $_POST['shippingZip'],
				':affId'=>(empty($fields['AFFID'])?NULL:$fields['AFFID']),
				':sourceValue1'=>(empty($fields['C1'])?NULL:$fields['C1']),
				':sourceValue2'=>(empty($fields['C2'])?NULL:$fields['C2']),
				':sourceValue3'=>(empty($fields['C3'])?NULL:$fields['C3']),
				':initialMerchantId'=>(empty($fields['initialMerchantId'])?NULL:$fields['initialMerchantId']),
				':forceMerchantId'=>(empty($fields['forceMerchantId'])?(empty($fields['dboverwritegw'])?NULL:$fields['dboverwritegw']):$fields['forceMerchantId'])
	        ];

			if (isset($fields['offers'])) {
				$product_arr=array_column($fields['offers'],'product_id');
				$body[':products']=implode(",",$product_arr);
			}
		
	        self::$db->beginTransaction();
	        $query = self::$db->prepare("
            INSERT INTO ".self::$hybridOrdersHelper."
                 (
					email,
					brand,
					orderdate,
					paySource,
					ipAddress,
					cardNumber,
					cardMonth,
					cardYear,
					cardSecurityCode,
					campaignId,
					phoneNumber,
					firstName,
					lastName,
					address1,
					city,
					state,
					postalCode,
					country,
					shipAddress1,
					shipCity,
					shipState,
					shipCountry,
					shipPostalCode,
					affId,
					sourceValue1,
					sourceValue2,
					sourceValue3,
					initialMerchantId,
					forceMerchantId,
					products
					) VALUES(
						:email,
						:brand,
						NOW(),
						:paySource,
						:ipAddress,
						:cardNumber,
						:cardMonth,
						:cardYear,
						:cardSecurityCode,
						:campaignId,
						:phoneNumber,
						:firstName,
						:lastName,
						:address1,
						:city,
						:state,
						:postalCode,
						:country,
						:shipAddress1,
						:shipCity,
						:shipState,
						:shipCountry,
						:shipPostalCode,
						:affId,
						:sourceValue1,
						:sourceValue2,
						:sourceValue3,
						:initialMerchantId,
						:forceMerchantId,
						:products
						) ON DUPLICATE KEY UPDATE 
					brand=VALUES(brand),
					orderdate=NOW(),
					paySource=VALUES(paySource),
					ipAddress=VALUES(ipAddress),
					cardNumber=VALUES(cardNumber),
					cardMonth=VALUES(cardMonth),
					cardYear=VALUES(cardYear),
					cardSecurityCode=VALUES(cardSecurityCode),
					campaignId=VALUES(campaignId),
					phoneNumber=VALUES(phoneNumber),
					firstName=VALUES(firstName),
					lastName=VALUES(lastName),
					address1=VALUES(address1),
					city=VALUES(city),
					state=VALUES(state),
					postalCode=VALUES(postalCode),
					country=VALUES(country),
					shipAddress1=VALUES(shipAddress1),
					shipCity=VALUES(shipCity),
					shipState=VALUES(shipState),
					shipCountry=VALUES(shipCountry),
					shipPostalCode=VALUES(shipPostalCode),
					affId=VALUES(affId),
					sourceValue1=VALUES(sourceValue1),
					sourceValue2=VALUES(sourceValue2),
					sourceValue3=VALUES(sourceValue3),
					initialMerchantId=VALUES(initialMerchantId),
					forceMerchantId=VALUES(forceMerchantId),
					products=VALUES(products)
            ");
	        $result = $query->execute($body);
			//echo $result->lastInsertId();
			//echo "=====";
			//echo $query->lastInsertId();
	        if(!$result)
	        {
	            throw new Exception("insert failed");
	        }
	        else{
				$lastInsertId = self::$db->lastInsertId();
	            self::$db->commit();
	            self::$db = null;
				return $lastInsertId ;
	             //$result;
	        }
	    }catch(Exception $e){
	        self::$db->rollBack();
	        self::$db = null;
	        throw $e;
	    }
	}
	public static function updateZeroDollarVxOrder($fields){
		try{
	        self::getconnection();
	        if(self::$db == null){
	            throw new Exception("connection failed");
	        }
	        
		
			$body = [
				':id' => empty($fields['id']) ? NULL : $fields['id']
			];
			
			$queryParts = []; // To store the dynamic query parts
			
			if (isset($fields['is_failed'])) {
				$body[':is_failed'] = $fields['is_failed'];
				$queryParts[] = "is_failed = :is_failed";
			}
			
			if (isset($fields['declineOrder'])) {
				$body[':declineOrder'] = $fields['declineOrder'];
				$queryParts[] = "declineOrder = :declineOrder";
			}

			if (isset($fields['retryMessage'])) {
				$body[':retryMessage'] = $fields['retryMessage'];
				$queryParts[] = "retryMessage = :retryMessage";
			}
	        
			$setQuery = implode(', ', $queryParts);
	        self::$db->beginTransaction();
	        $query = self::$db->prepare("Update ".self::$hybridOrdersHelper." SET $setQuery where id=:id");
	        $result = $query->execute($body);
	        if(!$result)
	        {
	            throw new Exception("insert failed");
	        }
	        else{
	            self::$db->commit();
	            self::$db = null;
	            return $result;
	        }
	    }catch(Exception $e){
	        self::$db->rollBack();
	        self::$db = null;
	        throw $e;
	    }
	}
/*	public static function GetReRouteFromDBOverwrite($fields){
		try{
			self::getconnection();
			if(self::$db == null){
				throw new Exception("connection failed");
			}
			$body=[
				":cardNumber"=> trim(str_replace('-', '', $fields['creditCardNumber'])),
				":cardMonth"=> trim($fields["expmonth"]),
				":cardYear"=> trim($fields["expyear"]),
				":cardSecurityCode"=> trim($fields["CVV"]),
			];
			$query = self::$db->prepare("SELECT forceMerchantId FROM ".self::$declineHelper2tb." WHERE cardNumber = :cardNumber AND cardMonth = :cardMonth AND cardYear = :cardYear AND cardSecurityCode = :cardSecurityCode AND isUpsale = FALSE LIMIT 1");
			$query->execute($body);
			$result = $query->fetch(PDO::FETCH_ASSOC);
			
			if(!empty($result))
			{
				return $result['forceMerchantId'];
			}
			else{
				return false;
			}
		}catch(Exception $e){
			return false;
		}
	}*/
		public static function GetReRouteFromDBOverwrite($fields){
		try{
			self::getconnection();
			if(self::$db == null){
				throw new Exception("connection failed");
			}
			$body=[
				":cardNumber"=> trim(str_replace('-', '', $fields['creditCardNumber'])),
				":email"=>$fields["email"]
			];
			$query = self::$db->prepare("SELECT forceMerchantId FROM ".self::$declineHelper2tb." WHERE (email=:email AND isUpsale = FALSE) OR (cardNumber = :cardNumber AND isUpsale = FALSE) LIMIT 1");
			$query->execute($body);
			$result = $query->fetch(PDO::FETCH_ASSOC);
			
			if(!empty($result))
			{
				return $result['forceMerchantId'];
			}
			else{
				return false;
			}
		}catch(Exception $e){
			return false;
		}
	}
}
?>