<?php

/**
//NULL. The value is a NULL value.
//INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
//REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
//TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
//BLOB. The value is a blob of data, stored exactly as it was input.


notnull 	NOT NULL 	Ensures the 'not null' constraint in both application and database level
email 		Checks if value is valid email.
notblank 	NOT NULL 	Checks if value is not blank.
nospace 		Checks if value has no space chars.
past 	CHECK constraint 	Checks if value is a date in the past.
future 		Checks if value is a date in the future.
minlength(length) 		Checks if value satisfies the minimum length.
country 		Checks if value is a valid country code.
ip 		Checks if value is valid IP (internet protocol) address.
htmlcolor 		Checks if value is valid html color.
range(min, max) 	CHECK constraint 	Checks if value is in range specified by arguments.
unique 	UNIQUE constraint 	Checks if value is unique in its database table.
regexp(expression) 		Checks if value matches a given regexp.
creditcard 		Checks whether the string is a well formated credit card number
digits(int, frac) 	Precision and scale 	Checks if given value has //int// number of integer digits and //frac// number of fractional digits
date 		Checks if given value is a valid date.:
readonly 		Checks if a field is modified and if it is returns false to force a field as readonly
unsigned 		Checks if given integer value is unsigned.
usstate 		Checks if given value is a valid US state code.
**/
// array of tables
// 0=table TABLE NAME, 
// 1=array of fields and restrictions
// 


//ORDER IS RESERVED WORD ORDER BY?

/**
	table
		field name | sql type | html type | html values, default value?
		
		1 item can belong to many kits
		1 kit can have many items = many to many so need separate table
		

**/
$db_schema = array (
	array("HORSEY", 
		array( 
		  	array("HORSEY_ID", "INTEGER" ),  
  			array("HORSEY_NAME", "TEXT" ),  
  			array("HORSEY_PRICE", "INTEGER" ), 
  			array("HORSEY_VERSION", "INTEGER" ), 
			array("HORSEY_IS_ACTIVE", "INTEGER", array("RADIO", "TRUE,FALSE") ),    	
			array("HORSEY_DESCRIPTION", "TEXT" ), 
			array("HORSEY_GEOMETRY", "TEXT",array("RADIO", "TRUE,FALSE") ), 
			array("HORSEY_BRANCH", "TEXT" , array("SELECT", "MEXICO,CANADA,USA,BRAZIL,AUSTRALIA")), 	    		 
  		)  
  	),

);


$TAB="\t";


function array_map_bind_value($field_name, $field_type )  {
	
	return 	'$prepare_sql->bindValue(\':'.$field_name.'\', $'.$field_name.', SQLITE3_'.$field_type.');';
}

function array_map_update_table($field_name )  {
	return 	$field_name.'= :'.$field_name;
}

function array_map_create_controller($field_name )  {
	return 	'$'.$field_name.'= filter_input(INPUT_GET, \''.$field_name.'\', FILTER_SANITIZE_SPECIAL_CHARS);';
}


function array_map_create_table($field_name, $field_type )  {
	return 	$field_name.' '.$field_type;
}

function array_map_before($field_name, $before )  {
	return 	$before.$field_name;
}

function array_dollar_before_comma($field_names) {
	$str='';
	foreach($field_names as $field_name) {
		$str.='$'.$field_name.',';
	}
	return substr($str, 0,-1);
}

function array_colon_comma($field_names) {
	$str='';
	foreach($field_names as $field_name) {
		$str.=':'.$field_name.',';
	}
	return substr($str, 0,-1);
}

function array_copy_sql_statement($field_names) {
	$str='';
	
	//$array_table[0]['height'],$array_table[0]['size'],

		
	foreach($field_names as $field_name) {
		$str.='$array_table[0][\''.$field_name.'\'],';
	}
	return substr($str, 0,-1);
}

function get_table_array_str($field_names) {
	$str='';
	
	//$array_table[0]['height'],$array_table[0]['size'],

		
	foreach($field_names as $field_name) {
		$str.='$'.$field_name.'= $db_array_table[0][\''.$field_name.'\'];'.PHP_EOL;
	}
	return $str;
}

function surround_fields($field_names, $beforestr, $afterstr) {
	$str='';		
	foreach($field_names as $field_name) {
		$str.=$beforestr.$field_name.$afterstr.PHP_EOL;
	}
	return $str;
}

//TODO dont escape when store in database, escape special chars when output?? to enable search
function get_db_create_table_str($field_name, $field_type )  {
	$str='';
	for($i = 0; $i < count($field_name); $i++) {
		if($i==0) {
			$str.=$field_name[$i].' '.$field_type[$i]." PRIMARY KEY,";
		} else {
			$str.=$field_name[$i].' '.$field_type[$i].",";
		}
	}
	return 	substr($str, 0,-1);
}


function array_map_form_create($field_name, $field_type, $field_input_type )  {
	$str = '<label for=\''.$field_name.'\'>'.$field_name.'</label>';
	if($field_input_type == null) {
		$str .= '<input type=\'text\' name=\''.$field_name.'\' id=\''.$field_name.'\' />';
	} elseif ( $field_input_type[0] == 'DATE') {
		$str .= '<input type=\'DATE\' name=\''.$field_name.'\' id=\''.$field_name.'\' />';		
	} elseif ( $field_input_type[0] == 'FILE') {

		$str .= '<input type=\'FILE\' name=\''.$field_name.'\' id=\''.$field_name.'\' />';		
	} elseif ( $field_input_type[0] == 'RADIO') {
		$radio_list = explode(",", $field_input_type[1]);
		foreach($radio_list as $radio_option) {
			$str .= '<label for=\''.$field_name.'\'>'.$radio_option.'</label>';			
			$str .= '<input type=\'RADIO\' name=\''.$field_name.'\' value=\''.$radio_option.'\' />';	
		}
	}	
 	elseif ( $field_input_type[0] == 'SELECT') {
		$select_list = explode(",", $field_input_type[1]);
		$str .= '<select name=\''.$field_name.'\'>';			
		foreach($select_list as $select_option) {

			$str .= '<option value=\''.$select_option.'\'>'.$select_option.'</option>';	
		}	
		$str .= '</select>';
	}
	$str .= '<br />';
	return $str;
}

function array_map_form_update($field_name, $field_type, $field_input_type )  {
	$str = '<label for=\''.$field_name.'\'>'.$field_name.'</label>';
	if($field_input_type == null) {
		$str .= '<input type=\'text\' name=\''.$field_name.'\' id=\''.$field_name.'\' value=\'<?php echo $'.$field_name.'; ?>\' />'.PHP_EOL;
	} elseif ( $field_input_type[0] == 'DATE') {
		$str .= '<input type=\'DATE\' name=\''.$field_name.'\' id=\''.$field_name.'\' value=\'<?php echo $'.$field_name.'; ?>\' />'.PHP_EOL;		
	} elseif ( $field_input_type[0] == 'FILE') {

		$str .= '<input type=\'FILE\' name=\''.$field_name.'\' id=\''.$field_name.'\' />'.PHP_EOL;		
	} elseif ( $field_input_type[0] == 'RADIO') {
		$radio_list = explode(",", $field_input_type[1]);
		foreach($radio_list as $radio_option) {
			$str .= '<label for=\''.$field_name.'\'>'.$radio_option.'</label>'.PHP_EOL;			
			$str .= '<input type=\'RADIO\' name=\''.$field_name.'\' value=\''.$radio_option.'\' />'.PHP_EOL;	
		}
	}	
 	elseif ( $field_input_type[0] == 'SELECT') {
		$select_list = explode(",", $field_input_type[1]);
		$str .= '<select name=\''.$field_name.'\'>'.PHP_EOL;	
		
		$str .= PHP_EOL.'<?php '.PHP_EOL;
		$str .= '$'.$field_name.'_options = array('.implode(',', array_map(function($opt){
			return '\''.$opt.'\'';
		}, $select_list)).');';

		
		$str .= 'foreach ($'.$field_name.'_options as $html_option) {';
		$str .= '    $is_selected = ($html_option == $'.$field_name.') ? \'selected\' : \'\';';
		$str .= '    echo "<option value=\'$html_option\' $is_selected>$html_option</option>";'.PHP_EOL;
		$str .= '}'.PHP_EOL;
		$str .= '?>'.PHP_EOL;
		
		
		$str .= '</select>'.PHP_EOL;
	}
	$str .= '<br />';
	return $str;
}

function array_map_create_row_controller_inputs($field_name, $field_type, $field_input_type )  {
	$str ='';
	if($field_input_type == null) {
		return $str;
	}
	elseif ( $field_input_type[0] == 'FILE') {

		
		//TODO must be multipart/form-data and form submit type must be POST not get
		$str .= "if(isset(\$_FILES['$field_name']) && is_uploaded_file(\$_FILES['$field_name']['tmp_name'])  ) {";
			
		$str .= "\$file_path = \$_FILES['$field_name']['tmp_name'];";
			
		$str .= "\$finfo = new finfo(FILEINFO_MIME);";
		$str .= "\$mimetype = \$finfo->file(\$file_path);";  
		$str .= "\$"."$field_name"."_BASE64 = base64_encode(file_get_contents(\$file_path));";
		$str .= " }";	
	}
	
	return $str;
}


$sql_model = "";
$create_sql_statement="";
$select_sql_statement="";
$copy_sql_statement="";
$update_sql_statement="";
$delete_sql_statement="";
$drop_create_table_sql="";

//EVERY FUNCTION has a server connection!
$server_connection_read_write = <<<'NOWDOC'
	$ROOT=''; //save root of web directory TODO somewhere behind public www
	//$read_write_mode=SQLITE3_OPEN_READONLY | SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE
	$db = new SQLite3($ROOT.'sqlmodels.sqlite3',SQLITE3_OPEN_READWRITE);//pdo create sqlite table
	$db->busyTimeout(60000);//60 seconds
	$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html 
	$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry;
	$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier
	$db->query("PRAGMA encoding = 'UTF-8';"); //watch php WHERE clause will not work since ISO encoding vs UTF 8,
	$db->query("PRAGMA foreign_keys = ON;");  //off by default , each connection must call this

NOWDOC;

$server_connection_read_only = <<<'NOWDOC'
	$ROOT=''; //save root of web directory TODO somewhere behind public www
	//$read_write_mode=SQLITE3_OPEN_READONLY | SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE
	$db = new SQLite3($ROOT.'sqlmodels.sqlite3',SQLITE3_OPEN_READONLY);//pdo create sqlite table
	$db->busyTimeout(60000);//60 seconds
	$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html 
	$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry;
	$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier
	$db->query("PRAGMA encoding = 'UTF-8';"); //watch php WHERE clause will not work since ISO encoding vs UTF 8,
	$db->query("PRAGMA foreign_keys = ON;");  //off by default , each connection must call this

NOWDOC;

$server_connection_create_table = <<<'NOWDOC'
	$ROOT=''; //save root of web directory TODO somewhere behind public www
	//$read_write_mode=SQLITE3_OPEN_READONLY | SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE
	$db = new SQLite3($ROOT.'sqlmodels.sqlite3',SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);//pdo create sqlite table
	$db->busyTimeout(60000);//60 seconds
	$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html 
	$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry;
	$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier
	$db->query("PRAGMA encoding = 'UTF-8';"); //watch php WHERE clause will not work since ISO encoding vs UTF 8,
	$db->query("PRAGMA foreign_keys = ON;");  //off by default , each connection must call this

NOWDOC;


foreach ($db_schema as $table) {
	$table_name =  $table[0];
	//echo "TABLE NAME:".$table_name."\n";
	$fields = $table[1];

	$array_field_names = array();
	$array_field_types = array();
	foreach($fields as $field) {
		$field_name = $field[0];
		$field_type = $field[1];
		//echo "    FIELD NAME:".$field_name."\n";
		//echo "        FIELD TYPE:".$field_type."\n";
		$array_field_names[] = $field_name;
		$array_field_types[] = $field_type;
	}
	
	$array_field_names_without_id = array_slice($array_field_names,1);


	$bind_value_array_string = implode(PHP_EOL,	array_map('array_map_bind_value',$array_field_names,$array_field_types) );
	
	$bind_value_array_string_without_id = implode(PHP_EOL,	
		array_map('array_map_bind_value', array_slice($array_field_names,1), array_slice($array_field_types,1) ) );
	
	$field_name_joined_comma = implode(', ', $array_field_names);
	$fill_dollars = array_fill(0, count($array_field_names), '$');
	$fill_colons = array_fill(0, count($array_field_names), ':');

	$fill_dollars_without_id = array_fill(0, count($array_field_names_without_id), '$');
		

	$field_names_joined_dollar_comma = implode(', ', array_map('array_map_before', $array_field_names , $fill_dollars) );
	$field_names_joined_colon_comma = implode(', ', array_map('array_map_before',$array_field_names , $fill_colons) );
	
	//$field_name_values_joined = array_map('array_map_create_table', $array_field_names,$array_field_types );
	//$field_name_values_joined_comma = implode(', ',$field_name_values_joined );
	

	$drop_create_table_sql .= '   $db->exec(\' DROP table IF EXISTS '.$table_name.';\');'.PHP_EOL;
	$drop_create_table_sql .= '   $db->exec(\' CREATE TABLE IF NOT EXISTS '.$table_name.' (';
	//INTEGER PRIMARY KEY
	$drop_create_table_sql .= get_db_create_table_str($array_field_names,$array_field_types);
	$drop_create_table_sql .= ');\');'.PHP_EOL;

		
	$create_sql_statement .= "function DB_INSERT_".$table_name."(";
	$create_sql_statement .= array_dollar_before_comma($array_field_names_without_id);
	$create_sql_statement .= ') {'.PHP_EOL;
	$create_sql_statement .= $server_connection_read_write.PHP_EOL;	
	$create_sql_statement .= '    $prepare_sql = $db->prepare( \'INSERT INTO '.$table_name.' (';	
	$create_sql_statement .= implode(', ', $array_field_names_without_id);	
	$create_sql_statement .= ') VALUES (';	
	$create_sql_statement .= array_colon_comma($array_field_names_without_id);	
	$create_sql_statement .= ')\');'.PHP_EOL.PHP_EOL;
	$create_sql_statement .= $bind_value_array_string_without_id.PHP_EOL;		
	$create_sql_statement .= '   $prepare_sql->execute();'.PHP_EOL;	
	$create_sql_statement .= '   $last_insert_row_id = $db->lastInsertRowID();'.PHP_EOL;		
	$create_sql_statement .= '   $db->close();'.PHP_EOL;
	$create_sql_statement .= '   return $last_insert_row_id;'.PHP_EOL;		
	$create_sql_statement .= '}'.PHP_EOL.PHP_EOL;		


	$update_header_sql = implode(', ', array_map('array_map_update_table', $array_field_names_without_id));
	$update_sql_statement .= "function DB_UPDATE_".$table_name."(";
	$update_sql_statement .= $field_names_joined_dollar_comma;
	$update_sql_statement .= ') {'.PHP_EOL;
	$update_sql_statement .=  $server_connection_read_write.PHP_EOL;	
	$update_sql_statement .= '   $prepare_sql = $db->prepare( \'UPDATE '.$table_name.' SET ';	
	$update_sql_statement .= $update_header_sql;	
	$update_sql_statement .= ' WHERE '.$table_name.'_ID=:'.$table_name.'_ID;\');'.PHP_EOL;
	$update_sql_statement .= $bind_value_array_string.PHP_EOL;		
	$update_sql_statement .= '   $prepare_sql->execute();'.PHP_EOL;	
	$update_sql_statement .= '   $db->close();'.PHP_EOL;		
	$update_sql_statement .= '}'.PHP_EOL.PHP_EOL;		
	
	$delete_sql_statement .= "function DB_DELETE_".$table_name."($";
	$delete_sql_statement .= $table_name."_ID";
	$delete_sql_statement .= ') {'.PHP_EOL;
	$delete_sql_statement .=  $server_connection_read_write.PHP_EOL;	
	$delete_sql_statement .= '   $prepare_sql = $db->prepare( \'DELETE FROM '.$table_name.' WHERE '.$table_name.'_ID = ?\');'.PHP_EOL;	
	$delete_sql_statement .= '   $prepare_sql->bindValue(1, $'.$table_name.'_ID, SQLITE3_INTEGER); '.PHP_EOL;		
	$delete_sql_statement .= '   $prepare_sql->execute();'.PHP_EOL;	
	$delete_sql_statement .= '   $db->close();'.PHP_EOL;
	$delete_sql_statement .= '}'.PHP_EOL.PHP_EOL;

	//shallow copy? need deeper copy with reference tables
	$copy_sql_statement .= "function DB_COPY_".$table_name."($";
	$copy_sql_statement .= $table_name."_ID";
	$copy_sql_statement .= ') {'.PHP_EOL;

	$copy_sql_statement .= '   $array_table = DB_SELECT_EXACT_'.$table_name.'_ID($'.$table_name.'_ID);'.PHP_EOL;	
	$copy_sql_statement .= '   $last_insert_row_id = DB_INSERT_'.$table_name.'('.PHP_EOL;	
	$copy_sql_statement .= array_copy_sql_statement($array_field_names_without_id);	
	$copy_sql_statement .= '   );'.PHP_EOL;
	$copy_sql_statement .= '   return $last_insert_row_id;'.PHP_EOL.'}'.PHP_EOL;		
/**
function db_copy_door($door_id) {
	$array_door =db_select_door($door_id);
	error_log(count($array_door));
	//copy door
	db_create_door(	$array_door[0]['width'],
		$array_door[0]['height'],
		$array_door[0]['gage'],
		$array_door[0]['handing'],
		$array_door[0]['order_id'], 
		$array_door[0]['door_quantity'],
		$array_door[0]['description']
		);
	//$width,$height,$gage,$handing,$order_id, $quantity, $description, $front_dxf,$back_dxf
	//need to get parts
	//need to get door parameters
}**/
		
	
		//db_SELECT_ORDER_EXACT_ORDER_ID
		//db_SELECT_ORDER_EXACT_ORDER_NAME
		//db_SELECT_ORDER_LIKE_ORDER_NAME
		//db_SELECT_ORDER_RANGE_ORDER_ID
		//variables field name, data type, range,exact,like
	foreach($fields as $field) {
		$field_name = $field[0];
		$field_type = $field[1];
		//TODO like might need %% quotations?
		$search_criteria=array('RANGE','EXACT','LIKE');
		foreach($search_criteria as $search_criterion) {
			if($search_criterion =='LIKE' && $field_type=='INTEGER') {
				continue;
			}
			if($search_criterion =='RANGE' && $field_type=='TEXT') {
				continue;
			}
			$select_sql_sub_statement = "function DB_SELECT_".$table_name."_".$search_criterion."_".$field_name."(";

			if ($search_criterion =='RANGE') {
				$select_sql_sub_statement .= '$LOWER_RANGE, $UPPER_RANGE';
			} 
			elseif ($search_criterion =='LIKE') {
				$select_sql_sub_statement .= '$LIMIT';
			}
			else {
				$select_sql_sub_statement .= '$'.$field_name;
			}
			$select_sql_sub_statement .= ') {'.PHP_EOL;
			$select_sql_sub_statement .=  $server_connection_read_only.PHP_EOL;	
			
			//need to continue or break out loop otherwise dangling functions TODO
			if($search_criterion == 'EXACT') {
			
				$select_sql_sub_statement .= '   $prepare_sql = $db->prepare( \'SELECT * FROM '.$table_name.' WHERE '.$field_name.' = ?\');'.PHP_EOL;	
				$select_sql_sub_statement .= '   $prepare_sql->bindValue(1, $'.$field_name.', SQLITE3_'.$field_type.'); '.PHP_EOL;		
			} 
			elseif ($search_criterion =='LIKE' && $field_type=='TEXT') {
				$select_sql_sub_statement .= '   $prepare_sql = $db->prepare( \'SELECT * FROM '.$table_name.' WHERE '.$field_name.' LIKE ?\');'.PHP_EOL;	
				$select_sql_sub_statement .= '   $prepare_sql->bindValue(1, \'%\'.$'.$field_name.'.\'%\', SQLITE3_'.$field_type.'); '.PHP_EOL;	
				$select_sql_sub_statement .= '   $prepare_sql->bindValue(2, $LIMIT, SQLITE3_INTEGER); '.PHP_EOL;						
			} 
			elseif ($search_criterion =='RANGE' && ($field_type=='INTEGER' || $field_type=='REAL' ) ) {
				$select_sql_sub_statement .= '   $prepare_sql = $db->prepare( \'SELECT * FROM '.$table_name.' WHERE '.$field_name.' >= ? AND '.$field_name.' <= ? \');'.PHP_EOL;	
				$select_sql_sub_statement .= '   $prepare_sql->bindValue(1, $LOWER_RANGE, SQLITE3_INTEGER); '.PHP_EOL;	
				$select_sql_sub_statement .= '   $prepare_sql->bindValue(2, $UPPER_RANGE, SQLITE3_INTEGER); '.PHP_EOL;			
			
						
			}
			

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

	//full text search using nested 
	foreach($fields as $field) 
	{
		$field_name = $field[0];
		$field_type = $field[1];


		if($field_type=='TEXT') 
		{

			//no space after nowdoc
			$sql_statement_nowdoc = <<<'NOWDOC'
		function DB_SELECT_[[SQL_TABLE_NAME]]_SEARCH_[[SQL_FIELD_NAME]] ( $[[SQL_FIELD_NAME]], $limit ) {
			[[SQL_CONNECTION_STRING]]
			$array_inputs = explode(' ', $[[SQL_FIELD_NAME]]);
			if(count($array_inputs) == 2) {

		   		$sql_str = 'SELECT * FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] 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 [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN(SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] 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 [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN(SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? and rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] 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 [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? and rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN ( SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] 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 [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? and rowid IN (SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? AND rowid IN ( SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? and rowid IN ( SELECT rowid FROM [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] 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 [[SQL_TABLE_NAME]] WHERE [[SQL_FIELD_NAME]] LIKE ? LIMIT ?';
				$prepare_sql = $db->prepare( $sql_str);
				$prepare_sql->bindValue(1, '%'.$HORSEY_CODE.'%', 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;
			}
		}		
NOWDOC;
			//search  replacement thetext
			$sql_statement_nowdoc = str_replace('[[SQL_TABLE_NAME]]', $table_name,  $sql_statement_nowdoc);	
			$sql_statement_nowdoc = str_replace('[[SQL_FIELD_NAME]]', $field_name,  $sql_statement_nowdoc);	
			$sql_statement_nowdoc = str_replace('[[SQL_CONNECTION_STRING]]', $server_connection_read_only,  $sql_statement_nowdoc);	
					
			$select_sql_statement .=$sql_statement_nowdoc.PHP_EOL.PHP_EOL;
		}
	}

}


//copy
//joins children
//check if id exists
//check if name exists etc
//find


$init_db = 'function init_db() {';
$init_db .= PHP_EOL.$server_connection_create_table.PHP_EOL.$drop_create_table_sql.'}'.PHP_EOL;


//echo $create_sql_statement.PHP_EOL;
//echo $select_sql_statement.PHP_EOL;
//echo $update_sql_statement.PHP_EOL;
//echo $delete_sql_statement.PHP_EOL;
//echo $drop_create_table_sql.PHP_EOL;
$txt='<?php '.PHP_EOL.$init_db.$create_sql_statement.$update_sql_statement.$delete_sql_statement.$copy_sql_statement.$select_sql_statement.PHP_EOL.'?>';

$myfile = fopen("sql-crud-orm-models.php", "w") ;
fwrite($myfile, $txt);
fclose($myfile);


//PHP forms==============PHP FORMs================

	//view tabular table  =  view-tabular-TABLE.php
	//view form table = view-form-TABLE.php
	//update table controller  =   update-row-TABLE.php
	//delete table controller  =   delete-row-TABLE.php
	//create table controller  =   create-row-TABLE.php
	//copy table controller  =   copy-row-TABLE.php
	
	//search filter function
	

//controllers  CONTROLLERS PHP
foreach ($db_schema as $table) {
	$table_name =  $table[0];
	//echo "TABLE NAME:".$table_name."\n";
	$fields = $table[1];

	$array_field_names = array();
	$array_field_types = array();
	$array_field_input_types = array();
	foreach($fields as $field) {
		$field_name = $field[0];
		$field_type = $field[1];
		if(array_key_exists(2, $field)) {
		
			//echo "true".$field[2][0];
			$array_field_input_types[] = $field[2];
		} else {
			$array_field_input_types[] = null;
		}
		

		//echo "    FIELD NAME:".$field_name."\n";
		//echo "        FIELD TYPE:".$field_type."\n";
		$array_field_names[] = $field_name;
		$array_field_types[] = $field_type;
	}
	

	$create_row_controller = '';
	$select_table_controller = '';
	$update_row_controller = '';
	$delete_row_controller = '';
	$copy_row_controller = '';
	
	$array_field_names_without_id = array_slice($array_field_names,1);
	$array_field_types_without_id = array_slice($array_field_types,1);
	$array_field_input_types_without_id = array_slice($array_field_input_types,1);
			
	$create_row_controller .= '<?php require \'sql-crud-orm-models.php\';'.PHP_EOL;
	$create_row_controller .= implode(PHP_EOL, array_map('array_map_create_controller', $array_field_names_without_id));
	
	//handle file upload
	$create_row_controller .= implode(PHP_EOL,array_map('array_map_create_row_controller_inputs', $array_field_names_without_id, $array_field_types_without_id, $array_field_input_types_without_id));
	

	$create_row_controller .= PHP_EOL.'DB_INSERT_'.$table_name.'(';
	$create_row_controller .= array_dollar_before_comma( $array_field_names_without_id);
	$create_row_controller .= ');'.PHP_EOL;
	$create_row_controller .= '?>';
	
	$update_row_controller .= '<?php require \'sql-crud-orm-models.php\';'.PHP_EOL;
	$update_row_controller .= implode(PHP_EOL, array_map('array_map_create_controller', $array_field_names));
	$update_row_controller .= PHP_EOL.'DB_UPDATE_'.$table_name.'(';
	$update_row_controller .= array_dollar_before_comma( $array_field_names);
	$update_row_controller .= ');'.PHP_EOL;
	$update_row_controller .= '?>';
	$filehandle = fopen("update_row_".$table_name."_controller.php", "w") ;
	fwrite($filehandle, $update_row_controller);
	fclose($filehandle);

	$delete_row_controller .= '<?php require \'sql-crud-orm-models.php\';'.PHP_EOL;
	$delete_row_controller .= 	'$'.$table_name.'_ID= filter_input(INPUT_GET, \''.$table_name.'_ID\', FILTER_SANITIZE_SPECIAL_CHARS);';
	$delete_row_controller .= PHP_EOL.'DB_DELETE_'.$table_name.'(';
	$delete_row_controller .= '$'.$table_name.'_ID';
	$delete_row_controller .= ');'.PHP_EOL;
	$delete_row_controller .= '?>';	
	

	//todeo get rid of id for create update fiunctioms TODO
	//join TODO

	
	$filehandle = fopen("create_row_".$table_name."_controller.php", "w") ;
	fwrite($filehandle, $create_row_controller);
	fclose($filehandle);


	$filehandle = fopen("delete_row_".$table_name."_controller.php", "w") ;
	fwrite($filehandle, $delete_row_controller);
	fclose($filehandle);
	
	// create form table  multipart/form-data can be used on all but will not be efficient | use post on all
	$create_row_form = '<form method=\'get\' action=\'/create_row_'.$table_name.'_controller.php\' enctype=\'application/x-www-form-urlencoded\'>';
	$create_row_form .= implode('<br/>',array_map('array_map_form_create', $array_field_names_without_id, $array_field_types_without_id, $array_field_input_types_without_id));
	$create_row_form .= '<input type=\'submit\' value=\'Create\' />';	
	$create_row_form .= '</form>';

	$filehandle = fopen("create_row_".$table_name."_form.php", "w") ;
	fwrite($filehandle, $create_row_form);
	fclose($filehandle);

	//update form	 
	

	$update_row_form = '<?php require \'sql-crud-orm-models.php\';'.PHP_EOL;
	$update_row_form .= '$'.$table_name.'_ID= filter_input(INPUT_GET, \''.$table_name.'_ID\', FILTER_SANITIZE_SPECIAL_CHARS);'.PHP_EOL;
	$update_row_form .= '$db_array_table = DB_SELECT_'.$table_name.'_EXACT_'.$table_name.'_ID($'.$table_name.'_ID);'.PHP_EOL;  
	//TODO fix all update forms not workin need if populate array etc
	$update_row_form .= get_table_array_str($array_field_names);

	$update_row_form .= '?>'.PHP_EOL.'<form method=\'get\' action=\'/update_row_'.$table_name.'_controller.php\' enctype=\'application/x-www-form-urlencoded\'>'.PHP_EOL;
	$update_row_form .= implode('<br/>'.PHP_EOL,array_map('array_map_form_update', $array_field_names, $array_field_types, $array_field_input_types));
	$update_row_form .= '<input type=\'submit\' value=\'Update\' />'.PHP_EOL;	
	$update_row_form .= '</form>'.PHP_EOL;

	$filehandle = fopen("update_row_".$table_name."_form.php", "w") ;
	fwrite($filehandle, $update_row_form);
	fclose($filehandle);
	
	//admin table??
			
}

$base_html = <<<'NOWDOC'
<!DOCTYPE html>
<html lang="en">
	<head>
		 <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- important for responsive web design -->
		 <meta name="supported-color-schemes" content="light dark">
		<meta charset="UTF-8">
		<style>
		@media (prefers-color-scheme: dark) {
			  html {
			    color-scheme: dark;
			    font-family:sans-serif;
			    background-color:black;
			  }
			    a:link,a:visited,a:hover,a:active {
				color: lightblue;
			    }


			}
			table, th, td {
  border: 1px inset;
}
		</style>
		<title>{{{HTML_TITLE}}}</title>
	</head>
	

	<body onload="document.getElementById('{{{ELEMENT_TO_FOCUS}}}').focus(); ">
	

{{{HTML_LINKS_HEADER}}}

{{{HTML_BODY_CONTENT}}}

	</body>
</html>
NOWDOC;


$admin_body = <<<'NOWDOC'
{{{HTML_ADMIN_LINKS}}}

<a href="init_db.php">&#128465;&#65039; Reset Database</a>	 	
NOWDOC;

$admin_index_html = str_replace( '{{{HTML_BODY_CONTENT}}}' , $admin_body, $base_html); 
$index_admin_links='';
foreach ($db_schema as $table) {
	$table_name =  $table[0];
	$index_admin_links .= '<a href=\'table_list_' . $table_name . '.php\'>' .$table_name. '</a> <br/ >'.PHP_EOL;
}
$admin_index_html = str_replace('{{{HTML_TITLE}}}'	, 'Admin Index', $admin_index_html);
$admin_index_html = str_replace('{{{HTML_LINKS_HEADER}}}'	, '', $admin_index_html);
$admin_index_html = str_replace('{{{HTML_ADMIN_LINKS}}}'	, $index_admin_links, $admin_index_html);


$filehandle = fopen("index.php", "w") ;
fwrite($filehandle, $admin_index_html);
fclose($filehandle);


foreach ($db_schema as $table) 
{
	$table_name =  $table[0];
	$fields = $table[1];
	$array_field_names = array();
	$array_field_types = array();
	$array_field_input_types = array();
	
	foreach($fields as $field) 
	{
		$field_name = $field[0];
		$field_type = $field[1];
		if(array_key_exists(2, $field)) {
			$array_field_input_types[] = $field[2];
		} else {
			$array_field_input_types[] = null;
		}
		$array_field_names[] = $field_name;
		$array_field_types[] = $field_type;
	}
	

	$list_html_table = "<div style='overflow-x:auto;'><table><thead><tr>";
	$list_html_table .= surround_fields($array_field_names , "<th>", "</th>");
	$list_html_table .= "</tr></thead><tbody>".PHP_EOL."<?php ".PHP_EOL;	
	$list_html_table .= "require 'sql-crud-orm-models.php';".PHP_EOL;
	
	$list_html_table .= '$db_table = DB_SELECT_'.$table_name.'_RANGE_'.$table_name.'_ID(0, 1000);';
	$list_html_table .= 'foreach($db_table as $table_row)';
	$list_html_table .= '{';
	$list_html_table .= 'echo \'<tr>\';';
	$list_html_table .= surround_fields($array_field_names , 'echo \'<td>\'.$table_row[\'' , '\'].\'</td>\';');				
	$list_html_table .= 'echo \'</tr>\';';
	$list_html_table .= '}';
	
	$list_html_table .= PHP_EOL."?>".PHP_EOL."</tbody></div>";	
	
	$crud_links = "<a href='create_row_".$table_name."_form.php'>Insert</a> ";
	$crud_links .= "<a href='upload_csv_".$table_name."_form.php'>Upload CSV</a> ";
	$crud_links .= "<a href='scroll_list_".$table_name.".php'>Scroll List</a> ";
	$crud_links .= "Default Filter Replace ";
	
	$table_list_html = '';
	$table_list_html =  str_replace( '{{{HTML_BODY_CONTENT}}}' , $list_html_table, $base_html); 
	$table_list_html = str_replace('{{{HTML_TITLE}}}'	, $table_name.' List', $table_list_html);
	$table_list_html = str_replace('{{{HTML_LINKS_HEADER}}}'	,$crud_links, $table_list_html);
	
	$filehandle = fopen('table_list_'.$table_name.".php", "w") ;
	fwrite($filehandle, $table_list_html);
	fclose($filehandle);
	
	
}


foreach ($db_schema as $table) 
{
	$table_name =  $table[0];
	$fields = $table[1];
	$array_field_names = array();
	$array_field_types = array();
	$array_field_input_types = array();
	
	foreach($fields as $field) 
	{
		$field_name = $field[0];
		$field_type = $field[1];
		if(array_key_exists(2, $field)) {
			$array_field_input_types[] = $field[2];
		} else {
			$array_field_input_types[] = null;
		}
		$array_field_names[] = $field_name;
		$array_field_types[] = $field_type;
	}
	

	$list_html = "<div style='overflow-x:auto;'>";

	$list_html .= PHP_EOL."<?php ".PHP_EOL;	
	$list_html .= "require 'sql-crud-orm-models.php';".PHP_EOL;
	
	$list_html .= '$db_table = DB_SELECT_'.$table_name.'_RANGE_'.$table_name.'_ID(0, 1000);';
	$list_html .= 'foreach($db_table as $table_row)';
	$list_html .= '{';
	$list_html .= 'echo \'<ul>\';';
	$list_html .= surround_fields($array_field_names , 'echo \'<li>\'.$table_row[\'' , '\'].\'</li>\';');				
	$list_html .= 'echo \'</ul>\';';
	$list_html .= '}';
	
	$list_html .= PHP_EOL."?>".PHP_EOL."";	
	
	$crud_links = "<a href='create_row_".$table_name."_form.php'>Insert</a> ";
	$crud_links .= "<a href='upload_csv_".$table_name."_form.php'>Upload CSV</a> ";
	$crud_links .= "<a href='table_list_".$table_name.".php'>Table List</a> ";
	$crud_links .= "Default Filter Replace";
	
	$scroll_list_html = '';
	$scroll_list_html =  str_replace( '{{{HTML_BODY_CONTENT}}}' , $list_html, $base_html); 
	$scroll_list_html = str_replace('{{{HTML_TITLE}}}'	, $table_name.' List', $scroll_list_html);
	$scroll_list_html = str_replace('{{{HTML_LINKS_HEADER}}}'	,$crud_links, $scroll_list_html);
	
	$filehandle = fopen('scroll_list_'.$table_name.".php", "w") ;
	fwrite($filehandle, $scroll_list_html);
	fclose($filehandle);	
	
}