<?php

	require_once('class.db.php');

	class Real_sentences_voting extends DB
	{		
		
		//get user submitted success voting sentence ids 
		public function getUserSuccessVotingSentenceIdsByUserId( $user_id ) {
			
			$current_count = 'SELECT sentence_id FROM real_sentences_voting WHERE user_id = ' . $user_id . ' AND is_success = 1 ';
			$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 assigned user empty vote success null entry's count
		public function get_user_empty_vote_count_by_real_sentence_id_and_user_id( $sentence_id, $user_id ) {
			$sqlUserVotingCount = 'SELECT * FROM `real_sentences_voting` 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 real_sentences_voting table
		public function saveInitialUserRealSentenceVoteEntry( $sentence_id, $user_id, $global_voting_max_submit_time_mins ) {
			$sqlInsert1 = 'INSERT INTO `real_sentences_voting`( `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 makeRealSentenceVotingFailedMaxSubmitTimeOver() {
			$sql_ssvmUpdate = 'UPDATE `real_sentences_voting` SET is_success = 0 where max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_ssvmUpdate);
		}
		
		// checking currently assigned sentence for voting 
		public function getCurrentlyAssignedRealSentenceIdsForVoting( $global_max_voting_count ) {
			
			$current_count = 'SELECT sentence_id FROM real_sentences_voting 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 success voting count for sentence
		public function get_success_rs_voting_count_by_sentence_id( $sentence_id ) {
			$sqlSuccessVotingCount = 'SELECT * FROM `real_sentences_voting` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1';
			$resultSuccessVotingCount = $this->con->query($sqlSuccessVotingCount);
			$success_voting_count = $resultSuccessVotingCount->num_rows;      
			return $success_voting_count;
		}
		
		//get assigned user empty vote success null entry's count
		public function get_user_empty_vote_count_by_rs_sentence_id_and_user_id( $sentence_id, $user_id ) {
			$sqlUserVotingCount = 'SELECT * FROM `real_sentences_voting` 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;
		}
		
		//apply user vote. update user vote for sentence suggestion to success
		public function applySuccessUserVoteForRealSentenceSuggestion($suggestion_id,$sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `real_sentences_voting` 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;
		}
		
		//after received 1st success vote, start voting
		public function updateRealSentenceVotingStartedAtDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `real_sentences` SET voting_started_at = NOW() WHERE id = ' . $sentence_id . ' AND voting_started_at IS NULL ';			
			$this->con->query($sqlUpdateTs);
		}

		//get no. of success voting to single suggestion
		public function getNoOfSuccessVotingToSingleSuggestion() {
			$max_no_of_success_voting_for_suggestion = 5;
			return $max_no_of_success_voting_for_suggestion;
		}
		
		//get max no. of voting count
		public function getMaxNoOfVotingCount() {
			$max_no_of_voting = 11;	
			return $max_no_of_voting;
		}
		
		//get voting max submit time in minutes
		public function getVotingMaxSubmiTimeMins() {
			$voting_max_submit_time_mins = 5;
			return $voting_max_submit_time_mins;
		}
		
		//get count - sentence has reached No. of Success Voting To a Particular Suggestion 
		public function checkRealSentenceHasReachedNoOfSuccessVotesForSuggestion($sentence_id, $no_of_success_voting_to_suggestion) {
			$sql_get_success_voting_for_suggestion = "SELECT voted_suggestion_id FROM real_sentences_voting 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 real_sentences table. Voting completed for sentence suggestion
		public function closeVotingForRealSentence($sentence_id) {
			$sqlUpdateSentence = 'UPDATE `real_sentences` SET is_voting_completed=1, voting_closed_at = NOW() WHERE id =' . $sentence_id . ' AND is_voting_completed = 0 AND voting_closed_at IS NULL';
			$this->con->query($sqlUpdateSentence);
		}
		
		//make user vote failed by sentence and user id
		public function makeUserRealSentenceVoteFailedBySentenceIdAndUserId($sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `real_sentences_voting` 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 real sentences file id by sentence id
		public function getRealSentencesFileId( $sentence_id ) {
			
			$real_sentences_file_id = 0;
			
			$sql_get_rs_file_id = 'SELECT real_sentences_file_id FROM `real_sentences` WHERE id = ' . $sentence_id;
			$result_rs_file_id = $this->con->query($sql_get_rs_file_id);
			while ($row_rs_file_id = $result_rs_file_id->fetch_object()) {
				$real_sentences_file_id = $row_rs_file_id->real_sentences_file_id;
			}
			
			return $real_sentences_file_id;
			
		}
		
		//get total no. of sentences count by file id 
		public function getNoOfSentencesCountByFileId( $real_sentences_file_id ) {
			$sql_no_of_sentences_count = 'SELECT id FROM `real_sentences` WHERE real_sentences_file_id = ' . $real_sentences_file_id;
			$result_sentences_count = $this->con->query($sql_no_of_sentences_count);
			$no_of_real_sentences_count = $result_sentences_count->num_rows;     
			return $no_of_real_sentences_count;
		}
		
		//get total no. of closed sentences count by file id 
		public function getNoOfClosedSentencesCountByFileId( $real_sentences_file_id ) {
			$sql_no_of_sentences_count = 'SELECT id FROM `real_sentences` WHERE is_voting_completed = 1 AND voting_started_at IS NOT NULL AND real_sentences_file_id = ' . $real_sentences_file_id;
			$result_closed_sentences_count = $this->con->query($sql_no_of_sentences_count);
			$no_of_closed_real_sentences_count = $result_closed_sentences_count->num_rows;     
			return $no_of_closed_real_sentences_count;
		}
		
		//update real sentences file is_closed status and update closed date and make file closed 
		public function updateRealSentenceFileStatusToClose( $real_sentences_file_id ) {
			$sqlUpdateRsf = 'UPDATE `real_sentences_files` SET is_closed = 1, closed_at = NOW() WHERE id = ' . $real_sentences_file_id . ' LIMIT 1';
			$this->con->query($sqlUpdateRsf);
		}
		
		//get language direction text alignment
		public function getLanguageDirectionAlignment( $language_id ) {
			
			$sql_get_language_dir = 'SELECT language_direction FROM `languages` WHERE id = ' . $language_id;
			$result_language_dir = $this->con->query( $sql_get_language_dir );
			while ($row_language_dir = $result_language_dir->fetch_object()) {
				$language_direction = $row_language_dir->language_direction;
			}
			
			if( $language_direction == 1 ) {
				//RTL
				$language_dir_align = ' text-right lang-dir-rtl ';
			} else {
				//LTR
				$language_dir_align = ' text-left ';
			}
			
			return $language_dir_align;
			
		}
		
		//get real sentence status background color
		public function getRealSentenceStatusBgColor( $sentence_id ) {
			
			$sql_get_real_sentence = 'SELECT * FROM `real_sentences` WHERE id = ' . $sentence_id;
			$result_real_sentence = $this->con->query( $sql_get_real_sentence );
			while ($row_real_sentence = $result_real_sentence->fetch_object()) {
				$is_suggestion_completed = $row_real_sentence->is_suggestion_completed;
				$is_voting_completed = $row_real_sentence->is_voting_completed;
			}			
			
			if( $is_voting_completed == 1 ) {
				//sentence voting completed
				$rs_bg_color = 'lightgreen';
			} elseif( $is_suggestion_completed == 1 ) {
				//sentence suggestions completed
				$rs_bg_color = 'orange';
			} else {
				//default
				$rs_bg_color = '#e6e6e6';
			}
			
			return $rs_bg_color;
			
		}
		
	}
?>