<?php

	//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.'word_v6.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;	
	}

	function close_db(&$db) {
		$db->close(); //always close
	}

	function db_accounts_create_new($random_name) {
		$MAX_WORD_INDEX = 4059;
		$db = &get_db(SQLITE3_OPEN_READWRITE);
		$insert_new_user = $db->prepare( 'INSERT INTO accounts ( username,last_session_hashid,word_number,number_tried,calc_score,last_word_id,currently_playing) VALUES (?,?,?,?,?,?,?)');
		$insert_new_user->bindValue(1, $random_name, SQLITE3_TEXT); //change later
		$insert_new_user->bindValue(2, $random_name, SQLITE3_TEXT); //everyone gets random number beginning
		$insert_new_user->bindValue(3, 1, SQLITE3_INTEGER); //1st word
		$insert_new_user->bindValue(4, 0, SQLITE3_INTEGER); //0 tries
		$insert_new_user->bindValue(5, 0, SQLITE3_FLOAT); //0 score
		$insert_new_user->bindValue(6, mt_rand(1, $MAX_WORD_INDEX), SQLITE3_INTEGER); // assume database count
		$insert_new_user->bindValue(7, 1, SQLITE3_INTEGER); //1= true, 0 false	
		$insert_new_user->execute();
		$db->close();
	}
	
	//TEST CASE db_accounts_create_new("testcaseone");
	
	function db_does_username_exists($username) {
		$db = &get_db(SQLITE3_OPEN_READONLY);
		$check_user_info = $db->prepare("SELECT username FROM accounts WHERE username = ? ;");
		$check_user_info->bindValue(1, $username , SQLITE3_TEXT);
		$check_user_info_result = $check_user_info->execute();
		$user_exists=false;
		if( is_a($check_user_info_result, 'SQLite3Result' )  ) {
			$array_return=$check_user_info_result->fetchArray(SQLITE3_ASSOC);
			if ( is_array($array_return) ===  true)  //no need for count, empty arrays not return
			{
				$user_exists=true;
			}
		}
		$db->close();
		return $user_exists;
	}
	//TEST CASE db_accounts_create_new("testcaseone");

	/**if (db_does_username_exists("testcaseone") === false) {
		error_log("testcaseone=false",4);
	} else {
		error_log("testcaseone is a user[correct]",4);
	}
	if (db_does_username_exists("shouldnotexist") === false) {
		error_log("shouldnotexist does not exists[correct]",4);
	} else {
		error_log("shouldnotexist=true",4);
	}**/

	function db_does_word_exists($word) {
		$db = &get_db(SQLITE3_OPEN_READONLY);
		$select_query = $db->prepare("SELECT * FROM words WHERE word_text=?;");
		$select_query->bindValue(1, $word , SQLITE3_TEXT);
		$select_query_result = $select_query->execute();
		$word_exists=false;
		if( is_a($select_query_result, 'SQLite3Result' )  ) {
			$array_return=$select_query_result->fetchArray(SQLITE3_ASSOC);
			if ( is_array($array_return) ===  true)  //no need for count, empty arrays not return
			{
				$word_exists=true;
			}
		}
		$db->close();
		return $word_exists;
	}
	//TEST CASE db_accounts_create_new("testcaseone");
	/**
	if (db_does_word_exists("testcaseone") === false) {
		error_log("testcaseone is not a word[correct]",4);
	} else {
		error_log("testcaseone=true",4);
	}
	if (db_does_word_exists("photo") === false) {
		error_log("shouldnotexist===false",4);
	} else {
		error_log("photo is a word[correct]",4);
	}**/

	function db_get_all_account_info($username) {
		$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
		$select_query = $db->prepare("SELECT accounts.user_id,username, profile_image_base64 , profile_image_mimetype ,  last_session_hashid , word_number, number_tried , calc_score , last_word_id , currently_playing, word_text, attempt_word, character_state, attempt_sequence FROM accounts LEFT JOIN words on accounts.last_word_id = words.word_id LEFT JOIN attempts on accounts.user_id = attempts.user_id AND accounts.last_word_id = attempts.word_id WHERE accounts.username = ? ORDER BY attempt_id ;");
		$select_query->bindValue(1, $username , 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;
	}

	//TEST CASE 
	//error_log(print_r(db_get_all_account_info("testcaseone")),4);


	function db_insert_attempt($user_id,$word_id,$attempt_word, $character_state) {
		$db = &get_db(SQLITE3_OPEN_READWRITE);//get by reference, assign to a variable for re-use
		//update db
		$insert_query = $db->prepare( 'INSERT INTO attempts ( user_id, word_id , attempt_word, character_state, attempt_sequence) VALUES (?,?,?,?,?)');
	
		$insert_query->bindValue(1, $user_id, SQLITE3_INTEGER); //change later
		$insert_query->bindValue(2, $word_id, SQLITE3_INTEGER); //change later
		$insert_query->bindValue(3, $attempt_word, SQLITE3_TEXT); //coded letter good, bad, possible
		$insert_query->bindValue(4, $character_state, SQLITE3_TEXT); //coded letter good, bad, possible
		$insert_query->bindValue(5, 999, SQLITE3_INTEGER); //change later
		$insert_query->execute();
		$db->close();
	}

	//TEST CASE 
	//db_insert_attempt(5,2628,"moped","IIIII");


	function db_get_attempts_filter_word_id_user_id( $word_id,$user_id) {
		$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
		$select_query = $db->prepare("SELECT * from attempts where word_id = ? and user_id = ? ORDER BY attempt_id;");
		$select_query->bindValue(1, $word_id , SQLITE3_INTEGER);
		$select_query->bindValue(2, $user_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;
	}
	//error_log(print_r(db_get_attempts_filter_word_id_user_id(1919,1)),4);


	function db_user_new_word($username) {
		$db = &get_db(SQLITE3_OPEN_READWRITE);//get by reference, assign to a variable for re-use

		$MAX_WORD_INDEX = 4059;
		$update_statement = $db->prepare('UPDATE accounts SET last_word_id = ? WHERE username = ?;');
		$random_index = mt_rand(1, $MAX_WORD_INDEX);
		$update_statement->bindValue(1, $random_index , SQLITE3_INTEGER);
		$update_statement->bindValue(2, $username, SQLITE3_TEXT);
		$update_result = $update_statement->execute();
		error_log($username." number >".$random_index,4);

		$db->close(); //always close
	}
?>