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


//return arrays and other associative arrays nothing else;
function &get_db($read_write_mode) {
	$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.'eav.sqlite3',$read_write_mode);//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

	return $db;	
}


//* Object APIs

//Object APIs provides operations to allocate a new object and id, and to retrieve, update or delete the object associated with an id.

function db_create_object($object_type) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$insert__sql = $db->prepare( 'INSERT INTO object_table (object_type, time_created) VALUES (?,?)');
	$insert__sql->bindValue(1, $object_type, SQLITE3_TEXT); 
	$insert__sql->bindValue(2, time(), SQLITE3_INTEGER); 
	$insert__sql->execute();
	$last_insert_row_id = $db->lastInsertRowID();
	$db->close();
	return $last_insert_row_id;
}

function db_get_object($object_id) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT object_type,created_time FROM object_table WHERE object_id = ?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query_result = $select_query->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;
}

function db_delete_object($object_id) {
	//MUST  need to cascade delete manually here!! or duplicate values TODO
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	
	$select_query = $db->prepare("SELECT * FROM data_table WHERE object_id=?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $data_key, SQLITE3_TEXT); 
	$select_query_result = $select_query->execute();
	$array_return = array();
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			$array_return[]=$arrayrow;
		}
		
	}
	foreach($array_return as $data)  {
		
		$delete_data_sql = $db->prepare( 'DELETE FROM data_table WHERE data_id = ?;');
		$delete_data_sql->bindValue(1, $data['data_id'], SQLITE3_INTEGER); 
		$delete_data_sql->execute();
		$delete_data_sql->close();
	}
	$delete_object_sql = $db->prepare( 'DELETE FROM object_table WHERE object_id = ?;');
	$delete_object_sql->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$delete_object_sql->execute();
	$db->close();	
	return $object_id;
}

function db_object_range($object_type, $pos, $limit) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM object_table WHERE object_type=? AND  object_id >= ? and object_id <= ? ORDER BY object_id ASC;");
	$select_query->bindValue(1, $object_type, SQLITE3_TEXT); 
	$select_query->bindValue(2, $pos, SQLITE3_INTEGER); 
	$select_query->bindValue(3, ($pos+$limit), SQLITE3_INTEGER); 
	$select_query_result = $select_query->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;
}

//* data APIs

//data APIs provides similar operations to add, modify and delete an data. However it provides a much richer set of APIs to query for datas. Some examples are


function db_create_data($object_id, $data_key, $data_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$insert__sql = $db->prepare( 'INSERT INTO data_table (object_id, data_key, data_value) VALUES (?,?,?)');
	$insert__sql->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$insert__sql->bindValue(2, $data_key, SQLITE3_TEXT); 
	$insert__sql->bindValue(3, $data_value, SQLITE3_TEXT); 
	$insert__sql->execute();
	$last_insert_row_id = $db->lastInsertRowID();
	$db->close();
	return $last_insert_row_id;
}

function db_get_data_all($object_id) {

	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM data_table WHERE object_id=?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 

	$select_query_result = $select_query->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;
}

function db_get_data($object_id, $data_key) {

	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM data_table WHERE object_id=? AND data_key = ?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $data_key, SQLITE3_TEXT); 
	$select_query_result = $select_query->execute();
	$single_value = null;
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		$arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC);
		$single_value=$arrayrow['data_value'];
		
	}
	$db->close();
	return $single_value;
}

function db_delete_data($object_id, $data_key) {
	//might need to cascade delete manually here!! or duplicate values TODO
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$delete__sql = $db->prepare( 'DELETE FROM data_table WHERE $object_id=? AND data_key = ?;');
	$delete__sql->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$delete__sql->bindValue(2, $data_key, SQLITE3_TEXT);
	$delete__sql->execute();
	$db->close();	
}

function db_update_data_value($object_id, $data_key, $data_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$update__sql = $db->prepare( 'UPDATE data_table SET data_value=? WHERE $object_id=? AND data_key = ?;');
	$update__sql->bindValue(1, $data_value, SQLITE3_TEXT); 
	$update__sql->bindValue(2, $object_id, SQLITE3_INTEGER); 
	$update__sql->bindValue(3, $data_key, SQLITE3_TEXT);
	$update__sql->execute();
}


//Things keep common attribute like up/down votes, a type, and creation date. The Data table has three columns: thing id, key, value. 
//each thing has its own table order id 
//utilities function
function db_attribute_get($object_id, $data_key) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM data_table WHERE object_id=? AND data_key = ?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $data_key, SQLITE3_TEXT); 
	$select_query_result = $select_query->execute();
	$single_value = null;
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		$arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC);
		$single_value=$arrayrow[0];
		
	}
	$db->close();
	return $single_value;
}

function db_attribute_count($object_id, $data_key) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT count(*) FROM data_table WHERE object_id=? AND data_type = ?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $data_key, SQLITE3_TEXT); 
	$select_query_result = $select_query->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;
}
//returns el-ements of the (id1, data_key) data list with index i ∈ [pos, pos + limit).
function db_attribute_range($object_id, $data_key, $pos, $limit) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM data_table WHERE object_id=? AND data_key = ? AND data_id >= ? and data_id <= ? ORDER BY data_id ASC;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $data_key, SQLITE3_TEXT); 
	$select_query->bindValue(3, $pos, SQLITE3_INTEGER); 
	$select_query->bindValue(4, ($pos+$limit), SQLITE3_INTEGER); 
	$select_query_result = $select_query->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;
}


function reset_db() {
	//cannot just use open create : error bad parameter or other API misuse
	$db = &get_db(SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);//pdo create sqlite table
	

	//always HAVE SQL CASCADE DELETE ON otherwise primary keys will be re-used, leading to duplicates 

	//object table
	//OBJECT ID 1 
	//OBJECT TYPE orders
	//TIME CREATED

	$db->exec( 'DROP table IF EXISTS object_table;');
	$db->exec( 'CREATE TABLE IF NOT EXISTS object_table ( object_id INTEGER PRIMARY KEY, object_type TEXT, time_created INTEGER);');
	
	//data table table
	//data_id not used
	//OBJECT ID 1 
	//data_key customer_name
	//data_value CoCheese
	$db->exec( 'DROP table IF EXISTS data_table;');
	$db->exec( 'CREATE TABLE IF NOT EXISTS data_table ( data_id INTEGER PRIMARY KEY,object_id INTEGER, data_key TEXT, data_value TEXT );');
							
	$db->close(); //always close
}	
?>