<?php
ini_set('display_errors', 'On');
error_reporting(E_ERROR);
include 'send_gcm.php';
include 'update_timestamp.php';
include 'insert_into_sync_table.php';

/*The following code will add/upd/del data in VEH_TABLE. Insert data into SYNC_TABLE. And notify partner phones*/

// array for JSON response (default to failure)
$response = array();
$response["success"] = 0;
$response["message"] = "Failed";

//get json string
$json = file_get_contents('php://input');

//get json array
$jsonArr = json_decode($json, true);

// check for required fields
if (isset($jsonArr['email']) && isset($jsonArr['type'])) {

	// include db config class
	require_once __DIR__ . '/db_config.php';
	
	// connecting to db
    	$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);
    	mysqli_set_charset($mysqli, "utf8");
	
	//get email and android id
	$email = $jsonArr['email'];
	$androidId = $jsonArr['androidId'];
	$source = $jsonArr['source'];	
	
	//check that android id is not in sync table
	if($stmt = $mysqli->prepare("SELECT * FROM SYNC_TABLE WHERE ANDROID_ID = ?")){
		$stmt->bind_param("s", $androidId);
		$stmt->execute(); 
		$stmt->store_result();
    		$num_rows = $stmt->num_rows;
    		
    		//proceed only if android id does not exist
    		if($num_rows == 0){
	    		$type = $jsonArr['type'];
			$id = $jsonArr['_id'];
			$mk = $jsonArr['make'];
			$md = $jsonArr['model'];
			$vid = $jsonArr['vehid'];
			$year = $jsonArr['year'];
			$lc = $jsonArr['lic'];
			$vin = $jsonArr['vin'];
			$insNo = $jsonArr['insuranceNo'];
			$notes = $jsonArr['notes'];
			$picPath = $jsonArr['picture'];
			$customSpecs = $jsonArr['customSpecifications'];
			
			//get user id
			if($stmt = $mysqli->prepare("SELECT USER_ID FROM USER_ID WHERE EMAIL = ?")){
				$stmt->bind_param("s", $email);	
				$stmt->execute();
				$stmt->bind_result($temp_user_id);
				while($stmt->fetch()){
		    			$user_id = $temp_user_id;
		    		}		    	
		    		
		    		//check if image has arrived
				if(!empty($picPath)){
					//change picPath to only name and append email id
					$picPath = str_replace(" ", "_", $picPath);
					//append email if it does not contain it already

					if(strpos($picPath, $email) !== false){										
						$picPath = basename($picPath);
					}
					//else append email
					else{
						$picPath = $email . "." . basename($picPath);
					}

					//set save path of file and change append email id to image name
					$file_path = "../FuelBuddyImages/";
					$file_path = $file_path . $picPath; 
					
					file_put_contents($file_path, base64_decode($jsonArr['img_file']));
				}
		    		
		    		//add data into table
		    		if(strcmp($type, "add") == 0){
		    			//get max id in case of data being entered from the web
		    			if(empty($id) || strcmp($id, "NEW")==0 || $id==0){
		    				$id = getMaxID($mysqli, $user_id);
		    			}
		    			
					if($stmt = $mysqli->prepare("INSERT INTO VEH_TABLE(USER_ID, _ID, MAKE, MODEL, LIC, VEHID, YEAR, VIN, INSURANCE_NO, NOTES, PICTURE, CUSTOM_SPECIFICATIONS) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")){
							    	
						$stmt->bind_param("iissssssssss", $user_id, $id, $mk, $md, $lc, $vid, $year, $vin, $insNo, $notes, $picPath, $customSpecs);
		    				$result = $stmt->execute(); 
		    						
		    				// successfully inserted into database
		    				if ($result) {
		    					//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, $type, $user_id, "VEH_TABLE", $androidId)){
		    						//add data into service table for the vehicle if vehicle is created from the web
		    						//this function also adds data into sync table, so can call sendGCM after this
		    						if(strcmp($source, "web")==0){
		    							addVehRecIntoService($mysqli, $user_id, $vid);
		    						}
		    						
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    								    						
		    							    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id);
		    					
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);
		       				}
		       				//if failure due to unique constraint then move on
						else if($mysqli->errno===1062){
							$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);
						}													
					}
				}
				//update vehicle rec
				else if(strcmp($type, "edit") == 0){
					//get old vehid, old pic path
					if($stmt = $mysqli->prepare("SELECT VEHID, PICTURE FROM VEH_TABLE WHERE USER_ID=? and _ID=?")){
						$stmt->bind_param("ii", $user_id, $id);
						$stmt->execute();
						$stmt->bind_result($temp_veh_id, $temp_old_pic);
						while($stmt->fetch()){
							$old_vid = $temp_veh_id;
							$old_pic = $temp_old_pic;
						}
					}	
					
					//if update is from web, and picPath is empty, that means user has not updated the pic
					//becuase pic cannot be deleted from the web, and if no new pic is attached then web send empty picPath
					if(strcmp($source, "web")==0 && empty($picPath)){
						$picPath = $old_pic;
					}				
					
					if($stmt = $mysqli->prepare("UPDATE VEH_TABLE SET MAKE=?, MODEL=?, LIC=?, VEHID=?, YEAR=?, VIN=?, INSURANCE_NO=?, NOTES=?, PICTURE=?, CUSTOM_SPECIFICATIONS=? WHERE USER_ID=? and _ID=?")){
						$stmt->bind_param("ssssssssssii", $mk, $md, $lc, $vid, $year, $vin, $insNo, $notes, $picPath, $customSpecs, $user_id, $id);
						$result = $stmt->execute();
						
						// rec has been updated succesfully (rec will be updated in MILEAGE_LOG via Foreign Key)
		    				if($result){	
		    					//if old vehid is different from the new one, update in service table
		    					//(log table will update itself becuase of on update cascade clause
		    					if(isset($old_vid) && strcmp($old_vid, $vid)!=0){
		    						$stmt = $mysqli->prepare("UPDATE SERVICE_TABLE SET VEHID=? WHERE VEHID=? AND USER_ID=?");
		    						$stmt->bind_param("ssi", $vid, $old_vid, $user_id);
								$stmt->execute();	
		    					}
		    					
		    					//if old pic is not empty and is different from new, then delete the old pic from folder
							if(isset($old_pic) && !empty($old_pic) && strcmp($old_pic, $picPath)!=0){
								$file_path = "../FuelBuddyImages/";
								$file_path = $file_path . $old_pic;
								unlink($file_path);
							}
										
		    					//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, $type, $user_id, "VEH_TABLE", $androidId)){
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    						
		    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id);	    		
		    						    					
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);
		    				}		    			
					}
				}
				//delete vehicle rec
				else if(strcmp($type, "del") == 0){
					//get image, vehid to use later
					$vehid = "";
					$pic = "";
					if($stmt = $mysqli->prepare("SELECT VEHID, PICTURE FROM VEH_TABLE WHERE USER_ID=? and _ID=?")){
						$stmt->bind_param("ii", $user_id, $id);
						$stmt->execute();
						$stmt->bind_result($temp_vehid, $temp_pic);
						while($stmt->fetch()){
		    					$vehid = $temp_vehid;						
		    					$pic = $temp_pic;
		    				}		  				
					}					
									
					if($stmt = $mysqli->prepare("DELETE FROM VEH_TABLE WHERE USER_ID=? AND _ID=?")){					
						$stmt->bind_param("ii", $user_id, $id);
						$result = $stmt->execute();
						
						// rec has been deleted succesfully (rec will be deleted in LOG_TABLE via Foreign Key)
		    				if($result){				
		    					//if image exists, delete it from the folder
		    					if(!empty($pic)){
		    						$file_path = "../FuelBuddyImages/";
								$file_path = $file_path . $pic;
								unlink($file_path); 
		    					}		    							
		    					
		    					//delete records from SERVICE_TABLE for this vehicle
		    					//since there is no foreign key on user_id/vehid due to ALL
		    					$stmt = $mysqli->prepare("DELETE FROM SERVICE_TABLE WHERE VEHID=?");
		    					$stmt->bind_param("s", $vehid);
							$stmt->execute();	
		    					
		    					//update sync table		    		
		    					if(insertIntoSyncTable($mysqli, $id, $type, $user_id, "VEH_TABLE", $androidId)){
		    						//get other reg ids of the same email
		    						$reg_id = getOtherRegIds($mysqli, $user_id, $androidId);		    						
		    						
		    						//send message to other phones belonging to same email
		    						sendGCMMessage($reg_id, "pull");
		    					}
		    					
		    					//update timestamp in Profile
		    					updateTimestamp($user_id);
		    							    		
		    					$response["success"] = 1;
							$response["message"] = "Success";
				
							// echoing JSON response
							echo json_encode($response);
		    				}
					}
				}				
			}	
		}	
		//else user's android id exists in the Sync table, inform him that data on his phone is out of sync				 			
		else{		
			//changed from gcm to return
			$response["success"] = 0;
		    $response["message"] = "out of sync";
				
			// echoing JSON response
			echo json_encode($response);
		}
		
		// Close the prepared statement.
		if($stmt != null){
	    		$stmt->close(); 
	    	} 
	}
	
	//else send generic failure message
	if($response["success"] == 0 && strcmp($response["message"], "out of sync")!==0){
		$response["success"] = 0;
		$response["message"] = "Failed";
								 
		// echoing JSON response
		echo json_encode($response);
	}
	
	mysqli_close($mysqli);
}
else{
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}

//function to get other reg ids
function getOtherRegIds($mysqli, $userID, $androidIDToIgnore){
	$sql = "";

	//if android id to ignore is null (called from web) then return all android_ids for that user id
	if(empty($androidIDToIgnore)){
		$sql = 	"SELECT REG_ID FROM PROFILE WHERE USER_ID = ?";
	}
	//else get android IDs for the same email id except the one from which this request has come
	else{
		$sql = "SELECT REG_ID FROM PROFILE WHERE USER_ID = ? AND ANDROID_ID!=?";
	}

	if($stmt = $mysqli->prepare($sql)){
		if(empty($androidIDToIgnore)){
			$stmt->bind_param("s", $userID);	
		}
		else{
			$stmt->bind_param("ss", $userID, $androidIDToIgnore);	
		}
		$stmt->execute();
		$stmt->bind_result($temp_reg_ids);
		while($stmt->fetch()){
			$reg_id[] = $temp_reg_ids;
		}
		
		// Close the prepared statement.
		if($stmt != null){
			$stmt->close(); 
	    	}

		return $reg_id;
	}
}

//function to get max _id in case of web add in
function getMaxID($mysqli, $user_id){
	if($stmt = $mysqli->prepare("SELECT MAX(_ID) FROM VEH_TABLE WHERE USER_ID=?")){
		$stmt->bind_param("i", $user_id);
		$stmt->execute();
		$stmt->bind_result($temp_id);
		while($stmt->fetch()){
			$id = $temp_id;
		}
		
		// Close the prepared statement.
		if($stmt != null){
			$stmt->close(); 
	    	} 
		
		return ++$id;		  		
	}
}

//function to create records for vehicle in the Service table
function addVehRecIntoService($mysqli, $user_id, $vehid){
	//get max id of service table to start with
	$stmt = $mysqli->prepare("SELECT MAX(_ID) FROM SERVICE_TABLE WHERE USER_ID=?");
	$stmt->bind_param("i", $user_id);
	$stmt->execute();
	$stmt->bind_result($temp_id);
	while($stmt->fetch()){
		$id = $temp_id;
	}
	
	if($stmt = $mysqli->prepare("SELECT DISTINCT SERVICE_NAME, TYPE FROM SERVICE_TABLE WHERE USER_ID=?")){
		$stmt->bind_param("i", $user_id);
    		$stmt->execute(); 
    		$stmt->bind_result($temp_service_name, $temp_type);    	
		while($stmt->fetch()){	
			$service_name[] = $temp_service_name;
			$type[] = $temp_type;
		}
		
		$cnt = count($service_name);
		
		if($cnt>0){
			for($i=0; $i<$cnt; $i++){
				if($type[$i] == 1){			
					//increment id
					$id = ++$id;

					$stmt = $mysqli->prepare("INSERT INTO SERVICE_TABLE(USER_ID, _ID, VEHID, TYPE, SERVICE_NAME, RECURRING) VALUES(?, ?, ?, ?, ?, ?)");			
					$stmt->bind_param("iisisi", $user_id, $id, $vehid, $type[$i], $service_name[$i], $rec=1);
					$stmt->execute();
					
					//insert into sync
					insertIntoSyncTable($mysqli, $id, "add", $user_id, "SERVICE_TABLE", "");		
				}
				else if($type[$i] == 2){
					//increment id
					$id = ++$id;				
		
					$stmt = $mysqli->prepare("INSERT INTO SERVICE_TABLE(USER_ID, _ID, VEHID, TYPE, SERVICE_NAME, RECURRING) VALUES(?, ?, ?, ?, ?, ?)");
					$stmt->bind_param("iisisi", $user_id, $id, $vehid, $type[$i], $service_name[$i], $tmp=0);
					$stmt->execute();
					
					//insert into sync
					insertIntoSyncTable($mysqli, $id, "add", $user_id, "SERVICE_TABLE", "");
				}
			}
		}								
			
		// Close the prepared statement.
		if($stmt != null){
	    		$stmt->close(); 
	    	}				    		
	}
}

?>