<?php

function get_db() {
	//RETRIEVE request variables
	$ROOT=''; //save root of web directory
	$db = new SQLite3($ROOT.'todo_v14.sqlite3');//pdo create sqlite table
	$db->busyTimeout(60000);
	$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; will it work on PDO vs SQLITE3?
	$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier and is less likely to "spread"
	$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 get_user_for_session() {
	//get post session id, always populated after authentication
	$session_hashid = filter_input(INPUT_POST, "session_hashid");
	return session_get_userid_given_session_hashid($session_hashid);
}

function session_get_userid_given_session_hashid($session_hashid) {

	//check if no user session id
	if (is_null($session_hashid)) {
		return null;
	}
	else 
	{
		$db = get_db();

		//get session information in database
		$sqlstatement = $db->prepare("select user_id from session where session_hashid = ? and time_created > ?;");
		$time_to_subtract_to_expire = 60 * 30 ; //in seconds 2 minutes [60 x 2]
		$session_timestamp = time() - $time_to_subtract_to_expire;
		error_log($session_timestamp." ".$session_hashid, 4);
		$sqlstatement->bindValue(1, $session_hashid, SQLITE3_TEXT);
		$sqlstatement->bindValue(2, $session_timestamp, SQLITE3_INTEGER);
		$sqlresult = $sqlstatement->execute();
		if( is_a($sqlresult, 'SQLite3Result' ) ){
			$arrayrow = $sqlresult->fetchArray(SQLITE3_ASSOC);

			if(is_array($arrayrow)) {

				$active_user_id = $arrayrow["user_id"];
				$db->close();
				return $active_user_id;
			}
			else
			{
				$db->close();
				return null;
			}
		}
		else
		{
			$db->close();
			return null;
		}
		$db->close();

	}
}

function session_get_username() {
	//get post session id, always populated after authentication
	$session_hashid = filter_input(INPUT_POST, "session_hashid");

	//check if no user session id
	if (is_null($session_hashid)) {
		return null;
	}
	else 
	{
		$db = get_db();

		//get session information in database
		$sqlstatement = $db->prepare("select username from select user_id from session where session_hashid = ? and time_created > ?;");
		$time_to_subtract_to_expire = 60 * 2; //in seconds 2 minutes
		$session_timestamp = time() - $time_to_subtract_to_expire;
		error_log($session_timestamp." ".$session_hashid, 4);
		$sqlstatement->bindValue(1, $session_hashid, SQLITE3_TEXT);
		$sqlstatement->bindValue(2, $session_timestamp, SQLITE3_INTEGER);
		$sqlresult = $sqlstatement->execute();
		if( is_a($sqlresult, 'SQLite3Result' ) ){
			$arrayrow = $sqlresult->fetchArray(SQLITE3_ASSOC);

			if(is_array($arrayrow)) {

				$active_username = $arrayrow["username"];
				$db->close();
				return $active_username;
			}
			else
			{
				$db->close();
				return null;
			}
		}
		else
		{
			$db->close();
			return null;
		}
		$db->close();

	}
}

function blog_get_all($user_id) {

	//RETRIEVE VALUES
	
	if($user_id == null) {return null;}
	
	$db = get_db();
	//cant use * since ambigous, MUST SAY blog.doc_id not doc_id
	$stmt = $db->prepare("select blog.doc_id, doc_text, doc_title, image_base64, datetime(doc_created_time, 'unixepoch') as datetime  from blog LEFT JOIN blog_image ON blog.doc_id = blog_image.doc_id LEFT JOIN image ON blog_image.image_id = image.image_id where user_id = ?;");
	$stmt->bindValue(1, $user_id , SQLITE3_INTEGER);
	$sqlresult = $stmt->execute();
	if( is_a($sqlresult, 'SQLite3Result' ) ){
		$arrayreturn = array();
		while($arrayrow = $sqlresult->fetchArray(SQLITE3_ASSOC) ){
			$arrayreturn[]=$arrayrow;
		}
		$db->close();
		//error_log(print_r($arrayreturn),4);
		return $arrayreturn;
	}
	else {
		$db->close();
		return null;
	}

	$db->close();
	return null;
	#base64_encode($bind_doc_image_lob)
}

function model_blog_get_by_doc_id($doc_id) {

	//RETRIEVE VALUES
	
	if($doc_id == null) {return null;}
	
	$db = get_db();
	$stmt = $db->prepare("select * from blog where doc_id = ?;");
	$stmt->bindValue(1, $doc_id , SQLITE3_INTEGER);
	$sqlresult = $stmt->execute();
	if( is_a($sqlresult, 'SQLite3Result' ) ){
		$arrayrow =  $sqlresult->fetchArray(SQLITE3_ASSOC);
		$db->close();
		if(is_array($arrayrow) )
		{
			return $arrayrow;
		}
		else 
		{
			return null;
		}
		//while($arrayrow = $sqlresult->fetchArray(SQLITE3_ASSOC) ){
		//	$arrayreturn[]=$arrayrow;
		//}


	}
	else {
		$db->close();
		return null;
	}

	$db->close();
	return null;
	#base64_encode($bind_doc_image_lob)
}

class ControllerError {
	public $count = 0;
	public $message = "";
	public $input_field_name = "";
	public function __construct($input_field_name, $message) {
		$this->input_field_name = $input_field_name;
		$this->message = $message;
	}
}

class CreateUserController {

	public $errors = array();
	public $user_id = null;
	public $session_hashid = '';
    	public function __construct() {
		$db = get_db();

		$form_userpassword = filter_input(INPUT_POST, 'form_userpassword');
		$form_username = filter_input(INPUT_POST, 'form_username'); 
		if ( is_null($form_userpassword)  == true) {
			$this->errors[] = new ControllerError("form_userpassword", "Password Is Blank");
		}
		if( is_null($form_username)  == true) { 
			$this->errors[] = new ControllerError("form_username", "User Name Is Blank");
		}
		if ( is_null($form_userpassword) ==false && is_null($form_userpassword)==false) {
			//CHECK if username exists in database
			//RETRIEVE VALUES
			$stmt = $db->prepare("select username from accounts where username=?;");
			$stmt->bindValue(1, $form_username, SQLITE3_TEXT);  //max length 256
			$stmtresult = $stmt->execute(); //returns false if error sql
			
			$doesUserExist = true;
			$sqlError = true;
			//0 is false | is_bool
			if (is_a($stmtresult, 'SQLite3Result')) {

				$resultarray = $stmtresult->fetchArray(SQLITE3_ASSOC);

				if (is_array($resultarray)) {
					$this->errors[] = new ControllerError("form_username", "User Exists ".htmlspecialchars($resultarray["username"]));
				}
				else 
				{	
					$doesUserExist = false;
					$sqlError = false;
				}
			}
			else
			{
				$this->errors[] = new ControllerError("form_username", "sql error");
			}

	
			if($sqlError == false && $doesUserExist == false) 
			{
				//INSERT USER IN DATABASE
				//HASH Password
				$hashed_password = password_hash($form_userpassword, PASSWORD_DEFAULT);
				$qry_insert = $db->prepare( 'INSERT INTO accounts (username, userpassword) VALUES (?,?)');
				$qry_insert->bindValue(1, $form_username, SQLITE3_TEXT);
				$qry_insert->bindValue(2, $hashed_password, SQLITE3_TEXT);
				$res = $qry_insert->execute()->finalize();
				$lastInsertId = $db->lastInsertRowID();
				$this->user_id = $lastInsertId;

				//INSERT SESSION IN DATABASE
				$session_hashid = bin2hex(random_bytes(6)); //to preven forgery

				$insert_session = $db->prepare( 'INSERT INTO session (session_hashid, user_id, time_created) VALUES (?,?,?)');
				$insert_session->bindValue(1, $session_hashid, SQLITE3_TEXT);
				$insert_session->bindValue(2, $this->user_id, SQLITE3_INTEGER);
				$insert_session->bindValue(3, time(), SQLITE3_INTEGER);
				$res = $insert_session->execute()->finalize();
				$this->session_hashid = $session_hashid;
				
			}

		}
		$db->close();
	}
	
}


class LoginUserController {

	public $errors = array();
	public $user_id = null;
	public $session_hashid = '';
    	public function __construct() 
	{
		$db = get_db();

		
		//RETRIEVE RAW UNfiltered request variables

		$form_userpassword = filter_input(INPUT_POST, 'form_userpassword');
		$form_username = filter_input(INPUT_POST, 'form_username'); 
		if ( is_null($form_userpassword)  == true) {
			$this->errors[] = new ControllerError("form_userpassword", "Password Is Blank");
		}
		if( is_null($form_username)  == true) { 
			$this->errors[] = new ControllerError("form_username", "User Name Is Blank");
		}
		if ( is_null($form_userpassword) ==false && is_null($form_userpassword)==false) {

			//RETRIEVE PASSWORD FROM DB
			$stmt = $db->prepare("select * from accounts where username=?;");
			$stmt->bindValue(1, $form_username, SQLITE3_TEXT);
			$executed = $stmt->execute();
			$results = $executed->fetchArray(SQLITE3_ASSOC); //fetches only one

			//Alternately will return false if there are no more rows. 
			if ( $results) 
			{
				$database_hashpassword = $results["userpassword"];
				//HASHPASSWORD is salted so not the same, need to call password_verify, order important
				if(password_verify($form_userpassword, $database_hashpassword )) {
				// If the password inputs matched the hashed password in the database
				// Do something, you know... log them in.

					$this->user_id = $results["user_id"];

					//INSERT SESSION IN DATABASE
					$this->session_hashid = bin2hex(random_bytes(7)); //to preven forgery

					$insert_session = $db->prepare( 'INSERT INTO session (session_hashid, user_id, time_created) VALUES (?,?,?)');
					$insert_session->bindValue(1, $this->session_hashid, SQLITE3_TEXT);
					$insert_session->bindValue(2, $this->user_id, SQLITE3_INTEGER);
					$insert_session->bindValue(3, time(), SQLITE3_INTEGER);
					$res = $insert_session->execute();

					error_log($this->session_hashid,4);

				} 
				else
				{
					$this->errors[] = new ControllerError("form_userpassword", "Wrong Password");
				}
			} 
			else
			{
				$this->errors[] = new ControllerError("form_username", "Incorrect Username");
			}
	
		}
		$db->close();
	}
	
}


class LogoutUserController {

	public $errors = array();
	public $user_id = -1;
    public function __construct() 
	{
		$db = get_db();

		
		//RETRIEVE RAW UNfiltered request variables
		//NEED TO PASS AROUND SESSION OBJECT, otherwise anyone with id and post can logout someone

		$form_userpassword = filter_input(INPUT_POST, 'user_id');
		$form_username = filter_input(INPUT_POST, 'form_username'); 
		if ( is_null($form_userpassword)  == true) {
			$this->errors[] = new ControllerError("form_userpassword", "Password Is Blank");
		}
		if( is_null($form_username)  == true) { 
			$this->errors[] = new ControllerError("form_username", "User Name Is Blank");
		}
		if ( is_null($form_userpassword) ==false && is_null($form_userpassword)==false) {

			//RETRIEVE PASSWORD FROM DB
			$stmt = $db->prepare("select * from accounts where username=?;");
			$stmt->bindValue(1, $form_username, SQLITE3_TEXT);
			$executed = $stmt->execute();
			$results = $executed->fetchArray(SQLITE3_ASSOC); //fetches only one
			
			//Alternately will return false if there are no more rows. 
			if ( $results) 
			{
				$database_hashpassword = $results["userpassword"];
				//HASHPASSWORD is salted so not the same, need to call password_verify, order important
				if(password_verify($form_userpassword, $database_hashpassword )) {
				// If the password inputs matched the hashed password in the database
				// Do something, you know... log them in.
					//my_session_regenerate_id();
					$this->user_id = $results["user_id"];

				} 
				else
				{
					$this->errors[] = new ControllerError("form_username", "Wrong Password");
				}
			} 
			else
			{
				$this->errors[] = new ControllerError("form_username", "User Name does not exists");
			}
	
		}
		$db->close();
	}
	
}


class AddBlogController {

	public $errors = array();
	public $user_id = -1;
	public function __construct() 
	{
		$hasErrors = false;
		$doc_title = filter_input(INPUT_POST, 'doc_title');
		$user_id = get_user_for_session();
		$doc_text = filter_input(INPUT_POST, 'doc_text');
		if ( is_null($doc_title)  == true ) {
			$this->errors[] = new ControllerError("doc_title", "Title is null");
			$hasErrors = true;
		}
		if( is_null($doc_text)  == true) { 
			$this->errors[] = new ControllerError("doc_text", "Text is null");
			$hasErrors = true;
		}
		if( is_null($user_id)  == true) { 
			$this->errors[] = new ControllerError("doc_text", "Session Expired");
			$hasErrors = true;
		}		
		if ($hasErrors == false) 
		{
			error_log('no erros',4);
			$db = get_db();
			
			
			//INSERT OR ADD VALUES
			$qry = $db->prepare( 'INSERT INTO blog (doc_title, doc_text, doc_created_time, user_id) VALUES (?,?, ?,?)');
			$qry->bindValue(1, $doc_title, SQLITE3_TEXT);
			$qry->bindValue(2, $doc_text, SQLITE3_TEXT);
    		$qry->bindValue(3, time(), SQLITE3_INTEGER);
    		$qry->bindValue(4, $user_id, SQLITE3_INTEGER);	    		
    		
    		$res = $qry->execute();
	    	$last_blog_id = $db->lastInsertRowID(); //$db->lastInsertId(); 
			//UPDATE post if optional image is there
	    		//The global $_FILES will contain all the uploaded file information.
	    		//$_FILES['userfile']['tmp_name'] need to move since temporary replace userfile with form input name!!
	    		//isset($_FILES['doc_image']) always return true, so use if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
	    	if(isset($_FILES['doc_image']) && is_uploaded_file($_FILES['doc_image']['tmp_name'])  ) {
				//check if image file
				list($gd_width, $gd_height, $gd_type, $gd_attr) = getimagesize($_FILES['doc_image']['tmp_name']); //list Assign variables as if they were an array
				//echo print_r($gd_width)."|width|".$gd_height."|height|".print_r($gd_type)."|type|".print_r($gd_attr)."|<array|";
				error_log(print_r($gd_width)."|width|".$gd_height."|height|".print_r($gd_type)."|type|".print_r($gd_attr)."|<array|", 4);

				
				//INSERT VALUE AS BASE 64 text 33% bigger but no need to deal with blobs and sql

				$file_path = $_FILES['doc_image']['tmp_name'];
				
				$finfo = new finfo(FILEINFO_MIME); // return mime type ala mimetype extension
				error_log('finifo'.$finfo->file($file_path));  //image/png; charset=binary
				//image/vnd.microsoft.icon; charset=binary  	image/avif | image/bmp | image/gif \ image/gif | image/png
				//image/webp
				//image/svg+xml ?? no https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types/Common_types
				//echo ">>>>>>".mime_content_type($_FILES['doc_image']['tmp_name']);
				$image_base64 = base64_encode(file_get_contents($file_path));
		
				$insert_image = $db->prepare( 'INSERT INTO image (image_base64) VALUES (?)');
				$insert_image->bindValue(1, $image_base64, SQLITE3_TEXT);	    		
    			$insert_image->execute();	
    			
				$last_image_id = $db->lastInsertRowID(); //$db->lastInsertId(); 	
			
			
				$insert_blog_image = $db->prepare( 'INSERT INTO blog_image (doc_id, image_id) VALUES (?,?)');
				$insert_blog_image->bindValue(1, $last_blog_id, SQLITE3_INTEGER);	
				$insert_blog_image->bindValue(2, $last_image_id, SQLITE3_INTEGER);	    		    		
    			$insert_blog_image->execute();	
    			
				//$db->beginTransaction();

				//$db->commit();
		    
		    }

			
			$db->close();
		}
	}
	
}


class DeleteBlogController {

	public $errors = array();
	public $user_id = -1;
	public function __construct() 
	{
		$hasErrors = false;
		$doc_id = filter_input(INPUT_POST, 'doc_id_choice');
		$user_id = get_user_for_session();
		
		
		if ( is_null($doc_id)  == true ) {
			$this->errors[] = new ControllerError("doc_id", "List Error, Please select a blog");
			$hasErrors = true;
		}
		if( is_null($user_id)  == true) { 
			$this->errors[] = new ControllerError("doc_id", "Session Timeout");
			$hasErrors = true;
		}
	
		if ($hasErrors == false) 
		{
			$db = get_db();
    			$delete_statement = $db->prepare('DELETE FROM blog WHERE doc_id = ?;');
			$delete_statement ->bindValue(1, $doc_id, SQLITE3_INTEGER);
    			$result = $delete_statement->execute();
			$db->close();
		}
	}
}


class UpdateBlogController {

	public $errors = array();
	public $user_id = -1;
	public function __construct() 
	{
		$hasErrors = false;
		//base64_encode
		$doc_id = filter_input(INPUT_POST, 'doc_id');
		$doc_title = filter_input(INPUT_POST, 'doc_title');
		$doc_text = filter_input(INPUT_POST, 'doc_text');
		if ( is_null($doc_id)  == true ) {
			$this->errors[] = new ControllerError("doc_id", "doc_id is null");
			$hasErrors = true;
		}
		if( is_null($doc_title)  == true) { 
			$this->errors[] = new ControllerError("doc_title", "doc_title is null");
			$hasErrors = true;
		}
		if( is_null($doc_text)  == true) { 
			$this->errors[] = new ControllerError("doc_text", "doc_text is null");
			$hasErrors = true;
		}
					error_log('outside',4);
		if ($hasErrors == false) {
			$db = get_db();
			error_log('inside',4);

			//$doc_id = filter_input(INPUT_POST, 'doc_id_choice'); no have separate form

			$update_statement = $db->prepare('UPDATE blog SET doc_title = ?, doc_text = ? WHERE doc_id = ?;');
			$update_statement->bindValue(1, $doc_title, SQLITE3_TEXT);
			$update_statement->bindValue(2, $doc_text, SQLITE3_TEXT);
			$update_statement->bindValue(3, $doc_id, SQLITE3_INTEGER);

			$result = $update_statement->execute();
			$db->close();
		}
		

	}
	
}


class CopyBlogController {

	public $errors = array();
	public $user_id = -1;
	public function __construct() 
	{
		$hasErrors = false;
		//base64_encode
		$doc_id = filter_input(INPUT_POST, 'doc_id_choice');
		$user_id = get_user_for_session();

		if ( is_null($doc_id)  == true ) {
			$this->errors[] = new ControllerError("doc_id", "doc_id is null");
			$hasErrors = true;
		}
		if( is_null($user_id)  == true) { 
			$this->errors[] = new ControllerError("user_id", "session is expired");
			$hasErrors = true;
		}

		if ($hasErrors == false) {
			$db = get_db();
			$from_blog = model_blog_get_by_doc_id($doc_id);


			$insert_statement = $db->prepare( 'INSERT INTO blog (doc_title, doc_text, doc_created_time, user_id) VALUES (?,?, ?,?)');
			$insert_statement->bindValue(1, $from_blog["doc_title"], SQLITE3_TEXT);
			$insert_statement->bindValue(2, $from_blog["doc_text"], SQLITE3_TEXT);
	    		$insert_statement->bindValue(3, time(), SQLITE3_INTEGER); //new time
	    		$insert_statement->bindValue(4, $user_id, SQLITE3_INTEGER); //session user id	
	    		//image missing	?? base64 encode all binary next time
	    		
	    		$res = $insert_statement->execute();

			$db->close();
		}
		

	}
	
}


?>