<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class User extends Model
{
    use HasFactory;

    protected $guarded = [];

    protected $with = ['questionAnswers', 'questions', 'attempt'];

    public function questionAnswers()
    {
        return $this->belongsToMany(QuestionAnswer::class, 'user_question_answers')->withTimestamps();
    }

    public function attempt()
    {
        return $this->hasOne(UserAttempt::class);
    }

    public function questions()
    {
        return $this->belongsToMany(Question::class, 'user_question_answers')->withPivot('question_answer_id')->withTimestamps();
    }

    public function term()
    {
        return $this->belongsTo(Term::class);
    }

    public function hasAnsweredAllQuestions()
    {
        return $this->questions()->count() == $this->questionAnswers()->count();
    }

    public function getTokenAttribute($value)
    {
        return strtoupper($value);
    }

    // This is only the score from the thirdGame (Aritmophobia)
    public function getScoreAttribute()
    {
        $correctAnswers = $this->questionAnswers->where('correct', 1)->count();
        $totalAnswers = $this->questionAnswers()->count();
        if ($totalAnswers == 0) {
            return 0;
        }

        $userScore = $correctAnswers / $totalAnswers * 100;

        return $userScore;
    }

    // Returns the number of people in which current user belongs 
    // For example if this method returns 5 it means the user is in top 5% of all users
    // IIf it returns 20 it means the user is in top 20% (actually he is among 15% and 20% best)...
    public function getPercentileAttribute()
    {
        $latestTerm = Term::orderByDesc('id')->first();
        $totalApplicants = User::where('term_id', $latestTerm->id)->whereHas('attempt')->count();

        //Workaround for the cold-start problem
        if ($totalApplicants < 100) $totalApplicants += 100;

        //The score will contain the exact percentage of the people I belong to. For example, 12.54%.
        $percentile = (count($this->getBetterApplicants($latestTerm)) + 1) / $totalApplicants * 100;

        //We adjust this number to always be a multiplier of 5 for aesthetic reasons
        return intval($percentile / 5) * 5 + 5;
    }

    public function getBetterApplicants($term)
    {
        return  DB::select("SELECT users.id, COUNT(*) as total_answers, 
                                SUM(question_answers.correct) as correct_answers, 
                                FORMAT(SUM(question_answers.correct) / COUNT(*) * 100,2) as score 
                                FROM users 
                                    JOIN user_attempts ON users.id = user_attempts.user_id 
                                    JOIN user_question_answers ON users.id = user_question_answers.user_id 
                                    LEFT JOIN question_answers ON question_answers.id = user_question_answers.question_answer_id
                                WHERE users.term_id = {$term->id}
                                GROUP BY users.id 
                                HAVING score >= $this->score
                                ORDER BY correct_answers DESC; ");
    }

    public function getCorrectAnswersAttribute()
    {
        return $this->questionAnswers->where('correct', 1)->count();
    }


    //The final score calculated from all three games.
    public function getFinalScoreAttribute()
    {
        //Game 1
        $game1Score = $this->attempt->memory_matrix_result ?? 0;

        //Game 2
        $game2Score = $this->attempt->speed_arithmetic_result ?? 0;

        //Game 3
        $game3Score = $this->score;

        //Final
        return $game1Score + $game2Score + $game3Score;
    }
}