<?php

	require_once('class.db.php');

	class Real_sentences_suggestions extends DB
	{	
		
		
		//get user submitted suggestions sentence ids from real_sentences_suggestions db table
		public function getUserSuccessSuggestionsSentenceIdsByUserId( $user_id ) {
			
			$current_count = 'SELECT sentence_id FROM real_sentences_suggestions WHERE user_id = ' . $user_id . ' AND is_locked = 1 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 real sentences success suggestions count for sentence 
		public function getRealSentencesSuccessSuggestionsCountBySentenceId( $sentence_id  ) {
			$sql_rs_suggestion = 'SELECT * FROM real_sentences_suggestions WHERE sentence_id = ' . $sentence_id . ' AND is_locked = 1 AND is_success = 1';
			$result_rs_suggestion = $this->con->query($sql_rs_suggestion);
			$success_suggestions_count = $result_rs_suggestion->num_rows;	
			return $success_suggestions_count;
		}
		
		//get real sentences success suggestions  
		public function getRealSentencesSuccessSuggestionsBySentenceId( $sentence_id  ) {
			$sql_rs_suggestion = 'SELECT * FROM real_sentences_suggestions WHERE sentence_id = ' . $sentence_id . ' AND is_locked = 1 AND is_success = 1 order by id LIMIT 3';
			$result_rs_suggestion = $this->con->query($sql_rs_suggestion);			
			return $result_rs_suggestion;
		}
		
		//get currently assigned real sentences suggestions sentence ids
		public function getCurrentlyAssignedRealSentencesSuggestionsSentenceIds() {
			
			$current_count = 'SELECT sentence_id FROM real_sentences_suggestions WHERE is_success IS NULL GROUP BY sentence_id HAVING count(sentence_id) >=3';
			$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;
			
		}
		
		//Code for checking if user is refreshing the page in 5 minutes 
		public function checkRealSentenceAsissngedToUserForSuggestion( $sentence_id, $user_id ) {
			$sql2 = 'SELECT * FROM real_sentences_suggestions WHERE sentence_id = ' . $sentence_id . ' AND user_id = ' . $user_id.' AND is_success IS NULL LIMIT 1 ';
			$result2 = $this->con->query($sql2);
			return $result2;
		}
		
		//Initial assign sentence for suggestion entry
		public function insertInitialRealSentenceSuggestionAndGetId($sentence_id,$user_id,$global_time_mins) {
			$sql_insert = "INSERT INTO `real_sentences_suggestions` (sentence_id,user_id,max_submit_time) VALUES (".$sentence_id.",".$user_id.",NOW() + INTERVAL {$global_time_mins} MINUTE)";						
			$insert_sentence_suggestion = $this->con->query($sql_insert);
			$last_inserted_id = $this->con->insert_id;
			return $last_inserted_id;
		}
		
		// SQL for marking the real sentence suggestion failed if time more than 5 mins of submission 
		public function makeRealSentenceSuggestionsFailedOverMaxTime() {
			$sql_rs_suggestion_Update = 'UPDATE `real_sentences_suggestions` SET is_locked = 1, is_success = 0 WHERE max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_rs_suggestion_Update);	
		}
		
		//accept the suggestion, update real sentence suggestion make lock and success suggestion 
		public function submitRealSentenceSuggestionAndUpdateToSuccess( $suggested_text, $suggestion_id ) {
			$sqlUpdateS = 'UPDATE `real_sentences_suggestions` SET is_locked = 1, is_success = 1, submitted_at = NOW(), suggested_text="'. $suggested_text .'" WHERE id = ' . $suggestion_id.' AND is_locked = 0' ;
			$resultUpdateS = $this->con->query($sqlUpdateS);
			return $resultUpdateS;
		}
		
		//while adding sentence first success suggestion, update suggestion started at date
		public function updateRealSentenceSuggestionStartedAtDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `real_sentences` SET suggestion_started_at = NOW() WHERE id = ' . $sentence_id . ' AND suggestion_started_at IS NULL ';			
			$this->con->query($sqlUpdateTs);
		}
		
		//close the self assessment test sentence for suggestion
		public function closeRealSentenceForSuggestions( $sentence_id ) {
			$sqlUpdateS = 'UPDATE `real_sentences` SET is_suggestion_completed = 1, suggestion_closed_at = NOW() WHERE id = ' . $sentence_id;
			$this->con->query($sqlUpdateS);
		}
		
		//make suggestion fail in real_sentences_suggestions db table 
		public function makeRealSentenceSuggestionFailed( $suggestion_id ) {
			$sqlUpdateSuggestion = 'UPDATE `real_sentences_suggestions` SET is_locked = 1, is_success = 0, submitted_at = NOW() WHERE id = ' . $suggestion_id ;			
			$this->con->query($sqlUpdateSuggestion);
		}
		
		//Code for fetching all the sentence ID those are having 3 success suggestions and update those sentences is suggestion completed status to 1
		public function update_all_previous_real_sentences_suggestion_completed(){

			$sql2 = 'SELECT sentence_id, count(sentence_id) FROM real_sentences_suggestions WHERE is_locked = 1 AND is_success = 1 AND sentence_id IN ( SELECT id FROM real_sentences WHERE is_suggestion_completed = 0 ) GROUP BY sentence_id HAVING count(sentence_id) >= 3';
			$result2 = $this->con->query($sql2);
			while ($row2 = $result2->fetch_object()) {
				$sentence_id2 = $row2->sentence_id;
				$sql_satsUpdate2 = 'UPDATE `real_sentences` SET is_suggestion_completed = 1, suggestion_started_at = NOW(), suggestion_closed_at = NOW() WHERE id = ' . $sentence_id2;
				$result_satsUpdate2 = $this->con->query($sql_satsUpdate2);
			}
			
		}
		
		//insert skipped sentence record in to skipped_real_sentences table
		public function saveSkippedRealSentence( $sentence_id, $user_id, $skip_sentence_during ) {			
			$sql_insert = "INSERT INTO `skipped_real_sentences` (sentence_id, user_id, skip_sentence_during ) VALUES (".$sentence_id.",".$user_id.",".$skip_sentence_during.")";			
			$result_insert = $this->con->query($sql_insert);
			return $result_insert;
		}
		
		//get user skipped sentence ids
		public function getSkippedRealSentenceIdsByUserID( $user_id ) {

			$current_skipped_id_sql = 'SELECT sentence_id FROM skipped_real_sentences 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->sentence_id;
				}
			}

			$skipped_id_arr = implode( ",",$skipped_id_arr );
			
			return $skipped_id_arr;
			
		}
		
		//get real sentence extra suggestions count 
		public function getRealSentenceExtraSuggestionsCountBySentenceId( $real_sentence_id ) {		
			$sql_get_rses = "SELECT * FROM real_sentences_extra_suggestions WHERE real_sentence_id = " . $real_sentence_id;
			$result_rses = $this->con->query( $sql_get_rses );
			$real_sentence_extra_suggestions_count = $result_rses->num_rows;
			return $real_sentence_extra_suggestions_count;
		}
		
		//Save extra suggestion
		public function saveRealSentenceExtraSuggestion($real_sentence_id,$user_id,$suggested_text) {
			$sql_insert = "INSERT INTO `real_sentences_extra_suggestions` (real_sentence_id,user_id,suggested_text) VALUES (".$real_sentence_id.",".$user_id.",'".$suggested_text."')";
			$result = $this->con->query($sql_insert);
			return $result;
		}
		
	}
?>