<?php

	require_once('class.db.php');

	class Test_sentences_voting extends DB
	{			
		
		//maximum no. of test voting sentences assigned, no. of test voting user can submit
		public function acceptMaxVotesForTestSentence() {			
			$max_votes_per_sentences = 11;			
			return $max_votes_per_sentences;
		}
		
		//maximum no. votes user can submit in a day
		public function maxPerDayVotesForTestSentences() {
			$max_votes_per_day = 25;			
			return $max_votes_per_day;
		}		
		
		//maximum vote submit time in minutes
		public function maxVoteSubmitTimeMinutes() {
			$max_vote_submit_time_mins = 5;			
			return $max_vote_submit_time_mins;
		}
		
		//maximum vote submit time in minutes
		public function maxVoteToSingleSuggestionToWin() {
			$no_of_success_vote_to_single_suggestion = 5;			
			return $no_of_success_vote_to_single_suggestion;
		}
		
		//get winning test sentence voting credit tmn default value
		public function winningTestSentenceVotingTmnCreditValue() {
			$winning_voting_tmn_credits = 10;
			return $winning_voting_tmn_credits;
		}
		
		//get maxiumum no. of skip voting require to skip test sentence from voting and user test
		public function maxNoOfSkipValueToSkipTestSentence() {
			$max_no_of_skip_value = 3;
			return $max_no_of_skip_value;
		}
		
		//get test voting sentence row
		public function getTestSentenceVotingRow( $language_id, $user_id, $not_in_id_str, $not_in_skipped_test_voting_sentence_ids ) {
			//Hindi-38, Spanish-87
			if( $language_id == 38 || $language_id == 87 ) {
				//take sentences from this date. date format yyyy-mm-dd
				$test_sentences_from_date = '2021-12-06';
				$sql_get_voting_sentnece = "SELECT * FROM `test_voting_sentences` WHERE language_id = " . $language_id . " AND is_skipped = 0 AND voting_completed_on IS NULL AND created_on >= '".$test_sentences_from_date."'  AND id NOT IN( SELECT sentence_id FROM test_voting_sentences_users_opinion WHERE user_id = " . $user_id . " AND is_success = 1 ) AND id NOT IN( ". $not_in_id_str ." ) AND id NOT IN( ". $not_in_skipped_test_voting_sentence_ids ." ) order by id LIMIT 1";
			} else {
				$sql_get_voting_sentnece = "SELECT * FROM `test_voting_sentences` WHERE language_id = " . $language_id . " AND is_skipped = 0 AND voting_completed_on IS NULL AND id NOT IN( SELECT sentence_id FROM test_voting_sentences_users_opinion WHERE user_id = " . $user_id . " AND is_success = 1 ) AND id NOT IN( ". $not_in_id_str ." ) AND id NOT IN( ". $not_in_skipped_test_voting_sentence_ids ." ) order by id LIMIT 1";
			}			
			$result = $this->con->query( $sql_get_voting_sentnece );
			return $result;
		}
		
		// checking currently assigned test sentence for voting 
		public function getCurrentlyAssignedTestSentenceIdsForVoting( $global_max_voting_count ) {
			
			$current_count = 'select sentence_id from test_voting_sentences_users_opinion where is_success is null group by sentence_id having count(sentence_id) >= ' . $global_max_voting_count;
			$result_cur = $this->con->query($current_count);

			$not_in_id_arr = array( 0 );
			if ($result_cur->num_rows > 0) {
				while ($row_cur = $result_cur->fetch_object()) {
					$not_in_id_arr[]=$row_cur->sentence_id;
				}
			}

			$not_in_id_str = implode( ",",$not_in_id_arr );
			
			return $not_in_id_str;
			
		}
		
		//get user empty vote success null entry's count
		public function get_user_empty_test_sentence_vote_count($sentence_id, $user_id) {
			$sqlUserVotingCount = 'SELECT * FROM `test_voting_sentences_users_opinion` WHERE sentence_id = ' . $sentence_id . ' AND user_id = ' . $user_id . ' AND is_success IS NULL';
			$resultUserVotingCount = $this->con->query($sqlUserVotingCount);
			$user_voting_count = $resultUserVotingCount->num_rows;     
			return $user_voting_count;
		}		
				
		//Insert initial user vote entry into sentence_suggestion_voting_map table
		public function saveInitialUserTestSentenceVoteEntry( $sentence_id, $user_id, $global_voting_max_submit_time_mins ) {
			$sqlInsert1 = 'INSERT INTO `test_voting_sentences_users_opinion`( `sentence_id`, `user_id`, `max_submit_time` ) VALUES '
						. '(' . $sentence_id . ',' . $user_id . ', NOW() + INTERVAL '.$global_voting_max_submit_time_mins.' MINUTE )';
			$this->con->query($sqlInsert1);	
		}
		
		// SQL for marking the voting failed if time more than 5 mins of submission 
		public function makeTestSentenceVotingFailedMaxSubmitTimeOver() {
			$sql_tvsuoUpdate = 'UPDATE `test_voting_sentences_users_opinion` SET is_success = 0 where max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_tvsuoUpdate);
		}
		
		//get success voting count for sentence
		public function get_success_test_sentence_voting_count_by_sentence_id($sentence_id) {
			$sqlSuccessVotingCount = 'SELECT * FROM `test_voting_sentences_users_opinion` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1';
			$resultSuccessVotingCount = $this->con->query($sqlSuccessVotingCount);
			$success_voting_count = $resultSuccessVotingCount->num_rows;      
			return $success_voting_count;
		}
		
		//after received 1st success vote, update voting started on date
		public function updateTestSentenceVotingStartedDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `test_voting_sentences` SET voting_started_on = NOW() where id = ' . $sentence_id . ' AND voting_started_on IS NULL ';			
			$this->con->query($sqlUpdateTs);
		}
		
		//apply user vote. update user vote for sentence suggestion to success
		public function applySuccessUserVoteForTestSentence($suggestion_id,$sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `test_voting_sentences_users_opinion` SET is_success = 1, voted_at = NOW(), voted_suggestion_id='. $suggestion_id .' WHERE sentence_id = ' . $sentence_id.' and user_id = ' . $user_id . ' AND is_success IS NULL';
			$resultUpdateS = $this->con->query($sqlUpdateS);
			return $resultUpdateS;
		}
		
		//get count - sentence has reached No. of Success Voting To a Particular Suggestion 
		public function checkTestSentenceHasReachedNoOfSuccessVotesForSuggestion($sentence_id, $no_of_success_voting_to_suggestion) {
			$sql_get_success_voting_for_suggestion = "SELECT voted_suggestion_id FROM test_voting_sentences_users_opinion WHERE sentence_id = " . $sentence_id . " AND is_success=1 GROUP BY voted_suggestion_id HAVING count(voted_suggestion_id) >= " . $no_of_success_voting_to_suggestion;			
			$result_get_success_voting_for_suggestion = $this->con->query( $sql_get_success_voting_for_suggestion );
			$count_success_voting_for_suggestion = $result_get_success_voting_for_suggestion->num_rows;			
			return $count_success_voting_for_suggestion;
		}
		
		//Close Sentence for voting in main test_voting_sentences table. Voting completed for sentence suggestion
		public function closeVotingForTestSentence($sentence_id) {			
			$sqlUpdateSentence = 'UPDATE `test_voting_sentences` SET voting_completed_on = NOW() WHERE id =' . $sentence_id;
			$this->con->query($sqlUpdateSentence);
		}	

		//make user vote failed by sentence and user id
		public function makeUserTestSentenceVoteFailedBySentenceIdAndUserId($sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `test_voting_sentences_users_opinion` SET is_success = 0, voted_at = NOW() where sentence_id = ' . $sentence_id.' and user_id = ' . $user_id . ' AND is_success IS NULL';
			$this->con->query($sqlUpdateS);
		}
		
		//Get users today's submitted success test sentences voting count
		public function getUserTodaySubmittedSuccessTestSentnecesVotingCount( $user_id ) {
			$sql_today_success_voting = "SELECT * FROM test_voting_sentences_users_opinion WHERE user_id = " . $user_id . " AND DATE_FORMAT(voted_at,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') AND is_success = 1 ";
			$today_success_voting_result = $this->con->query($sql_today_success_voting);
			$today_success_voting_count = $today_success_voting_result->num_rows;
			return $today_success_voting_count;
		}
		
		//Get winning test sentence suggestion id by sentence id
		public function getWinningTestSentenceSuggestionIdBySentenceId( $sentence_id ) {
			$sql_winning_test_sentence_suggestion = "SELECT voted_suggestion_id, count(voted_suggestion_id) as voted_suggestion_count FROM test_voting_sentences_users_opinion WHERE sentence_id = ".$sentence_id." AND is_success=1 GROUP BY voted_suggestion_id order by voted_suggestion_count DESC LIMIT 1 ";
			$result_winning_test_sentence_suggestion = $this->con->query($sql_winning_test_sentence_suggestion);			
			
			while ($row_winning_test_sentence_suggestion = $result_winning_test_sentence_suggestion->fetch_object()) {
				$winning_test_sentence_suggestion_id = $row_winning_test_sentence_suggestion->voted_suggestion_id;
			}
			
			return $winning_test_sentence_suggestion_id;
		}
		
		//Get winning test sentence suggestion's voted user's ids by winning suggestion id and sentence id
		public function getWinningTestSentenceVotedUserIds( $winning_suggestion_id, $sentence_id ) {
			
			$sqlU = 'SELECT * FROM `test_voting_sentences_users_opinion` WHERE voted_suggestion_id = ' . $winning_suggestion_id. ' AND sentence_id = ' . $sentence_id. ' AND is_success = 1' ;
			$resultU = $this->con->query($sqlU);
			
			$voted_user_ids_and_suggestion_ids_arr = array();
			while ($row_vote = $resultU->fetch_object()) {				
				$voted_user_ids_arr[] = $row_vote->user_id;
			}
			
			return $voted_user_ids_arr;
			
		}
		
		//get winning test sentence suggestion voting credits num of rows
		public function getWinningTestSentenceVotingCreditsRow( $sentence_id ) {
			
			$sqlSentenceWin = 'SELECT sentence_id FROM `winning_test_sentence_voting_credits` WHERE sentence_id = ' . $sentence_id . ' LIMIT 1';
			$resultSentenceWin = $this->con->query($sqlSentenceWin);			
			
			$sentence_win_rows = $resultSentenceWin->num_rows;

			return $sentence_win_rows;
		}
		
		//Insert into winning test sentence voting credits to the voted user ids
		public function insertIntoWinningTestSentenceVotedUserCredits($sentence_id,$suggestion_id,$user_id,$winning_credits) {
			$sql_insert = "INSERT INTO `winning_test_sentence_voting_credits` (sentence_id,suggestion_id,user_id,winning_credits) VALUES ({$sentence_id},{$suggestion_id},{$user_id},{$winning_credits}) ";			
			$this->con->query($sql_insert);
		}
		
		
		//Process winning test sentence voted user id credits by sentence id
		public function processWinningTestSentenceVotingCreditsBySentenceId( $sentence_id ) {
			
			//check if sentence has already been win record
			$sentence_win_rows = $this->getWinningTestSentenceVotingCreditsRow( $sentence_id );
			
			if( $sentence_win_rows == 0 ) {

				//Get default winning voteing credit tmn values				
				$voting_suggestion_tmn = $this->winningTestSentenceVotingTmnCreditValue();				
				
				//get correct suggestion id for the test sentence
				$correct_suggestion_id = $this->getTestSentenceCorrectSuggestionId($sentence_id);
				
				//get winning suggestion id (suggestion id which has highest votes) by sentence Id
				$winning_suggestion_id = $this->getWinningTestSentenceSuggestionIdBySentenceId($sentence_id);				
									
				//Compare winning suggestion and correct suggestion are not equal
				if( $correct_suggestion_id != $winning_suggestion_id ) {
					//other suggestion won - set is_other_suggestion_win to 1
					$is_other_suggestion_win = 1;					
				} else {
					$is_other_suggestion_win = 0;
				}
				
				$this->updateTestSentenceVotingSuggestionWonStatus($sentence_id, $is_other_suggestion_win, $winning_suggestion_id);

				//Get winning suggestion's voted user's ids by winning suggestion id
				$winning_suggestion_voted_user_ids_arr = $this->getWinningTestSentenceVotedUserIds( $winning_suggestion_id, $sentence_id );

				if (count($winning_suggestion_voted_user_ids_arr) > 0) {
					
					//Calculate per user tmn credits
					$tmn_per_user = round((float) $voting_suggestion_tmn / count($winning_suggestion_voted_user_ids_arr), 3);
					
					//Give credit to winning test sentence suggestion voted users
					foreach($winning_suggestion_voted_user_ids_arr as $winning_suggestion_voted_user_id) {
						
						$this->insertIntoWinningTestSentenceVotedUserCredits($sentence_id,$winning_suggestion_id,$winning_suggestion_voted_user_id,$tmn_per_user);
						
					}
					
				}
			
			}
			
		}
		
		//insert skipped record in to the skipped_test_sentences_voting db table
		public function saveSkippedTestSentencesVoting($skipped_sentence_id,$user_id,$skipped_reason_id) {
			$sql_insert = "INSERT INTO `skipped_test_sentences_voting` ( test_sentence_id, user_id, skipped_reason_id ) VALUES (".$skipped_sentence_id.",".$user_id.",".$skipped_reason_id.")";
			$this->con->query($sql_insert);
		}	
		
		//get user skipped test sentence voting ids
		public function getSkippedTestVotingSentenceIdsByUserID( $user_id ) {

			$current_skipped_id_sql = 'SELECT test_sentence_id FROM skipped_test_sentences_voting WHERE user_id = ' . $user_id;						
			$result_current_skipped_id = $this->con->query($current_skipped_id_sql);

			$skipped_id_arr = array( 0 );
			if ($result_current_skipped_id->num_rows > 0) {
				while ($row_current_skipped_id = $result_current_skipped_id->fetch_object()) {
					$skipped_id_arr[]=$row_current_skipped_id->test_sentence_id;
				}
			}

			$skipped_ids = implode( ",",$skipped_id_arr );
			
			return $skipped_ids;
			
		}

		//get skipped test sentence voting count by sentence id
		public function getSkippedTestSentenceVotingCountBySentenceId( $sentence_id ) {
			$sql_get_skipped_votings = "SELECT id FROM `skipped_test_sentences_voting` WHERE test_sentence_id = ". $sentence_id ;
			$result_skipped_votings = $this->con->query( $sql_get_skipped_votings );
			$skipped_voting_counts = $result_skipped_votings->num_rows;
			return $skipped_voting_counts;
		}
		
		//update test voting sentence skipped status, is_skipped - 1. It will skip sentence from user test and test sentence voting as well 
		public function updateSkippedTestSentenceStatus( $sentence_id ) {
			$sqlUpdateS = 'UPDATE `test_voting_sentences` SET is_skipped = 1 WHERE id = ' . $sentence_id.' AND is_skipped = 0';
			$this->con->query($sqlUpdateS);
		}
		
		//get correct suggestion id by sentence id
		public function getTestSentenceCorrectSuggestionId( $sentence_id ) {
			
			$sql_get_correct_suggestion_id = 'SELECT correct_suggestion_id FROM test_voting_sentences WHERE id = ' . $sentence_id;						
			$result_correct_suggestion = $this->con->query($sql_get_correct_suggestion_id);			
			
			while ($row_correct_suggestion = $result_correct_suggestion->fetch_object()) {
				$correct_suggestion_id = $row_correct_suggestion->correct_suggestion_id;
			}
			
			return $correct_suggestion_id;
			
		}

		//update other suggestion win status, is_other_suggestion_win - 1
		public function updateTestSentenceVotingSuggestionWonStatus( $sentence_id, $is_other_suggestion_win, $winning_suggestion_id ) {
			$sqlUpdateS = 'UPDATE `test_voting_sentences` SET is_other_suggestion_win = '. $is_other_suggestion_win. ', winning_suggestion_id = ' . $winning_suggestion_id . ' WHERE id = ' . $sentence_id.' AND winning_suggestion_id IS NULL ';
			$this->con->query($sqlUpdateS);
		}
		
	}
?>