<?php

namespace App\Http\Controllers;

use App\Models\Term;
use App\Models\User;
use App\Models\Academy;
use App\Models\QuestionAnswer;
use App\Models\UserAttempt;
use App\Models\UserQuestionAnswers;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ApiResultController extends Controller
{
    public function getByAcademy(Academy $academy)
    {
        $latestTerm = Term::orderByDesc('id')->first();
        if (!$latestTerm) {
            return api()->error()->send();
        }

        $data = DB::select(
            "SELECT users.token, 
                -- user_attempts.start_time, 
                -- user_attempts.end_time, 
                user_attempts.academy_id, 
                CAST(SUM(question_answers.correct) as INT) as score, 
                IFNULL(RIGHT(SEC_TO_TIME(academies.duration - TIME_TO_SEC(TIMEDIFF(user_attempts.end_time, user_attempts.start_time))), 5), '-')  as time_left,
                users.award as award
            FROM users 
                JOIN user_attempts ON users.id = user_attempts.user_id 
                LEFT JOIN user_question_answers ON user_question_answers.user_id = users.id 
                LEFT JOIN question_answers ON question_answers.id = user_question_answers.question_answer_id 
                LEFT JOIN academies ON user_attempts.academy_id = academies.id 
            WHERE users.term_id = {$latestTerm->id} 
                -- AND user_attempts.end_time IS NOT NULL 
                AND user_attempts.academy_id = {$academy->id}
            GROUP BY users.id 
            ORDER BY score DESC, time_left DESC;"
        );

        return api()->success()->data($data)->send();
    }

    public function getByToken($token)
    {
        $latestTerm = Term::orderByDesc('id')->first();
        if (!$latestTerm) {
            return api()->error()->send();
        }

        $user = User::where('token', $token)->where('term_id', $latestTerm->id)->first();
        //If the user doesn't exists, of has no attempt or hasn't finished the assessmnet, break
        if (!$user || is_null($user->attempt)) { // || is_null($user->attempt->end_time)) {
            return api()->error()->send();
        }

        $academy = Academy::where('id', $user->attempt->academy_id)->first();

        $userScore = (int) QuestionAnswer::whereIn('id', UserQuestionAnswers::where('user_id', $user->id)->pluck('question_answer_id')->toArray())->sum('correct');
        //user hasn't finished, his time should be printed as '-'
        if (!$user->attempt->end_time) {
            $userTime = $academy->duration;
        } else {
            $userTime = $user->attempt->end_time->diffInSeconds($user->attempt->start_time);
        }
        $userTimeLeft = $academy->duration - $userTime;

        //The following query selects all the users that are better than the current user
        //It will always return the current user as last row in the resulting table
        //That's an easy way to calculate which place the user is at
        $data = DB::select(
            "SELECT users.token, 
                academies.name as academy_name,
                user_attempts.academy_id, 
                CAST(SUM(question_answers.correct) as INT) as score, 
                (academies.duration - TIME_TO_SEC(TIMEDIFF(user_attempts.end_time, user_attempts.start_time))) as time_left,
                users.award 
            FROM users 
                JOIN user_attempts ON users.id = user_attempts.user_id 
                JOIN user_question_answers ON user_question_answers.user_id = users.id 
                JOIN question_answers ON question_answers.id = user_question_answers.question_answer_id 
                JOIN academies ON user_attempts.academy_id = academies.id 
            WHERE users.term_id = {$latestTerm->id} 
                -- AND user_attempts.end_time IS NOT NULL 
                AND user_attempts.academy_id = {$user->attempt->academy_id}
            GROUP BY users.id 
            HAVING 
                CAST(SUM(question_answers.correct) as INT) > $userScore OR 
                (
                    CAST(SUM(question_answers.correct) as INT) = $userScore AND
                    TIME_TO_SEC(TIMEDIFF(MAX(user_attempts.end_time), MAX(user_attempts.start_time))) <= $userTime
                )
                
            ORDER BY score DESC, time_left DESC;"
        );

        $data = [
            'score' => $userScore,
            'time_left' => $userTimeLeft ?? '/',
            'place' => count($data),
            'award' => $user->award,
            'academy_id' => $academy->id
        ];
        return api()->success()->data($data)->send();
    }
}