<?php

class OAuth2 {

	public function getAllTokens($db, $member_id) {
		$result = array();
		$total = 0;
		$q = "SELECT * FROM oauth2_tokens WHERE member_id=${member_id} ORDER BY id DESC";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$total = pg_num_rows($r);
			while($f=pg_fetch_assoc($r)) {
				list($decrypted,$googleKMS) = self::decrypt($f["refresh_token_encrypted"]);
				$f["refresh_token"] = $decrypted;
				list($decrypted,$googleKMS) = self::decrypt($f["access_token_encrypted"], $googleKMS);
				$f["access_token"] = $decrypted;
				$result[] = $f;
			}
		}
		return array($total, $result);
	}

	public function getLatestToken($db, $member_id, $oauth_provider_id) {
		$result = array();
		$total = 0;
		$q = "SELECT * FROM oauth2_tokens WHERE member_id=${member_id} AND oauth_provider_id=${oauth_provider_id} ORDER BY id DESC LIMIT 1";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			list($decrypted,$googleKMS) = self::decrypt($f["refresh_token_encrypted"]);
			$f["refresh_token"] = $decrypted;
			list($decrypted,$googleKMS) = self::decrypt($f["access_token_encrypted"], $googleKMS);
			$f["access_token"] = $decrypted;
			$result = $f;
		}
		return $result;
	}

	public function getTokenById($db, $id) {
		$result = array();
		$q = "SELECT * FROM oauth2_tokens WHERE id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			list($decrypted,$googleKMS) = self::decrypt($f["refresh_token_encrypted"]);
			$f["refresh_token"] = $decrypted;
			list($decrypted,$googleKMS) = self::decrypt($f["access_token_encrypted"], $googleKMS);
			$f["access_token"] = $decrypted;
			$result = $f;
		}
		return $result;
	}

	private function encrypt($plainText, $googleKMS=NULL) {
		global $savvyext;
		$cipherText = NULL;
		try {
			if ($googleKMS==NULL) {
				$projectId = $savvyext->cfgReadChar('google.kms_project_id');
				$authFile = $savvyext->cfgReadChar('google.kms_auth_file');
				$keyRingId = $savvyext->cfgReadChar('google.kms_keyring_id');
				$keyId = $savvyext->cfgReadChar('google.kms_key_id');
				/*error_log("projectId=$projectId");
				error_log("authFile=$authFile");
				error_log("keyRingId=$keyRingId");
				error_log("keyId=$keyId");*/
				$googleKMS = new GoogleKMS(
					$projectId,
					$authFile,
					$keyRingId,
					$keyId
				);
			}
			$cipherText = bin2hex($googleKMS->encrypt($plainText));
		} catch (Exception $e) {
			error_log($e->getMessage());
		}
		return [$cipherText, $googleKMS];
	}

	private function decrypt($cipherText, $googleKMS=NULL) {
		global $savvyext;
		$plainText = NULL;
		try {
			if ($googleKMS==NULL) {
				$projectId = $savvyext->cfgReadChar('google.kms_project_id');
				$authFile = $savvyext->cfgReadChar('google.kms_auth_file');
				$keyRingId = $savvyext->cfgReadChar('google.kms_keyring_id');
				$keyId = $savvyext->cfgReadChar('google.kms_key_id');
	
				$googleKMS = new GoogleKMS(
					$projectId,
					$authFile,
					$keyRingId,
					$keyId
				);
			}
			$plainText = $googleKMS->decrypt(hex2bin($cipherText));
		} catch (Exception $e) {
			error_log($e->getMessage());
		}
		return [$plainText, $googleKMS];
	}

	public function saveToken($db, $oauth2_provider_id, $member_id, $refresh_token, $access_token, $email, $name, $user_id="") {
		
		$result = array();
		$db_oauth2_provider_id = (int)$oauth2_provider_id;
		$db_member_id = (int)$member_id;
		// TODO: clear
		$db_refresh_token = substr(pg_escape_string($refresh_token),0,500);
		// TODO: clear
		$db_access_token = substr(pg_escape_string($access_token),0,500); 
		list($refresh_token_encrypted,$googleKMS) = self::encrypt($refresh_token);
		$db_refresh_token_encrypted = pg_escape_string($refresh_token_encrypted);
		list($access_token_encrypted,$googleKMS) = self::encrypt($access_token, $googleKMS);
		$db_access_token_encrypted = pg_escape_string($access_token_encrypted);
		if ($refresh_token_encrypted==NULL || $access_token_encrypted==NULL) {
			return NULL;
		}
		$db_email = pg_escape_string($email);
		$db_name = pg_escape_string($name);
		$db_user_id = pg_escape_string($user_id);
		$q = "INSERT INTO oauth2_tokens (oauth2_provider_id, member_id, refresh_token, access_token, ";
		$q.= "email, name, expires_in, refresh_token_encrypted, access_token_encrypted, user_id) VALUES (";
		$q.= "${db_oauth2_provider_id},${db_member_id},'${db_refresh_token}','${db_access_token}',";
		$q.= "'${db_email}','${db_name}',now() + interval '3600','${db_refresh_token_encrypted}',";
		$q.= "'${db_access_token_encrypted}','${db_user_id}'";
		$q.= ") RETURNING id";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			OAuth2::updateMemberEmailStatus($db, $db_member_id, 1);
			return OAuth2::getTokenById($db, $f[0]);
		}
		return NULL;
	}

	public function getMemberByEmail($db, $email) {
		$result = array();
		$db_email = pg_escape_string(strtolower($email));
		$q = "SELECT * FROM members WHERE lower(email)='${db_email}' OR lower(username)='${db_email}'";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			$result = $f;
		}
		return $result;
	}

	public static function getMemberEmailByUserId($db, $oauth2_provider_id, $user_id) {
		$db_oauth2_provider_id = (int)$oauth2_provider_id;
		$db_user_id = pg_escape_string($user_id);
		$q = "SELECT email FROM oauth2_tokens WHERE oauth2_provider_id=${db_oauth2_provider_id} ";
		$q.= " AND user_id='${db_user_id}' AND (email<>'' OR email IS NOT NULL) ORDER BY created DESC LIMIT 1";
		// error_log($q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			return $f[0];
		}
		return NULL;
	}

	public function remove($db, $member_id) {
		$res = NULL;
		$err = NULL;
		$mid = (int)$member_id;
		try {
			$q = "SELECT md5(username) AS username,md5(firstname) AS firstname,md5(lastname) AS lastname,md5(email) AS email,md5(phone) AS phone,password FROM members WHERE id=${mid}";
			$r = pg_query($db, $q);
			if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
				$nmid = 0;
				$vals = []; $keys = [];
				foreach ($f as $key=>$val) {
					$keys[] = $key;
					$vals[] = $val;
				}
				$q = "INSERT INTO members (id,".implode(",",$keys).") VALUES(-${mid},'".implode("','",$vals)."') RETURNING id";
				$r = pg_query($db, $q);
				if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
					$nmid = $f['id'];
				} else {
					$q = "SELECT * FROM members WHERE id=-${mid}";
					$r = pg_query($db, $q);
					if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
						$nmid = $f['id'];
					} else {
						throw new Exception("Failed to hash member data");
					}
				}
				if ($nmid<0) {
					$needs_delete = false;
					$q = "SELECT * FROM trackedemail_item WHERE member_id=${mid}";
					$r = pg_query($db, $q);
					if ($r && pg_num_rows($r)) {
						$needs_delete = true;
					}
					if ($needs_delete) {
						// TODO: clear e-mail data...
						//$q = "UPDATE trackedemail_item SET member_id=-${mid},subject=NULL,message=NULL,message_from=NULL,hash=NULL,oauth2_pull_job_id=NULL,message_date=NULL WHERE member_id=${mid}";
						// DEBUG: testing
						$q = "UPDATE trackedemail_item SET member_id=-${mid} WHERE member_id=${mid}";
						$r = pg_query($db, $q);
						if ($r && pg_affected_rows($r)) {
							$res["mails_deleted"] = pg_affected_rows($r);
							$res["message"] = "E-mails deleted";
						} else {
							throw new Exception("Failed to delete messages!");
						}
					} else {
						$res["mails_deleted"] = 0;
						$res["message"] = "No e-mails found to delete";
					}
					// TODO: delete...
					$r = null;
					// Delete OAuth2 pull job threads
					$q = "delete from oauth2_pull_job_threads where oauth2_token_id in (select id from oauth2_tokens where member_id=${mid})";
					//$r = pg_query($db, $q);
					$res["pull_threads_deleted"] = (int)pg_affected_rows($r);
					// Delete OAuth2 pull jobs
					$q = "delete from oauth2_pull_jobs where oauth2_token_id in (select id from oauth2_tokens where member_id=${mid})";
					//$r = pg_query($db, $q);
					$res["pull_jobs_deleted"] = (int)pg_affected_rows($r);
					// Delete OAuth2 tokens
					$q = "delete from oauth2_tokens where member_id=${mid}";
					//$r = pg_query($db, $q);
					$res["oauth2_tokens_deleted"] = (int)pg_affected_rows($r);
				} else {
					throw new Exception("Failed to hash member's data");
				}
			} else {
				throw new Exception("Failed to load member record");
			}
		} catch (Exception $e) {
			$err = $e->getMessage();
			if (pg_last_error()!="") {
				$err.= ": ".pg_last_error();
			}
		}
		return [$res, $err];
	}

	function updateMemberEmailStatus($db, $member_id, $status=0){
		try {
			$q = "UPDATE members SET email_connected=${status} WHERE id=${member_id}";
			error_log('update email connected: '.$q);
			$r = pg_query($db, $q);
		} catch (Exception $e) {
			$err = $e->getMessage();
			if (pg_last_error()!="") {
				$err.= ": ".pg_last_error();
				error_log('update email connected error: '.$err);
			}
		}
	}

	function removeTokens($db, $member_id){
		$res = NULL;
		$err = NULL;
		$mid = (int)$member_id;
		try {
			$q = "SELECT member_id FROM oauth2_tokens WHERE member_id=${mid}";
			$r = pg_query($db, $q);
			if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
				OAuth2::updateMemberEmailStatus($db, $mid, 0);
				$q = "UPDATE oauth2_pull_job_threads SET started=NOW(), completed=NOW() WHERE oauth2_token_id IN (SELECT id FROM oauth2_tokens WHERE member_id=${mid})";
				error_log('update oauth2_pull_job_threads: '.$q);
				$r = pg_query($db, $q);
				$q = "UPDATE oauth2_pull_jobs  SET started=NOW(), completed=NOW() WHERE oauth2_token_id IN (SELECT id FROM oauth2_tokens WHERE member_id=${mid})";
				error_log('update oauth2_pull_jobs: '.$q);
				$r = pg_query($db, $q);
				$q = "UPDATE oauth2_tokens SET expires_in=now()-interval '4 hours' WHERE member_id=${mid}";
				error_log('update oauth2_tokens: '.$q);
				$r = pg_query($db, $q);
				$res["message"] = "Email is disconnected";
				$res["oauth2_tokens_deleted"] = (int)pg_affected_rows($r);
			} else {
				throw new Exception("Failed to load tokens");
			}
		} catch (Exception $e) {
			$err = $e->getMessage();
			if (pg_last_error()!="") {
				$err.= ": ".pg_last_error();
			}
		}
		error_log('removeTokens: '.json_encode([$res, $err]));
		return [$res, $err];
	}


	function saveMembersSurvey($db, $surveyData,$out) {
		if (empty($out["added"])) {//only save first time
			$member_id = $out['member_id'];
			foreach($surveyData as $group_key=>$survey){
				$answers = isset($survey['answers'])?$survey['answers']:[];
				foreach($answers as $answer_key => $value){
					if($value==true){
						$q = "INSERT INTO members_onboarding_survey ( member_id, answers_key, answers,status, added) VALUES (" . $member_id . ",'" . $answer_key . "','" . $value . "', 1, now())";
						$res1 = pg_query($db, $q);
						if ($res1 and pg_num_rows($res1) > 0) {
							//logger
						}
					}
				}
			}
			
		} 
	}
}

// vi:ts=2