<?php
	// Create connection
	$conn = mysqli_connect('localhost', 'root', '', 'thydzik_misc');

	// Check connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	}
	
	$curl = curl_init();
	curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
	curl_setopt($curl, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows NT 6.1; rv:15.0) Gecko/20100101 Firefox/15.0.1');
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);

	
	$sql_latlon = 'SELECT * FROM `amex_latlon`';
	$result_latlon = mysqli_query($conn, $sql_latlon);
	
	while ($row_latlon = mysqli_fetch_assoc($result_latlon)) {
		
		echo "{$row_latlon['lat']}, {$row_latlon['lon']}\r\n";

		
		curl_setopt($curl, CURLOPT_URL, "https://bdaas.americanexpress.com/api/servicing/v1/maps/?&advanced_categories=Shop-Small&lat_lng={$row_latlon['lat']},{$row_latlon['lon']}&country_code=AU&page_size=100");
		
		
		if ($result = curl_exec($curl)) {
			
			$result_arr = json_decode($result, true);

			
			$merchants = $result_arr['merchants'];
			
			foreach ($merchants as $merchant) {
		
				$sql2 = "SELECT * FROM `amex_stores` WHERE `identifier`={$merchant['identifier']}";
				$query2 = mysqli_query($conn, $sql2);
				if (mysqli_num_rows($query2) == 0) {
					//add the store record
					
					$merchant['name'] = mysqli_real_escape_string($conn, cleanTxt($merchant['name']));
					$merchant['address_lines'] = mysqli_real_escape_string($conn, cleanTxt($merchant['address']['address_lines'][0]));
					$merchant['city'] = mysqli_real_escape_string($conn, cleanTxt($merchant['address']['city']));
					$merchant['state'] = mysqli_real_escape_string($conn, cleanTxt($merchant['address']['state']));
					$merchant['category'] = mysqli_real_escape_string($conn, cleanTxt($merchant['category_details']['level_2_categories'][0]));
					$merchant['postal_code'] = $merchant['address']['postal_code'];
					$merchant['cat_name'] = $merchant['category_details']['name'];


					$sql = "INSERT INTO `amex_stores` SET 
						`identifier`={$merchant['identifier']},
						`latitude`={$merchant['address']['latitude']},
						`longitude`={$merchant['address']['longitude']},
						`name`='{$merchant['name']}',
						`address_lines`='{$merchant['address_lines']}',
						`city`='{$merchant['city']}',
						`state`='{$merchant['state']}',
						`postal_code`={$merchant['postal_code']},
						`cat_name`='{$merchant['cat_name']}',
						`category`='{$merchant['category']}'";
		
					if (mysqli_query($conn, $sql) === false) {
						echo 'Error updating the table';
						test_write($sql);
					}
				}
			}
			
			//mark the lat/lon as queried
			$row_latlon['count'] = $row_latlon['count'] + 1;
			$sql_latlon2 = "UPDATE `amex_latlon` SET `count`={$row_latlon['count']} WHERE `ID`={$row_latlon['ID']}";
			mysqli_query($conn, $sql_latlon2);
		} else {
			echo "Returned empty string.\r\n";
		}
	}