<?php

	require_once('class.db.php');

	class Translators extends DB {		
		
		//get current user level by user id
		public function getCurrentUserLevelByUserId( $user_id ) {
			$sql_get_user_level = "SELECT user_level FROM tbl_translators WHERE id = " . $user_id;
			$user_level_result = $this->con->query($sql_get_user_level);
			while ($row_user_level = $user_level_result->fetch_object()) {
				$current_user_level = (int)$row_user_level->user_level;
			}
			return $current_user_level;
		}
		
		//upgrade user level till 2-5th level, 7-10 level
		public function upgradeUserLevel($user_id, $user_level) {
			
			//get user level logs by user id and user level
			$sql_get_user_level_logs = "SELECT *, locked_till - NOW() as locked_time_diff_seconds from user_level_logs WHERE user_id = " . $user_id . " AND level_no = " . $user_level . " ORDER BY id DESC LIMIT 1";
			$get_user_level_logs_result = $this->con->query( $sql_get_user_level_logs );
			$user_level_logs_count = $get_user_level_logs_result->num_rows;
			
			//if user level log record found
			if( $user_level_logs_count > 0 ) {
				
				//get lock till time difference in seconds
				while ($row_user_level_logs = $get_user_level_logs_result->fetch_object()) {
					$locked_time_diff_seconds = (int)$row_user_level_logs->locked_time_diff_seconds;
				}
				
				//check if locked period off
				if( $locked_time_diff_seconds < 0 ) {
					
					$upgrade_to_user_level = $user_level + 1;
					
					if( $upgrade_to_user_level != 6 && $upgrade_to_user_level != 11 ) {
					
						//upgrade user level to one level up
						$sql_upgrade_user_level = "UPDATE tbl_translators SET user_level = " .$upgrade_to_user_level .", user_level_updated_at = NOW() WHERE id = " . $user_id . " AND user_level = " . $user_level . " LIMIT 1";
						$this->con->query( $sql_upgrade_user_level );
					
					}
					 
				}
				
			}			
			
		}	
		
		
		//get all closed suggestion and voting success ratio
		public function getAllClosedSuggestionAndVotingSuccessRatio( $total_suggestions_provided_count, $total_closed_suggestion_count, $total_winning_suggestion_count, $total_voting_provided_count, $total_closed_voting_count, $total_winning_voting_count ) {
			
			$suggestion_success_percentage = 0;
			$voting_success_percentage = 0;
			
			if( $total_suggestions_provided_count == $total_closed_suggestion_count && $total_voting_provided_count == $total_closed_voting_count ) {
				$suggestion_success_percentage = $total_winning_suggestion_count / $total_suggestions_provided_count * 100;
				$voting_success_percentage = $total_winning_voting_count / $total_voting_provided_count * 100;
			}
			
			$success_ratio = ($suggestion_success_percentage + $voting_success_percentage) / 2;
			return $success_ratio;
			
		}
		
		//Upgrade Step2 Level 6, Level 11
		public function upgradeUserLevelStep2( $user_id, $user_level, $user_success_ratio, $admin_decided_success_ratio ) {
			
			//get user level logs by user id and user level
			$sql_get_user_level_logs = "SELECT *, locked_till - NOW() as locked_time_diff_seconds from user_level_logs WHERE user_id = " . $user_id . " AND level_no = " . $user_level . " ORDER BY id DESC LIMIT 1";
			$get_user_level_logs_result = $this->con->query( $sql_get_user_level_logs );
			$user_level_logs_count = $get_user_level_logs_result->num_rows;
			
			//if user level log record found
			if( $user_level_logs_count > 0 ) {
				
				//get lock till time difference in seconds
				while ($row_user_level_logs = $get_user_level_logs_result->fetch_object()) {
					$locked_time_diff_seconds = (int)$row_user_level_logs->locked_time_diff_seconds;
				}
				
				//check if locked period off
				if( $locked_time_diff_seconds < 0 ) {
					
					$upgrade_to_user_level = $user_level + 1;
					
					if( ($upgrade_to_user_level == 6 || $upgrade_to_user_level == 11) && $user_success_ratio >= $admin_decided_success_ratio ) {
					
						//upgrade user level to one level up
						$sql_upgrade_user_level = "UPDATE tbl_translators SET user_level = " .$upgrade_to_user_level .", user_level_updated_at = NOW() WHERE id = " . $user_id . " AND user_level = " . $user_level . " LIMIT 1";
						$this->con->query( $sql_upgrade_user_level );
					
					}
					 
				}
				
			}
			
		}
		
		
		//get all suggestion and voting success ratio [though all suggestions & voting not closed]
		public function getAllSuggestionAndVotingSuccessRatio( $total_closed_suggestion_count, $total_winning_suggestion_count, $total_closed_voting_count, $total_winning_voting_count ) {				
			
			$success_ratio = 0;						
			$suggestion_success_percentage = 0;	
			$voting_success_percentage = 0;
						
			if( $total_closed_suggestion_count > 0 ) {
				$suggestion_success_percentage = $total_winning_suggestion_count / $total_closed_suggestion_count * 100;
			}
			
			if( $total_closed_voting_count > 0 ) {
				$voting_success_percentage = $total_winning_voting_count / $total_closed_voting_count * 100;
			}			
						
			$success_ratio2 = ($suggestion_success_percentage + $voting_success_percentage) / 2;
			
			return $success_ratio2;
			
		}		
		
	}

?>