<?php

require 'sql-crud-orm-models.php';

$quran_query= filter_input(INPUT_POST, 'quran_query', FILTER_SANITIZE_SPECIAL_CHARS);

//TODO add to ORM
function db_search_quran($query_key, $limit) {
	$db = &get_db(SQLITE3_OPEN_READONLY);


	$array_inputs = explode(" ",$query_key);
	if(count($array_inputs) == 2) {

   		$sql_str = 'SELECT * FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ?) LIMIT ?';
	    $prepare_sql = $db->prepare( $sql_str);
		$prepare_sql->bindValue(1, '%'.$array_inputs[0].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(2, '%'.$array_inputs[1].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(3, $limit, SQLITE3_INTEGER); 

		$select_query_result = $prepare_sql->execute();
		$array_return = array();
		if( is_a($select_query_result, 'SQLite3Result' )  )
		{
		   while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			   $array_return[]=$arrayrow;
		   }
		}
		$db->close();
		return $array_return;	
	}
	elseif(count($array_inputs) == 3) {
		$sql_str = 'SELECT * FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? AND rowid IN(SELECT rowid FROM quran WHERE translation LIKE ?) ) LIMIT ?';
	    $prepare_sql = $db->prepare( $sql_str);
		$prepare_sql->bindValue(1, '%'.$array_inputs[0].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(2, '%'.$array_inputs[1].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(3, '%'.$array_inputs[2].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(4, $limit, SQLITE3_INTEGER); 

		$select_query_result = $prepare_sql->execute();
		$array_return = array();
		if( is_a($select_query_result, 'SQLite3Result' )  )
		{
		   while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			   $array_return[]=$arrayrow;
		   }
		}
		$db->close();
		return $array_return;	
	}
	elseif(count($array_inputs) == 4) {
		$sql_str = 'SELECT * FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? AND rowid IN(SELECT rowid FROM quran WHERE translation LIKE ? and rowid IN (SELECT rowid FROM quran WHERE translation LIKE ?) ) ) LIMIT ?';
	    $prepare_sql = $db->prepare( $sql_str);
		$prepare_sql->bindValue(1, '%'.$array_inputs[0].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(2, '%'.$array_inputs[1].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(3, '%'.$array_inputs[2].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(4, '%'.$array_inputs[3].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(5, $limit, SQLITE3_INTEGER); 

		$select_query_result = $prepare_sql->execute();
		$array_return = array();
		if( is_a($select_query_result, 'SQLite3Result' )  )
		{
		   while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			   $array_return[]=$arrayrow;
		   }
		}
		$db->close();
		return $array_return;	
	}
	elseif(count($array_inputs) == 5) {
		$sql_str = 'SELECT * FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? and rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? AND rowid IN ( SELECT rowid FROM quran WHERE translation LIKE ?  ) ) ) ) LIMIT ?';
	    $prepare_sql = $db->prepare( $sql_str);
		$prepare_sql->bindValue(1, '%'.$array_inputs[0].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(2, '%'.$array_inputs[1].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(3, '%'.$array_inputs[2].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(4, '%'.$array_inputs[3].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(5, '%'.$array_inputs[4].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(6, $limit, SQLITE3_INTEGER); 

		$select_query_result = $prepare_sql->execute();
		$array_return = array();
		if( is_a($select_query_result, 'SQLite3Result' )  )
		{
		   while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			   $array_return[]=$arrayrow;
		   }
		}
		$db->close();
		return $array_return;	
	}
	elseif(count($array_inputs) == 6) {
		$sql_str = 'SELECT * FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? AND rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? and rowid IN (SELECT rowid FROM quran WHERE translation LIKE ? AND rowid IN ( SELECT rowid FROM quran WHERE translation LIKE ? and rowid IN ( SELECT rowid FROM quran WHERE translation LIKE ? )  ) ) ) ) LIMIT ?';
	    $prepare_sql = $db->prepare( $sql_str);
		$prepare_sql->bindValue(1, '%'.$array_inputs[0].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(2, '%'.$array_inputs[1].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(3, '%'.$array_inputs[2].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(4, '%'.$array_inputs[3].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(5, '%'.$array_inputs[4].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(6, '%'.$array_inputs[5].'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(7, $limit, SQLITE3_INTEGER); 

		$select_query_result = $prepare_sql->execute();
		$array_return = array();
		if( is_a($select_query_result, 'SQLite3Result' )  )
		{
		   while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			   $array_return[]=$arrayrow;
		   }
		}
		$db->close();
		return $array_return;	
	}	
	else 
	{
		$sql_str = 'SELECT * FROM quran WHERE translation LIKE ? LIMIT ?';
	    $prepare_sql = $db->prepare( $sql_str);
		$prepare_sql->bindValue(1, '%'.$query_key.'%', SQLITE3_TEXT); 
		$prepare_sql->bindValue(2, $limit, SQLITE3_INTEGER); 

		$select_query_result = $prepare_sql->execute();
		$array_return = array();
		if( is_a($select_query_result, 'SQLite3Result' )  )
		{
		   while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			   $array_return[]=$arrayrow;
		   }
		}
		$db->close();
		return $array_return;
	}

}


$array_values = db_search_quran($quran_query, 10);
$array_inputs = explode(" ",$quran_query);

foreach($array_values as $array_value) {

	echo "<tr>";
		echo "<td>";
			$final_value = $array_value['translation'];
			foreach($array_inputs as $input_value)
			{
				// str_replace search replace subject - case sensitive - sqlite not?
				$final_value = str_replace(strtolower($input_value), "<b style='color:red;'>".$input_value."</b>", strtolower($final_value));
			}


			echo "".$final_value;
		echo "</td>";
	echo "</tr>";
}

?>