<?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';

$data = array();
$data['status'] = 300;
$offset = 0;
$user_id = $_SESSION['tm_id'];
if(isset($_POST['skip_id']) && $_POST['skip_id'] != '0') {
	// Lock the sentence for other users
	$sqlUpdate = 'UPDATE `tbl_sentence` SET is_lock = 0, locked_at = "" where id = "' . base64_decode($_POST['skip_id']) .'"';
	$resultUpdate = $mysqli->query($sqlUpdate);
	
	$user_idT = base64_decode($_POST['skip_id']) .",";
	// Increment users total votes
	$sqlUpdateUS = 'UPDATE `tbl_translators` SET skip_voting_sentences = concat(ifnull(skip_voting_sentences,""), "' . $user_idT . '") where id = ' . $user_id;
	$resultUpdateUS = $mysqli->query($sqlUpdateUS);
}
if (isset($_SESSION['tm_language_id']) && isset($_SESSION['tm_id'])) {
    $category_id = $_SESSION['tm_language_id'];
    
	if(isset($_POST['offset']))
		$offset = $_POST['offset'];
    $sentence_id = '';

    $sqlOpenSentence = 'select id from tbl_sentence where is_open_for_voting = 1 and category_id = ' . $category_id;
    $resultOpenSentence = $mysqli->query($sqlOpenSentence);

    while ($row = $resultOpenSentence->fetch_object()) {
        $sentence_id .= $row->id . ',';
    }

    $sentence_id = rtrim($sentence_id, ',');

   $sql = 'select id, sentence_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 NOT IN (select sentence_id from tbl_user_suggestion_mapping where (user_id = "' . $user_id . '" or FIND_IN_SET("' . $user_id . '",voted_user_id)) and category_id = ' . $category_id . ') AND sentence_id NOT IN (select skip_voting_sentences from tbl_translators where id = ' . $user_id . ') and sentence_id IN (' . $sentence_id . ') GROUP BY sentence_id HAVING COUNT(sentence_id) = 5 order by TotalVotes DESC LIMIT 1 OFFSET '.$offset;
	
    $result = $mysqli->query($sql);
 
    if ($result->num_rows > 0) {
        $data['status'] = 200;

        while ($row = $result->fetch_object()) {

            $sqlSentence = 'SELECT * FROM `tbl_sentence` WHERE id = ' . $row->sentence_id;
            $resultSentence = $mysqli->query($sqlSentence);

            $sqlSuggestion = 'SELECT * FROM `tbl_user_suggestion_mapping` WHERE sentence_id = ' . $row->sentence_id . ' and user_id != "' . $user_id .'" ORDER BY user_id';
            $resultSuggestion = $mysqli->query($sqlSuggestion);

            $temp = array();
            $data['suggestion'] = array();
			$suggestion1 = array();
            while ($rowSentence = $resultSentence->fetch_object()) {
				// Lock the sentence for other users
				$sqlUpdate = 'UPDATE `tbl_sentence` SET is_lock = 1, locked_at = NOW() where id = ' . $rowSentence->id;
				$resultUpdate = $mysqli->query($sqlUpdate);
			
                $data['id'] = base64_encode($rowSentence->id);
                $data['original_message'] = stripslashes($rowSentence->original_message);	
			}

            while ($rowSuggestion = $resultSuggestion->fetch_object()) {
                $suggestion = array();
                $suggestion['suggestion_id'] = base64_encode($rowSuggestion->id);
                $suggestion['translation_message'] = stripslashes($rowSuggestion->suggestion);
                array_push($data['suggestion'], $suggestion);
            }
        }
    }
}
echo json_encode($data);