<?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
// 

$TAB="\t";


function array_map_bind_value($field_name, $field_type )  {
	return 	'$prepate_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_table($field_name, $field_type )  {
	return 	$field_name.' '.$field_type;
}

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

$db_schema = array (
	array("table", 
		array( 
		  	array("field_ID", "INTEGER" ),  
  			array("field_NAME", "TEXT" ),  
  			array("field_PRICE", "INTEGER" ), 
  			array("field_VERSION", "INTEGER" ), 
			array("fiekd_IS_ACTIVE", "INTEGER" ),    	
			array("field_DESCRIPTION", "TEXT" ), 
			array("field_BRANCH", "TEXT" ), 	    		 
  		)  
  	),
	array("table2", 
		array(
			array("tabke2_ID", "INTEGER" ),  
			array("table2_NAME", "TEXT"),
			array("table3_CODE", "TEXT"),
			array("table2_DATE", "INTEGER"),
		)
	),
	array("table3",
		array( 
			array("ORDER_ID", "INTEGER" ),  
  			array("ORDER_Attribute_KEY", "TEXT" ),  
  		)	
	)
);

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

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;
	}
	

	$bind_value_array = array_map('array_map_bind_value',$array_field_names,$array_field_types);

	$bind_value_array_string = implode(PHP_EOL,	$bind_value_array );
	$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), ':');;
	$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 );
	
	$update_header_sql = implode(', ', array_map('array_map_update_table', $array_field_names));
	
	
	$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.' (';
	$drop_create_table_sql .= $field_name_values_joined_comma;
	$drop_create_table_sql .= ');\');'.PHP_EOL;

		
	$create_sql_statement .= "function db_create_".$table_name."(";
	$create_sql_statement .= $field_names_joined_dollar_comma;
	$create_sql_statement .= ') {'.PHP_EOL;
	$create_sql_statement .= '    $db = &get_db(SQLITE3_OPEN_READWRITE);'.PHP_EOL;	
	$create_sql_statement .= '    $prepare_sql = $db->prepare( \'INSERT INTO '.$table_name.' (';	
	$create_sql_statement .= $field_name_joined_comma;	
	$create_sql_statement .= ') VALUES (';	
	$create_sql_statement .= $field_names_joined_colon_comma;	
	$create_sql_statement .= ')\');'.PHP_EOL;
	$create_sql_statement .= $bind_value_array_string.PHP_EOL;		
	$create_sql_statement .= '   $prepare_sql->execute();'.PHP_EOL;	
	$create_sql_statement .= '   $db->close();'.PHP_EOL;		
	$create_sql_statement .= '}'.PHP_EOL.PHP_EOL;		


	$update_sql_statement .= "function db_update_".$table_name."(";
	$update_sql_statement .= $field_names_joined_dollar_comma;
	$update_sql_statement .= ') {'.PHP_EOL;
	$update_sql_statement .= '   $db = &get_db(SQLITE3_OPEN_READWRITE);'.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;\');';
	$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 .= '   $db = &get_db(SQLITE3_OPEN_READWRITE);'.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;

	
		//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';
			} else {
				$select_sql_sub_statement .= '$'.$field_name;
			}
			$select_sql_sub_statement .= ') {'.PHP_EOL;
			$select_sql_sub_statement .= '   $db = &get_db(SQLITE3_OPEN_READONLY);'.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;						
			} 
			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 <= ? \');'.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;
		}
	}


}


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

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.$drop_create_table_sql.$create_sql_statement.$update_sql_statement.$delete_sql_statement.$select_sql_statement.PHP_EOL.'?>';

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


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

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;
	}
}