<?php

class Tac_CustomImgCaptcha_Model_Captcha extends XenForo_Model
{

	
	public function getCaptchaByRand()
	{
		
		return $this->_getDb()->fetchRow('
			SELECT *
			FROM sf_customimgcaptcha_answers 
			ORDER BY RAND() LIMIT 0,1;
		');
	}

	
	public function getCaptchaByUUID($uuid)
	{
		return $this->_getDb()->fetchRow('
			SELECT *
			FROM sf_customimgcaptcha_answers 
			WHERE private_uuid = ?
		', $uuid);
	}


	public function getPrivateUUID($public_uuid)
	{
		return $this->_getDb()->fetchRow('
			SELECT private_uuid
			FROM sf_customimgcaptcha_uuids 
			WHERE public_uuid = ?
		', $public_uuid);
	}

	
	
	public function getCaptchaById($id)
	{
		return $this->_getDb()->fetchRow('
			SELECT *
			FROM sf_customimgcaptcha_answers 
			WHERE id = ?
		', $id);
	}

	
	public function getCaptchas(array $fetchOptions = array())
	{
		$limitOptions = $this->prepareLimitFetchOptions($fetchOptions);

		return $this->fetchAllKeyed($this->limitQueryResults(
			'
				SELECT *
				FROM sf_customimgcaptcha_answers
				ORDER BY id DESC
			', $limitOptions['limit'], $limitOptions['offset']
		), ';id');
	}

	public function getPrivateUUIDsAndAnswers(array $fetchOptions = array())
	{
		$limitOptions = $this->prepareLimitFetchOptions($fetchOptions);

		return $this->fetchAllKeyed($this->limitQueryResults(
			'
				SELECT private_uuid, answers, file_name, width,	height
				FROM sf_customimgcaptcha_answers
				ORDER BY id DESC
			', $limitOptions['limit'], $limitOptions['offset']
		), ';id');
	}	
	
	
	public function countRows()
	{
		return $this->_getDb()->fetchOne('
			SELECT  COUNT(id)
			FROM sf_customimgcaptcha_answers
		');
	}	
	
	
	public function deleteCaptchaByUUID($uuid)
	{
		$db = $this->_getDb();
		$db->delete('sf_customimgcaptcha_answers', 'private_uuid = ' . $db->quote($uuid));
	}
	
	
	
	public function hex2bin4oldPhp($h) {
		if (!is_string($h)) return null;
		$r = '';
		for ($a = 0; $a < strlen($h); $a += 2) 
		{
			$r .= chr(hexdec($h{$a}.$h{($a + 1)}));
		}
		return $r;
	}
	
	
	
	
	public function RunCaptchaInsertSQL($file, $delimiter = ';')
	{
	    set_time_limit(0);
	    if (is_file($file) === true)
	    {
	        $file = fopen($file, 'r');
	        
	        if (is_resource($file) === true)
	        {
	            $query = array();
	            while (feof($file) === false)
	            {
	                $query[] = fgets($file);
	                $newQuery = "";
	                if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1)
	                {
	                    $query = trim(implode('', $query));
	                    
	                    $rand = md5(rand());
						$randomPrefix = substr($rand, 0, 12); // to avoid colision when the same uuid is created (from same milisec)
			   			$private_uuid = uniqid($randomPrefix);
			   			
			   			
	                    $query = str_replace("INSERT INTO sf_customimgcaptcha_answers (img, img_type, private_uuid, file_name, width, height, answers, insert_time) VALUES (", '', $query);
	                    $remove = array("'",'"',")");
	                    $query = str_replace($remove, '', $query);
	                    $values = array_map('trim', explode(",",$query));
	                    
	                    /*
	                    $newQuery = "INSERT INTO sf_customimgcaptcha_answers (img, img_type, private_uuid, file_name, width, height, answers, insert_time) VALUES (".
	                    mysql_real_escape_string($values[0]).",". // img
	                    "'".mysql_real_escape_string($values[1])."',". // img_type
	                    "'".$private_uuid."',";
	                    "'".mysql_real_escape_string($values[1])."',". // file_name
	                    "'".mysql_real_escape_string($values[1])."',". // width
	                    "'".mysql_real_escape_string($values[1])."',". // height
	                    */
	                    
	                    $answers = "";
	                    //the answer are all the remaining values, except the last:
	                    for ($i = 6; $i < (count($values)-1); $i++) 
	                    {
	    					if($i != (count($values)-2))
	    					{
		    				//	$answers .= mysql_real_escape_string($values[$i]).",";
		    					$answers .= $values[$i].",";
		    				}
	    					else 
	    					{
		    				//	$answers .= mysql_real_escape_string($values[$i]);
		    					$answers .= $values[$i];
		    				}
						}
	                    $newQuery .= "'".$answers."',".
	                    time().")";
	                    
	                    //dont run query! Now use a datawriter for each insert:
	                    
	                    $writer = XenForo_DataWriter::create('Tac_CustomImgCaptcha_DataWriter_Captcha'); 
					   	$writer->set('private_uuid', $private_uuid); 
					   	$writer->set('img', $this->hex2bin4oldPhp(substr($values[0],2)));
					   	$writer->set('img_type', $values[1]);
					   	$writer->set('file_name', $values[3]);
					   	$writer->set('width', $values[4]);
					   	$writer->set('height', $values[5]);
					   	$writer->set('answers', $answers);      
					   	$writer->set('insert_time', time());         
					   	$writer->save();
	                   
	                    
	                    // dont flush at > 0, or XF throws:
	                    // An exception occurred: ob_end_clean(): failed to delete buffer. No buffer to delete in FrontController.php on line 164
	                    while (ob_get_level() > 1)
	                    {
	                        ob_end_flush();
	                    }
	                    flush();	                    
	                }
	
	                if (is_string($query) === true)
	                {
	                    $query = array();
	                }
	            }
	            return fclose($file);
	        }
	    }
	    
	    return false;
	}

	
	
	public function getObsfucatedUsername($input)
	{
		// problem, this could be filled out from other areas than the registration page, in which case we will have email/username
		
		// first check to see if the honey pots have been filled, if they have, just use those values
		if(array_key_exists('username' , $input) && $input['username'] != "abaf1b4e8d0e34afa3")
		{
			return $input['username'];
		}		
		// if not, we need to search through the input array to dig out username
		$inputNewKey = array();
		foreach($input as $key => $val)
		{
			if ($key != "_origRoutePath" && $key != "_matchedRoutePath" && $key != "register/register" ){
				$inputNewKey[] = $val;			
			}
		}
		
		if($inputNewKey){
			for ($i = 0; $i <= 4; $i++) 
			{
			    if ((count($inputNewKey) >= $i) && ($inputNewKey[$i] != "") && ($inputNewKey[$i] != "abaf1b4e8d0e34afa3")) 
				{
					return $inputNewKey[$i];   
				}
			}
		}

		return "";	
	}	

	/*
	public function countsPassed($public_uui)
	{
		// for each row, get the county dynamically (lots of queries, but it's something that should be done on the fly)
		
		
	}
	*/
		
	public function getObsfucatedEmail($input)
	{
	    // problem, this could be filled out from other areas than the registration page, in which case we will have email/username
	    
	    
	    
		// first check to see if the honey pots have been filled, if they have, just use those values
		if(array_key_exists('email' , $input) && $input['email'] != "x@819978f0-0b0f-11e2-892e-0800200c9a66.com")
		{
			return $input['email'];
		}
		if(array_key_exists('fb_token' , $input))
		{
			return "Via FaceBook";
		}
			
		// if not, we need to search through the input array to dig out email
		$inputNewKey = array();
		// for non SEO urls, the paths are sent.. remove these for constancy
		foreach($input as $key => $val)
		{
			if ($key != "_origRoutePath" && $key != "_matchedRoutePath" && $key != "register/register" && $key != "tutor-registration/register"  ){
				$inputNewKey[] = $val;			
			}			
		}
		// 8 - 12 are potential emails 
	 	for ($i = 5; $i <= 9; $i++) 
	 	{
		 	if(count($inputNewKey) > $i)
		 	{
			    if (($inputNewKey[$i] != "") && ($inputNewKey[$i] != "x@819978f0-0b0f-11e2-892e-0800200c9a66.com")) 
				{
					return $inputNewKey[$i];
				}
			}
		}
		
		return "";					
	}		
		
	
	public function getCaptchaTableSQL(){
	
		$rows = $this->getCaptchas();
    	$return = "";

	      foreach($rows as $row)
	      {
	      	$img = (isset($row['img']) ? '0x'.bin2hex($row['img']) : ''); 
	        $return .= 'INSERT INTO sf_customimgcaptcha_answers (img, img_type, private_uuid, file_name, width, height, answers, insert_time) VALUES (';
	        $return .= $img.', "'.$row['img_type'].'", "'.$row['private_uuid'].'", "'.$row['file_name'].'", "'.$row['width'].'", "'.$row['height'].'", "'.$row['answers'].'", "'.$row['insert_time'].'"'; 
	        $return.= ");\n\n\n";
	      }      
    	return $return;
	}

	
	
}