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

pflog( "=========================================================================== paragraph id ====".$_POST['paragraph_id']."==== user id =====".$_SESSION['tm_id']);
pflog( "Response:\n". print_r( $_POST, true ) );
if (isset($_SESSION['tm_id'])) {
	if (isset($_POST['specials'])) {	
		// 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;
        $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;
			}
		}
		$specialsArray = json_decode($_POST['specials']);
		if(!empty($specialsArray)) {
			foreach($specialsArray as $key=>$value) {
			    
				$sentence_id = $key;
				$suggestion_id = base64_decode($value);
				$user_id = $_SESSION['tm_id'];
				$sqlS11 = 'SELECT * FROM `tbl_user_votes_marketing_tmn` WHERE user_id = "'.$user_id.'" AND suggestion_id = "'.$suggestion_id.'"';
				$resultS11 = $mysqli->query($sqlS11);
				if($resultS11->num_rows == 0){
    				$language_id = $_SESSION['tm_language_id'];		
    				
    				$sqlSuggestionCount = 'select SUM(IFNULL((CHAR_LENGTH(voted_user_id) - CHAR_LENGTH(REPLACE(voted_user_id, ",", ""))),0))  as TotalVotes from tbl_user_suggestion_marketing_mapping where sentence_id = "' . $sentence_id . '"';
    			   
    				$resultSuggestionCount = $mysqli->query($sqlSuggestionCount);
    				
    				$sentenceTotalVotes = $resultSuggestionCount->fetch_object()->TotalVotes;
    				$new_isert = 0;
    				if ($sentenceTotalVotes < 5) {
    					
    					// Append user_id to voted_user_id
    					$user_idT = $user_id . ',';
    					$sqlInsert = 'UPDATE `tbl_user_suggestion_marketing_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_marketing_tmn table				
    					$sqlInsert1 = 'INSERT INTO `tbl_user_votes_marketing_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);
    					pflog( $sqlInsert1 );
    					// Increment users total votes
    					$sqlUpdateUS = 'UPDATE `tbl_translators` SET total_votes_marketing = total_votes_marketing + 1 where id = ' . $user_id;
    					$resultUpdateUS = $mysqli->query($sqlUpdateUS);
    					
    					
    					// Mark as vote inserted
    					$new_isert = 1;
    					$sentenceTotalVotes++;
    					$data['status'] = 200;
    					$data['sentence_id'] = $sentence_id;
    					$data['msg'] = "Vote submitted successfully.";
    				} 
    				if ($sentenceTotalVotes >= 5) {
    					
    					// Check if sentence is open or closed
    					$sqlSId = 'SELECT is_open_for_voting FROM `tbl_sentence_marketing` 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_marketing_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_marketing_tmn table	
    							$sqlInsert1 = 'INSERT INTO `tbl_user_votes_marketing_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 total_votes_marketing = total_votes_marketing + 1 where id = ' . $user_id;
    							$resultUpdateUS = $mysqli->query($sqlUpdateUS);
    					
    							$sentenceTotalVotes++;
    							$data['status'] = 200;
    							$data['sentence_id'] = $sentence_id;
    							$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_marketing_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 >= 5 || $sentenceTotalVotes >= 11) {	
    							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_marketing_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, ','));
    									$comma_voted_user_id = rtrim($rowWinningSentence->voted_user_id, ',');
    									$winning_suggestion_id = $rowWinningSentence->id;
    									$winning_user_id = $rowWinningSentence->user_id;
    
    									// Close Sentence for voting
    									$sqlUpdateSentence = 'UPDATE `tbl_sentence_marketing` SET is_open_for_voting = 0 WHERE id =' . $sentence_id;
    									$resultUpdateSentence = $mysqli->query($sqlUpdateSentence);
    									
    									// Increment closed suggestion count
    									$sqlS11 = 'SELECT user_id FROM tbl_user_suggestion_marketing_mapping WHERE sentence_id  = "' . $sentence_id . '" AND user_id != 0';
                                		$resultS11 = $mysqli->query($sqlS11);
                                		if($resultS11->num_rows > 0) {
                                			while ($rowS11 = $resultS11->fetch_object()) {
                                			    $tuser_id = $rowS11->user_id;
                                			    $sqlUpdateCS = 'UPDATE `tbl_translators` SET closed_suggestions_marketing = closed_suggestions_marketing + 1 WHERE id = "'.$tuser_id.'"';
        								        $resultUpdateCS = $mysqli->query($sqlUpdateCS);
                                			}
        		                        }
    									
    									/*$sqlUpdateCS = 'UPDATE `tbl_translators` SET closed_suggestions_marketing = closed_suggestions_marketing + 1 WHERE id IN ()';
    									$resultUpdateCS = $mysqli->query($sqlUpdateCS);*/
    									
    									// Give TMN credits to the user whose suggestion has won
    									$sqlUpdateSuggestion = 'UPDATE `tbl_user_suggestion_marketing_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_marketing and add Winning TMSs to tmn_credits
    									$sqlUpdateWinningSuggestionUser = 'UPDATE `tbl_translators` SET winning_suggestions_marketing = winning_suggestions_marketing + 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
    									$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);
    									}
    									
										// Add wining TMNs to all users who have voted the winning suggesion 
										$sqlUpdate1 = 'UPDATE `tbl_user_votes_marketing_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 IN (' . $comma_voted_user_id . ')';
										$resultUpdate1 = $mysqli->query($sqlUpdate1);

										// Increment users winning_votes_marketing and add Winning TMSs to tmn_credits
										$sqlUpdateUser = 'UPDATE `tbl_translators` SET winning_votes_marketing = winning_votes_marketing + 1, tmn_credits = tmn_credits + ' . $tmn_per_user . ' where id IN (' . $comma_voted_user_id . ')';
										$resultUpdateUser = $mysqli->query($sqlUpdateUser);
									
    									// Increment closed votes count								
    									$sqlUpdateCS1 = 'SELECT GROUP_CONCAT( voted_user_id SEPARATOR "") as user_ids FROM `tbl_user_suggestion_marketing_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_marketing for each users who have voted the winning suggesion 
    										$cv_user_ids = rtrim($cv_user_ids, ',');
    										$sqlUpdateCS = 'UPDATE `tbl_translators` SET closed_votes_marketing = closed_votes_marketing + 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_marketing
    									SELECT * FROM tbl_sentence_marketing
    									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_marketing
    									SELECT * FROM tbl_user_suggestion_marketing_mapping
    									WHERE sentence_id = "'.$sentence_id.'"';
    									$resultDuplicateSuggession = $mysqli->query($sqlDuplicateSuggession);	
    									
    									// Increment paragraph closed sentences count
    									$sqlUpdateCP = 'SELECT sentences, sentences_closed, main_paragraph_id FROM `tbl_paragraphs` where id = "' . $_POST['paragraph_id'].'"';
    									$resultUpdateCP = $mysqli->query($sqlUpdateCP);
    									$pStatus = '';
    									if($resultUpdateCP->num_rows > 0 ) {
    										while ($rowCP = $resultUpdateCP->fetch_object()) {
    											$sentences = $rowCP->sentences;
    											$sentences_closed = $rowCP->sentences_closed;
    											if(intval($sentences_closed) + 1 == intval($sentences)) {
    												$pStatus = ', status = 1';
    												
    												// Increment original paragraph closed subparagraph count
    												$sqlUpdateCM = 'SELECT paragraphs, paragraphs_closed FROM `tbl_paragraphs_original` where id = "' . $rowCP->main_paragraph_id . '"';
    												$resultUpdateCM = $mysqli->query($sqlUpdateCM);
    												$pmStatus = '';
    												if($resultUpdateCM->num_rows > 0 ) {
    													while ($rowCM = $resultUpdateCM->fetch_object()) {
    														$paragraphs = $rowCM->paragraphs;
    														$paragraphs_closed = $rowCM->paragraphs_closed;
    														if(intval($paragraphs_closed) + 1 == intval($paragraphs)) {
    															$pmStatus = ', status = 1';
    														}
    													}
    												}
    												$sqlUpdateSM = 'UPDATE `tbl_paragraphs_original` SET paragraphs_closed = paragraphs_closed + 1 '.$pmStatus.' where id = "' . $rowCP->main_paragraph_id .'"';
    												$resultUpdateSM = $mysqli->query($sqlUpdateSM);
    											}
    										}
    									}
    									$sqlUpdateSP = 'UPDATE `tbl_paragraphs` SET sentences_closed = sentences_closed + 1 '.$pStatus.' where id = "' . $_POST['paragraph_id'].'"';
    									$resultUpdateSP = $mysqli->query($sqlUpdateSP);								
    								}							
    							}
    						}
    					} 
    				} 
			
			    }
			}
			// UnLock the paragraph for other users
			$sqlUpdateS = 'UPDATE `tbl_paragraphs` SET is_lock = 0, locked_user_id = 0, locked_at = "" where id = "' . $_POST['paragraph_id'].'"';
			$resultUpdateS = $mysqli->query($sqlUpdateS);
			$data['status'] = 200;
			$data['msg'] = "Votes submitted successfully.";
		} else {
			$data['status'] = 300;
			$data['msg'] = "Something went wrong, Please try again.";
		}
	} else {
		$data['status'] = 300;
		$data['msg'] = "Something went wrong, Please try again.";
	}
} else {
    $data['status'] = 700;
}
 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_marketing_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;
}
function pflog( $msg = '', $close = false ) {
    static $fh = 0;
    global $module;

    // Only log if debugging is enabled
    if( true )
    {
        if( $close )
        {
            fclose( $fh );
        }
        else
        {
            // If file doesn't exist, create it
            if( !$fh )
            {
                $pathinfo = pathinfo( __FILE__ );
                $fh = fopen( $pathinfo['dirname'] .'/market_logs.log', 'a+' );
            }

            // If file was successfully created
            if( $fh )
            {
                $line = date( 'Y-m-d H:i:s' ) .' : '. $msg ."\n";

                fwrite( $fh, $line );
            }
        }
    }
}