<?php

	require_once('class.db.php');

	class Suggestions_marketing extends DB {
		
		//Code for checking a	
		public function getActiveSuggestionsMarketingCountBySentenceId( $sentence_id  ) {
			$sql_sentence_suggestion_marketing_map = 'SELECT * FROM sentence_suggestion_marketing_map WHERE sentence_id = ' . $sentence_id . ' AND is_locked = 0 AND is_success IS NULL';
			$result_sentence_suggestion_marketing_map = $this->con->query($sql_sentence_suggestion_marketing_map);
			$sentence_suggestion_marketing_map_count = $result_sentence_suggestion_marketing_map->num_rows;	
			return $sentence_suggestion_marketing_map_count;
		}
		
		//Code for checking if sentence is previously assigned to user 
		public function checkIsSentencePreviouslyAssignedToUser( $sentence_id, $user_id ) {
			$sql2 = ' SELECT * FROM sentence_suggestion_marketing_map WHERE sentence_id = ' . $sentence_id . ' AND user_id = ' . $user_id.' and is_success is null limit 1 ';
			$result2 = $this->con->query($sql2);
			$sentence_suggestion_marketing_map_user_count = $result2->num_rows;
			return $sentence_suggestion_marketing_map_user_count;
		}
		
		public function getLastAssignedSuggestionMarketingRowByUser( $sentence_id, $user_id ) {
			$sql2 = ' SELECT * FROM sentence_suggestion_marketing_map 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 marketing entry
		public function insertInitialSentenceSuggestionMarketingMapAndGetId($sentence_id,$user_id,$global_time_mins) {
			$sql_insert = "INSERT INTO sentence_suggestion_marketing_map (sentence_id,user_id,max_submit_time) VALUES (".$sentence_id.",".$user_id.",NOW() + INTERVAL {$global_time_mins} MINUTE)";
			$insert_sentence_map = $this->con->query($sql_insert);
			$last_inserted_id = $this->con->insert_id;
			return $last_inserted_id;
		}
		
		//get success suggested records
		public function getSuccessSuggestedMarketingBySentenceId( $sentence_id ) {
			$sqlSuggestionSuccessCount = 'SELECT * FROM `sentence_suggestion_marketing_map` WHERE sentence_id = ' . $sentence_id . ' AND is_success = 1';
			$resultSuggestionSuccessCount = $this->con->query($sqlSuggestionSuccessCount);        
			return $resultSuggestionSuccessCount;
		}
		
		//accept the suggestion, update sentence suggestion marketing map make lock and success suggestion 
		public function submitSuggestionMarketingAndUpdateToSuccess( $suggested_text, $map_id ) {
			$sqlUpdateS = 'UPDATE `sentence_suggestion_marketing_map` SET is_locked = 1, is_success = 1, submitted_at = NOW(), suggested_text="'. $suggested_text .'" where id = ' . $map_id.' and is_locked = 0' ;			
			$resultUpdateS = $this->con->query($sqlUpdateS);
			return $resultUpdateS;
		}
		
		//make suggestion fail in sentence suggestion marketing map
		public function makeSentenceSuggestionMarketingFailed( $suggestion_marketing_map_id ) {
			$sqlUpdateSsm = 'UPDATE `sentence_suggestion_marketing_map` SET is_locked = 1, is_success = 0, submitted_at = NOW() where id = ' . $suggestion_marketing_map_id ;			
			$this->con->query($sqlUpdateSsm);
		}
		
		//get user's success sentence suggestions by sentence id
		public function getUserSubmittedSuccessMarketplaceSuggestiongs( $sentence_id, $user_id ) {
			$sql_success = "SELECT * FROM `sentence_suggestion_marketing_map` WHERE sentence_id = " . $sentence_id . " AND user_id = " . $user_id . " AND is_locked = 1 AND is_success = 1";
			$result_success = $this->con->query( $sql_success );			
			return $result_success;	
		}

		//get Sentence marketing rows by paragraph id
		public function getSentenceMarketingRowsById( $id ) {
			$sql_sentence_marketing1 = 'SELECT * FROM `tbl_sentence_marketing` WHERE `id` = ' . $id;
			$result_sentence_marketing1 = $this->con->query( $sql_sentence_marketing1 );
			return $result_sentence_marketing1;
		}
		
		//get Sentence marketing rows by paragraph id
		public function getSentenceMarketingRowsByParagrphId( $paragraph_id ) {
			$sql_sentence_marketing = 'SELECT * FROM `tbl_sentence_marketing` WHERE `paragraph_id` = ' . $paragraph_id . ' ORDER BY sequence';
			$result_sentence_marketing = $this->con->query( $sql_sentence_marketing );
			return $result_sentence_marketing;
		}
		
		//get success suggestion count by paragraph id and user id
		public function getSuccessSuggestionMarketingCount( $paragraph_id, $user_id ) {
			$sql_success_suggestion_count = 'SELECT t1.id FROM tbl_sentence_marketing t1 INNER JOIN sentence_suggestion_marketing_map t2 ON t1.id = t2.sentence_id WHERE t1.paragraph_id = '.$paragraph_id.' AND t2.user_id = '.$user_id.' AND t2.is_locked = 1 AND t2.is_success = 1';
			$result_success_suggestion_count = $this->con->query($sql_success_suggestion_count);
			return $result_success_suggestion_count->num_rows;	
		}
		
		//fetching the marketplace sentence for suggestion	
		public function getMarketplaceSentenceForSuggestion( $user_id, $category_id, $not_in_id_str ){
			$sql = 'SELECT * FROM tbl_sentence_marketing WHERE is_lock = 0 AND category_id='.$category_id.' AND id >= 2075 
			AND id NOT IN ( select sentence_id from sentence_suggestion_marketing_map WHERE  is_locked = 1 AND is_success = 1 and user_id = "'.$user_id.'" ) 
			AND id NOT IN( '.$not_in_id_str.' ) ORDER BY id LIMIT 1 ';			
			$result = $this->con->query($sql);			
			return $result;
		}
		
		//checking currently suggested sentence 
		public function getCurrentlyAssignedMarketplaceSuggestionsSentenceIds() {
			
			$current_count = 'select sentence_id from sentence_suggestion_marketing_map 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;
			
		}
		
		// SQL for marking the marketplace suggestion sentence failed if time more than 5 mins of submission STARTS
		public function makeMarketplaceSuggestionsFailedOverMaxTime() {
			$sql_ssmUpdate = 'UPDATE `sentence_suggestion_marketing_map` SET is_locked = 1, is_success = 0 where max_submit_time < NOW() AND is_success IS NULL';
			$this->con->query($sql_ssmUpdate);	
		}
		
		//Code for fetching all the sentence ID those are having 
		public function marketplace_update_all_previous_non_submited_to_sucess0(){

			$sql2 = 'select sentence_id, count(sentence_id) from sentence_suggestion_marketing_map WHERE is_locked = 1 AND is_success = 1 AND sentence_id IN ( SELECT id FROM tbl_sentence_marketing where is_lock = 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_ssmUpdate2 = 'UPDATE `tbl_sentence_marketing` SET is_lock = 1 , locked_at=now() where id = ' . $sentence_id2;
				$result_ssmUpdate2 = $this->con->query($sql_ssmUpdate2);	
			}
			
		}

		//get next sentence 
		public function getNextMarketplaceSentenceForTranslation() {
			$sql_next = 'SELECT * FROM tbl_sentence_marketing WHERE is_lock = 0 AND id >= 2075  AND id NOT IN ( select sentence_id from sentence_suggestion_marketing_map) ORDER BY id LIMIT 1 ';	
			$result_next = $this->con->query($sql_next);
			return $result_next;
		}
		
		//while adding sentence first success suggestion, update suggestion pending at date
		public function updateMarketplaceSentencePendingAtDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `tbl_sentence_marketing` SET suggestion_started_pending_at = NOW() where id = ' . $sentence_id . ' AND suggestion_started_pending_at IS NULL ';			
			$this->con->query($sqlUpdateTs);
		}
		
		//lock the sentence in main tbl_sentence_marketing table 
		public function lockMarketplaceSentenceForSuggestions( $sentence_id ) {
			$sqlUpdateS = 'UPDATE `tbl_sentence_marketing` SET is_lock = 1, locked_at = NOW() where id = ' . $sentence_id;
			$this->con->query($sqlUpdateS);
		}
		
		//after received 3 success suggestions, start voting
		public function updateMarketplaceSentenceVotingStartedAtDate( $sentence_id ) {
			$sqlUpdateTs = 'UPDATE `tbl_sentence_marketing` SET voting_started_at = NOW() where id = ' . $sentence_id . ' AND voting_started_at IS NULL';
			$this->con->query($sqlUpdateTs);
		}
		
		//get document title by [tbl_sentence_marketing] paragraph_id or [tbl_paragraphs] id
		public function getDocumentTitleByParagraphId($paragraph_id) {
			$sql_get_docs_title = 'SELECT title from docs WHERE id = (SELECT tpo.doc_id FROM `tbl_paragraphs` tp INNER JOIN tbl_paragraphs_original tpo ON tp.main_paragraph_id = tpo.id WHERE tp.id = '.$paragraph_id.')';
			$result_get_docs_title = $this->con->query($sql_get_docs_title);
			$row_get_docs_title = $result_get_docs_title->fetch_object();
			$document_title = $row_get_docs_title->title;
			return $document_title;
		}
		
	}

?>