<?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;
if (isset($_SESSION['tm_language_id']) && isset($_SESSION['tm_id'])) {
    $category_id = $_SESSION['tm_language_id'];
    $user_id = $_SESSION['tm_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 IN (' . $sentence_id . ') GROUP BY sentence_id HAVING COUNT(sentence_id) = 5 order by TotalVotes DESC LIMIT 25 OFFSET '.$offset;
    $result = $mysqli->query($sql);
 
    if ($result->num_rows > 0) {
        $data['status'] = 200;
        $data['sentence'] = array();

        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();
            $temp['suggestion'] = array();
			$suggestion1 = array();
            while ($rowSentence = $resultSentence->fetch_object()) {
                $temp['sentence_id'] = base64_encode($rowSentence->id);
				
//                $temp['original_message'] = mb_convert_encoding(stripslashes($rowSentence->original_message), "HTML-ENTITIES", "UTF-8");
                $temp['original_message'] = stripslashes($rowSentence->original_message);	
			}

            while ($rowSuggestion = $resultSuggestion->fetch_object()) {
                $suggestion = array();
                $suggestion['suggestion_id'] = base64_encode($rowSuggestion->id);
//                $suggestion['translation_message'] = mb_convert_encoding(stripslashes($rowSuggestion->suggestion), "HTML-ENTITIES", "UTF-8");
                $suggestion['translation_message'] = stripslashes($rowSuggestion->suggestion);
                array_push($temp['suggestion'], $suggestion);
            }

            array_push($data['sentence'], $temp);
        }
		/*if(!empty($data['sentence'])) {
			shuffle($data['sentence']);
		}*/
    }
}
echo json_encode($data);