<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

include 'connection.php';
if (isset($_POST['sid']) && isset($_POST['suid']) && isset($_SESSION['tm_id'])) {
    $sentence_id = base64_decode($_POST['sid']);
    $suggestion_id = base64_decode($_POST['suid']);
    $user_id = $_SESSION['tm_id'];
    $language_id = $_SESSION['tm_language_id'];	
	
	if (is_numeric($sentence_id) && is_numeric($suggestion_id)) {
		
		// Get tms values from settings table
		$daily_vote_tmn_limit = 0;
		$tmn_per_vote = $tmn_credits = $voters_tmn_for_winning_suggestion = $winning_suggestion_tmn = 0;

		$sqlSettings = 'SELECT * FROM `settings` WHERE 1 ';
		$resultSettings = $mysqli->query($sqlSettings);
		if($resultSettings->num_rows > 0) {
			while ($row = $resultSettings->fetch_object()) {
				$daily_vote_tmn_limit = $row->daily_vote_tmn_limit;
				$tmn_per_vote = $row->tmn_per_vote;
				$voters_tmn_for_winning_suggestion = $row->voting_suggestion_tmn;
				$winning_suggestion_tmn = (int) $row->winning_suggestion_tmn;
			}
		}
		
		$sqlSuggestion = 'SELECT * FROM tbl_user_suggestion_mapping where sentence_id = (select sentence_id from tbl_user_suggestion_mapping where id = ' . $suggestion_id . ') and (FIND_IN_SET(' . $user_id . ',voted_user_id))';
		$resultSuggestion = $mysqli->query($sqlSuggestion);
		$sqlSuggestionCount = 'select SUM(IFNULL((CHAR_LENGTH(voted_user_id) - CHAR_LENGTH(REPLACE(voted_user_id, ",", ""))),0))  as TotalVotes from tbl_user_suggestion_mapping where sentence_id = "' . $sentence_id . '"';
	   
		$resultSuggestionCount = $mysqli->query($sqlSuggestionCount);
		
		if ($resultSuggestion->num_rows == 0) {
			$sentenceTotalVotes = $resultSuggestionCount->fetch_object()->TotalVotes;
			$new_isert = 0;
			if ($sentenceTotalVotes < 7) {
				
				// Append user_id to voted_user_id
				$user_idT = $user_id . ',';
				$sqlInsert = 'UPDATE `tbl_user_suggestion_mapping` SET voted_user_id = concat(ifnull(voted_user_id,""), "' . $user_idT . '") where id = ' . $suggestion_id;
				$resultInsert = $mysqli->query($sqlInsert);
				
				// Insert user vote into tbl_user_votes_tmn table				
				$sqlInsert1 = 'INSERT INTO `tbl_user_votes_tmn`(`user_id`, `suggestion_id`, `credits`, `created_at`) VALUES '
						. '(' . $user_id . ',' . $suggestion_id . ',' . $tmn_credits . ',"' . date('Y-m-d H:i:s') . '")';
				$resultInsert1 = $mysqli->query($sqlInsert1);
				
				// Increment users total votes
				$sqlUpdateUS = 'UPDATE `tbl_translators` SET voting_sentences = concat(ifnull(voting_sentences,""), "' . $sentence_id."," . '"), total_votes = total_votes + 1 where id = ' . $user_id;
                $resultUpdateUS = $mysqli->query($sqlUpdateUS);
				
				
				// Mark as vote inserted
				$new_isert = 1;
				$sentenceTotalVotes++;
				$data['status'] = 200;
				$data['msg'] = "Vote submitted successfully.";
			} 
			if ($sentenceTotalVotes >= 7) {
				
				// Check if sentence is open or closed
				$sqlSId = 'SELECT is_open_for_voting FROM `tbl_sentence` WHERE id = "'.$sentence_id.'"';
				$resultSId = $mysqli->query($sqlSId);
				$is_open = 0;
				while ($rowSId = $resultSId->fetch_object()) {
					$is_open = $rowSId->is_open_for_voting;
				}
				if($is_open == 1) {
					
					// Insert vote if not inserted
					if($new_isert == 0) {
						// Append user_id to voted_user_id
						$user_idT = $user_id . ',';
						$sqlInsert = 'UPDATE `tbl_user_suggestion_mapping` SET voted_user_id = concat(ifnull(voted_user_id,""), "' . $user_idT . '") where id = ' . $suggestion_id;
						$resultInsert = $mysqli->query($sqlInsert);

						// Insert user vote into tbl_user_votes_tmn table	
						$sqlInsert1 = 'INSERT INTO `tbl_user_votes_tmn`(`user_id`, `suggestion_id`, `credits`, `created_at`) VALUES '
								. '(' . $user_id . ',' . $suggestion_id . ',' . $tmn_credits . ',"' . date('Y-m-d H:i:s') . '")';
						$resultInsert1 = $mysqli->query($sqlInsert1);
						
						// Increment users total votes
						$sqlUpdateUS = 'UPDATE `tbl_translators` SET voting_sentences = concat(ifnull(voting_sentences,""), "' . $sentence_id."," . '"), total_votes = total_votes + 1 where id = ' . $user_id;
						$resultUpdateUS = $mysqli->query($sqlUpdateUS);
				
						$sentenceTotalVotes++;
						$data['status'] = 200;
						$data['msg'] = "Vote submitted successfully.";
					}
					
					
					// Count total users voted for the sentence
					$suggestionTotalUsers = 0;
					$sqlWinning1 = 'SELECT SUM(IFNULL((CHAR_LENGTH(voted_user_id) - CHAR_LENGTH(REPLACE(voted_user_id, ",", ""))),0)) as TotalUsers FROM `tbl_user_suggestion_mapping` WHERE sentence_id = "' . $sentence_id . '" AND id = "'.$suggestion_id.'"';
					$resultWinning1 = $mysqli->query($sqlWinning1);
					if($resultWinning1->num_rows > 0) {
						$suggestionTotalUsers = $resultWinning1->fetch_object()->TotalUsers;					
					} 
										
					if($suggestionTotalUsers >= 7 || $sentenceTotalVotes >= 21) {	
						if (IsSentenceNowClosedForVoting($mysqli, $sentence_id)) {
							
							$sqlWinningSentence = 'SELECT id, user_id, voted_user_id, SUM(IFNULL((CHAR_LENGTH(voted_user_id) - CHAR_LENGTH(REPLACE(voted_user_id, ",", ""))),0)) as TotalVotes  FROM `tbl_user_suggestion_mapping` WHERE sentence_id = ' . $sentence_id . ' group by id order by TotalVotes DESC LIMIT 1 OFFSET 0 ';
							$resultWinningSentence = $mysqli->query($sqlWinningSentence);

							while ($rowWinningSentence = $resultWinningSentence->fetch_object()) {
								$voted_user_id = explode(',', rtrim($rowWinningSentence->voted_user_id, ','));
								$winning_suggestion_id = $rowWinningSentence->id;
								$winning_user_id = $rowWinningSentence->user_id;

								// Close Sentence for voting
								$sqlUpdateSentence = 'UPDATE `tbl_sentence` SET is_open_for_voting = 0 WHERE id =' . $sentence_id;
								$resultUpdateSentence = $mysqli->query($sqlUpdateSentence);
								
								// Increment closed suggestion count
								$sqlUpdateCS = 'UPDATE `tbl_translators` SET closed_suggestions = closed_suggestions + 1 WHERE id IN (SELECT user_id FROM tbl_user_suggestion_mapping WHERE sentence_id  = "' . $sentence_id . '" AND user_id != 0)';
								$resultUpdateCS = $mysqli->query($sqlUpdateCS);
								
								// Give TMN credits to the user whose suggestion has won
								$sqlUpdateSuggestion = 'UPDATE `tbl_user_suggestion_mapping` SET is_winning = 1, `winning_credits`=' . $winning_suggestion_tmn . ', `updated_at`="' . date('Y-m-d H:i:s') . '" WHERE id =' . $winning_suggestion_id;
								$resultUpdateSuggestion = $mysqli->query($sqlUpdateSuggestion);
																
								// Increment users winning_suggestions and add Winning TMSs to tmn_credits
								$sqlUpdateWinningSuggestionUser = 'UPDATE `tbl_translators` SET winning_suggestions = winning_suggestions + 1, tmn_credits = tmn_credits + ' . $winning_suggestion_tmn . ' where id = ' . $winning_user_id;
								$resultUpdateWinningSuggestionUser = $mysqli->query($sqlUpdateWinningSuggestionUser);

								// Give TMN credits to all those users who have voted for this suggestion								
								$voters_tmn_for_winning_suggestion;
								$tmn_per_user = 0;
								if (count($voted_user_id) > 0) {
									$tmn_per_user = round((float) $voters_tmn_for_winning_suggestion / count($voted_user_id), 3);
								}
								
								for ($i = 0; $i < sizeof($voted_user_id); $i++) {
									
									// Add wining TMNs to all users who have voted the winning suggesion 
									$sqlUpdate1 = 'UPDATE `tbl_user_votes_tmn` SET `winning_credits`=' . $tmn_per_user . ', `updated_at`="' . date('Y-m-d H:i:s') . '" WHERE suggestion_id =' . $winning_suggestion_id . ' and user_id = ' . $voted_user_id[$i];
									$resultUpdate1 = $mysqli->query($sqlUpdate1);

									// Increment users winning_votes and add Winning TMSs to tmn_credits
									$sqlUpdateUser = 'UPDATE `tbl_translators` SET winning_votes = winning_votes + 1, tmn_credits = tmn_credits + ' . $tmn_per_user . ' where id = ' . $voted_user_id[$i];
									$resultUpdateUser = $mysqli->query($sqlUpdateUser);
								}
								
								// Increment closed votes count								
								$sqlUpdateCS1 = 'SELECT GROUP_CONCAT( voted_user_id SEPARATOR "") as user_ids FROM `tbl_user_suggestion_mapping` where sentence_id = '.$sentence_id.'';
								$resultUpdateCS1 = $mysqli->query($sqlUpdateCS1);
								$cv_user_ids = '';
								if($resultUpdateCS1->num_rows > 0 ) {
									while ($rowCS1 = $resultUpdateCS1->fetch_object()) {
										$cv_user_ids = $rowCS1->user_ids;
									}
								}
								if( $cv_user_ids != '') {
									// Increment closed_votes for each users who have voted the winning suggesion 
									$cv_user_ids = rtrim($cv_user_ids, ',');
									$sqlUpdateCS = 'UPDATE `tbl_translators` SET closed_votes = closed_votes + 1 WHERE id IN ('.$cv_user_ids.')';
									$resultUpdateCS = $mysqli->query($sqlUpdateCS);
								}
								
								// insert winning sentence table record into clone tableINSERT INTO tbl_winning_sentence
								$sqlDuplicateSentence = 'INSERT INTO tbl_winning_sentence
								SELECT * FROM tbl_sentence
								WHERE id = "'.$sentence_id.'"';
								$resultDuplicateSentence = $mysqli->query($sqlDuplicateSentence);
								
								// insert winning sentence suggessions table records into clone tableINSERT INTO tbl_winning_sentence
								$sqlDuplicateSuggession = 'INSERT INTO tbl_user_winning_suggestion_mapping
								SELECT * FROM tbl_user_suggestion_mapping
								WHERE sentence_id = "'.$sentence_id.'"';
								$resultDuplicateSuggession = $mysqli->query($sqlDuplicateSuggession);
								
								// UnLock the sentence for other users
								$sqlUpdateS = 'UPDATE `tbl_sentence` SET is_lock = 0, locked_at = "" where id = ' . $sentence_id;
								$resultUpdateS = $mysqli->query($sqlUpdateS);

							}
							$data['status'] = 200;
							$data['msg'] = "Vote submitted successfully.";
						}
					}
				} else {
					$data['status'] = 300;
					$data['msg'] = "This sentence has already reached 21 votes.<br> Please skip this and contribute on a new one.";
				}  
			}  
		} else {
			$data['status'] = 300;
			$data['msg'] = "You have already submitted vote for this suggestion.";
		}
	} else {
		$data['status'] = 300;
		$data['msg'] = "Something went wrong, Please try again.";
	}
} else {
	$data['status'] = 300;
	$data['msg'] = "Something went wrong, Please try again.";
}
 echo json_encode($data);
function IsSentenceNowClosedForVoting($mysqli, $sentence_id) {
    $sqlWinningSentence = 'SELECT SUM(IFNULL((CHAR_LENGTH(voted_user_id) - CHAR_LENGTH(REPLACE(voted_user_id, ",", ""))),0)) as TotalVotes  FROM `tbl_user_suggestion_mapping` WHERE sentence_id = ' . $sentence_id . ' group by id order by TotalVotes DESC ';
    $resultWinningSentence = $mysqli->query($sqlWinningSentence);
    $suggestions_with_same_max_count = 0;
    $max_vote_per_suggestion = 0;
    while ($row = $resultWinningSentence->fetch_object()) {
        if ($row->TotalVotes > $max_vote_per_suggestion) {
            $max_vote_per_suggestion = $row->TotalVotes;
        } else if ($row->TotalVotes == $max_vote_per_suggestion) {
            $suggestions_with_same_max_count++;
        }
    }

    return $suggestions_with_same_max_count > 0 ? FALSE : TRUE;
}