<?php

	require_once('class.db.php');

	class Voting_marketing extends DB {
		
		//get sentence ids which has submitted 3 success suggestions
		public function getSuccessMarketplaceSuggestionsSentenceIds() {			
			
			//optimize get success sentence ids query			
			$success_sentence_ids_sql = 'SELECT ssmm.sentence_id FROM tbl_sentence_marketing tsm INNER JOIN sentence_suggestion_marketing_map ssmm ON tsm.id = ssmm.sentence_id WHERE tsm.is_lock = 1 AND tsm.suggestion_started_pending_at IS NOT NULL AND tsm.voting_started_at IS NOT NULL AND tsm.voting_closed_at is NULL AND ssmm.is_success = 1 GROUP BY ssmm.sentence_id HAVING count(ssmm.sentence_id) >=3';
			
			$result_success_sentences_id = $this->con->query($success_sentence_ids_sql);

			$success_sentence_id_arr = array( 0 );
			if ($result_success_sentences_id->num_rows > 0) {
				while ($row_sucess_sentence_id = $result_success_sentences_id->fetch_object()) {
					$success_sentence_id_arr[]=$row_sucess_sentence_id->sentence_id;
				}
			}

			$success_ids = implode( ",",$success_sentence_id_arr );
			
			return $success_ids;
			
		}
		
		//Fetch sentence ids from tbl_sentence_marketing which are ready to start for voting 
		public function getMarketplaceSentenceIdsReadyToStartForVoting( $success_sentence_ids, $category_id ) {			
			
			$sqlOpenSentence = 'SELECT id FROM tbl_sentence_marketing WHERE is_lock = 1 AND suggestion_started_pending_at IS NOT NULL AND voting_started_at IS NOT NULL AND voting_closed_at IS NULL AND category_id='.$category_id.' AND id IN( '.$success_sentence_ids.' ) order by id';			
			$resultOpenSentence = $this->con->query($sqlOpenSentence);

			$sentence_id_arr = array(0);
			while ($row = $resultOpenSentence->fetch_object()) {
				$sentence_id_arr[]= $row->id;
			}

			$sentence_ids = implode( ",", $sentence_id_arr );
			
			return $sentence_ids;
			
		}
		
		// checking currently assigned sentence for voting 
		public function getCurrentlyAssignedMarketplaceSentenceIdsForVoting( $global_max_voting_count ) {
			
			$current_count = 'SELECT sentence_id FROM sentence_suggestion_voting_marketing_map 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 success suggested sentence ids and whose voting are not closed yet		
		public function getUserSuccessMarketplaceSuggestionsSentenceIdsByUserID( $user_id ) {			
			
			//optimize skipped voting query			
			$user_success_suggestion_sentence_ids_sql = 'SELECT ssmm.sentence_id FROM tbl_sentence_marketing tsm INNER JOIN sentence_suggestion_marketing_map ssmm ON tsm.id = ssmm.sentence_id WHERE tsm.is_lock = 1 AND tsm.suggestion_started_pending_at is NOT NULL AND tsm.voting_started_at is NOT NULL AND tsm.voting_closed_at is NULL AND ssmm.user_id = '.$user_id.' AND ssmm.is_locked=1 AND ssmm.is_success = 1';
			
			$result_user_success_suggestion_sentence_ids = $this->con->query($user_success_suggestion_sentence_ids_sql);

			$user_success_suggestion_sentence_id_arr = array( 0 );
			if ($result_user_success_suggestion_sentence_ids->num_rows > 0) {
				while ($row_user_success_suggestion_sentence_id = $result_user_success_suggestion_sentence_ids->fetch_object()) {
					$user_success_suggestion_sentence_id_arr[]=$row_user_success_suggestion_sentence_id->sentence_id;
				}
			}

			$user_success_suggestion_sentence_ids = implode( ",",$user_success_suggestion_sentence_id_arr );
			
			return $user_success_suggestion_sentence_ids;
			
		}
		
		//Pick one sentence for voting
		public function pickMarketplaceSentenceForVoting( $sentence_ids, $user_id, $not_in_id_str, $not_in_user_success_suggestion_sentence_ids ) {
			$sql = 'select id, sentence_id from sentence_suggestion_marketing_map where sentence_id IN (' . $sentence_ids . ') AND sentence_id NOT IN( select sentence_id from sentence_suggestion_voting_marketing_map where sentence_id IN (' . $sentence_ids . ') AND user_id = '.$user_id .' AND is_success = 1 ) AND sentence_id NOT IN( '.$not_in_id_str.' ) AND sentence_id NOT IN( '.$not_in_user_success_suggestion_sentence_ids.' ) group by sentence_id order by sentence_id LIMIT 1 ';			
			$result = $this->con->query($sql);
			return $result;
		}
		
		// get original message's translation suggestions by sentence id
		public function getMarketplaceTranslationSuggestionsBySentenceId( $sentence_id ) {
			$sqlSuggestion = 'SELECT * FROM `sentence_suggestion_marketing_map` WHERE sentence_id = ' . $sentence_id . ' AND is_locked = 1 AND is_success = 1 ORDER BY id LIMIT 3 ';
            $resultSuggestion = $this->con->query($sqlSuggestion);
			return $resultSuggestion;
		}
		
		//get assigned user empty vote success null entry's count
		public function get_marketplace_user_empty_vote_count_by_sentence_id_and_user_id($sentence_id, $user_id) {
			$sqlUserVotingCount = 'SELECT * FROM `sentence_suggestion_voting_marketing_map` 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_marketing_map table
		public function saveInitialMarketplaceUserVoteEntry( $sentence_id, $user_id, $global_voting_max_submit_time_mins ) {
			$sqlInsert1 = 'INSERT INTO `sentence_suggestion_voting_marketing_map`( `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 makeMarketplaceVotingFailedMaxSubmitTimeOver() {
			$sql_ssvmmUpdate = 'UPDATE `sentence_suggestion_voting_marketing_map` SET is_success = 0 where max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_ssvmmUpdate);
		}
		
		//user's success sentence voting
		public function getUserSubmittedSuccessMarketplaceVoting( $sentence_id, $user_id ) {
			$sql_success = "SELECT * FROM `sentence_suggestion_voting_marketing_map` WHERE sentence_id = " . $sentence_id . " AND user_id = " . $user_id . " AND is_success = 1";
			$result_success = $this->con->query( $sql_success );			
			return $result_success;
		}
		
		//get success voting count for sentence
		public function get_success_marketplace_voting_count_by_sentence_id($sentence_id) {
			$sqlSuccessVotingCount = 'SELECT * FROM `sentence_suggestion_voting_marketing_map` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1';
			$resultSuccessVotingCount = $this->con->query($sqlSuccessVotingCount);
			$success_voting_count = $resultSuccessVotingCount->num_rows;      
			return $success_voting_count;
		}
		
		//apply user vote. update user vote for sentence suggestion to success
		public function applySuccessUserVoteForSMarketplaceSentenceSuggestion($suggestion_id,$sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `sentence_suggestion_voting_marketing_map` SET is_success = 1, voted_at = NOW(), voted_translation_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;
		}
		
		//Close Sentence for voting in main tbl_sentence_marketing table. Voting completed for sentence suggestion
		public function closeMarketplaceVotingForSentence($sentence_id) {			
			$sqlUpdateSentence = 'UPDATE `tbl_sentence_marketing` SET voting_closed_at = NOW() WHERE id =' . $sentence_id;
			$this->con->query($sqlUpdateSentence);
		}
		
		//make user vote failed by sentence and user id
		public function makeMarketplaceUserVoteFailedBySentenceIdAndUserId($sentence_id,$user_id) {
			$sqlUpdateS = 'UPDATE `sentence_suggestion_voting_marketing_map` 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 count - sentence has reached No. of Success Voting To a Particular Suggestion 
		public function checkMarketplaceSentenceHasReachedNoOfSuccessVotesForSuggestion($sentence_id, $no_of_success_voting_to_suggestion) {
			$sql_get_success_voting_for_suggestion = "SELECT voted_translation_id FROM sentence_suggestion_voting_marketing_map WHERE sentence_id = " . $sentence_id . " AND is_success=1 GROUP BY voted_translation_id HAVING count(voted_translation_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;
		}
		
		//get marketplace success suggestion voting count by paragraph id and user id
		public function getSuccessSuggestionVotingMarketingCount( $paragraph_id, $user_id ) {
			$sql_success_suggestion_count = 'SELECT t1.id FROM tbl_sentence_marketing t1 INNER JOIN sentence_suggestion_voting_marketing_map t2 ON t1.id = t2.sentence_id WHERE t1.paragraph_id = '.$paragraph_id.' AND t2.user_id = '.$user_id.' AND t2.is_success = 1';
			$result_success_suggestion_count = $this->con->query($sql_success_suggestion_count);
			return $result_success_suggestion_count->num_rows;
		}
		
	}

?>